How to make a cell reference in a formula equal a cell with the same name as a different sheet in the same wokrbook

[alex13392]

Hi guys,

So here is my problem. Im looking to make a summary sheet from my workbook highlighting key values from all the different sheets.
What i want to do is use a formula to quickly get data from a different sheet that has the same name as the cell in my summary sheet.

To explain properly ill give an example:

Summary Sheet

	A	B	C
1
2	Sheet Name	Aim	Formula i want to use
3	Orange	=Orange!A1	=A4!A1
4	Purple
5	Blue

<tbody>
</tbody>

So essentially i want to click and drag down the formula in C3 so it automatically gets the data from Purple!A1 and Blue!A1. Make Sense?

If anyone could provide me with formula i should put into C3 to make it easy it would make my life much easier!

Thanks

 

[FormR]

Hi, try:

=INDIRECT("'"&A4&"'!A1")

 

[alex13392]

Hi, try:

=INDIRECT("'"&A4&"'!A1")

Wow Thanks so much! You hero. Thanks for replying so quickly too.

Love this website.