Using previously populated cell on a past worksheet.

[gawa_ram]

Guys,

I'm constructing a spreadsheet for work and need one final calculation before it is complete.

The spreadsheet is working out the MPG for vehicles on a day-by-day basis before giving an overall monthly total at the end. The work sheets at the bottom represent each day of the month.

The sheets themselves are all the same and sorted by Unit number so each cell on each spreadsheet will represent the same unit.

I have the MPG calculation filled in so that it will use the amount of fuel drawn the previous day divided by the miles travelled.

However, not all drivers fill up every day and so if a driver say fills up on the 1st then doesn't fill up again to the 7th, the formula is looking for a fill on the 6th so it can work out the MPG. Obviously this will be blank so no MPG will be calculated.

Is there anyway I can get Excel to look back and pick up the last populated cell in that row and use that for the calculation? In the above example, it will ignore the 2nd-6th and will recognise a fuel-up on the 1st and use that drawing?

Many thanks for your help.

 

[VoG]

To find the last filled cell try like this

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	1	3	7	5	2						2
Sheet3

 

[gawa_ram]

Thanks VoG - would this work looking back through worksheets though for populated K3 cell on each sheet if you get what I mean?

Thanks again.

 

[VoG]

Sorry, no. I obviously misunderstood the question.

 

[gawa_ram]

No problem VoG - I need the formula to look through the worksheets and find the last populated cell of K3-140ish every time and use that to work out the MPG the next time the driver fills up.

If anyone can help it would be much appreciated.

 

[gawa_ram]

I've now amended the spreadsheet so VoG's above formula would work.

However when I input the LOOKUP formula I require each cell to look into three cells above it and on above to the 1st of the month to see what was the last drawing.

For example:

A driver has drawn fuel on 1st month then doesn't do so until 10th of the month. As drivers can draw up to three times in one day there is an area of 3x31 (days of the month) cells allowed for drawings from I6-K36. Therefore, I need the LOOKUP to look-up I6-K14 (1st-9th) to see when the last drawing was made and use that to work out the MPG for those 10 days of the month.

The LOOKUP I have entered from above, returns a DIV/0 figure as K14 is blank and it won't pick up I6.

Can anyone help with this problem? This will complete the spreadsheet for me!!

Many thanks.