Reference to cell of the sheet that another cell is referencing

[jlea94]

Hi there!
I'm a student intern trying to help out a colleague of mine by making his life with Excel a lot easier.

Here is the schematic of what I'm trying to achieve:

http://s27.postimg.org/861dvxjte/myexcelproblem.jpg

note: P47 should be P37, it's a typo (further information explains the picture)

The full list of sheet names are in the sheet 'SheetList' in the rows of column A.

Basically I have a summary sheet where I'm trying to pull certain cells from each of the sheets in the workbook into a table in my summary sheet.

There are either 1 or 2 hazards in every sheet, Hazard 1 is located at C23 of every sheet, Hazard 2 is located at either C33 or C37 of every sheet.

I want to fill in Location/Plant and Existing Control Measures based on what sheet I'm referencing in my Hazards/Risks section.

Location/Plant is at D3 of every sheet.
Existing Control Measures is either at P33 (if Hazard 2 was at C33) or P37 (if Hazard 2 was at C37)

The function code used for getting Hazard 1, "Entanglement Hazards..." (refer to my picture) is: =INDIRECT("'"&SheetList!$A1&"'!C23")

The function code used for getting Hazard 2, "CIP Burns..." (refer to my picture) is:
=IF( (INDIRECT("'"&SheetList!$A1&"'!C33"))=0, (INDIRECT("'"&SheetList!$A1&"'!C37")), (INDIRECT("'"&SheetList!$A1&"'!C33")) )

So the logic I want to achieve is basically:

For Location/Plant, look in the same sheet that the corresponding hazard is referencing, and reference this sheet at D3.

For Existing Control Measures, look in the same sheet that the hazard is referencing, and reference this sheet at P23 if Hazard 1 (C23), or P33 if Hazard 2 (C33) or P37 if Hazard 2 (C37)

Thank you so much for the help, you guys are truly what makes the world go round!

 

[konew1]

For Location/Plant =INDIRECT("'"&SheetList!$A1&"'!D3")
For control measures hazard 1 =INDIRECT("'"&SheetList!$A1&"'!p23")
For control measures hazard 2 =IF( (INDIRECT("'"&SheetList!$A1&"'!C33"))=0, (INDIRECT("'"&SheetList!$A1&"'!P37")), (INDIRECT("'"&SheetList!$A1&"'!P33")) )

 

[jlea94]

Hi there and thanks for the help.

I didn't describe the problem very clearly so this is what I want to achieve now:

Hazard code:
=INDIRECT("'"&SheetList!$A3&"'!C23")

I want to find 'A3' from the bold part then stick it here:

Location/Plant code:
=INDIRECT("'"&SheetList!$[insert A3 here]&"'!D3")

 

[konew1]

Are you looking for formula for the summary sheet?
Then you plan on copying it down all the rows to retrieve data from each of the data sheets?
Maybe I misunderstand, but maybe...

Location/Plant code:
=INDIRECT("'"&SheetList!$A3&"'!D3")