| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting cell information
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Getting cell information
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 :-) ---------------------------------------------------------------------------- "Kevin Baker" wrote in message om... 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 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.605 / Virus Database: 385 - Release Date: 01/03/2004 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Getting cell information
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 "Kevin Baker" wrote in message om... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Getting cell information
Wow ! Why !??
Regards Trevor "Ken Wright" wrote in message ... 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 :-) -------------------------------------------------------------------------- -- "Kevin Baker" wrote in message om... 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 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.605 / Virus Database: 385 - Release Date: 01/03/2004 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Getting cell information
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" wrote in message ... Wow ! Why !?? Regards Trevor "Ken Wright" wrote in message ... 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 :-) -------------------------------------------------------------------------- -- "Kevin Baker" wrote in message om... 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 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.605 / Virus Database: 385 - Release Date: 01/03/2004 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Getting cell information
Neat
"Bob Phillips" wrote in message ... 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" wrote in message ... Wow ! Why !?? Regards Trevor "Ken Wright" wrote in message ... 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 :-) -------------------------------------------------------------------------- -- "Kevin Baker" wrote in message om... 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 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.605 / Virus Database: 385 - Release Date: 01/03/2004 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Getting cell information
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. In article , "Bob Phillips" wrote: 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. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Getting cell information
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" wrote in message ... 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. In article , "Bob Phillips" wrote: 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| use other cell information to put formula in current cell | Excel Worksheet Functions | |||
| If blank cell, copy information from cell above | Excel Worksheet Functions | |||
| Combining multiple cell information into one cell | Excel Discussion (Misc queries) | |||
| When copying a cell to another cell the information is duplicated. | Excel Worksheet Functions | |||
| Auto-fill cell based on adjacent cell information.. | Excel Worksheet Functions | |||
Linear Mode
