Select all cells not previously selected from a list (range of cells)

[creeker69]

Is there a way to select all the remaining Makes in this example?

The Formula in D4 is =SUMPRODUCT((MAKE=$C4)*(YEARSOLD=$B$2)*(STORE="RED")*(SALETYPE="RETAIL")*1
In E4 =SUMPRODUCT((MAKE=$C4)*(YEARSOLD=$B$2)*(STORE="RED")*(SALETYPE="RETAIL")*COST/$D4))

Occasionally we sell an odd MAKE that is not listed in B4:B36.
I would like to add a single line on B37 (which I will call MISC as a MAKE) to capture all the previously unreported Makes.

I tried to name B4:B36 a range called Valid_Makes.

I am failing but I am trying to tell Excel to do this :

=if what is in B37 is NOT a Valid_Makes THEN
SUMPRODUCT((MAKE=$C4)*(YEARSOLD=$B$2)*(STORE="RED")*(SALETYPE="RETAIL")*1)
ELSE ""

I hope I have explained myself correctly. If we sell a MAKE that is NOT in our usual list, then gather all of the odd-ball MAKES and report them on one line.

STORE, MAKE, SALETYPE, YEARSOLD, COST are all named ranges on the data sheet in my workbook.

I thank you in advance,
Ron

 

[SkipVought]

HI,

1) your example is only a pic. Not acceptable! Either provide a table/text example or upload your workbook.

2) Where do you use Valid_Makes in your formula?

3) Where is Store defined?

Can't make heads or tails of your workbook without your workbook or lots more definition of all your named

Actually your table is too pretty. You need ONE ROW OF HEADINGS. Also your "odd" row needs to be included in your table/name reference. I prefer to use the Structured Table feature, introduced in Excel 2007. No need for Named arranges in most conditions!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[creeker69]

Skip,

Thanks for the quick reply.



1) your example is only a pic. Not acceptable! Either provide a table/text example or upload your workbook.
I have uploaded my workbook.
2) Where do you use Valid_Makes in your formula?
I have yet to use VALID_MAKES in my formula as I cannot figure out how to get Excel to do what I am trying to do. And that is, Add/Select (in C39) all Makes that were NOT yet selected in the Makes above it (C4:C38) which is the range of VALID_MAKE.
3) Where is Store defined?
The STORE and all the other ranges are defined on a data sheet in the workbook; with the exception of VALID_MAKE which is defined on the WHSL UNITS BY MAKE worksheet.

I apologize as I was not aware the I could upload the workbook and was hoping that a picture and an explanation cculd do it. Me ineptitude in Excel even transcends itself into my request and I apologize.

Thanks and have a Happy and safe 4th!

Ron

 

[JVFriederick]

Just a hunch / suggestion......

Why not add single row with formula SUMPRODUCT((YEARSOLD=$B$2)*(STORE="RED")*(SALETYPE="RETAIL")*1)-SUM(B4:B36)

That would calculate the number not shown in the fixed list.

JVF

 

[SkipVought]

Okay, I made the following changes:

1. Changed the heading in WHSL-RETAIL-DATA-ALL column A to MANUAL PICKED, (removed the LineFeed)

2. Named the range in that column to MANUAL_PICKED. I have no idea why you have all those empty rows??? That is not a best and accepted practice.

3. Named the $B$2 cell, SelectedYear.

3a. Named C2 cell SelectedType containing WHSL

3b. Entered RED, BLUE, WHITE in ROW 1 in all five columns in each group.

3c. Changed the heading from 2015 AVG DAYS to 2015 AVG AGE

4. Changed the formula in D4 to
[tt]
=SUMPRODUCT((IF($C4="MISC",MANUAL_PICKED="X",MAKE=$C4))*(YEARSOLD=SelectedYear)*(STORE=E$1)*(SALETYPE=SelectedType)*1)
[/tt]
...and entered it as an ARRAY FORMULA (ctrl+SHIFT+ENTER)

5. Changed the formula in E4 to
[tt]
=IF(ISERROR(SUMPRODUCT((STORE=E$1)*(IF($C4="MISC",MANUAL_PICKED="X",MAKE=$C4))*(YEARSOLD=SelectedYear)*(SALETYPE=SelectedType)*INDIRECT(MID(E$3,10,LEN(E$3)-9))/$D4))," - ",SUMPRODUCT((IF($C4="MISC",MANUAL_PICKED="X",MAKE=$C4))*(YEARSOLD=SelectedYear)*(STORE=E$1)*(SALETYPE=SelectedType)*INDIRECT(MID(E$3,10,LEN(E$3)-9))/$D4))
[/tt]
...and entered it as an ARRAY FORMULA (ctrl+SHIFT+ENTER).

This means that you need only TWO formulas to do the job without making more modification. See the [highlight #FCE94F]YELLOW highlight[/highlight] in the uploaded workbook (3 row examples)


General Comment:
You have made this application much harder to maintain that it need to be.
1. ALL table headings ought to be in ONE ROW.
2. All Table headings ought to be unique.
3. Use the Create Names in TOP row feature to create all range names in one operation. It would be better to use a macro to do this.
4. All formulas, such as the one here modified, ought NOT to contain literal values if at all possible. This would enable you to copy the formula DOWN and ACROSS.
5. When referencing lookup ranges, such as Cost, Recon, Gross, Age, (as in WHSL-RETAIL-DATA-ALL), from a table as in WHSL-UNITS BY MAKE, the heading data, Cost, Recon, Gross, Age, ought to correspond EXACTLY, in order to enable a single formula to be used. The INDIRECT() function makes this possible. I used the MID() function to get this data from existing headings. You could add rows above/hidden to contain such data as a row for Store (RED, BLUE, WHITE) and a row for Cost, Recon, Gross & Age to eliminate using the MID() function and keep AVG DAYS. This would eleminate the need for literals in your formulas.




Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Here's the upload...

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Hello.........

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[JVFriederick]

Gone Johnson.......

 

[SkipVought]

???

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]