Forum Discussion
If order number matches a cell on the Indexing Sheet, display name on the Filled Orders sheet
ALLENCANADA The Data Drop wasn't so bad after all, although I don't like the merged cells.
Since you are an MS365 subscriber and intend to also use this on the web, I believe you can get rid of the indexing sheet altogether and make use of modern dynamic array functions, some named ranges and a filter on the Filled Orders sheet. Please see attached and let me know if you can work with this.
Note that I changed the order of the columns in the Filled Order sheet to make it easier. One single formula in B2 now fills the table dynamically.
=FILTER(FILTER(dataDrop,oredrType="N1:Sold Order"),selected)And a simple XLOOKUP fills in the Client name.
On top of that, the file size reduced from 5.6MB to 36KB.
Hopefully, this will "unhurt" your brain. Have a good week-end!
Hey Riny!
Thanks for the attempt! You're right in a few areas. The Data drop is coming from an excel export from another system that isn't organized very well. The purpose of this system is to take that export (drop it in the 'data drop') index it, and match the data to mine in the 'order input' and create a list of the filled orders that will automatically update their location statues every time that I pull a new export from the other system and paste it into the 'data drop'.
As you can see, I've updated the 'Filled Orders' tab to contain the full suite of information I was hoping to populate there. I had left it out cause once I got the right formula to complete this task, I would have been able to finish the page myself and didn't want to trouble anyone nice to help me with additional coding.
Basically, most of the data is already on 'indexing sheet' but the other system never links the order / vehicle / location entries with the clients name. So I'm looking to match the order number with the 'indexing sheet order number and 'order entry' order number that will link the name.
ALLENCANADA You can take the formula I mentioned before and have it point to 'Order Entry'!A2 rather than just A2. That works similar.
But there are perhaps better ways if you are on a modern Excel version. Which one are you using?