Formulas across multiple sheets

[thekgbjr]

Scenario:
Home Health Clinic. I have 1 sheet per resident (40 in total) Page 1 is a summaryu page. Line 1 will summarize Sheet 2 (Resident1). Line 2 will summarize Sheet 3 (Resident2) etc... Is there a way that I can copy the formulas from the syummary page to automatically refer to the individual sheets instead of having to manually start on the summary page and do individual formulas for each resident sheet?

Does that make sense?

 

[jimrward]

on the summary sheet you could use the indirect and Row functions, for example to return the contents of cell h4 from each sheet you could have the following starting in row 1 of the summary, if it starts in row 2 then drop the + 1 bit

=INDIRECT("sheet" & ROW()+1 & "!H4")

and then drag down the formula