Forum Discussion
EXCEL QUESTION
- I have a spreadsheet with 3 tabs, in each tab I have numbered boxes. They are now separated per tabs because they are in different stages/status of a specific process, for example most recently date of arrival & departure to & from a specific place.
That said, I have a 4th tab with all those numbered boxes listed and I want to bring (from those 3 different tabs) TO this 4th tab the most recently date of arrival & departure of each box number.
Basically, this 4th table would be a quicky reference showing their last movement (location) & most recently date it was moved.
Thank You very much for your help & contribution 😊
Hi fmdon ,
thank you for further details my suggestion is shown in the attached sample file.
10 Replies
This is a 365 approach that uses a single formula (in this case in cell Q2)
The formula is
= LET( combined, VSTACK( EXPAND(dateExp, , 4,"Exp"), EXPAND(dateImp, , 4,"Imp"), EXPAND(dateEmpty,,4,"Empty") ), sorted, SORT(combined, 2,-1), REDUCE({"Box","Date","Location","Status"}, box, LAMBDA(a,b, VSTACK(a, XLOOKUP(b, TAKE(sorted,,1), sorted)) ) ) )Before stacking the tables an addition field is introduced to record the status of each record. The combined array is sorted to bring the most recent events to the top for XLOOKUP. REDUCE is needed because the result to return is an array of arrays (pretty much like every other calculation you will ever encounter!)
Is the suggestion in the attached sample file similar to what you are looking for? Both formulas must be entered as arrayformulas with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2021.
- yes OliverScheurich it is pretty similar containing those main informations in all the 3tabs, and in this 4th tab where I want to bring the most recently position, I have all the 300 boxes listed!!
Do you see any way of lookup into those 3 different tabs and updating/ bringing to this 4th one (that is my boxes list) always tying up the lookup and the bringing the results always considering the last movement and date ?
Thanks for your time and attention!Attached is my sample file which returns the location of the boxes along with their most recent dates of arrival and departure. However i wonder what the content of the 3rd table is. I understand that there is a 1st table for the dates of arrival and a 2nd table for the dates of departure. Perhaps you can give an example of the data of the 3rd table.
- *The question is*: how to bring to a 4th tab list of boxes (from the 3 tabs) the most update updates position / date of each box events ?
