Share via

Need to update data based on most recent entry/date

Anonymous
2017-10-14T22:59:22+00:00

Here is the scenario:

I have two sheets: Sheet 1 is an inventory activity log which is a running list updated whenever an item's status changes, Sheet 2 is a list of inventory items and their current status, location, etc. (This is a much more extensive spreadsheet - I shrunk it down drastically to ask this question)

I need a way for the most recent information entered in Sheet 1 (Inventory Activity Log) to automatically update the appropriate cells in Sheet 2 (Dry Suit Inventory Status).

Inventory Activity Log (NOTICE INVENTORY #17 IS ENTERED TWICE):

INVENTORY ACTIVITY LOG
Date Item Inventory # Status Location Notes
1/1/2017 Dry Suit 21 In Storage Room 2 Rack 3
1/2/2017 Dry Suit 17 In Storage Room1 Rack 4
1/3/2017 Dry Suit 22 In Service Dive Boat Community Suit
1/4/2017 Dry Suit 17 In Service Dive Boat Community Suit
1/5/2017 Dry Suit 20 In Storage Dive Boat Rack 3
1/6/2017 Dry Suit 23 In Service Dive Boat Community Suit

Dry Suit Inventory Status:

Dry Suit Inventory Status
Make Model Size Serial # Inventory # Status Location Notes
Viking Ice Commander L 12345 17
Viking Ice Commander L 12346 18
Viking Ice Commander L 12347 19
Viking Ice Commander XL 12348 20
Viking Ice Commander XL 12349 21
Viking Ice Commander XXL 12350 22

I need to find a way to fill in the Status, Location, and Notes columns with the most recent entry for the corresponding inventory#. 

Please help, I'm stumped!

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Ashish Mathur 101.9K Reputation points Volunteer Moderator
2017-10-14T23:53:28+00:00

Hi,

Enter this formula in cell F4 of sheet2 and copy down/right

=LOOKUP(2,1/('sheet1'!$C$4:$C$9=$E4),'sheet1'!D$4:D$9)

Hope this helps.

Was this answer helpful?

1 person found this answer helpful.
0 comments

9 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ashish Mathur 101.9K Reputation points Volunteer Moderator
    2017-10-16T01:52:22+00:00

    Hi,

    I cannot understand your first question.  As regards the second one, try this revised formula in cell F4

    =IF(LOOKUP(2,1/('sheet1'!$C$4:$C$9=$E4),'sheet1'!D$4:D$9)=0,"",

    LOOKUP(2,1/('sheet1'!$C$4:$C$9=$E4),'sheet1'!D$4:D$9))

    Hope this helps.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2017-10-15T01:39:33+00:00

    Two follow up questions:

    First Question: I am starting this inventory tracking program from scratch, so there will actually be no data in Sheet 1 until people start moving things in and out of service/storage. That sheet will only show changes in the status, location, notes.

    So, how can I have a beginning value in these cells that contain your formula, if that item has not been entered into the activity log yet?

    For example: On day one of implementing this inventory tracking program, lets say that Inventory#17 is in storage, and Inventory#18 is in service. How can I show that status prior to their inventory number being entered into the Activity Log on Sheet 1?

    Second Question: If create an entry in Sheet 1, but "Location" blank in Sheet 1, for example, I get a "0" in the "Location" column in Sheet 2. How can I prevent the "0" and just leave "Location" blank for that inventory item on Sheet 2?

    Thanks again!

    Was this answer helpful?

    0 comments
  3. Ashish Mathur 101.9K Reputation points Volunteer Moderator
    2017-10-15T00:22:37+00:00

    You are welcome.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2017-10-15T00:20:51+00:00

    Thank you! I think that'll do it! I'll integrate it in to the rest of my spreadsheet and will check back to this post if I run into any other problems!

    Thanks again!

    Was this answer helpful?

    0 comments