Forum Discussion
If order number matches a cell on the Indexing Sheet, display name on the Filled Orders sheet
Ugh, my brain hurts..
I would like the Filled Orders sheet to display the name of the client listed on the Order Entry sheet beside the matching order number if their order number matches the order number on the Indexing sheet.
Any help would be amazingly appreciated!
Thanks in advance!
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!
9 Replies
ALLENCANADA Which Excel version are you using (on your Mac)?
The design of the workbook leaves much to be desired. Unless you deliberately removed columns, not to reveal sensitive data. The Data Drop is badly structured, but that's perhaps out of your control.
Anyway, the Indexing sheet seems to take care of the bad "Data Drop" and now you want just a list of names in Filled Orders sheet, without a visible link to the underlying orders. I wonder what purpose that may have and would suggest to add a column into the Order Entry sheet identifying all orders present in the Indexing sheet containing filled orders, I presume. That could be like this:
The formula in B2 being:
=IF(ISNA(MATCH(A2,'Indexing Sheet'!$D$2:$D$500,0)),"-","YES")..... and copy it down.
You could use a similar technique to create a list of names in the Filled Orders sheet, if that really what you want.
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?
