Copying Down Formulas Q

[John]

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

 

[Guest]

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/ )

 

[Guest]

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]

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

 

[eklarsen]

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