Excel 2003 - VBA - Sheet Referencing in Formulas


C

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
 
Ad

Advertisements

S

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)"
 
C

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
 
Ad

Advertisements

D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top