Making one cell in another row equal another all the time

[Hugh Jeego]

For example, the reason I want this is that as part of my excel workbook, I
keep car mileage broken up into personal and work. I have the date, the
starting number and the cell after that the ending number then after that
"w" for work or "p" for personal and then I made the difference between them
come up under either the personal or work columns. However - and quite
obviously - the ending number on one row is the exact same number as the
starting number on the next row. So what I want to do, if possible, is to
make the starting number on the next row ALWAYS fill in using the ending
number of the row before that on each occasion. So if the ending number is
10010 one day, the starting number is auto filled in using that number and
if, another day the ending number is 20020 then the starting number the next
day auto fill in. The date doesn't come into this because, quite often, I
need to put personal and work related numbers in on the same day.

Thanks for any help.

 

[Pete_UK]

Suppose the start-number column is B and the end number is in column
C, and you have a header row so that the data proper starts on row 2.

In B2 you will have to enter the first starting number yourself, but
you can then use this formula in B3:

=IF(C2="","",C2)

and then copy this down as far as you think you might need it.

Hope this helps.

Pete

 

[Hugh Jeego]

Pete,

Thanks for that but I figured it out for myself eventually and it was much
easier. If, for example, D331 is the ending cell for the day and C332 has to
exact same as that then all I had to do was put, in C332 the simple formula
=D331 then expand that highlighted C332 down to the bottom square in that
row. Works a treat.

Suppose the start-number column is B and the end number is in column
C, and you have a header row so that the data proper starts on row 2.

In B2 you will have to enter the first starting number yourself, but
you can then use this formula in B3:

=IF(C2="","",C2)

and then copy this down as far as you think you might need it.

Hope this helps.

Pete

 

[Pete_UK]

Well, I'm glad to hear that you figured it out yourself.

One problem in copying your formula down to the botton of the sheet is
that it will show 0 when the corresponding cell in column C is blank,
whereas mine will return a blank.

Thanks for feeding back, though.

Pete

 

[Hugh Jeego]

I just thought I would add this to end this thread -

I ended up using this formula that I worked out for myself. All I can say is
"it works, too".

=IF(D342>0,D342,"")

.....where the cell starting that row is C343 so refers backwards to D342. I
can see, now that I worked it out for myself, where yours is better, though.
I didn't understand it that easily before and when you said that my previous
attempt left a column of zeroes instead of spaces, I decided to find a way
around it with what I had. That was my attempt. In attempting to figure it
out I obviously learned more. I have a similar thing in the same row,
every row:

=IF(E343="P",D343-C343," ")

This is to divide Personal mileage from Work Mileage. If the value in 343 on
a particular row is P then the answer is the starting number subtracting
from the ending number but if it isn't equal to P then it is blank. In the
Work column I have the same thing excepting where it equals P it is looking
for equal to W. That sorts the distance in any trip to the right column and
at the bottom of each column is the total for Personal or Work. Why go to
that trouble? In Australia you are required to keep a logbook for kilometres
travelled depending on your circumstances, for tax. In my case, self
employed and using my own car, I needed only to keep it for 3 months to
average the use out and claim on tax that way. However, if you keep a
running total all year round, you get a better percentage on tax as a tax
deduction. Given Excel will do it all for me, I just do it that way and
deduct more. I used to keep that same total and add it all up at the end of
the tax year each year which was a real pain. Nothing like referring to the
bottom of the page and claiming, that way!!

Well, I'm glad to hear that you figured it out yourself.

One problem in copying your formula down to the botton of the sheet is
that it will show 0 when the corresponding cell in column C is blank,
whereas mine will return a blank.

Thanks for feeding back, though.

Pete