Copying Down Formulas Q
 

I have the following 3 formulas in rows 3-5

=INDEX(Master!$A$16:$C$43,MATCH(B9,Master!$A$16:$A $43,),MATCH("Subacc",Maste
r!$A$16:$C$16,))
=INDEX(Master!$A$16:$C$43,MATCH(B9,Master!$A$16:$A $43,),MATCH("Subacc",Maste
r!$A$16:$C$16,))
=INDEX(Master!$A$16:$C$43,MATCH(B9,Master!$A$16:$A $43,),MATCH("Subacc",Maste
r!$A$16:$C$16,))

I wish to copy them down to rows 6-8 so that the "B9" within them changes to
"B10". Currently if I just copy and Paste it changes to "B12". I'm sure its
a Offset formula I need but I can't figure it out. I have 400 rows in total
to copy down, so changing it manually would be a big effort

Thanks

Hi John , .. .

If you are copying down to a list of values, why not just use the vlookup
function. This will enable you to match you text from any workbook.

Please let me know whether this helps you or not. . . . .. ..


Regards

Garreth Lombard ( http://www.geocities.jp/xlmaniacs/ )

"John" wrote:

I have the following 3 formulas in rows 3-5

=INDEX(Master!$A$16:$C$43,MATCH(B9,Master!$A$16:$A $43,),MATCH("Subacc",Maste
r!$A$16:$C$16,))
=INDEX(Master!$A$16:$C$43,MATCH(B9,Master!$A$16:$A $43,),MATCH("Subacc",Maste
r!$A$16:$C$16,))
=INDEX(Master!$A$16:$C$43,MATCH(B9,Master!$A$16:$A $43,),MATCH("Subacc",Maste
r!$A$16:$C$16,))

I wish to copy them down to rows 6-8 so that the "B9" within them changes to
"B10". Currently if I just copy and Paste it changes to "B12". I'm sure its
a Offset formula I need but I can't figure it out. I have 400 rows in total
to copy down, so changing it manually would be a big effort

Thanks

Hi John

Looking at your specific problem, i come to the conclusion that you can use
the vlookup function to simplify your search.
This function can be used to cross reference your text as well . . .Here is
a simple example how to use the vllokup function . ..

=VLOOKUP(A1,C50:F54,MATCH(F57,D49:F49,0)+1,FALSE)

Modify to your needs though. lol

"John" wrote:

I have the following 3 formulas in rows 3-5

=INDEX(Master!$A$16:$C$43,MATCH(B9,Master!$A$16:$A $43,),MATCH("Subacc",Maste
r!$A$16:$C$16,))
=INDEX(Master!$A$16:$C$43,MATCH(B9,Master!$A$16:$A $43,),MATCH("Subacc",Maste
r!$A$16:$C$16,))
=INDEX(Master!$A$16:$C$43,MATCH(B9,Master!$A$16:$A $43,),MATCH("Subacc",Maste
r!$A$16:$C$16,))

I wish to copy them down to rows 6-8 so that the "B9" within them changes to
"B10". Currently if I just copy and Paste it changes to "B12". I'm sure its
a Offset formula I need but I can't figure it out. I have 400 rows in total
to copy down, so changing it manually would be a big effort

Thanks

Thanks for your reply Garreth, that still wouldn't resolve the problem of
now having to change the variable "A1" in every 3rd row, firstly to "A2"
then 3 rows further down to "A3" etc






"Garreth" wrote in message
...
Hi John

Looking at your specific problem, i come to the conclusion that you can
use
the vlookup function to simplify your search.
This function can be used to cross reference your text as well . . .Here
is
a simple example how to use the vllokup function . ..

=VLOOKUP(A1,C50:F54,MATCH(F57,D49:F49,0)+1,FALSE)

Modify to your needs though. lol

"John" wrote:

I have the following 3 formulas in rows 3-5


=INDEX(Master!$A$16:$C$43,MATCH(B9,Master!$A$16:$A $43,),MATCH("Subacc",Maste
r!$A$16:$C$16,))

=INDEX(Master!$A$16:$C$43,MATCH(B9,Master!$A$16:$A $43,),MATCH("Subacc",Maste
r!$A$16:$C$16,))

=INDEX(Master!$A$16:$C$43,MATCH(B9,Master!$A$16:$A $43,),MATCH("Subacc",Maste
r!$A$16:$C$16,))

I wish to copy them down to rows 6-8 so that the "B9" within them
changes to
"B10". Currently if I just copy and Paste it changes to "B12". I'm sure
its
a Offset formula I need but I can't figure it out. I have 400 rows in
total
to copy down, so changing it manually would be a big effort

Thanks

An easy way, but not the best..
After copying the formula down, hightlight the row, hit ctrl f, find
"B9" and replace with "B10".


--
eklarsen
------------------------------------------------------------------------
eklarsen's Profile: http://www.excelforum.com/member.php...fo&userid=9001
View this thread: http://www.excelforum.com/showthread...hreadid=319649