Re Car Log spreadsheet 0 how do I

[pano]

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

 

[Martin Fishlock]

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.

 

[T. Valko]

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]

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

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