Excel 2003 - VBA - Sheet Referencing in Formulas

[Craig Brandt]

Hi Guys:



I'm drawing a blank and need some help.

I have a table with row 3 containing sheet names (currently pointing at 106
sheets).

In rows 4-250 I want to put formulas that will look for something (located
in Column B) in each of the sheets named in row 3. The first cell in the
target sheet contains the last row with valid data.



Example:



Cell "D3" = P02-05

Cell "B5" = 123456789IBM

In cell "D5" I want to generate a formula that will look in sheet P02-05,
search for the contents of "B5" throughout the sheet and return the Value in
column 8.

Cell "D5" should be =Vlookup($B5,'P02-05'!$A$2:$H$1080,8,0)



My thinking on VBA Code :

Cells(5,4).formulaR1C1 = "=Vlookup(RC2,'" & ^^^ & "'!R2C1:R" & ^^^ &
"C8,8,0)"



I'm having difficulty figuring out how to pull the Sheet name then the
Number of Lines from the target sheet ("A1" in the target sheet).



Any help would be appreciated,
Thanks,
Craig

 

[ShaneDevenshire]

Hi,

First this would work:

Cells(5,4) = "=Vlookup($B5,'P02-05'!$A$2:$H$1080,8,0)"

To make the sheet name dynamic so that is refers to a cell you want to
modify this to read:

Cells(5,4) = "=VLOOKUP($B5,INDIRECT(B3&""!$A$2:$H$1080""),8,0)"

 

[Craig Brandt]

Hi Shane:

Thanks for the response. I think you are going in the right direction, but
the code didn't work. It gave me a #REF in the cell.
I did change the B3 in the formula to a D3, still to no avail.

I changed my tack and used NAMES to identify the tables. This made the whole
mess easier to handle, and I got over the hurdle and it is working.

One day, I would like to understand how to substitute the contents of a cell
for the Sheet Name. I have run into several cases where it would have been
very helpful.

Thanks again for you input,

Craig

 

[Dave Peterson]

Maybe...

With ActiveSheet
.Range("E4").Formula = "=vlookup($b5,'" & .Range("D3").Value _
& "'!$a$2:$H$1080,8,0)"
End With

If there's nothing else on that worksheet that contains the table, I'd just
use: $a:$h

With ActiveSheet
.Range("E4").Formula = "=vlookup($b5,'" & .Range("D3").Value _
& "'!$a:$H,8,0)"
End With

It makes life a bit easier.