Suggestion needed - reference cell looks at one range then see if any

[hawley]

My brain is out of ideas so was hoping someone else had a thought or could nudge me in the right direction.

Here is what will happen:
1. Data is dumped from a database and will be put in the Data Worksheet (columns A-D).
2. Column D from the Data worksheet will be copied and paste into the Reference/Entry Worksheet in column A.
3. In the Data Worksheet column E, F, G, and H have a formula. Theses are the different versions of what can be entered in the Reference/Entry Worksheet (column D-G is the entry, column C is the final result of all) by the user. (col A, col A and B, col A, B, and C, col B, col B and C, etc...) There are more combo's then I what I have in the example.
4. In the Reference/Entry Worksheet the user will enter in column D, E, F, and or G. Each line will be different. Column C is a formula that will concatenate what they have entered. This column should match one of the cells in the Data Worksheet in column E,F,G, or H.
5. I want to make sure that what the user enters can account for all items in column A on the Reference/Entry Worksheet. So I want to take the value in the Reference/Entry Worksheet from column A and find the row it is on in the Data sheet and then see if the values for that row from column E,F,G or H are found in the Reference/Entry Worksheet in column C. If the value is found then say added, if not then say needed.
6. Just think of Reference/Entry Worksheet as two different sections. A reference section (column A-B) and an entry section (column C-G).


Data Worksheet
A B C D E F G H
104 20 21 CR10600S 104 10420 1042021 1042021DUS
104 20 21 CR10600SX 104 10420 1042021 1042021DUS
104 20 21 CR10603S 104 10420 1042021 1042021DUS
104 20 21 CR10603SX 104 10420 1042021 1042021DUS
104 20 22 CR49605S 104 10420 1042022 1042022HKZ
104 20 22 CR49605SX 104 10420 1042022 1042022HKZ
104 20 23 CR39601P 104 10420 1042023 1042023SPR

Reference/Entry Worksheet
"....In Pricing Sheet" is where I am looking to put needed or added or something like it.
A B C D E F G
STYLE …IN PRICING SHEET Concat DEPT MCL SCL STYLE
CR10600S Added 1042021 104 20 21
CR10600SX Added 1042022 104 20 22
CR10603S Added
CR10603SX Added
CR49605S Added
CR49605SX Added
CR39601P Needed

Any ideas on how to do this in formulas and without code? I have tried and looked into index, match, dget and lookup. I just don't think I am finding the right combination of formulas. Any nudge or thoughts would be helpful. Thanks in advance!!!!!!

 

[SkipVought]

Hi,

1. Your SOURCE data sheet ought to have data headings...
[tt]
Dept MCL SCL Style Concat
104 20 21 CR10600S 1042021
104 20 21 CR10600SX 1042021
104 20 21 CR10603S 1042021
104 20 21 CR10603SX 1042021
104 20 22 CR49605S 1042022
104 20 22 CR49605SX 1042022
104 20 23 CR39601P 1042023
[/tt]
I use the headings to make Named Ranges.

Here's the formula assuming that the Entry sheet has identicak headings where applicable...
[tt]
C2: =INDEX(INDIRECT(C$1),MATCH($A2,Style,0),1)
[/tt]
Copy across and down



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[hawley]

I forgot the headings in my example...sorry about that. You are correct in what you have for headings though.

My only question with your formula is are you putting that formula in C2 on the Reference sheet? If so that is not really what I am looking for, unless I misunderstand. C2 on the Reference sheet will be a concat field for the values placed in D-G. I am looking for a formula to go into column B to say added or needed. The only thing is that the style in the Entry sheet doesn't really go with the concat field of that row. I guess maybe instead of two sheets think of having three sheets. The Data, Reference and Style worksheet.

Data Worksheet
A B C D E F G H
Dept MCL SCL STYLE A AB ABC ABCD
104 20 21 CR10600S 104 10420 1042021 1042021DUS
104 20 21 CR10600SX 104 10420 1042021 1042021DUS
104 20 21 CR10603S 104 10420 1042021 1042021DUS
104 20 21 CR10603SX 104 10420 1042021 1042021DUS
104 20 22 CR49605S 104 10420 1042022 1042022HKZ
104 20 22 CR49605SX 104 10420 1042022 1042022HKZ
104 20 23 CR39601P 104 10420 1042023 1042023SPR

Reference/Entry Worksheet
A B C D E F
Concat DEPT MCL SCL STYLE Price
1042021 104 20 21 15.00
1042022 104 20 22 25.00

Style Worksheet
"....In Pricing Sheet" is where I am looking to put needed or added or something like it.
A B
STYLE Style Priced
CR10600S Added
CR10600SX Added
CR10603S Added
CR10603SX Added
CR49605S Added
CR49605SX Added
CR39601P Needed

1. Data from the database is dumped into the Data Worksheet in columns A-D. E-H are concat fields showing every possibility of how the fields can be combined together. (I did shorten this for the example.)
2. The user will enter in data on the Reference/Entry Worksheet for columns B-F. Column A will be a formula that will concat column B-E. (This will be used to relate back to the Data Worksheet)
3. Row D from the Data worksheet will be copied into column A on the Style Worksheet.
4. I need a formula in Column B of the Style Worksheet that says if the style was priced on the Reference/Entry Worksheet. Although I can't use the style as a Reference point. I have to look back on the Data Worksheet for that style row and then see if any of the variations from E-H are on the Reference/Entry Worksheet in column A. If any varaition exists then put added...otherwise needed.

Any ideas or suggestions would be greatful!!!

 

[SkipVought]

1. Columns A-H Named Ranges on Data

2. Column A-F Named Ranges on Refrence/Entry

3. Formula in Style B2...
[tt]
B2: =If(isna(Match(index(ABC,Match(A2,STYLE,0),1),Concat,0)),"Needed","Added")
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

If it were me, I would use MS Query to copy return the data from Data sheet Style column to the Style sheet. Once the QT has been added (one time), all you need do to get a new resultset into Style, is Data > Refresh. If you CHECk the Data Range Properties: Fill down formulas in columns adjacent to data, then the data is there AND the formulas are present in EVERY ROW OF DATA.

faq68-5829


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kwbMitel]

This looks like a job for Vlookup to me.

Vlookup and be used in a formula.

Vlookup will search a defined array (first column used for search) and return a value in adjacent cells of the same row for comparison purposes with the data entry.

*******************************************************
Occam's Razor - All things being equal, the simplest solution is the right one.

 

[hawley]

Thank you all for the suggestions.

kwbMitel - I tried vlookup but because the column I want to look at is not always the frist column vlookup wasn't working.

Skip - I will look at MS Query and see what it does. Thanks.

I did get another solution on a different forum that worked as well. Here is the solution in case you are interested.

For simplicity, let's assume the following...
A1:H8 contains:
Code:
Data Worksheet
A B C D E F G H
Dept MCL SCL STYLE A AB ABC ABCD
104 20 21 CR10600S 104 10420 1042021 1042021DUS
104 20 21 CR10600SX 104 10420 1042021 1042021DUS
104 20 21 CR10603S 104 10420 1042021 1042021DUS
104 20 21 CR10603SX 104 10420 1042021 1042021DUS
104 20 22 CR49605S 104 10420 1042022 1042022HKZ
104 20 22 CR49605SX 104 10420 1042022 1042022HKZ
104 20 23 CR39601P 104 10420 1042023 1042023SPR

A12:E14 contains:
Code:
Reference/Entry Worksheet
A B C D E F
Concat DEPT MCL SCL STYLE Price
1042021 104 20 21 15.00
1042022 104 20 22 25.00

A18:B25 contains:
Code:
Style Worksheet
"Syle Priced" is where I am looking to put needed or added or something like it.
A B
STYLE Style Priced
CR10600S Added
CR10600SX Added
CR10603S Added
CR10603SX Added
CR49605S Added
CR49605SX Added
CR39601P NeededTry...

B19, copied down:

=IF(SUMPRODUCT(--ISNUMBER(MATCH($A$13:$A$14,INDEX($E$2:$H$8,MATCH(A19,$D$2:$D$8,0),0),0))),"Added","Needed")

 

[SkipVought]

You REALLY ought to understand and use the power of Named Ranges.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[hawley]

Thanks for the suggestion. I do understand Named Ranges and use them often. I was just trying to understand how to do this before I added named ranges into the equation.