How to auto populate values from diagonal cells in different columns

[Tchme2]

Hi, I want to enter numbers in Column H and have Column E auto populate with diagonal values like this ignoring blank cells.

 

[kanadaaa]

I think most people here would wonder, when they understand what you want, why not just type "=H5" in E9 and "=H9" in E12.
If you're looking for a way to automate even that, we'd need a systematic pattern of how the destination cell is decided.

 

[Tchme2]

Thank you for getting back to me. I should have been more clear. Want E9 to get value from last non empty cell above H9. I need to enter for each day the vehicle is used and sometimes the vehicle is not used on a particular day which leaves some rows empty. I want those rows where the vehicle was not used to remain empty but I want the last previous mileage (Col H) to auto populate in the next day the vehicle is used (Col E).

 

[Fluff]

How about

=MAX(H3:H8)

 

[kanadaaa]

Want E9 to get value from last non empty cell above H9.

This can be achieved with the formula below in H9:

=LOOKUP(2,1/(H3:H8<>""),H3:H8)

But this isn't dynamic, unfortunately.
One solution I can think of is to prepare a column and rows for dates and a VBA code that populates the value in the last nonblank cell in column H to the row for a date that is matched with the date when you enter a new mileage.

 

[Tchme2]

Thanks for answering. That just gives me the highest number in the column. I am looking for the last non blank cell in Col H above E9

 

[Fluff]

That just gives me the highest number in the column.

But surely the mileage will never go down (unless your illegally clocking your vehicles), so it will give the last value.

 

[Tchme2]

Thanks Everyone for answering. I decided to work on a VBA code it's easier.

 

[Tchme2]

But surely the mileage will never go down (unless your illegally clocking your vehicles), so it will give the last value.

Since it will be placed in Col E and copied down it just gives me the same highest number from Col H in the cells of Col E

 

[Fluff]

If you want to copy it down (which you never mentioned) then use

=MAX(H$3:H8)

and the range will expand as you drag it down col E
or use this mod to Kanadaaa's formula

        =LOOKUP(2,1/(H$3:H8<>""),H$3:H8)