How do I create a sheet in Excel that updates itself from other sh

[Mark]

I am interested in creating what is essentially an automated summary
worksheet in a workbook that would take data from multiple other worksheets
in the same workbook. My goal is to allow me to update data in sheet1,
sheet2, etc, so that it all automatically enters itself into a consolidated
summary table on sheet 7. All of the sheets will have the same fields, but I
want to be able to sort the summary data using the list features.

Instead of linking every cell in sheet 7 to every potential corresponding
cell in sheet1, can I create lists on sheet1, sheet2, etc., that essentially
export their data to a master list on sheet7? ie. If I currently have 3 rows
of data on sheet1, but expect 20 by the end of the year, I don't want sheet7
to have links to 17 blank rows within sheet1's list. Instead it would add
rows as they are added to sheet1's list. This way I don't have to leave lots
of blank rows in sheet7 that are waiting for the data to be filled into
sheet1, sheet2, etc.

Hopefully, that all makes sense. I can't find anything in the help section
that explains what I am looking for, can anyone out there help? thanks!

 

[imills]

I have the exact same problem. I hope someone can answer this for us!!

 

[tkt_tang]

1. My Excel workbook contains multiple worksheets and I have also
attempted to extract (thereof) certain facts and figures for displaying
in a summary sheet.

2. Such attempt is very much a personal effort (and there's not any
general requirement on the layout and structure of the corresponding
worksheets).

3. I could allow for the (progressive) expansion of individual
worksheets (such that the facts and figures are automatically captured
in the summary sheet). Admittedly, it sounds more sophisticated than
practical ; despite initial planning, the things could sometimes crash
due to unforeseen circumstances. However, the glitches could be readily
remedied (just as I have said, it's a personal effort, such that it
could hardly be suited for other users).

4. The gist of the matter is that one ought to be capable of deploying
formulae in a summary sheet. The development of the workbook, in
general, is a progressive one (again, a personal effort to suit
personal requirements).

5. You could be rest assured that the personal development experience
is rewarding along the way.

6. Regards.

 

[Mark]

What?

is this supposed to help in some way?

Anyone else know of how to transfer added rows of data automatically as
detailed in my original post? thank you.

 

[karsin]

I am working on something like that too, to share with you I discovered
the function called vlookup & indirect is quite useful.

However, there are problem I can't solve with the above. This is how it
goes....

I have a summary workbook called "PartnerRawDataTracker.xls" and 3
other workbook namely "(200511) RawData.xls" & "(200512) RawData.xls" &
"(200601) RawData.xls" There are a lists of companies with number of
purchases in each. In my "PartnerRawDataTracker.xls" i used this
=INDIRECT("'[("&$B$2&") RawData.xls]"&$B$2&"'!A2"), which &$B$2&
equalvalent to 200601 in a column of the same sheet, which is
changable. Now when I change my year, I able to retrieve the data from
the specific workbook..... HOWEVER, In

=INDIRECT("'[("&$B$2&") RawData.xls]"&$B$2&"'!A2") it only points to
the same column"A2" of the 200601 workbook, I cannot copy & paste for
it to copy whole range of companies !!! How do I use the same formula
BUT with A2+1 incorporated ?

This method doesn't seem to solve the data expansion problem too, if i
have 5 companies now, in 200603 I might have 8 companies, how can i
expands?


Please share.
Thanks

KS

 

[karsin]

and .... I hope not to use any VBA or macro function is it possible ?

thanks

KS

 

[Roger Govier]

Hi

try
=INDIRECT("'[("&$B$2&") RawData.xls]"&$B$2&"'!A"&ROW(A2))
Row(A2) will return 2 when in cell A2, which will be concatenated to the
A to give A2.
As you copy down, then row number will increase, hence the cell ref will
increase.