#### Help Needed: "IF" formula/logic too limited - what to do next?

```I need to determine a value that is predicated upon the selections of about
6 or so drop down menus.  It'd be General Text within drop-downs but final
value is an Accountant price

Basically if someone selects Option #1 out of Cells A-G's drop down menus,
then the value of H1 = a cell on another page that fits the category of 1-
1-1-1-1-1 from the drop down menus.

I don't know how to do this.  I imagine it's possible but have no clue.

```
 0
3/17/2005 5:48:26 PM
excel 39879 articles. 2 followers.

14 Replies
940 Views

Similar Articles

[PageSpeed] 45

```From what you say, the meat of this problem lies in the layout/format of the
data you have in this "other sheet".  Given these six or so values from the
drop-down cells, how you use them with the "other sheet" data to come up
with the final value will drive the solution.
You can use a VBA macro or a VBA user-defined function to get the final
answer, but you will have to provide the details of how the answer is
derived from this "other sheet", knowing the 6 or so values, before anyone
"Combat High" <highschool@sucks.com> wrote in message
news:Xns961C83C9F43DChighschoolsuckscom@216.77.188.18...
>I need to determine a value that is predicated upon the selections of about
> 6 or so drop down menus.  It'd be General Text within drop-downs but final
> value is an Accountant price
>
> Basically if someone selects Option #1 out of Cells A-G's drop down menus,
> then the value of H1 = a cell on another page that fits the category of 1-
> 1-1-1-1-1 from the drop down menus.
>
> I don't know how to do this.  I imagine it's possible but have no clue.
>
> Your help is greatly appreciated.

```
 0
ottokmnop (389)
3/17/2005 7:14:22 PM
```OK.  Well the subject matter is Interior Doors.

Variables of doors hinge (pun intended) around:

door width  -  about 9 or 10 different widths
pre-hung or slab
pre-cased? (yes/no)
casing type  -  4 types
jamb type  -  2 types
single door OR double door
door type  -  3 or 4 styles
if double door, is it a bifold (yes/no)
if double door, is a T-Astrical included (yes/no)
if double door, is a Ball Catch included (yes/no)

....these variables do not need to interact...it'd be nice if they did
but that's not the concern.  The concern is figuring out, once all
options are decided upon, then I'd like each specific combination
represent a static value in a cell on another sheet.  There are a few
hundred values on the other sheet - so I don't even know if the
methodology of what I want to accomplish is logical or the best.
However, drop-down menus with general text values is a requirement.

I've never done VBA or macros - I'm an Excel novice, btw.

Thank you for responding.

> From what you say, the meat of this problem lies in the layout/format
> of the data you have in this "other sheet".  Given these six or so
> values from the drop-down cells, how you use them with the "other
> sheet" data to come up with the final value will drive the solution.
>     You can use a VBA macro or a VBA user-defined function to get the
>     final
> answer, but you will have to provide the details of how the answer is
> derived from this "other sheet", knowing the 6 or so values, before
> "Combat High" <highschool@sucks.com> wrote in message
> news:Xns961C83C9F43DChighschoolsuckscom@216.77.188.18...
>>I need to determine a value that is predicated upon the selections of
>> 6 or so drop down menus.  It'd be General Text within drop-downs but
>> final value is an Accountant price
>>
>> Basically if someone selects Option #1 out of Cells A-G's drop down
>> menus, then the value of H1 = a cell on another page that fits the
>> category of 1- 1-1-1-1-1 from the drop down menus.
>>
>> I don't know how to do this.  I imagine it's possible but have no
>> clue.
>>
>> Your help is greatly appreciated.
```
 0
3/18/2005 3:29:35 PM
```That was not what I wanted you to give me.  You must understand that Excel,
or any other software, is extremely stupid.  In fact, it has an intelligence
of dead zero.  Excel cannot do anything that you cannot do yourself.  Excel
can only do it faster.
For Excel to do what you want, you must tell Excel how to do it.  In
extreme detail!  In your problem, you have a number of variables (the values
of the drop-down cells) defined.  You want to come up with some one value
and you want to use the values of those variables to do so.  Well, you
obviously have some logic path that you use to arrive at that one value.
You must explain that logic path.  In extreme detail!!
Let me put this another way.  Pretend that someone who knows nothing
about your problem walks in the door.  You want to tell him how to arrive at
this one value.  What are you going to tell him?   What are you going to
show him?  HTH   Otto
"Combat High" <highschool@sucks.com> wrote in message
news:Xns961D6C0B8A840highschoolsuckscom@216.77.188.18...
> OK.  Well the subject matter is Interior Doors.
>
> Variables of doors hinge (pun intended) around:
>
> door width  -  about 9 or 10 different widths
> pre-hung or slab
> pre-cased? (yes/no)
> casing type  -  4 types
> jamb type  -  2 types
> single door OR double door
> door type  -  3 or 4 styles
> if double door, is it a bifold (yes/no)
> if double door, is a T-Astrical included (yes/no)
> if double door, is a Ball Catch included (yes/no)
>
>
> ...these variables do not need to interact...it'd be nice if they did
> but that's not the concern.  The concern is figuring out, once all
> options are decided upon, then I'd like each specific combination
> represent a static value in a cell on another sheet.  There are a few
> hundred values on the other sheet - so I don't even know if the
> methodology of what I want to accomplish is logical or the best.
> However, drop-down menus with general text values is a requirement.
>
> I've never done VBA or macros - I'm an Excel novice, btw.
>
> Thank you for responding.
>
>
>
>> From what you say, the meat of this problem lies in the layout/format
>> of the data you have in this "other sheet".  Given these six or so
>> values from the drop-down cells, how you use them with the "other
>> sheet" data to come up with the final value will drive the solution.
>>     You can use a VBA macro or a VBA user-defined function to get the
>>     final
>> answer, but you will have to provide the details of how the answer is
>> derived from this "other sheet", knowing the 6 or so values, before
>> "Combat High" <highschool@sucks.com> wrote in message
>> news:Xns961C83C9F43DChighschoolsuckscom@216.77.188.18...
>>>I need to determine a value that is predicated upon the selections of
>>> 6 or so drop down menus.  It'd be General Text within drop-downs but
>>> final value is an Accountant price
>>>
>>> Basically if someone selects Option #1 out of Cells A-G's drop down
>>> menus, then the value of H1 = a cell on another page that fits the
>>> category of 1- 1-1-1-1-1 from the drop down menus.
>>>
>>> I don't know how to do this.  I imagine it's possible but have no
>>> clue.
>>>
>>> Your help is greatly appreciated.

```
 0
ottokmnop (389)
3/21/2005 9:39:56 PM
```Oh I'm quite aware of Excel's limitations.  My project in this is sort of
our 'last hurrah' in Excel before we move to an Access, or better,
solution/implementation.

OK, I understand how I'm failing you.  What I am doing is making a
customized cost estimation system for a remodeler.  This system not only
helps us price a job out, but it has to be easy to read - so much so that
ANY given prospect could easily view it and understand what they're seeing.

However, I see now that I've also failed to explain my immediate problem:

I do not have the logic to arrive at that one value.  If I did, then I
wouldn't have a problem.  Creating such logic in of itself is my problem.
:(

On a sheet, we'll call the sheet "Doors", in Row 1 we have General Text
cells that describe what the cells underneath are - like a Jeopardy game
board.  So A1 will read something like "Potent Potables" - j/k, sorry for
the SNL reference.

A1="Door Style"
B1="Door Type"
C1="Door Width"
D1="Door Swing"
E1="Jamb Width"
F1="Casing Style"
G1="T-Astrical?"
H1="Ball Catch?"
I1="Room Location"
J1="Quantity"
K1="Material Price"

Each cell underneath in a given column has a drop-down menu pointing to a
names reference:

Name, "Door Style" contains:
Colonist
Princeton
Slab
French (3x5)
French (Other)
Other

Name, "Door Type" contains:
Single Interior
Single Exterior
Double Interior
Double Exterior
Single Pocket
Double Pocket
Bi-Fold

Name, "Door Width" contains:
1/0
1/4
1/6
1/8
2/0
2/4
2/6
2/8
3/0
4/0
5/0
6/0

Name, "Door Swing" contains:
LH
RH

Name, "Jamb Width" contains:
4-9/16" Split
6-1/2" Split

Name, "Casing Style" contains:
356
356/445
445
445/Howe
356/Howe
Howe
Other
None

Name, "T-Astrical" contains:
Yes
No

Name, "Ball catch" contains:
Yes
No

Name, "Room Location" references another sheet, let's call that sheet
"Rooms"...this column and the variables within have no bearing on what I
need to accomplish: it exists for coworkers' understanding on where this
particular door is going to go.

Name, "Quantity" contains:
1 //default//
2
3
4
5

I am unsure what to put in cells K2-Kxxx at this point.

Now, in a second sheet, we'll call it "Door Price List" I have a plethora
of hard numbers which I presume will need to be setup in a similar fashion
but it would not need "Doors" I column or "DOORS" J column - "Door Price
List" I column would contain a hardcoded price per row.  Then I presume I
have the lovely task of hard-writing all the doggone doors in the world in
this sheet, which their respective price in the H column per row.

What I would like is to be able to pick a single variable in each of the
cells of a row in "Doors" sheet and somehow the Doors' K column would be
able to reference the "Door Price List" sheet's appropriate H column
pricing by matching up all these variables.

I don't know how to do that, however.  I can do it to a minute scale using
the IF argument, but that of course is when there's <=8 different
possibilities...I have a few hundred possibilities now and I have no clue
what is recommended at this point in time.  I know conceptually what I'd
like to do with Excel but have no clue how to implement my concept.  :(

> That was not what I wanted you to give me.  You must understand that
> Excel, or any other software, is extremely stupid.  In fact, it has an
> intelligence of dead zero.  Excel cannot do anything that you cannot
> do yourself.  Excel can only do it faster.
>     For Excel to do what you want, you must tell Excel how to do it.
>     In
> extreme detail!  In your problem, you have a number of variables (the
> values of the drop-down cells) defined.  You want to come up with some
> one value and you want to use the values of those variables to do so.
> Well, you obviously have some logic path that you use to arrive at
> that one value. You must explain that logic path.  In extreme detail!!
>     Let me put this another way.  Pretend that someone who knows
>     nothing
> about your problem walks in the door.  You want to tell him how to
> arrive at this one value.  What are you going to tell him?   What are
> you going to show him?  HTH   Otto

> "Combat High" <highschool@sucks.com> wrote in message
> news:Xns961D6C0B8A840highschoolsuckscom@216.77.188.18...
>> OK.  Well the subject matter is Interior Doors.
>>
>> Variables of doors hinge (pun intended) around:
>>
>> door width  -  about 9 or 10 different widths
>> pre-hung or slab
>> pre-cased? (yes/no)
>> casing type  -  4 types
>> jamb type  -  2 types
>> single door OR double door
>> door type  -  3 or 4 styles
>> if double door, is it a bifold (yes/no)
>> if double door, is a T-Astrical included (yes/no)
>> if double door, is a Ball Catch included (yes/no)
>>
>>
>> ...these variables do not need to interact...it'd be nice if they did
>> but that's not the concern.  The concern is figuring out, once all
>> options are decided upon, then I'd like each specific combination
>> represent a static value in a cell on another sheet.  There are a few
>> hundred values on the other sheet - so I don't even know if the
>> methodology of what I want to accomplish is logical or the best.
>> However, drop-down menus with general text values is a requirement.
>>
>> I've never done VBA or macros - I'm an Excel novice, btw.
>>
>> Thank you for responding.
>>
>>
>>
>>> From what you say, the meat of this problem lies in the
>>> layout/format of the data you have in this "other sheet".  Given
>>> these six or so values from the drop-down cells, how you use them
>>> with the "other sheet" data to come up with the final value will
>>> drive the solution.
>>>     You can use a VBA macro or a VBA user-defined function to get
>>>     the final
>>> answer, but you will have to provide the details of how the answer
>>> is derived from this "other sheet", knowing the 6 or so values,
>>> "Combat High" <highschool@sucks.com> wrote in message
>>> news:Xns961C83C9F43DChighschoolsuckscom@216.77.188.18...
>>>>I need to determine a value that is predicated upon the selections
>>>> 6 or so drop down menus.  It'd be General Text within drop-downs
>>>> but final value is an Accountant price
>>>>
>>>> Basically if someone selects Option #1 out of Cells A-G's drop down
>>>> menus, then the value of H1 = a cell on another page that fits the
>>>> category of 1- 1-1-1-1-1 from the drop down menus.
>>>>
>>>> I don't know how to do this.  I imagine it's possible but have no
>>>> clue.
>>>>
>>>> Your help is greatly appreciated.
>
>
>

```
 0
3/23/2005 4:20:14 PM
```A uncomplicated approach to your problem would be to have *each* of your
variables in a simple, 2 column datalist, where one column contains the
variable name, and the other column the price (cost).
Since each variable name is unique, they can *all* be in a *single* list.

I counted 41 variables that you listed in your post.
So, say on Sheet2 you start the datalist in A1:A41, and the costs in B1:B41.

With the results of your drop-down list choices in A2: H2 of Sheet1 (Column
I and J to be ignored), enter this formula in K2:

=SUMPRODUCT((A2:H2=Sheet2!A1:A41)*Sheet2!B1:B41)

If you were going to have multiple rows of drop-down choices for many
different door types on the same sheet, and you therefore wanted to copy
this formula down Column K, don't forget to change the cell references to
absolute:

=SUMPRODUCT((A2:H2=Sheet2!\$A\$1:\$A\$41)*Sheet2!\$B\$1:\$B\$41)

You should also watch out for your "Door Width" data.
Make sure that the formats match *exactly* between the datalist and the
drop-down choices.
Those types of numbers might easily confuse XL.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"Combat High" <highschool@sucks.com> wrote in message
news:Xns962274DBE1EEhighschoolsuckscom@216.77.188.18...
Oh I'm quite aware of Excel's limitations.  My project in this is sort of
our 'last hurrah' in Excel before we move to an Access, or better,
solution/implementation.

OK, I understand how I'm failing you.  What I am doing is making a
customized cost estimation system for a remodeler.  This system not only
helps us price a job out, but it has to be easy to read - so much so that
ANY given prospect could easily view it and understand what they're seeing.

However, I see now that I've also failed to explain my immediate problem:

I do not have the logic to arrive at that one value.  If I did, then I
wouldn't have a problem.  Creating such logic in of itself is my problem.
:(

On a sheet, we'll call the sheet "Doors", in Row 1 we have General Text
cells that describe what the cells underneath are - like a Jeopardy game
board.  So A1 will read something like "Potent Potables" - j/k, sorry for
the SNL reference.

A1="Door Style"
B1="Door Type"
C1="Door Width"
D1="Door Swing"
E1="Jamb Width"
F1="Casing Style"
G1="T-Astrical?"
H1="Ball Catch?"
I1="Room Location"
J1="Quantity"
K1="Material Price"

Each cell underneath in a given column has a drop-down menu pointing to a
names reference:

Name, "Door Style" contains:
Colonist
Princeton
Slab
French (3x5)
French (Other)
Other

Name, "Door Type" contains:
Single Interior
Single Exterior
Double Interior
Double Exterior
Single Pocket
Double Pocket
Bi-Fold

Name, "Door Width" contains:
1/0
1/4
1/6
1/8
2/0
2/4
2/6
2/8
3/0
4/0
5/0
6/0

Name, "Door Swing" contains:
LH
RH

Name, "Jamb Width" contains:
4-9/16" Split
6-1/2" Split

Name, "Casing Style" contains:
356
356/445
445
445/Howe
356/Howe
Howe
Other
None

Name, "T-Astrical" contains:
Yes
No

Name, "Ball catch" contains:
Yes
No

Name, "Room Location" references another sheet, let's call that sheet
"Rooms"...this column and the variables within have no bearing on what I
need to accomplish: it exists for coworkers' understanding on where this
particular door is going to go.

Name, "Quantity" contains:
1 //default//
2
3
4
5

I am unsure what to put in cells K2-Kxxx at this point.

Now, in a second sheet, we'll call it "Door Price List" I have a plethora
of hard numbers which I presume will need to be setup in a similar fashion
but it would not need "Doors" I column or "DOORS" J column - "Door Price
List" I column would contain a hardcoded price per row.  Then I presume I
have the lovely task of hard-writing all the doggone doors in the world in
this sheet, which their respective price in the H column per row.

What I would like is to be able to pick a single variable in each of the
cells of a row in "Doors" sheet and somehow the Doors' K column would be
able to reference the "Door Price List" sheet's appropriate H column
pricing by matching up all these variables.

I don't know how to do that, however.  I can do it to a minute scale using
the IF argument, but that of course is when there's <=8 different
possibilities...I have a few hundred possibilities now and I have no clue
what is recommended at this point in time.  I know conceptually what I'd
like to do with Excel but have no clue how to implement my concept.  :(

> That was not what I wanted you to give me.  You must understand that
> Excel, or any other software, is extremely stupid.  In fact, it has an
> intelligence of dead zero.  Excel cannot do anything that you cannot
> do yourself.  Excel can only do it faster.
>     For Excel to do what you want, you must tell Excel how to do it.
>     In
> extreme detail!  In your problem, you have a number of variables (the
> values of the drop-down cells) defined.  You want to come up with some
> one value and you want to use the values of those variables to do so.
> Well, you obviously have some logic path that you use to arrive at
> that one value. You must explain that logic path.  In extreme detail!!
>     Let me put this another way.  Pretend that someone who knows
>     nothing
> about your problem walks in the door.  You want to tell him how to
> arrive at this one value.  What are you going to tell him?   What are
> you going to show him?  HTH   Otto

> "Combat High" <highschool@sucks.com> wrote in message
> news:Xns961D6C0B8A840highschoolsuckscom@216.77.188.18...
>> OK.  Well the subject matter is Interior Doors.
>>
>> Variables of doors hinge (pun intended) around:
>>
>> door width  -  about 9 or 10 different widths
>> pre-hung or slab
>> pre-cased? (yes/no)
>> casing type  -  4 types
>> jamb type  -  2 types
>> single door OR double door
>> door type  -  3 or 4 styles
>> if double door, is it a bifold (yes/no)
>> if double door, is a T-Astrical included (yes/no)
>> if double door, is a Ball Catch included (yes/no)
>>
>>
>> ...these variables do not need to interact...it'd be nice if they did
>> but that's not the concern.  The concern is figuring out, once all
>> options are decided upon, then I'd like each specific combination
>> represent a static value in a cell on another sheet.  There are a few
>> hundred values on the other sheet - so I don't even know if the
>> methodology of what I want to accomplish is logical or the best.
>> However, drop-down menus with general text values is a requirement.
>>
>> I've never done VBA or macros - I'm an Excel novice, btw.
>>
>> Thank you for responding.
>>
>>
>>
>>> From what you say, the meat of this problem lies in the
>>> layout/format of the data you have in this "other sheet".  Given
>>> these six or so values from the drop-down cells, how you use them
>>> with the "other sheet" data to come up with the final value will
>>> drive the solution.
>>>     You can use a VBA macro or a VBA user-defined function to get
>>>     the final
>>> answer, but you will have to provide the details of how the answer
>>> is derived from this "other sheet", knowing the 6 or so values,
>>> "Combat High" <highschool@sucks.com> wrote in message
>>> news:Xns961C83C9F43DChighschoolsuckscom@216.77.188.18...
>>>>I need to determine a value that is predicated upon the selections
>>>> 6 or so drop down menus.  It'd be General Text within drop-downs
>>>> but final value is an Accountant price
>>>>
>>>> Basically if someone selects Option #1 out of Cells A-G's drop down
>>>> menus, then the value of H1 = a cell on another page that fits the
>>>> category of 1- 1-1-1-1-1 from the drop down menus.
>>>>
>>>> I don't know how to do this.  I imagine it's possible but have no
>>>> clue.
>>>>
>>>> Your help is greatly appreciated.
>
>
>

```
 0
ragdyer1 (4060)
3/25/2005 3:25:05 AM
```Thanks for the reply!

I'm a little confused (again):

There are much more than 41 costs, so I'm confused by the, "So, say
on Sheet2 you start the datalist in A1:A41, and the costs in B1:B41."
suggestion.  Are you saying therefore that there'd be may costs DataLists
in C1:C41, D1:D41, etc.?

> A uncomplicated approach to your problem would be to have *each* of
> your variables in a simple, 2 column datalist, where one column
> contains the variable name, and the other column the price (cost).
> Since each variable name is unique, they can *all* be in a *single*
> list.
>
> I counted 41 variables that you listed in your post.
> So, say on Sheet2 you start the datalist in A1:A41, and the costs in
> B1:B41.
>
> With the results of your drop-down list choices in A2: H2 of Sheet1
> (Column I and J to be ignored), enter this formula in K2:
>
> =SUMPRODUCT((A2:H2=Sheet2!A1:A41)*Sheet2!B1:B41)
>
> If you were going to have multiple rows of drop-down choices for many
> different door types on the same sheet, and you therefore wanted to
> copy this formula down Column K, don't forget to change the cell
> references to absolute:
>
> =SUMPRODUCT((A2:H2=Sheet2!\$A\$1:\$A\$41)*Sheet2!\$B\$1:\$B\$41)
>
> You should also watch out for your "Door Width" data.
> Make sure that the formats match *exactly* between the datalist and
> the drop-down choices.
> Those types of numbers might easily confuse XL.

```
 0
3/28/2005 5:58:38 PM
```> Thanks for the reply!
>
> I'm a little confused (again):
>
>          There are much more than 41 costs, so I'm confused by the,
>          "So, say
> on Sheet2 you start the datalist in A1:A41, and the costs in B1:B41."
> suggestion.  Are you saying therefore that there'd be may costs
> DataLists in C1:C41, D1:D41, etc.?
>

As a follow-up: there's not a 1:1 relationship between each variable and a
cost, only a 1:1 relationship between every single possible combination of
the variables and a cost, so that is what truly confuses me.

>
>> A uncomplicated approach to your problem would be to have *each* of
>> your variables in a simple, 2 column datalist, where one column
>> contains the variable name, and the other column the price (cost).
>> Since each variable name is unique, they can *all* be in a *single*
>> list.
>>
>> I counted 41 variables that you listed in your post.
>> So, say on Sheet2 you start the datalist in A1:A41, and the costs in
>> B1:B41.
>>
>> With the results of your drop-down list choices in A2: H2 of Sheet1
>> (Column I and J to be ignored), enter this formula in K2:
>>
>> =SUMPRODUCT((A2:H2=Sheet2!A1:A41)*Sheet2!B1:B41)
>>
>> If you were going to have multiple rows of drop-down choices for many
>> different door types on the same sheet, and you therefore wanted to
>> copy this formula down Column K, don't forget to change the cell
>> references to absolute:
>>
>> =SUMPRODUCT((A2:H2=Sheet2!\$A\$1:\$A\$41)*Sheet2!\$B\$1:\$B\$41)
>>
>> You should also watch out for your "Door Width" data.
>> Make sure that the formats match *exactly* between the datalist and
>> the drop-down choices.
>> Those types of numbers might easily confuse XL.
>

```
 0
3/28/2005 7:13:20 PM
```"RagDyer" <ragdyer@cutoutmsn.com> wrote in
news:#ofpO5OMFHA.3328@TK2MSFTNGP14.phx.gbl:

> You should also watch out for your "Door Width" data.
> Make sure that the formats match *exactly* between the datalist and
> the drop-down choices.
> Those types of numbers might easily confuse XL.

....Yes, I had to format the row as "Text" - otherwise "1/0" created a date.
:(
```
 0
3/28/2005 7:23:27 PM
```Appreciate the feed-back ... BUT ... I'm confused by all your replies.

Are you satisfied with the suggested formula and procedure ... or not ???

Do you need any additional suggestions or clarification?
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

"Combat High" <highschool@sucks.com> wrote in message
news:Xns962793B7D991Bhighschoolsuckscom@216.77.188.18...
"RagDyer" <ragdyer@cutoutmsn.com> wrote in
news:#ofpO5OMFHA.3328@TK2MSFTNGP14.phx.gbl:

> You should also watch out for your "Door Width" data.
> Make sure that the formats match *exactly* between the datalist and
> the drop-down choices.
> Those types of numbers might easily confuse XL.

....Yes, I had to format the row as "Text" - otherwise "1/0" created a date.
:(

```
 0
ragdyer1 (4060)
3/28/2005 7:48:54 PM
```I DO need additional help if it is convenient for you, yes, please.

Our BellSouth newsgroup access has been screwed up the past 24+ hours.
I see now that 1 or even 2 additional posts of mine reg: this topic
never made its way here.  :(  They claim it's fixed now - we shall see
(or *won't* see I guess would be more-appropriate).

Well I saw some things on LOOKUP (and MATCH) that is *almost* perfect
for me but, for example, I need the LOOKUP_VALUE to be an ARRAY which
fails when I try.

Your suggestion was great but I failed to explain something (again) to
you:

there's not a 1:1 relationship between a given variable and a cost.
There is a relationship between an array of variables from selected in
A2:H2 to a given cost.  Therefore there are hundreds (or more?) of
costs.  :(  That's what complicates things in my mind.  ...I'd make a
terrible programmer!  :)

If I hardcode all the possible variable combinations in an array on
Sheet2, how can verify a random selection of variables in A2:H2 to not
only find that same combination in the hardcoded array, but return its
cost value (Sheet2!I2) back to K2?

> Appreciate the feed-back ... BUT ... I'm confused by all your replies.
>
> Are you satisfied with the suggested formula and procedure ... or not
???
>
> Do you need any additional suggestions or clarification?
```
 0
3/29/2005 6:55:05 PM
```Combat High wrote:
..
>
> Well I saw some things on LOOKUP (and MATCH) that is *almost* perfect

> for me but, for example, I need the LOOKUP_VALUE to be an ARRAY which

> fails when I try.
>

Makes you want to say hmmmmmmm.

> Your suggestion was great but I failed to explain something (again)
to
> you:
>
> there's not a 1:1 relationship between a given variable and a cost.
> There is a relationship between an array of variables from selected
in
> A2:H2 to a given cost.  Therefore there are hundreds (or more?) of
> costs.  :(  That's what complicates things in my mind.  ...I'd make a

> terrible programmer!  :)
>
> If I hardcode all the possible variable combinations in an array on
> Sheet2, how can verify a random selection of variables in A2:H2 to
not
> only find that same combination in the hardcoded array, but return
its
> cost value (Sheet2!I2) back to K2?
>
>
>

that is interesting.  there's got to be places where you can steal some
programming for this.  but there HAS to be some way that you
arrange/categorize the variables that makes more sense than just saying
there are hundreds.  that would be a start

mk5000

"paris always requests A.M to DJ her parties"--paris hilton's reps.

```
 0
3/30/2005 12:18:36 AM
```I'd like to see exactly what you're talking about.
Why don't you e-mail me your list of cost variables.
Cut out cutout from my address.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

"Combat High" <highschool@sucks.com> wrote in message
news:Xns96288F77AFEEBhighschoolsuckscom@216.77.188.18...
I DO need additional help if it is convenient for you, yes, please.

Our BellSouth newsgroup access has been screwed up the past 24+ hours.
I see now that 1 or even 2 additional posts of mine reg: this topic
never made its way here.  :(  They claim it's fixed now - we shall see
(or *won't* see I guess would be more-appropriate).

Well I saw some things on LOOKUP (and MATCH) that is *almost* perfect
for me but, for example, I need the LOOKUP_VALUE to be an ARRAY which
fails when I try.

Your suggestion was great but I failed to explain something (again) to
you:

there's not a 1:1 relationship between a given variable and a cost.
There is a relationship between an array of variables from selected in
A2:H2 to a given cost.  Therefore there are hundreds (or more?) of
costs.  :(  That's what complicates things in my mind.  ...I'd make a
terrible programmer!  :)

If I hardcode all the possible variable combinations in an array on
Sheet2, how can verify a random selection of variables in A2:H2 to not
only find that same combination in the hardcoded array, but return its
cost value (Sheet2!I2) back to K2?

> Appreciate the feed-back ... BUT ... I'm confused by all your replies.
>
> Are you satisfied with the suggested formula and procedure ... or not
???
>
> Do you need any additional suggestions or clarification?

```
 0
ragdyer1 (4060)
3/30/2005 2:31:24 AM
```Well I am completely ignorant of any "Excel Scenes" to steal programming
for one thing.  Also, I don't doubt that you're right in that there's
probably an easy way to arrange all the variables but I have no clue.
My probability and statistics knowledge is non-existent but I'm
speculating I have tens-of-thousands of possibilities with the
previously-posted variables lists.

>
> Combat High wrote:
> .
>>
>> Well I saw some things on LOOKUP (and MATCH) that is *almost* perfect
>
>> for me but, for example, I need the LOOKUP_VALUE to be an ARRAY which
>
>> fails when I try.
>>
>
> Makes you want to say hmmmmmmm.
>
>> Your suggestion was great but I failed to explain something (again)
> to
>> you:
>>
>> there's not a 1:1 relationship between a given variable and a cost.
>> There is a relationship between an array of variables from selected
> in
>> A2:H2 to a given cost.  Therefore there are hundreds (or more?) of
>> costs.  :(  That's what complicates things in my mind.  ...I'd make a
>
>> terrible programmer!  :)
>>
>> If I hardcode all the possible variable combinations in an array on
>> Sheet2, how can verify a random selection of variables in A2:H2 to
> not
>> only find that same combination in the hardcoded array, but return
> its
>> cost value (Sheet2!I2) back to K2?
>>
>>
>>
>
> that is interesting.  there's got to be places where you can steal
some
> programming for this.  but there HAS to be some way that you
> arrange/categorize the variables that makes more sense than just
saying
> there are hundreds.  that would be a start
>
> mk5000
>
> "paris always requests A.M to DJ her parties"--paris hilton's reps.
>
>

```
 0
3/30/2005 4:33:25 PM
```Combat High wrote:
> Well I am completely ignorant of any "Excel Scenes" to steal
programming
> for one thing.  Also, I don't doubt that you're right in that there's

> probably an easy way to arrange all the variables but I have no clue.

> My probability and statistics knowledge is non-existent but I'm
> speculating I have tens-of-thousands of possibilities with the
> previously-posted variables lists.
>
>
>

Not havinga  clue what biz this is we are talking about, I would
consider getting a basic accounting manual that has similar to your
biz.  it should be a start.

mk5000

"Jennifer looked like a million dollars.  Marc opened the door of their
Mercedes like a real gentleman.  She seemed delighted to be at his
side"--an onlooker

```
 0
4/7/2005 5:42:14 AM

Similar Artilces:

Need Gift receipt as a option on POS screen
As I understand it, the gift receipt replace the current receeipt. It need to be a option under the function keys, that will allow you to print the original receipt for the customer, then print a gift receipt if requested. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message p...

letters in IF formulas?
If the value of a cell is a letter, can this be used in an IF formula? I am making a simple chore spreadsheet for my kids. In this sheet, each chore is represented by a letter. (i.e. B=clean bedroom) Also, each chore has a certain value amount with it (i.e. B=\$1). I would like to be able to have the kids enter in the chore letter when it's completed and have the computer do all the calculations of amounts and totals. Can this be done? For example: =IF(c7=B,1,IF(c7=K,2)) ...

Summing cells values based on IF formulas
My sheet has cells whose values are determined by the result of an IF formula, eg =IF(LEFT(F20,3)=I\$5,\$G20,0) I want to have a total of each colum but when I insert a SUM formula it gives a 0 result. I thought it might be something to do with the calculation order but I don't know how to change this. Any ideas how I can get this to work? Thanks, Seamus Seamus, Your 'LEFT(F20,3)' function is returning a text string. If you replace it with 'VALUE(LEFT(F20,3))', the text string will be converted to a numerical value. If you are using Excel 2003, try the Tools\Form...

Help me rough out steps for a DIF project, please.
I think the following idea can be accomplished using Excel's Data Interchange Format. A. I have three retail stores with standalone POS systems. I can pull reports from each system. I want to consolidate these reports at the headquarters level. B. Each store can export the report in a DIF format. C. Each report contains sales for departments. Each department has a unique department number. Each store has a unique store number, though not on the report. Question: What would be the broad steps involved in combining the data into one report at headquarters level? I think I recall...

Formula Simplifications Using Nested If, Indirect & Address Functi
After reviewing some previous posts, I've come up with the following function. Ideally, I would like to give it a Name reference, but it is too long. Any suggestions for simplifying and shortening? The formula looks at the left adjacent cell for location codes that correspond to location descriptions. The column locations have the potential of changing so I needed a solution that could dynamically change without a fixed reference point. Let me know if you need any other information. Thank you in advance for any assistance. =IF(INDIRECT(ADDRESS(ROW(),COLUMN()-1))=&quo...

I need a message if number of duplicate records exceeds number all
Thanks in advance for any help. I'm trying to create If DCount code on my form that will count the number of duplicate records and then notify me when the number of duplicate records exceeds the number allowed. The number of duplicates allowed is established in tblFloorProgCriteria in the field FloorProgMaxObservations. Here's what I have so far...It's giving me the message as soon as I attempt to add the first record. If DCount("*", "tblFloorProgAudit", "[AuditID] = " & Me.[AuditID] & " And [FloorProgCriteriaID] = ...

Modify a Formula 01-05-10
Hi. I use this formula: =COUNTIF(I5:I40;TRUE)/COUNTA(I5:I40) I would like to modify it so that the value I put in Cell A1 (eg 100) will change the formula to this: =COUNTIF(I5:I100;TRUE)/COUNTA(I5:I100) Thank you in advance. Check the INDIRECT() function in HELP -- Kind regards, Niek Otten Microsoft MVP - Excel "carl" <carl@discussions.microsoft.com> wrote in message news:635DC7A7-0B24-4C8C-8317-D9FA59DAE124@microsoft.com... > Hi. > > I use this formula: > > =COUNTIF(I5:I40;TRUE)/COUNTA(I5:I40) > > I would like to...

Combination of IF, AND, and COUNTIF; need help
Hi, I am trying to make a calculation that will take Pass/Fail values of P or F and give me an overall value of P or F. There is also an issue if a value is Not Tested, NT. For example, I have three levels of requirements (parent, child, and grandchild). Parent is level 1, Child is level 2, and Grandchild is level 3. For each grandchild requirement multiple tests can be completed with a P, F, or NT result. I have come up with a formula that will give me an overall result of P or F: =IF(COUNTIF(H8:H9,"F")>0,"F","P") to say If any value i...

Need rows in Column A removed if they fully or partially match with any Column B row
Hi There, I have rows of text strings in Column A and in Column B. I need rows in Column A removed if they partially or exactly match with text strings in any Column B row. Your input is appreciated! Alexa You may get better, er, some responses if you describe what "partially match" means. registrations@discountcosmeticsguide.com wrote: > > Hi There, > > I have rows of text strings in Column A and in Column B. > > I need rows in Column A removed if they partially or exactly > match with text strings in any Column B row. > > Your input is appreci...

Help!! To modify existing formula
Hi A cell (H6) in my spreadhseet currently contains the following formula: =IF(ISBLANK(G6),"",NETWORKDAYS(E6,G6)) If the value returned is >1 then i want the font in the cell to turn red. Please help. Thanks Dan If you are using Excel 2007 you can use conditional formatting. It should show up on the "Home" banner. If you're not in 2007, not sure that it can be done. "housinglad" wrote: > Hi > > A cell (H6) in my spreadhseet currently contains the following formula: > > =IF(ISBLANK(G6),"",NETWO...

Formula to connect cells in different worksheets?
How do i link an employee to their wage automatically? Is it possible to enter in my employee's name and hours, and have it automatically calculate their wage? I have all my employees wages in another worksheet right now. At this time I go to my job record worksheet I enter in my employee, enter the hours type =left arrow tab to worksheet with wages and click on the employee then hit enter, this gives me there total wage. I am trying to see if I can program the name and wage together somehow, that when I do type the name it calculates with the hours and gives me a total. tb Use ...

Help with SUMIF
I have the following formula to calculate some numeric data: =SUMIF(F6:F27,F5,S6:S27) (this is in cell S5) the qualifying code is in column F and the numeric data to sum is in column S Problem. When I insert a row just below row 5, the formula in S5 changes to: =SUMIF(F7:F27,F5,S7:S27) I will be adding data to the new row6 and I need it to become a part of the calculation. I need the formula to remain F6: and S6: How can I do that? Glen One way =SUMIF(INDIRECT("F6:F27"),F5,INDIRECT("S6:S27")) -- Regards, Peo Sjoblom "Glen Mettler" <glen.e.m...

PLEASE PLEASE, can someone help me. I posted this on Nov. 6 and last week. I am having to do a "repair" EVERYDAY to get my sounds back for when I get a new email , contacts sign -on etc. All my other programs have sound so I know that it is just in the messenger. When I am done using the messenger I sign out and then the next time I sign in I have to do the repair. THIS IS VERY ANNOYING TO HAVE TO DO EVERYDAY!!! I have used the tool to do a clean uninstall and have installed and reinstalled several times. Vista Home Premium sp2 NIS 2010 worked with 8.5 IE...

CountIf Limitation?
I have a column that contains three text descriptions ie. Intermediate Average or Advance. Using the CountIf function , I am able to count each description separately and then show the overall total. Can I modify the Countif to add up the three descriptions and display a single total of their occurance? Thanks very much One way: =SUMPRODUCT(COUNTIF(A:A,{"Intermediate","Advance","Average"})) In article <06f001c48c09\$d156fdb0\$a401280a@phx.gbl>, "Deltaecho" <anonymous@discussions.microsoft.com> wrote: > I have a column that conta...

IIF Statement
I have a table full of items, below is one example from the table. I am trying to do a query that will display the longest dimension (as L1) for each item. Item field is text, all others are numbers. Here is my data: Item = Y10227 ItemLength = 8.375 ItemWidth = 9.25 ItemHeight = 5.125 Here is the field in my query to determine the largest of the dimensions: L1: IIf([ItemLength]>[ItemHeight] And [ItemWidth],[ItemLength],IIf([ItemHeight]>[ItemLength] And [ItemWidth],[ItemHeight],IIf([Itemwidth]>[itemheight] And [itemlength],[itemwidth]))) The result I am gettin...

EXPERT HELP REQUIRED
Can any one offer any assistance with the following query. See example table in a worksheet; Column A B C D E F Seq Month C/C A/C Ac name Actual � 1 Jun 04 7313 30613 Training(NR) �2,250 2 Jun 04 7323 60301 Plant Hire �5,500 3 Jul 04 7324 30810 Prot clthng �1,500 4 Aug 04 7313 30612 Training (R) �6,500 ETC.... NOTES: -The data at the moment extends to 600 line items for one mont therefore potentially could be circa 7,500 line items -I dont want to use pivot tables as this is to be template to issue to other users with little or no...

Countif help!!!
Thanks in advance for any help offered. I have a table with GPA's and need to calculate the different ranges. I have the function for 3.0 an above and less than 2.5. I am having trouble figuring out the formul to count how many students are in the range of 2.50-2.99. I thought I could do this: =countif(f2:f406, ">=2.5+<=2.99") I get "0" as a result. any help is appreciated : -- Message posted from http://www.ExcelForum.com Hi, You can use Sumproduct... =SUMPRODUCT(--(F2:F406>=2.5),--(F2:F406<=2.99)) Hope this helps -- Message posted from h...

What is wrong with this COUNTIF formula?
Hi, I'm sure its a very obvious mistake I've made, but this is the firs time I've attempted to use Excel. I am typing in the following formula to find a percentage of a numbe of values, but to ignore any values that are zero or less. The value are changeable so it is vital that all cells that could be filled ar considered. I used the COUNTIF function so only values greater tha zero are considered. This is what i entered:- > =SUM((F8,I8,L8,O8,R8,U8,X8,AA8)/800%)/COUNTIF(F8,I8,L8,O8,R8,U8,X8,AA8,">0") It doesn't look right...i've tried without some bracke...

How do I make a formula refer to given data even if I do a sort f.
I want to create a formula in a spreadsheet that refers to specific cells of data. However, I also want to be able to sort my data but keep the formula refering to the same information. IE, say I have a collumn of ten numbers. I would like to be able to have a formula take the average of the first 5 listed. If I then did a sort function of these ten numbers that changes the order of the numbers, I want my formula to have the same result because it is still referencing the correct information. ...

how can i have certain formulas come up in a specific color
i AM WORKING ON A STAFF SCHEDULE. TIMES ARE ENTER BY A FORMULA. I WOULD LIKE TO HIGHLIGHT CERTAIN FORMULAS TO COME UP IN A SPECIFC COLOR. It is considered VERY rude to shout by typing in all CAPS. It's also harder to read. Use format>conditional format>formula is> -- Don Guillett SalesAid Software donaldb@281.com "TEDDY" <TEDDY@discussions.microsoft.com> wrote in message news:4550FF41-498B-48A2-AB5C-10791F2D30E0@microsoft.com... > i AM WORKING ON A STAFF SCHEDULE. TIMES ARE ENTER BY A FORMULA. I WOULD LIKE > TO HIGHLIGHT CERTAIN FORMULAS TO COME UP IN A S...

IIF Statement Help 01-29-10
I am getting an error messge saying a I am missing a ( or|and I can not figure out what is wrong. Can you please assist with debugging the following code: Test: IIF(Month(Date())>10 AND Month([Contract Info].[Start Date])<3, Year(Date())+1, Year(Date()) Essentially I want this calculated field to take a look at the current month and for January and February add one the the current year, otherwise the field should be the current year you are missing a parenthesis at the very end. that last parenthesis you have is ending only the Year function, but you also n...

Excel nested IF formula question
I have a question regarding Nested If statements in Excel 2003. I have a commission structure below that needs to be nested 0-\$50K = 10% 50-100K = 20% 100-150K = 25% 150-200K = 30% 200K+ - 35% For example: Fee commission rate commission 19,125 10% 1,912.50 8,000 10% 800.00 8,000 10% 800.00 8,000 10% 800.00 Total Billings: 43,125 Here is where I am running into a problem. At this ...

Simplified SUMPRODUCT formula !
Dear sir, I have a problem regarding the SUMPRODUCT function. I used to add some numbers from number of worksheets such as WK1, WK2, WK3 etc. As a result, my formula become very long as following: =SUMPRODUCT((WK1!\$E\$2:\$E\$100=\$D8)*(G\$3<WK1!\$B\$2:\$B\$100)*(WK1!\$B\$2:\$B\$100<=H\$3) ,(WK1!\$D\$2:\$D\$100))+SUMPRODUCT((WK2!\$E\$2:\$E\$100=\$D8)*(G\$3<WK2!\$B\$2:\$B\$100)* (WK2!\$B\$2:\$B\$100<=H\$3),(WK2!\$D\$2:\$D\$100))+SUMPRODUCT((WK3!\$E\$23:\$E\$100=\$D8)* (G\$3<WK3!\$B\$2:\$B\$100)*(WK3!\$B\$2:\$B\$100<=H\$3),(WK3!\$D\$2:\$D\$100)) My question is that is there any way to have one formula which can perform the same fu...

Help needed to modify macro to convert User IDs to Names
Hello I have a macro which I've been using for quite some time. This macro allows the user to identify the User IDs of people listed in the spreadsheet. However, I now need a macro to do the reverse. That is, it needs to find the names of people whose User IDs are listed in the spreadsheet. If possible, it would be great to list the given name and surname in separate columns, although I could do this separately via another macro. Is it possible to modify the existing code below to achieve what I want (i.e. so that it does the reverse of what it currently does)? I...

Do DLLs need to clean up if process gets deleted?
I'm making a DLL which has a routine that opens a file, allocates memory, does stuff, deallocates the memory, and closes the file before returning. My question is what happens if the process gets deleted while it's in the middle of that routine? Does the open file automatically get closed? Does the allocated memory automatically get deallocated? Or do I have to put cleanup code in the case DLL_PROCESS_DETACH:? BOOL WINAPI DllMain( HINSTANCE hinstDLL, // handle to DLL module DWORD fdwReason, // reason for calling function LPVOID lpReserved ) // reserved { sw...