Getting cell information

K

Kevin Baker

Hi everyone!

I have a workbook with several sheets, each with daily cash
collections from different vending machines in Column D. I would have
to have a totals page that would take the last collection from Column
D of each sheet. The last sheet would function like a summary page
showing the total collected for that day. As I enter a new collection
on the cash collection sheet, that value (since it is the last
collection) would be automatically put on the summary sheet.

Thanks
Kevin
 
K

Ken Wright

These will return the last numeric entry in Col D from the Sheets listed. Just
change the sheet references to match yours.

=LOOKUP(9.99999999999999E+307,Sheet1!D:D)
=LOOKUP(9.99999999999999E+307,Sheet2!D:D)
=LOOKUP(9.99999999999999E+307,Sheet3!D:D)
etc

Note, if you have spaces in your file names you will need to surround the
reference with quotes, eg:-

=LOOKUP(9.99999999999999E+307,'Sheet 1'!D:D)
 
T

Trevor Shuttleworth

Kevin

for sheet1: =OFFSET(Sheet1!D1,COUNTA(Sheet1!D:D)-1,0)
for sheet2: =OFFSET(Sheet2!D1,COUNTA(Sheet2!D:D)-1,0)

etc

Note: there must not be any blank cells *amongst* the data in column D on
the worksheets

Regards

Trevor
 
T

Trevor Shuttleworth

Wow ! Why !??

Regards

Trevor


Ken Wright said:
These will return the last numeric entry in Col D from the Sheets listed. Just
change the sheet references to match yours.

=LOOKUP(9.99999999999999E+307,Sheet1!D:D)
=LOOKUP(9.99999999999999E+307,Sheet2!D:D)
=LOOKUP(9.99999999999999E+307,Sheet3!D:D)
etc

Note, if you have spaces in your file names you will need to surround the
reference with quotes, eg:-

=LOOKUP(9.99999999999999E+307,'Sheet 1'!D:D)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

-------------------------------------------------------------------------- --
It's easier to beg forgiveness than ask permission :)
-------------------------------------------------------------------------- --
Click to expand...
 
B

Bob Phillips

Because 9.99999999999999E+307 is the largest value you can get in a cell, so
if you look it up, you get the closest match, which for LOOKUP is the last.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
T

Trevor Shuttleworth

Neat

Bob Phillips said:
Because 9.99999999999999E+307 is the largest value you can get in a cell, so
if you look it up, you get the closest match, which for LOOKUP is the last.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
Click to expand...
 
J

JE McGimpsey

Because it looks so blasted ungainly, I've got it the value defined as
_MAXDOUBLE in my workbook template so I don't have to look at it.
 
B

Bob Phillips

So have I, although I call mine __MaxValue, and it loads on open of workbook
(although I admit to rarely using it as it doesn't handle text in tests).

Bob

JE McGimpsey said:
Because it looks so blasted ungainly, I've got it the value defined as
_MAXDOUBLE in my workbook template so I don't have to look at it.
Click to expand...
last.
 
Top