FORMULA Help

[Dean Hewer]

Greetings NG,

Simple request. Excel file has multiple sheets.

Sheet 1 National MASTER CUSTOMER LIST (About 2100 records).
Sheet 2 Regions List customers. A=customer number, B to F is the address of
the location (About 2300 records).
Sheet 3 - 33 are the sales reps. Each of them has customers assigned to
them. A=customer number, etc.
In the Cell B1 on each of these sheets is the Reps Name.
The range of customers is from A3 to A100

Here is what I like to do.
In Sheet 2, H10, I want to place a formula that will find the matching
customer number and then place the Reps name in that cell. So it is looking
for a match of the customer number in SHEET to and finding that match
somewhere on sheets 3 - 33. If it does not find a match, I would like to
enter the value 'UNASSIGNED'.

Any help would be greatly appreciated. I will check this post from time to
time. Thanks


_____________________________
Dean Hewer

 

[Don Guillett]

Have a look in the help index for MATCH to find the row which you can then
use within an INDEX formula

 

[aresen]

Greetings NG,

Simple request. Excel file has multiple sheets.

Sheet 1 National MASTER CUSTOMER LIST (About 2100 records).
Sheet 2 Regions List customers. A=customer number, B to F is the address of
the location (About 2300 records).
Sheet 3 - 33 are the sales reps. Each of them has customers assigned to
them. A=customer number, etc.
In the Cell B1 on each of these sheets is the Reps Name.
The range of customers is from A3 to A100

Here is what I like to do.
In Sheet 2, H10, I want to place a formula that will find the matching
customer number and then place the Reps name in that cell. So it is looking
for a match of the customer number in SHEET to and finding that match
somewhere on sheets 3 - 33. If it does not find a match, I would like to
enter the value 'UNASSIGNED'.

Any help would be greatly appreciated. I will check this post from time to
time. Thanks


_____________________________
Dean Hewer

Unless you go with a macro, you're looking at a rather complex
solution. Using MATCH, the formula would have to comprehend every sheet
individually and concatenate the results. For example,
=IF(ISNA(MATCH(H10,'Sheet 3'!A:A,0)),"",'Sheet 3'!B1) interrogates the
first sheet. Expanding this, =IF(ISNA(MATCH(H10,'Sheet
3'!A:A,0)),"",'Sheet 3'!B1)&IF(ISNA(MATCH(H10,'Sheet
4'!A:A,0)),"",'Sheet 4!B1)...&=IF(ISNA(MATCH(H10,'Sheet
33'!A:A,0)),"",'Sheet 33'!B1) gets you a response from each sheet.
Finishing it off, you get =IF(LEN(IF(ISNA(MATCH(H10,'Sheet
3'!A:A,0)),"",'Sheet 3'!B1)&IF(ISNA(MATCH(H10,'Sheet
4'!A:A,0)),"",'Sheet 4!B1)...&=IF(ISNA(MATCH(H10,'Sheet
33'!A:A,0)),"",'Sheet 33'!B1))=0,"UNASSIGNED",IF(ISNA(MATCH(H10,'Sheet
3'!A:A,0)),"",'Sheet 3'!B1)&IF(ISNA(MATCH(H10,'Sheet
4'!A:A,0)),"",'Sheet 4!B1)...&=IF(ISNA(MATCH(H10,'Sheet
33'!A:A,0)),"",'Sheet 33'!B1)). This doesn't check to see if multiple
reps are claiming the customer which is another problem.

An easier approach requiring a disciplined sheet naming convention is
to use a 3D summation. For example, Let's say the first sheet is named
"Sheet01" and the last named "Sheet31". All other sheets are named
accordingly and MUST reside between these two worksheets. On each sheet
you include a formula in cell C1 (arbitrary, but must be the same on
each worksheet and not in column A) "=IF(ISNA(MATCH('Sheet
2'!H10,A:A,0)),"",1001)". The 1001 will refer to the first sheet. Use
1002 for the second, 1003 for the third and so on.

Now on the Sheet 2 in I10 you have the formula
=SUM('Sheet01:Sheet31'!C1). If there's no match you'll get 0. If
there's a match you'll get something like 1017. Anything greater than
1031 like 3063 indicates more than one match in this case, 3 matches.
Now in H10 the formula would be
=IF(I10=0,"Unassigned",IF(10>1031,"Multiple
reps",INDIRECT("Sheet"&RIGHT(H10,2)&"!B1")).

One last thing. Remember Excel will look at every sheet bounded by the
two worksheets. If you move one out of the range (before Sheet01 or
after Sheet31) it will not be included.