SOLVED

If order number matches a cell on the Indexing Sheet, display name on the Filled Orders sheet

%3CLINGO-SUB%20id%3D%22lingo-sub-3329420%22%20slang%3D%22en-US%22%3EIf%20order%20number%20matches%20a%20cell%20on%20the%20Indexing%20Sheet%2C%20display%20name%20on%20the%20Filled%20Orders%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3329420%22%20slang%3D%22en-US%22%3E%3CP%3EUgh%2C%20my%20brain%20hurts..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20the%20Filled%20Orders%20sheet%20to%20display%20the%20name%20of%20the%20client%20listed%20on%20the%20Order%20Entry%20sheet%20beside%20the%20matching%20order%20number%20if%20their%20order%20number%20matches%20the%20order%20number%20on%20the%20Indexing%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20amazingly%20appreciated!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3329420%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3330478%22%20slang%3D%22en-US%22%3ERe%3A%20If%20order%20number%20matches%20a%20cell%20on%20the%20Indexing%20Sheet%2C%20display%20name%20on%20the%20Filled%20Orders%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3330478%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368849%22%20target%3D%22_blank%22%3E%40ALLENCANADA%3C%2FA%3E%26nbsp%3BWhich%20Excel%20version%20are%20you%20using%20(on%20your%20Mac)%3F%3C%2FP%3E%3CP%3EThe%20design%20of%20the%20workbook%20leaves%20much%20to%20be%20desired.%20Unless%20you%20deliberately%20removed%20columns%2C%20not%20to%20reveal%20sensitive%20data.%26nbsp%3BThe%20Data%20Drop%20is%20badly%20structured%2C%20but%20that's%20perhaps%20out%20of%20your%20control.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnyway%2C%20the%20Indexing%20sheet%20seems%20to%20take%20care%20of%20the%20bad%20%22Data%20Drop%22%20and%20now%20you%20want%20just%20a%20list%20of%20names%20in%20Filled%20Orders%20sheet%2C%20without%20a%20visible%20link%20to%20the%20underlying%20orders.%20I%20wonder%20what%20purpose%20that%20may%20have%20and%20would%20suggest%20to%20add%20a%20column%20into%20the%20Order%20Entry%20sheet%20identifying%20all%20orders%20present%20in%20the%20Indexing%20sheet%20containing%20filled%20orders%2C%20I%20presume.%20That%20could%20be%20like%20this%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Riny_van_Eekelen_0-1651821485999.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F369728i88610C09B17254C3%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Riny_van_Eekelen_0-1651821485999.png%22%20alt%3D%22Riny_van_Eekelen_0-1651821485999.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThe%20formula%20in%20B2%20being%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DIF(ISNA(MATCH(A2%2C'Indexing%20Sheet'!%24D%242%3A%24D%24500%2C0))%2C%22-%22%2C%22YES%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E.....%20and%20copy%20it%20down.%3C%2FP%3E%3CP%3EYou%20could%20use%20a%20similar%20technique%20to%20create%20a%20list%20of%20names%20in%20the%20Filled%20Orders%20sheet%2C%20if%20that%20really%20what%20you%20want.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3331997%22%20slang%3D%22en-US%22%3ERe%3A%20If%20order%20number%20matches%20a%20cell%20on%20the%20Indexing%20Sheet%2C%20display%20name%20on%20the%20Filled%20Orders%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3331997%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHey%20Riny!%3C%2FP%3E%3CP%3EThanks%20for%20the%20attempt!%20You're%20right%20in%20a%20few%20areas.%20The%20Data%20drop%20is%20coming%20from%20an%20excel%20export%20from%20another%20system%20that%20isn't%20organized%20very%20well.%20The%20purpose%20of%20this%20system%20is%20to%20take%20that%20export%20(drop%20it%20in%20the%20'data%20drop')%20index%20it%2C%20and%20match%20the%20data%20to%20mine%20in%20the%20'order%20input'%20and%20create%20a%20list%20of%20the%20filled%20orders%20that%20will%20automatically%20update%20their%20location%20statues%20every%20time%20that%20I%20pull%20a%20new%20export%20from%20the%20other%20system%20and%20paste%20it%20into%20the%20'data%20drop'.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20you%20can%20see%2C%20I've%20updated%20the%20'Filled%20Orders'%20tab%20to%20contain%20the%20full%20suite%20of%20information%20I%20was%20hoping%20to%20populate%20there.%20I%20had%20left%20it%20out%20cause%20once%20I%20got%20the%20right%20formula%20to%20complete%20this%20task%2C%20I%20would%20have%20been%20able%20to%20finish%20the%20page%20myself%20and%20didn't%20want%20to%20trouble%20anyone%20nice%20to%20help%20me%20with%20additional%20coding.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBasically%2C%20most%20of%20the%20data%20is%20already%20on%20'indexing%20sheet'%20but%20the%20other%20system%20never%20links%20the%20order%20%2F%20vehicle%20%2F%20location%20entries%20with%20the%20clients%20name.%20So%20I'm%20looking%20to%20match%20the%20order%20number%20with%20the%20'indexing%20sheet%20order%20number%20and%20'order%20entry'%20order%20number%20that%20will%20link%20the%20name.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3332045%22%20slang%3D%22en-US%22%3ERe%3A%20If%20order%20number%20matches%20a%20cell%20on%20the%20Indexing%20Sheet%2C%20display%20name%20on%20the%20Filled%20Orders%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3332045%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368849%22%20target%3D%22_blank%22%3E%40ALLENCANADA%3C%2FA%3E%26nbsp%3BYou%20can%20take%20the%20formula%20I%20mentioned%20before%20and%20have%20it%20point%20to%3CFONT%20color%3D%22%23DF0000%22%3E%26nbsp%3B'Order%20Entry'!A2%3C%2FFONT%3E%20rather%20than%20just%3CFONT%20color%3D%22%23DF0000%22%3E%20A2%3C%2FFONT%3E.%20That%20works%20similar.%3C%2FP%3E%3CP%3EBut%20there%20are%20perhaps%20better%20ways%20if%20you%20are%20on%20a%20modern%20Excel%20version.%20Which%20one%20are%20you%20using%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3332053%22%20slang%3D%22en-US%22%3ERe%3A%20If%20order%20number%20matches%20a%20cell%20on%20the%20Indexing%20Sheet%2C%20display%20name%20on%20the%20Filled%20Orders%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3332053%22%20slang%3D%22en-US%22%3EExcel%2016.6%20for%20Mac.%20This%20doc%20will%20also%20be%20a%20shared%20live%20document%20used%20on%20Excel%20online.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3332111%22%20slang%3D%22en-US%22%3ERe%3A%20If%20order%20number%20matches%20a%20cell%20on%20the%20Indexing%20Sheet%2C%20display%20name%20on%20the%20Filled%20Orders%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3332111%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368849%22%20target%3D%22_blank%22%3E%40ALLENCANADA%3C%2FA%3E%26nbsp%3BI%20take%20it%20that's%20with%20an%20MS365%20subscription.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3332113%22%20slang%3D%22en-US%22%3ERe%3A%20If%20order%20number%20matches%20a%20cell%20on%20the%20Indexing%20Sheet%2C%20display%20name%20on%20the%20Filled%20Orders%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3332113%22%20slang%3D%22en-US%22%3ECorrect!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3332550%22%20slang%3D%22en-US%22%3ERe%3A%20If%20order%20number%20matches%20a%20cell%20on%20the%20Indexing%20Sheet%2C%20display%20name%20on%20the%20Filled%20Orders%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3332550%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368849%22%20target%3D%22_blank%22%3E%40ALLENCANADA%3C%2FA%3E%26nbsp%3BThe%20Data%20Drop%20wasn't%20so%26nbsp%3B%20bad%20after%20all%2C%20although%20I%20don't%20like%20the%20merged%20cells.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESince%20you%20are%20an%20MS365%20subscriber%20and%20intend%20to%20also%20use%20this%20on%20the%20web%2C%20I%20believe%20you%20can%20get%20rid%20of%20the%20indexing%20sheet%20altogether%20and%20make%20use%20of%20modern%20dynamic%20array%20functions%2C%20some%20named%20ranges%26nbsp%3B%20and%20a%20filter%20on%20the%20Filled%20Orders%20sheet.%20Please%20see%20attached%20and%20let%20me%20know%20if%20you%20can%20work%20with%20this.%3C%2FP%3E%3CP%3ENote%20that%20I%20changed%20the%20order%20of%20the%20columns%20in%20the%20Filled%20Order%20sheet%20to%20make%20it%20easier.%20One%20single%20formula%20in%20B2%20now%20fills%20the%20table%20dynamically.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DFILTER(FILTER(dataDrop%2CoredrType%3D%22N1%3ASold%20Order%22)%2Cselected)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EAnd%20a%20simple%20XLOOKUP%20fills%20in%20the%20Client%20name.%3C%2FP%3E%3CP%3EOn%20top%20of%20that%2C%20the%20file%20size%20reduced%20from%205.6MB%20to%2036KB.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHopefully%2C%20this%20will%20%22unhurt%22%20your%20brain.%20Have%20a%20good%20week-end!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3332752%22%20slang%3D%22en-US%22%3ERe%3A%20If%20order%20number%20matches%20a%20cell%20on%20the%20Indexing%20Sheet%2C%20display%20name%20on%20the%20Filled%20Orders%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3332752%22%20slang%3D%22en-US%22%3EWell%2C%20isn't%20that%20nifty...%20Does%20the%20filled%20orders%20eta%20data%20containers%20dynamically%20adjust%20as%20more%20items%20match%20the%20criteria%20from%20the%20data%20drop%20or%20is%20it%20capped%20at%20the%2051%20lines%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3332856%22%20slang%3D%22en-US%22%3ERe%3A%20If%20order%20number%20matches%20a%20cell%20on%20the%20Indexing%20Sheet%2C%20display%20name%20on%20the%20Filled%20Orders%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3332856%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368849%22%20target%3D%22_blank%22%3E%40ALLENCANADA%3C%2FA%3E%26nbsp%3BIt%20will%2C%20but%20you%20have%20to%20make%20sure%20that%20the%20name%20ranges%20that%20I%20called%20%22dataDrop%22%20and%20%22ordrType%22%20(sorry%20for%20the%20type%20there)%20are%20updated%20correctly%20if%20the%20data%20range%20expands.%3C%2FP%3E%3CP%3EBut%20give%20a%20try.%20Update%20some%20records%20in%20the%20Data%20Drop%20and%20Order%20Entry%20sheets%20manually%20to%20convince%20yourself%20that%20it%20works.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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!

 

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:

Riny_van_Eekelen_0-1651821485999.png

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.

@Riny_van_Eekelen 

 

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?

Excel 16.6 for Mac. This doc will also be a shared live document used on Excel online.
best response confirmed by ALLENCANADA (Occasional Contributor)
Solution

@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!

Well, isn't that nifty... Does the filled orders eta data containers dynamically adjust as more items match the criteria from the data drop or is it capped at the 51 lines?

@ALLENCANADA It will, but you have to make sure that the name ranges that I called "dataDrop" and "ordrType" (sorry for the type there) are updated correctly if the data range expands.

But give a try. Update some records in the Data Drop and Order Entry sheets manually to convince yourself that it works.