Hello! I'm new here and could use some assistance adapting my current formula to take into account multiple naming conventions for our models. I have an excel sheet that gets raw data for stock supplied/returned dropped into it from several hundred of our customers. Unfortunately, naming conventions have become a huge hindrance making what was an automated process manual.
Feel free to open up the example provided and follow on:
On the 'ALL SALES' sheet you will find in 'C2' my current formula: =SUMIF('WE 0601'!$A:$A,$A2,'WE 0601'!$E:$E)
What this currently does is looks into the raw data we dump into one of our week ending sheets which in the case of 'C2' is the 'WE 0601' sheet as per the header. Firstly it looks up the contents of every item in column A, checks it against 'A2' back in the 'ALL SALES' and for every match it finds it reads the numerical data in column E back in the 'WE 0601' sheet. Then it adds all those numbers found and outputs a total back into 'C2'.
As discussed earlier, the issue this formula has now is that i'm dealing with multiple naming variants for an individual model coming through in our raw data. So i'm wondering is there a way to update my formula to check against all possible naming conventions for an individual model, and in the event that multiple are found it still adds them all together.
I created a 'NAMING CONVENTIONS' sheet that I would like to use as a glossary for the formula to check possible naming conventions.
Basically, as an example if A2 in 'ALL SALES' was RED, I would like it to jump into 'NAMING CONVENTIONS', check all possible naming conventions next to red, then jump back into 'WE0601' check A:A for any and all of the naming conventions, and for everyone found it checks Column E and grabs the numerical value adding them all together and provides a total back in 'C2' where the formula is.
Any help would be much appreciated!
Bookmarks