Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Re Car Log spreadsheet 0 how do I
Hi all,
I have a predicament if a car is not used for a few days the user will not fill in the odometer reading so it will show up as 0 in =Front33!$O$2 now I have 35 sheets called front1 to front35 each sheet gets the end odometer reading from the previous sheet for the next days start re4ading is there a way that if the current sheet gets 0 it can go back to the next sheet that has an end odometer reading??? Maybe I'm thinking too hard about this and cant see the wood for the trees!! Thanks Stephen |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Re Car Log spreadsheet 0 how do I
Can you not use the max function.
OK you ask how does this work. You have two cells from the previoius day the b/fwd figure and the c/fwd figure. So on today to use the =max(sum(bfwd), sum(cfwd)) bfwd is the cell on the previous sheet with the b/fwd value and cfwd is the cell on the previous sheet with the cfwd values. and this will b/fwd the larger of the two numbers which should work for when there is no rental odometer reading? Another way to do it is to have a cell on the current days sheet with the carry forward reading with =if(isblank(odo),bwfd, odo) you have to replace bwfd with the cell for the b/fwd value and odo with the cell where the odo is entered. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "pano" wrote: Hi all, I have a predicament if a car is not used for a few days the user will not fill in the odometer reading so it will show up as 0 in =Front33!$O$2 now I have 35 sheets called front1 to front35 each sheet gets the end odometer reading from the previous sheet for the next days start re4ading is there a way that if the current sheet gets 0 it can go back to the next sheet that has an end odometer reading??? Maybe I'm thinking too hard about this and cant see the wood for the trees!! Thanks Stephen |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Re Car Log spreadsheet 0 how do I
Here's one way. It's a little "over the top" maybe? but at least you don't
have to enter separate formulas on 34 sheets. I'm assuming on each sheet you enter a beginning OD reading and an end OD reading. The beginning OD reading is the end OD reading from the previous sheet. So, you need a formula on sheets 2 - 35 to get the end OD reading from sheets 1 - 34. I'll assume cell N2 is the beginning OD reading and O2 (manually entered) is the end OD reading. Create a list of formulas that get the value from cell O2 of sheets 1 - 34. Enter this formula on some sheet (I'll use Sheet1) in cell A1 and copy down to A34: =INDIRECT("Front"&ROWS($1:1)&"!O2") Select sheets Front2 through Front35. These sheets will all be grouped. What you do to one of these sheets will be done to all of these sheets. We need to get part of sheet name to be used in another formula. With those sheets grouped enter this formula in some cell. I'll enter it in cell M1: =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+6,255)+0 This will return the number portion of the sheet name. You could just select each individual sheet and enter this number manually if you want but this formula method is faster. Now, with the sheets still grouped enter this formula in cell N2: =IF(COUNTIF(Sheet1!A1:A34,"0")=0,"",LOOKUP(2,1/(Sheet1!A1:INDEX(Sheet1!A1:A34,M1-1)0),Sheet1!A1:INDEX(Sheet1!A1:A34,M1-1))) Now, ungroup all the sheets. Right click any grouped sheet tab and select Ungroup sheets. This will return the last non-zero value from a previous sheet. Biff "pano" wrote in message ups.com... Hi all, I have a predicament if a car is not used for a few days the user will not fill in the odometer reading so it will show up as 0 in =Front33!$O$2 now I have 35 sheets called front1 to front35 each sheet gets the end odometer reading from the previous sheet for the next days start re4ading is there a way that if the current sheet gets 0 it can go back to the next sheet that has an end odometer reading??? Maybe I'm thinking too hard about this and cant see the wood for the trees!! Thanks Stephen |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Re Car Log spreadsheet 0 how do I
On Feb 12, 5:04 pm, "T. Valko" wrote:
Here's one way. It's a little "over the top" maybe? but at least you don't have to enter separate formulas on 34 sheets. I'm assuming on each sheet you enter a beginning OD reading and an end OD reading. The beginning OD reading is the end OD reading from the previous sheet. So, you need a formula on sheets 2 - 35 to get the end OD reading from sheets 1 - 34. I'll assume cell N2 is the beginning OD reading and O2 (manually entered) is the end OD reading. Create a list of formulas that get the value from cell O2 of sheets 1 - 34. Enter this formula on some sheet (I'll use Sheet1) in cell A1 and copy down to A34: =INDIRECT("Front"&ROWS($1:1)&"!O2") Select sheets Front2 through Front35. These sheets will all be grouped. What you do to one of these sheets will be done to all of these sheets. We need to get part of sheet name to be used in another formula. With those sheets grouped enter this formula in some cell. I'll enter it in cell M1: =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+6,255)+0 This will return the number portion of the sheet name. You could just select each individual sheet and enter this number manually if you want but this formula method is faster. Now, with the sheets still grouped enter this formula in cell N2: =IF(COUNTIF(Sheet1!A1:A34,"0")=0,"",LOOKUP(2,1/(Sheet1!A1:INDEX(Sheet1!A1:*A34,M1-1)0),Sheet1!A1:INDEX(Sheet1!A1:A34,M1-1))) Now, ungroup all the sheets. Right click any grouped sheet tab and select Ungroup sheets. This will return the last non-zero value from a previous sheet. Biff "pano" wrote in message ups.com... Hi all, I have a predicament if a car is not used for a few days the user will not fill in the odometer reading so it will show up as 0 in =Front33!$O$2 now I have 35 sheets called front1 to front35 each sheet gets the end odometer reading from the previous sheet for the next days start re4ading is there a way that if the current sheet gets 0 it can go back to the next sheet that has an end odometer reading??? Maybe I'm thinking too hard about this and cant see the wood for the trees!! Thanks Stephen- Hide quoted text - - Show quoted text - WHOOOOOAAAAA, and I thought it would be easy what a mind spinner, I'll give it a go thanks so much to both for your suggestions. regards Stephen |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Big problem with excel recalculating spreadsheet | Excel Discussion (Misc queries) | |||
Linked Spreadsheet Opens | Excel Discussion (Misc queries) | |||
summarize data from one spreadsheet to other spreadsheet | Excel Worksheet Functions | |||
Working spreadsheet highlighting function for Excel 2007 | Excel Worksheet Functions | |||
Spreadsheet merging problems | Excel Worksheet Functions |