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

dzaak

New Member
Joined
Mar 8, 2016
Messages
4
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.
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

MisterProzilla

Active Member
Joined
Nov 12, 2015
Messages
263
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

New Member
Joined
Mar 8, 2016
Messages
4
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

Active Member
Joined
Nov 12, 2015
Messages
263
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

New Member
Joined
Mar 8, 2016
Messages
4
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,122
Messages
5,628,822
Members
416,342
Latest member
BlueDevil12

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top