Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Copy a Formula down with a Twist Q
I would appreciate any heply in solving what seems a simple problem.
I wish to copy down a formula in Cell AA1, that contains a reference to B1 down to AA6, but instead of it showing B6 I want it to show B7. I have an extended formula in AA1 but only the reference to B1 is the critical part as I just want to simplify my post. I scanned the Groups for an answer and I'm getting more confused. I've to copy this formula 400 times so that why I don't want to do it manually! Thanks |
#2
|
|||
|
|||
Hi
in what other cells do you want to copy it and how should the ell reference change. Also always better to post the actual formula! -- Regards Frank Kabel Frankfurt, Germany "John" schrieb im Newsbeitrag ... I would appreciate any heply in solving what seems a simple problem. I wish to copy down a formula in Cell AA1, that contains a reference to B1 down to AA6, but instead of it showing B6 I want it to show B7. I have an extended formula in AA1 but only the reference to B1 is the critical part as I just want to simplify my post. I scanned the Groups for an answer and I'm getting more confused. I've to copy this formula 400 times so that why I don't want to do it manually! Thanks |
#3
|
|||
|
|||
Have you looked at help under OFFSET?
It's not very clear from your post WHEN you want it to offset by one row (from B6 to B7), e.g. only in the 6th row? "John" wrote in message ... I would appreciate any heply in solving what seems a simple problem. I wish to copy down a formula in Cell AA1, that contains a reference to B1 down to AA6, but instead of it showing B6 I want it to show B7. I have an extended formula in AA1 but only the reference to B1 is the critical part as I just want to simplify my post. I scanned the Groups for an answer and I'm getting more confused. I've to copy this formula 400 times so that why I don't want to do it manually! Thanks |
#4
|
|||
|
|||
Hi Frank
=INDEX(Master!$A$16:$C$43,MATCH(B9,Master!$A$16:$A $43,),MATCH("Subacc",Maste r!$A$16:$C$16,)) This formula resides in AA9 and I wish to copy it exactly to AA15, except I need B9 above to change to B10 I think in actual fact I got this formula from you about a year ago for another spreadsheet Rgds "Frank Kabel" wrote in message ... Hi in what other cells do you want to copy it and how should the ell reference change. Also always better to post the actual formula! -- Regards Frank Kabel Frankfurt, Germany "John" schrieb im Newsbeitrag ... I would appreciate any heply in solving what seems a simple problem. I wish to copy down a formula in Cell AA1, that contains a reference to B1 down to AA6, but instead of it showing B6 I want it to show B7. I have an extended formula in AA1 but only the reference to B1 is the critical part as I just want to simplify my post. I scanned the Groups for an answer and I'm getting more confused. I've to copy this formula 400 times so that why I don't want to do it manually! Thanks |
#5
|
|||
|
|||
Hi
question is, as you said you want to copy it to more than 400 cells, waht are the next cells. That is: What is the order for your copy process -- Regards Frank Kabel Frankfurt, Germany "John" schrieb im Newsbeitrag ... Hi Frank =INDEX(Master!$A$16:$C$43,MATCH(B9,Master!$A$16:$A $43,),MATCH("Subacc", Maste r!$A$16:$C$16,)) This formula resides in AA9 and I wish to copy it exactly to AA15, except I need B9 above to change to B10 I think in actual fact I got this formula from you about a year ago for another spreadsheet Rgds "Frank Kabel" wrote in message ... Hi in what other cells do you want to copy it and how should the ell reference change. Also always better to post the actual formula! -- Regards Frank Kabel Frankfurt, Germany "John" schrieb im Newsbeitrag ... I would appreciate any heply in solving what seems a simple problem. I wish to copy down a formula in Cell AA1, that contains a reference to B1 down to AA6, but instead of it showing B6 I want it to show B7. I have an extended formula in AA1 but only the reference to B1 is the critical part as I just want to simplify my post. I scanned the Groups for an answer and I'm getting more confused. I've to copy this formula 400 times so that why I don't want to do it manually! Thanks |
#6
|
|||
|
|||
Basically Frank start point is in AA9 that is followed by simply formulas in
AA10-AA14 - there ok, I can manage them, then AA9 is repeated in AA15 - except for that one reference i.e. it should be B10, then I have simply formulas again in AA16-AA20, then AA21 will have the same formula as in AA9 except this time itwill be B11, this all continues in every 6th row B12,B13,B14 etc Thanks "Frank Kabel" wrote in message ... Hi question is, as you said you want to copy it to more than 400 cells, waht are the next cells. That is: What is the order for your copy process -- Regards Frank Kabel Frankfurt, Germany "John" schrieb im Newsbeitrag ... Hi Frank =INDEX(Master!$A$16:$C$43,MATCH(B9,Master!$A$16:$A $43,),MATCH("Subacc", Maste r!$A$16:$C$16,)) This formula resides in AA9 and I wish to copy it exactly to AA15, except I need B9 above to change to B10 I think in actual fact I got this formula from you about a year ago for another spreadsheet Rgds "Frank Kabel" wrote in message ... Hi in what other cells do you want to copy it and how should the ell reference change. Also always better to post the actual formula! -- Regards Frank Kabel Frankfurt, Germany "John" schrieb im Newsbeitrag ... I would appreciate any heply in solving what seems a simple problem. I wish to copy down a formula in Cell AA1, that contains a reference to B1 down to AA6, but instead of it showing B6 I want it to show B7. I have an extended formula in AA1 but only the reference to B1 is the critical part as I just want to simplify my post. I scanned the Groups for an answer and I'm getting more confused. I've to copy this formula 400 times so that why I don't want to do it manually! Thanks |
#7
|
|||
|
|||
One way, you can try replacing B9 with in that formula (in AA9) with
INDIRECT("B"&9+(ROW()-9)/6) then copy it down. If you copy it to cells other than A15, A21, A27 (adding 6) it will return #REF. "John" wrote in message ... Hi Frank =INDEX(Master!$A$16:$C$43,MATCH(B9,Master!$A$16:$A $43,),MATCH("Subacc",Maste r!$A$16:$C$16,)) This formula resides in AA9 and I wish to copy it exactly to AA15, except I need B9 above to change to B10 I think in actual fact I got this formula from you about a year ago for another spreadsheet Rgds "Frank Kabel" wrote in message ... Hi in what other cells do you want to copy it and how should the ell reference change. Also always better to post the actual formula! -- Regards Frank Kabel Frankfurt, Germany "John" schrieb im Newsbeitrag ... I would appreciate any heply in solving what seems a simple problem. I wish to copy down a formula in Cell AA1, that contains a reference to B1 down to AA6, but instead of it showing B6 I want it to show B7. I have an extended formula in AA1 but only the reference to B1 is the critical part as I just want to simplify my post. I scanned the Groups for an answer and I'm getting more confused. I've to copy this formula 400 times so that why I don't want to do it manually! Thanks |
#8
|
|||
|
|||
Thanks Dave that works,I've been trying INDIRECT all afternoon trying to get
the syntax right, that / 6 is neat logic Thanks "Dave R." wrote in message ... One way, you can try replacing B9 with in that formula (in AA9) with INDIRECT("B"&9+(ROW()-9)/6) then copy it down. If you copy it to cells other than A15, A21, A27 (adding 6) it will return #REF. "John" wrote in message ... Hi Frank =INDEX(Master!$A$16:$C$43,MATCH(B9,Master!$A$16:$A $43,),MATCH("Subacc",Maste r!$A$16:$C$16,)) This formula resides in AA9 and I wish to copy it exactly to AA15, except I need B9 above to change to B10 I think in actual fact I got this formula from you about a year ago for another spreadsheet Rgds "Frank Kabel" wrote in message ... Hi in what other cells do you want to copy it and how should the ell reference change. Also always better to post the actual formula! -- Regards Frank Kabel Frankfurt, Germany "John" schrieb im Newsbeitrag ... I would appreciate any heply in solving what seems a simple problem. I wish to copy down a formula in Cell AA1, that contains a reference to B1 down to AA6, but instead of it showing B6 I want it to show B7. I have an extended formula in AA1 but only the reference to B1 is the critical part as I just want to simplify my post. I scanned the Groups for an answer and I'm getting more confused. I've to copy this formula 400 times so that why I don't want to do it manually! Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
When I copy down into the next cell the formula changes correctly. | Excel Discussion (Misc queries) | |||
How do I copy a formula in excel where part remains absolute the . | Excel Discussion (Misc queries) | |||
Copy Formula But Keep 1 Common Cell | Excel Discussion (Misc queries) | |||
How do I copy a formula? | Excel Worksheet Functions | |||
Copy formula to every 12th cell | Excel Worksheet Functions |