Return cell value from database

[dtbrooks7]

Hi, I have a database of responders to a large survey which will be having additions made constantly.

I have created a formula to return me a list of the sheets in the workbook (1 sheet per responder) and now I want to collect all response to each questions. Is there a formula I can use to solve the below? All responses are in Cell H5 on the individual sheets

so that the answer column returns the value of cell H3 on sheet "Response1" ?

Ideally I don't want to have to write in the name of each individual sheet name as there are a couple of hundred of them! Also if possible without VBA would be preffered!

 

[Fluff]

Hi & welcome to MrExcel.
How about
=INDIRECT("'"&A2&"'!H3")

 

[dtbrooks7]

Hi & welcome to MrExcel.
How about
=INDIRECT("'"&A2&"'!H3")

Thanks! but unfortunately not working!

 

[Fluff]

In what way?

 

[dtbrooks7]

In what way?

It is always returning a 0 value

 

[Fluff]

Is there anything in H3 on those sheets?
In your op you mention both H5 & H3

 

[dtbrooks7]

Yep… my bad was all in H5 haha! works a treat. THanks!

 

[Fluff]

Glad it' sorted & thanks for the feedback