If...and Then Go To Next Worksheet To Find Corresponding Numbers

[Vinnie Chan]

Hi,

I need help quick!

I have a file with 2 worksheets: Worksheet "Data" contains Store Locations, Store Numbers, Categories, Brands, Sales, Clusters, % Demand, etc.

Under % Demand on Worksheet "Data", I need a formula to look for % Demand from Worksheet "Stores" that related to the same categories, brands and stores numbers.

Please help. Thanks a lot.

Vinnie

[NBVC]

So you're looking for an instance where Categore, brand and store number all match your input values and then return the % demand?

[Vinnie Chan]

NBVC,

I have attaching this file for you. Formula needed under Col G in Worksheet "Data".

Thanks in advance for your help.

[NBVC]

Try again....

[NBVC]

That attachment doesn't really help to answer my question...


also please post an excel sample.

[Vinnie Chan]

I try to zip the file below 100 KB, and I've already removed most of the data but it still over 100 KB. That's why I try pdf it.

Please tell me how to attach the file.

Thanks.

[NBVC]

I am going to then make some assumptions...

Let's say you need to match all 3 of category, brand and store number, which are displayed in A1, B1 and C1 of Data sheet.

Now let's say that your Stores sheet lists the categories in column A, the brands in column B and the store numbers in column C....and the % Demand in column D.

a formula such as the following should extract the % Demand that corresponds to the 3 criterial in A1, B1 and C1 of Data Sheet.

Please Login or Register  to view this content.

after adjusting all the ranges to suit, you must then confirm the formula with CTRL+SHIFT+ENTER not just ENTER. You will see { } brackets appear around the formula. Then you can copy it down.

[Vinnie Chan]

Thanks for your quick reply.

I try your formula, but didn't really work, it return as #N/A.

It is more complicated than the file I attached. There are 29 brands with 3 categories and hundreds of stores #s. Total of over 20000 entries. Each brand has its % Demand column.

It won't allow me to attach the exact file because of the file size.

Would it be too hard to just do it in one formula?

Thanks for help.

[NBVC]

Why not just create a sample workbook with some sample data showing what you want to achieve...

[Vinnie Chan]

I tried very hard to reduce the file size.

Same as I mentioned before. In Worksheet "Data", 1st match Category, then Brand and Store Number, after match of these, go to "Store" to find % Demand, i.e., Row 2 Bar Soap, Alpen, Store Number 642. go to "Store" look for Alpen Toilet Soap, % Demand (which is Col I) to find Store Number 642. That is the number I need to put in Worksheet "Data" G2.

Thanks for your help.

[NBVC]

I think the formula I previously suggested is indeed what you are looking for.

You just have to adjust the ranges and references to suit your data layout.

Don't forget that for the formula to work, you must then confirm it by holding the CTRL+SHIFT+ENTER keys, not just the ENTER key.

If you can tell me the exact ranges in sheet "Store" and the exact positions of the 3 things to look for in sheet "Data", I can try to adjust the formula for you...

[Vinnie Chan]

Hi NBVC,

Please read #10, and I've attached sample of the file there.

Thanks a lot for your help.

Vinnie

[NBVC]

You're going to have to give me a hint..

What is the answer for the first few and how did you get those answers?

Use actual cell references in your answer.

[Vinnie Chan]

Sorry, I don't get what your question is.

If you mean the numbers in Col F "Cluster", they're from other file.

I believe both worksheets are generated from other program developed by our company, but I'm not familiar with that.

I think under the 3 criterias, I need to look for % Demand nos. in Worksheet "Stores", therefore, I might need to change the column headings e.g. LIFE (CONTROL LABEL) - KILOGRAMS (TOILET SOAP) ** to read LB BAR SOAP ALL TYPE, otherwise, I can't see any relationship between 2 worksheets.

I need to resolve this very soon. Please, please help.

Thanks.

[NBVC]

That's kind of my point.... I don't see where the relations are and if you can't than how would anyone outside your company....

You need to to be able to find matches by making sure that the headers and row records have identical (or something close) matches in the Data sheet....

Like what is "BAR SOAP ALL TYPES (NOT BABY)"? How would you match that in the Store sheet?

What is "ALPEN"? How would you match that?

etc...

by the way I see you must be from the GTA.

[Vinnie Chan]

You're right. I'm in Markham, Ontario.

This is the question from other person in other department...anyway...

The headings in worksheet "Stores" aren't the same as those in "Data". If I change the column headings in "Stores" to be identical. ie. Alpen is the same as Alpen Secret in worksheet "Store" and Life is "LB", etc. However, there are 2 categories, Bar Soap and Body Wash. Some brands have both. What's the better way to change headings in "Stores"?

Under the 3 criterias in worksheet "Data": Fineline (Category), Brand and Store Number, we need to find % Demand in worksheet "Stores" which matches all 3 criterias in "Data.

NBVC, is it much clear?

Thanks a lot for your help.

[NBVC]

Alright, see if this helps get us on the way....

First go to the Stores Sheet and change the formula in B3 to =MID(A3,5,3)+0 and copy it down all the way.

This converts the store number to an actual number to match the store number format in column B of Data sheet.

The try this formula in G2 of Data Sheet:

Please Login or Register  to view this content.

copied down.

You will see some values...confirm they are right. The #N/A's mean no matches found...probably because you slimmed down the sheet.

You may have to adjust the bottoms of the ranges in the formula to ensure you get all your original data....

What I did to match the right column is to look for the word that is in column D of Data sheet (i.e. the brand) in row 1 of Stores sheet.... then if the word "Soap" appears in column C of Data sheet, then use only the range of columns in Stores Sheet that have contain the same word (ie. columns E:Y in this sample), otherwise look in the remaining columns (ie. columns Z:AK). The +1 is to offset by 1 column since the % Demand is the middle column of each brand.

Hope this helps you.

[Vinnie Chan]

I'm going to try it when I get to my office.

Will let you know if it works.

Thanks a lot for your help.

[Vinnie Chan]

Thank you very much NBVC.

It works perfect.

Vinnie

[NBVC]

That's great!

Thanks for the feedback!