Vlookup formula copy

[Confusicous]

I have a Vlookup formula that I want to copy over but everytime I do it
changes...Example: Original formula =VLOOKUP(A4,'P35'!$A$2:$H$83,7,FALSE)
when I copy and paste right next to it I get
=VLOOKUP(B4,'P35'!$A$2:$H$83,7,FALSE)...Actually what I need is the B4 to
remain A4 and if its possible to get the 'P35' to go to 'P36'...I am open to
any thoughts or ideas. Thanks in advance

 

[Mike H]

Try this

=VLOOKUP($A$4,INDIRECT("'P"&ROW(A35)&"'!$A$2:$H$83"),7,FALSE)

Mike

 

[Jim Thomlinson]

To keep the cell reference the same you the $ sign to indicate an absolute
reference...

=VLOOKUP($A4,'P35'!$A$2:$H$83,7,FALSE)

In this case the $ will hold the A constant but allow the 4 to increment.

As for incrementing the sheet name there is no built in functionallity to do
that.

 

[Confusicous]

Thanks for both of the responses...very helpful

Try this

=VLOOKUP($A$4,INDIRECT("'P"&ROW(A35)&"'!$A$2:$H$83"),7,FALSE)

Mike

 

[Mike H]

Glad I could help

Thanks for both of the responses...very helpful