0

I need advice as to how I can create a data entry worksheet in Excel and have it transform into a table in a separate worksheet. This is used for tracking the location of items shipped to different locations.

Sample Data - this is the page for data entry

Based on the data, plot into a table like this for easier reference

Once an item is taken back, the information is deleted and the shipping process continues. Not sure if this can be done in Excel but am just exploring possibilities.

2
  • "Once an item is taken back, the information is deleted" - do you mean information is deleted from data entry sheet and should be deleted from the other sheet too? How many entries and destinations do you have? Jun 12, 2019 at 3:04
  • Thanks for your reply. The items that are shipped to various location are on loan. So when it is returned, the information should be removed from the 2nd worksheet.. the 2nd worksheet is essentially to provide a pictorial view of where the items had been sent to. Eg. Today item code 15432 is shipped out and data is entered into Sheet 1, this information gets plotted into Sheet 2. As soon as item is returned, the information on Sheet 2 will be removed. I am guessing I should do this manually, just ensuring the formula is still there.
    – Jen
    Jun 14, 2019 at 5:55

1 Answer 1

0

Assuming the source data (1st table) is located in Sheet1, Column A-D, and the 2nd table is in Sheet2, with the "Item Code" text is in A1, "WH1"-"WH3" is in B1-D1.

Put in A2 of sheet2 :

=IF(Sheet1!A2="","",Sheet1!A2)

and drag downwards. Then in B2, put :

=IFERROR(INDEX(Sheet1!$C:$C,MATCH(1,INDEX((Sheet1!$A:$A=$A2)*(Sheet1!$B:$B=B$1),0,1),0))&" ("&TEXT(INDEX(Sheet1!$D:$D,MATCH(1,INDEX((Sheet1!$A:$A=$A2)*(Sheet1!$B:$B=B$1),0,1),0)),"MM/DD/YY")&")","")

and drag till D6.

Idea : use multiple criteria index match to 'load' the values based on values in columnA Sheet2 and Row1 Sheet2.

Hope it helps. (:


Extra info :

The $ in the formula are used to 'lock' the column/row when we copy/drag the 1 cell formula. It saves a LOT of formula re-write when some of the formula element doesn't change per row/column.

6
  • System prompted "Formula is missing of a parenthesis". Could you kindly help me to check as I am pretty lost in the formula :( . Still need to do more reading up on the multiple criteria index match to understand better. Thanks heaps!
    – Jen
    Jun 14, 2019 at 5:57
  • Ops.. my bad.. I missed some part when I copying it.. sorry. | Corrected.
    – p._phidot_
    Jun 14, 2019 at 6:29
  • Thanks a lot! It works perfectly!!
    – Jen
    Jun 17, 2019 at 4:16
  • Glad it solves. (added some info in the answer).. Please click on the 'tick' to mark this question as answered. ( :
    – p._phidot_
    Jun 17, 2019 at 4:56
  • Thanks for the extra info. I do understand how the $ is used in the formula but still trying to learn how to use multiple criteria index match ^-^ Thanks a lot.
    – Jen
    Jun 19, 2019 at 14:47

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .