Vlook up values and saving them

[need4excel]

Hi guys,

I need help on this.

I would like to tie and record the car's plate number and turn-around-time for each stage but there's a catch. Read on please.

For example: 3 person brings their car in for cleaning and drying.

Cleaning stage:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Number Plate[/TD]
[TD]Time of entry[/TD]
[/TR]
[TR]
[TD]Car 1[/TD]
[TD]2651[/TD]
[TD]12:00pm[/TD]
[/TR]
[TR]
[TD]Car 2[/TD]
[TD]1554[/TD]
[TD]12:05pm[/TD]
[/TR]
[TR]
[TD]Car 3[/TD]
[TD]3223[/TD]
[TD]12:06pm[/TD]
[/TR]
</tbody>[/TABLE]

Car 1 and 2 move to the next stage which is drying, hence, Car 1 and 2 will disappear from the Cleaning Table.

Drying Stage:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Number Plate[/TD]
[TD]Time of entry[/TD]
[/TR]
[TR]
[TD]Car 1[/TD]
[TD]2651[/TD]
[TD]12:04pm[/TD]
[/TR]
[TR]
[TD]Car 2[/TD]
[TD]1554[/TD]
[TD]12:11pm[/TD]
[/TR]
</tbody>[/TABLE]

Drying Stage shows the time of entry too. With that, we are able to calculate the time taken to wash the car.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Number Plate[/TD]
[TD]Cleaning[/TD]
[/TR]
[TR]
[TD]2651[/TD]
[TD]4 mins (A)[/TD]
[/TR]
[TR]
[TD]1554[/TD]
[TD]6 mins (C)[/TD]
[/TR]
</tbody>[/TABLE]


Provided the Car 1 and 2 doesn't disappear from the table, the (A) formula would be =vlookup(2651,drying stage table, 3,0) - vlookup(2651, cleaning stage table, 3,0) Is this formula correct? It goes the same for (C) as well.

But the problem is, Car 1 and Car 2 will disappear from Cleaning Table after cleaning.

How do I store the data upon entry so it wouldn't give a #N/A and to be able to calculate the turn-around-time at the same time? Is there a workaround it?

PS: I've also tried the "save the last entry" through macro but it only works for the particular cell. It would be perfect if I could tie the macro to the number plate, if that's ever possible.
The macro formula I got is from this: https://www.extendoffice.com/documents/excel/5056-excel-remember-save-previous-cell-value.html

Thank you for your time and help!!

 

[Special-K99]

Why don't you have just one table?

Name	Number plate	Cleaning time	Drying time	Duration
Car 1	2651	        12:00           12:04           00:04
Car 2	1554            12:05           12:11           00:06
Car 3	3223            12:06

That way the cars never disappear unless you delete them and all the data is available.
Just looking at the above table I can see Car 3 hasn't entered the drying stage yet

 

[need4excel]

Thanks for your reply. The problem here is there’s another macro placed in the system (created by previous employee) to automactially move the cars over to the next stage