Create reference (tab name) based on cell values to create the link

[hro5e]

Hi,

I'm having real trouble in searching for the answer to my problems, perhaps I am not wording it correctly.

I have 2 tabs:

Sheet1
Sheet2

If I want the value in cell A2 from Sheet2 to be in cell A2 in Sheet1, the formula would simply be:

='Sheet2'!A2

Now I want to create a formula that pieces together the formula above based on the text in another cell.

So, I would want to recreate ='Sheet2'!A2 but I want the name 'Sheet2' to be taken from cell A1. So the idiots formula (in my head) would be:

='A1&"!"'&A2

Which would be the same as ='Sheet2!A2, which will get the data from A2 in Sheet 2.

The idea behind this, is that I need to extract data from certain tabs (sheets) but only based on the value of the data in A1 in Sheet 1.

So if the value in A1 in Sheet1 was:

SOO-1

the formula ='A1&"!"'&A2 would create the outcome ='SOO-1'!A2

Which ultimately searches and extracts the data in cell A2 from the sheet tab called SOO-1

I hope this makes sense.

 

[Fluff]

How about

=indirect("'"&A1&"'!A2")

 

[hro5e]

Hi, thanks for the swift reply,


I get this error message:ErrorFunction INDIRECT parameter 1 value is ''S001'!A2'. It is not a valid cell/range reference.

 

[hro5e]

It works now

How bizarre, I had to delete the value in A1 and retype it

 

[Fluff]

Glad to help & thanks for the feedback.