Automatically update Excel cover sheet with data from other sheets

Here is something I would like to be true for every value of X:

On the cover sheet, I would like cells A to K in Row X to take the values from cells J5 to T5 in Sheet X.

Do I need to learn VBA?

-------------Problems Reply------------

No, VBA is not necessary. On the cover sheet in cell A of row X, enter an equals sign, then switch to sheet X and click on cell J5. Press enter. Then do the same foreach other cell on the cover sheet.
Many thanks, Bob. If this were something I wanted to apply to hundreds of cells, I'm presuming it would be better to automate the process somehow, so perhaps Basic is going to be necessary after all...
You can probably drag the formulas or rearrange the formula's text so that it can be dragged. Can you give a better example (using actual row and column values) so that we can offer some better suggestions.

Yep, apologies for lack of clarity, Bob.

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 sheet will seamlessly change its value too.

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

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 through T. In that case, the formula in column 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.

Thanks Bob. Since there will be hundreds of new sheets which I would like to contribute to the cover sheet, I suppose I was hoping there might be a method that would simply fill in a new row on the cover every time I added a new sheet. This is partly to save manhours and partly to minimise the room for error. Perhaps life is not that simple!
You might be able to automate this process by using Macros. You could get started by recording the process of adding a new worksheet and then adding the new row to the cover sheet. You will need to customize your macro so that it knows where the last row data is on the cover sheet so that the next row would be used for your new data.

Category:Office for Mac Views:9 Time:2013-10-31

Related post

Copyright (C), All Rights Reserved.

processed in 0.126 (s). 11 q(s)