Getting cell information

[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

 

[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)
=LOOKUP(9.99999999999999E+307,Sheet2!D)
=LOOKUP(9.99999999999999E+307,Sheet3!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)

 

[Trevor Shuttleworth]

Kevin

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

etc

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

Regards

Trevor

 

[Trevor Shuttleworth]

Wow ! Why !??

Regards

Trevor

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)
=LOOKUP(9.99999999999999E+307,Sheet2!D)
=LOOKUP(9.99999999999999E+307,Sheet3!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)

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

-------------------------------------------------------------------------- --
It's easier to beg forgiveness than ask permission
-------------------------------------------------------------------------- --

 

[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)

 

[Trevor Shuttleworth]

Neat

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)

 

[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.

 

[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

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.

last.