How to link several separate reports in Excel?

M

MichelleFromAR

I'm not sure this can be done, but if so, can anyone help me? What I a
trying to do is this. I have several different reports that I have t
enter the same data into. Is there anyway to link the reports, so tha
when I enter data into one, it will automatically be entered into th
appropriate spaces in the others?

Thanks in Advance,
Michell
 
K

Ken Wright

If in cell A10 you wanted the data that you typed into A2, then in cell A10 put
=A2. Next time you change something in A2, A100 will reflect what you have put
in.
 
M

MichelleFromAR

Thanks.
I understand how to do have info automatically fill in on the sam
table, I was trying to figure out how to have it automatically fill i
onto a completely different table.

Thanks again,
Michell
 
K

Ken Wright

In exactly the same way. Give us a bit more detail about your tables and where
they are, ie are they on the same sheet, in the same workbook etc..
 
M

MichelleFromAR

Sorry, no, they are completely separate. Not on the same sheet o
workbook.

Thanks
MIchell
 
K

Ken Wright

Open up the two workbooks, ie the one where you want to pull the data into, and
the one where you want to pull the data from. Now do Window / Arrange / Tiled.
In the workbook you want to pull data into, type = and then using the mouse
click on the cell in the other workbook that you want to pull from. Hit enter
and close the source workbook and you will now see a link in the format:-

='C:\Documents and Settings\Default\My Documents\[All Sign Ups.xls]Sheet1'!$A$7

The bit in [ ] is your filename. Note those single quotes as well, it won't
work without them. You can build these manually if you want, but persaonlly I
still find it a lot easier to do it as I just said.
 
B

Bon Rouge

Hey there,
I found this useful because I want to do something similar. I hav
lots of worksheets in about four different workbooks. I want to creat
a list of the last entries last entries of a particular column in thos
sheets.
How could I do this?
I hope you can help
 
K

Ken Wright

Give us some more detail - How may sheets are we talking in each workbook - How
many of the last entries are we talking, ie is it the last 1, 2, 3, 4 etc. Is
this all being summarised in a single workbook?

Where I'm headed is that it may well be easier to create a summary range in each
workbook that pulls in the last entry from each required column for all the
relevant sheets in that book (This can be automated) and then simply put in some
hard links to those ranges from the summary workbook.
 
B

Bon Rouge

This is the second time in one night someone here'S given me just what
needed - thanks.
I was trying to link directly to the end of a certain column in ever
sheet - there are a lot of them - then I found that if I put a link t
the last column at the top I can link to that easily from other pages.
Great.
Thanks again.

Just for information... I have about five workbooks with about 4
sheets in each. I need(ed) to keep an eye on when the last entry wa
made in each sheet. To get that on a single page I needed to get th
last entered date from each page.
It's done now (though it took ages to go through them all)
 
K

Ken Wright

Ok then let me really tick you off :)

If the dates are entered sequentially with the last date being the latest, then
you could simply have used =MAX(A:A) to give you that value instead of trawling
down to find the date itself.

If not then you could probably just have used

=LOOKUP(9.99999999999999E+307,A:A) for last numeric entry in a column

If you hit sthe same isuuse and the data is not numeric then you can probably
use:-

=LOOKUP(REPT("z",255),A:A) for last text entry in a column

Assuming you had 100 sheets ine ach workbook and you were looking for the last
entry in Col A on each sheet, you could have grouped all the sheets and then
assuming youw anted to put the value into cell H1 on each sheet, just put the
formula I gave you in, hit enter and then ungroup the sheets. :)
 
B

Bon Rouge

Hey Ken,

Obviously, I don't know what I'm doing here... I'm just looking for
answers and listening to what people tell me.
I was trying to find the last date in the column and I was told to do
this :

{=INDEX($A$1:$A$500,LARGE(IF($A$1:$A$500<>"",ROW($A$1:$A$500)),1),1)}

Now I find that this :

=MAX(A:A)

would do nicely.

I definitely prefer the second one - so simple...

Cheers,

Steve
 
K

Ken Wright

Aahhh - but that is because someone covered all the bases for you, which means
you will get the last entry whether or not the data is sorted, which you
wouldn't get with MAX(A:A). If you know for sure that your dates will all be
sequential such that the last is the latest, then yes indeed MAX(A:A) will do it
for you.

Horses for courses, the trick is knowing when to apply what.
 
Top