I have a lot of pieces of information coming in from different people. Each of t

Question

I have a lot of pieces of information coming in from different people. Each of them becomes a new sheet of a master spreadsheet. And for each of them, the work is summarised in cells J5, K5, L5, M5, N5, O5, P5, Q5, R5, S5 and T5 of the relevant sheet.

What I hope to be able to do is to give that spreadsheet a kind of cover page (sheet 1) which is automatically updated with all the relevant data. So in row 2, it will display the values from cells J5, K5, L5, M5, N5, O5, P5, Q5, R5, S5 and T5 in sheet 2. In row 3, J5, K5, L5, M5, N5, O5, P5, Q5, R5, S5 and T5 from sheet 3, and so on.

And when a later set of data comes in and I add that as sheet 100 -- then, in that first sheet, row 100 would then display the values from cells J5, K5, L5, M5, N5, O5, P5, Q5, R5, S5 and T5 in sheet 100.

And(!) if I later find an error in sheet 66 and change it, then row 66 of the cover sheeet will seamlesssly changee ists value too.

I may, of course, be naive in thinking that there is a way of making this happen automatically...!!!

Explanation / Answer

Well, there certainly is a way to have this happen automatically. All you will have to do is to fill down the data into a new row on the cover sheet. It would be pretty easy if you could store the name of the sheet in a column on the cover sheet, say column A.

Then on the cover sheet, row 2, in cell J5, enter the formula:=indirect("'"&A2&"'!j5")
manually do this for cells K through T on the cover sheet, row 2. Now all you have to do is attach a new worksheet, enter its name in column A of the next available row, and then select cells in columns J through T of the row immediately above and drag down to the new row.

(If you were to switch to R1C1 notation instead of A1 it would be even easier to enter the original formulas. You would only have to do it for column J and then you could fill to the right to generate the fromulas for K thrrough T. In that casee, the formulaa in coclumn J (10) would be: =indirect("'"&rc1&"'!rc"&column(),false).
Fill this to the right to column T (now column 20). then select cells in column 10 through 20 and fill down for as many rows as needed.

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.