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

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Sheet Name[/TD]
[TD="align: center"]Aim[/TD]
[TD="align: center"]Formula i want to use[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Orange[/TD]
[TD]=Orange!A1[/TD]
[TD]=A4!A1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Purple[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Blue[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.