Cell stimulating search on seperate sheet

[Harvey12]

Hi All,

I really need some help!!

I have a problem where I need a cell to control a lookup. Cell D1 is the cell in question where the information can be variable such as: A,B,C,D, despite that each variable will have a corresponding sheet also named: A,B,C,D and so on. Inside each sheet cell H20 holds a numerical value eg: 30,40,50 etc.

I need a formula of some kind which will take the information in D1, then look into the corresponding sheet and bring back value H20 from that sheet and put it into E1.

Is this possible? Can someone please assist?

Many thanks
Harvey

 

[MorriSiege]

Could you provide the sheet for me to understand more please?

 

[Harvey12]

Hi MorriSiege,

Unfortunately no, the sheet is filled with confidential information.

Is there any other way I can help you understand?

Many thanks
Harvey

 

[MorriSiege]

=IF($D$1<>"",INDIRECT("'"&$D$1&"'!$H$20"),"")

Try this.

Place the formula into E1

 

[Harvey12]

Thank you so much, worked perfectly!!

 

[MorriSiege]

Glad I could help Harvey