Reference a cell in another worksheet based on a particular value (without macros)

[dzaak]

Hi Everyone,

I'm new here.

I have a question about having a cell referencing in another workbook. I hope my explanation makes sense.

Let's say I have 3 worksheets called "Data1", "Data2" and "Report"

In 'Report'!A1 I have typed in the text "Data1" or "Data2".
In 'Report'!A2, I would like a formula to reference A1 in the worksheet named in the cell 'Report'!A1 .

eg.
1. 'Report!A1 says "Data1", so 'Report!A2 will reference 'Data1'!A1
2. 'Report!A1 says "Data2", so 'Report!A2 will reference 'Data2'!A1

Is this possible to do within a formula and no macros?

Thanks.

 

[MisterProzilla]

I think what you're wanting to do there is use the INDIRECT function, which allows you to use a text string as a reference. Unfortunately it doesn't work well with external references I learned that the hard way the other day. It'll work, but you have to have both documents open for it return a value. If you close the target doc, the cell will return blank - fairly useless really.

I figured out a pretty circuitous workaround for my own purposes, but I don't think it would suit yours

 

[dzaak]

Thanks Mister.

If I choose not to update links, would it return the correct value from the last time the corresponding workbooks were all open?
Alternatively, I can have everything in one workbook, but different tabs, however that will get messy. I'll be working about 20 tabs, so hiding and un-hiding them would really suck.

Anyway, I'll have a play around with it. Thanks again.

 

[MisterProzilla]

Hi dzaak,

Unfortunately no, I don't think that would work. From what I understand, INDIRECT is what's known as a volatile formula, so it updates itself whenever a change is made to the sheet requiring any kind of re-calculation.

Regarding the multiple sheets, I have a doc with about 60 sheets and it's workable, it just depends what you're doing with them. I just made it easier to get around mine by putting hyperlinks into a cover sheet, with a link back to the cover sheet from each. That way, you can keep them all hidden and just get around using links.

 

[dzaak]

Ok, so I've got it working fine within the same file with multiple tabs, but haven't tested it on separate workbooks. Judging by the replies so far, I doubt it will work, but at least worth a try.

 

[MisterProzilla]

Disappointment awaits ye.