CODEWITHSUNDEEP
excelreferenceexternal
PaulN
PaulN

Reputation: 11

EXCEL: Variable external reference Connection not resolving

I have a spreadsheet that contains one summary worksheet and over 300 detail worksheets. The summary page is used simply as a roll-up of key metrics.

What I need to do now is, using a separate sheet create a dynamic link to the data on the detail sheets, grabbing selected information for an appointment record.

I successfully resolve the worksheet name using VLOOKUP, and have that in a cell.

When I try to take that cell and create a reference to the detail worksheet, the formula doesn't resolve. No, it hasn't been changed to text.

Here's an example: Customer # 535018 resolves correctly to 'Aberdeen Gardens'

This is the data that sits in cell C4.

In cell B6 I want Excel to use the data in C4 to construct and resolve the cell C5 on the Aberdeen Gardens worksheet.

I used the following: =" ' " & $c$4 & " '!$c$5" {spaces are for comprehension}

When I hit enter, what I see in the cell is 'Aberdeen Gardens'!$C$5 which is exactly correct to access the data I want to see. When I type this reference in without a formula, it resolves and does what I want.

What the heck have I not done right?

Upvotes: 0

Views: 38

Answers (1)

BruceWayne
BruceWayne

Reputation: 23285

You'd want to use Indirect().

=Indirect("'" & $C$4 & "'!" & "$C$5") {I added spaces for comprehension}

Make sure your final reference (the one that's for the cell itself) is also in quotes. I forget sometimes and try =Indirect("'" & $C$4 & "'!" & $C$5) and get confused when it doesn't work. Add quotes!

Upvotes: 0

Related Questions