Vlook up values and saving them

need4excel

New Member
Joined
Sep 19, 2018
Messages
2
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:
NameNumber PlateTime of entry
Car 1265112:00pm
Car 2155412:05pm
Car 3322312:06pm

<tbody>
</tbody>

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:
NameNumber PlateTime of entry
Car 1265112:04pm
Car 2155412:11pm

<tbody>
</tbody>

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

Number PlateCleaning
26514 mins (A)
15546 mins (C)

<tbody>
</tbody>


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!!
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
Why don't you have just one table?

Code:
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
 
Last edited:

need4excel

New Member
Joined
Sep 19, 2018
Messages
2
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,407
Messages
5,641,955
Members
417,249
Latest member
serrulate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top