#### 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
833 Views

Similar Articles

[PageSpeed] 25

```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:

sumifs help
I have the following formula. =SUMIFS(Table1[2],\$A\$11:\$A\$22,\$A38,\$B\$11:\$B\$22,\$B38) It now needs to be changed to a formula that can handle text instead of numbers. How do i do it? Use Countif instead of Sumif from =SUMIF(Table1[2],\$A\$11:\$A\$22,\$A38,\$B\$11:\$B\$22,\$B38) to =CountIF(Table1[2],\$A\$11:\$A\$22,\$A38,\$B\$11:\$B\$22,\$B38) Do you really have a function Countifs with an "S" at the end? thie maybe an UDF that needs to be modified. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this th...

help help help IF function
i am new to excell and need help with a problem...... the problem is iam looking for a amount < = 3000.00 and iam to display in bold and in light green i have never had to deal with that i got as far as =IF<=3000 then iam lost or it that even right?? I'm far from a whiz at Excel but I'd use conditional formatting. -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 "kimmy" <kimmy@discussions.microsoft.com> wrote in message news:ADC77884-FC87-47A3-90F2-D2AAF957F5D1@microsoft.com... >i a...

IF OR help
I have entered the formula =IF(OR(D5="K",D5<=4,M32<=95),"INELIGIBLE"," ") I am trying to get a result that will display INELIGIBLE when the value in D5 is K,1,2,3,4 OR the value in M32 is less than 96. with this formula I now get INELIGIBLE when D5 is blank. I would like cell to be blank unless one of the specific conditions is met. If either D5 or M32 is blank, I would like this cell to also be blank M32 contains a formula that returns a blank cell unless data is entered elsewhere in the workbook Thanks for all the help -- dbconn...

Need help to modify code to track changes in a column
I have been tasked with tracking if people fill in their time sheets in on time. I was able to find the following code to track and copy changes to rows within a spreadsheet, but I need to modify it to be able to do columns instead any help would be appreciated. -------------- Dim myRows() As Long Private Sub Worksheet_Change(ByVal Target As Range) Dim myRow As Long Dim myVal As Variant If Target.Cells.Count > 1 Then Exit Sub On Error GoTo NotDimmed test = UBound(myRows) GoTo Dimmed NotDimmed: ReDim myRows(1 To 1) Dimmed: For i = 1 To UBound(myRows) If myRows(...

Help me identify the symbols in "Icon" field of my Inbox in Outloo
How does one manage such a query in the simplistic "Help" offered by these people? Where can I find the answer to my question? "Icon" in the search and help facility treat it only as icon the symbol in Windows, not as a specific header in my Inbox in Outlook 2003, the Email program of Microsoft Office. How does one manage a post in which the user failed even to post a question? You force us to guess what it is. That wastes our time and yours. My guess as to what your question is would have this answer: http://www.howto-outlook.com/howto/icons.htm -- Russ Valentine [...

Need CRM Classroom training in Southern California
I need CRM Customization training. Does anyone know where I can receive CRM classroom training in California? Customersource only lists training sessions in Canada. Thanks, Jonathan N. I can get you in touch with contractor-based training. Please send an email to my profile's address. Dave "jonn" wrote: > I need CRM Customization training. > Does anyone know where I can receive CRM classroom training in California? > Customersource only lists training sessions in Canada. > > Thanks, > Jonathan N. ...

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...

round result of sumif formula
Hi I have a worksheet that calculates targets for 29 people based on thier percentage share of customers within an area using s sumif formula. My problem is that by allocating % share of a monthly target excel calculates to decimal points and i then have to overtype results so they add up particularly where monthly targets are low. Is there a way that I can get excel to round highest numbers in a range up to interger and lowest results are rounded down? Hope someone can help. To round to the nearest integer: =ROUND(your_formula_here,0) -- Biff Microsoft Excel MVP ...

Formula help needed if possible!?
Hi there, i'm attempting to implement a cross-sheet formula to count the occurances of a single phrase or grouping in one sheet, producing a simple total figure in a cell in another. To put it into real terms, in one sheet there is a collumn with a series of initials indicating an occurence related to their performance - which I am looking to produce a small scorecard in a separate sheet for each user. Eh Sheet 1 - B7:B47 have phrases such as GD (KS), AG (KS) etc etc etc - I need a formula which counts the occurances of a specified such phrase, say GD (KS) and totals the figure in ...

help with IF THEN #3
What is the correct syntax to write a statement that, in english, does this If cell L4 = 0 Then delete cells K4 through M4 and shift the cells up Better yet can something be written that checks all L cells for the 0 value and if it finds 0 then deletes the adjacent K through M cells and shifts all cells up TIA ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ ...

Copy formula for ranges from diff sheets
A bit stuck hope someone can help. Am trying to copy a vertical range from a series of sheets onto on reference sheet horizontally. Any ideas? -- Berni ----------------------------------------------------------------------- Bernie's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1457 View this thread: http://www.excelforum.com/showthread.php?threadid=26197 you can Copy, Paste Special - Transpose -- icestationzbr ----------------------------------------------------------------------- icestationzbra's Profile: http://www.excelforum.com/member.php?action=...

I use several mail accounts, all of which are accessed via POP3. I have set up rules using the Outlook 2002 wizard that move certain incoming messages from the inbox to other folders, based on various criteria. Under Outlook 2000, those folders would get opened up in the tree list so that the bold folder names (for folders with the filtered, unread messages) could be seen. With Outlook 2002, even that small indication of filtered, unread mail is gone. I've found messages over a week old because I was unaware of this (mis)behavior. I have set up a saved search that locates all unread messa...

Using a dynamic formula to calculate results based on different assumptions
Hi everybody, I am looking for a simple way to resolve the following problem - don't worry if it sounds too architectural, you don't have to be an architect to solve this! I have a table of assumptions organised like this: Landuse Unit size Type 1 Type 2 Type 3............n Apt - 2bed 100m2 10% 50% 50% Apt - 1bed 60m2 20% 50% Shop 40m2 70% 50% ..... N I'd like to sum the total number of units based on the Type in another column (on another page) by di...

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...

Please, need help with multiple "if" conditions
This spreadsheet has 5 columns. In Column B, "District," you input the number of a school district. There are 20 districts, so these will between 1 and 20, inclusive. Each district will appear many times in the column (there are over 1,500 rows). Now: Dave is assigned to districts 1,3,4,6,9,17 and 18. Bill is assigned to districts 2,5,11,12,14,15 and 20 Mary is assigned to districts 7,8,10,13,16 and 19. (I'm guessing at these assignments - the actual assignments may be slightly different) What I need to do is configure this spreadsheet so that when the district number is ent...

Nested if statements Arrays
I basically have a collum of dates and I want to have a descriptive cell with the range of dates. The dates are all entered from another sheet by a user and this page is a summary page the formula looks something like: =A25&"-"&IF(AND(A26=0,A27=0,A28=0,A29=0,A30=0,A31=0,A32=0,A33=0,A34=0,A3 5=0,A36=0,A37=0),A25,IF(AND(A27=0,A28=0,A29=0,A30=0,A31=0,A32=0,A33=0,A3 4=0,A35=0,A36=0,A37=0),A26,IF(AND(A28=0,A29=0,A30=0,A31=0,A32=0,A33=0,A3 4=0,A35=0,A36=0,A37=0),A27 with A25 being the first date and the sequence of IF statements determining what the end date should be. This works...

Query with IIF Expression Need Help
Hi ALL, I have a query which has the following fields: ROOMS RESNAME ARRIVAL DEPARTURE I have added another field in my query named ROOMS IN as an expression where I want it to return TODAY by looking at another table which has only one record which is the running date table name:RUNDATE and field DATE and then looking at the arrival date and adding two days if applicable to return to the expression field LINEN "TODAY" LINEN: IIf([ARRIVAL]="([RUNDATE]![DATE]+2)";"TODAY";"2 DAYS") it returns 2 days to the other records which is fin...

dateDiff help
I have a query that uses the dateDiff function to calculate the number of weeks between dates. The problem I am having is if the dates are less than a week apart how can I get the query to update my text field to 1. Thanks in advance. So you want any fraction of a week to be counted as a week? So 1 day or 7 days would be 1 week; 8 - 14 days would be 2 weeks, etc. Try: - Int(DateDiff("d", [EndDate], [StartDate]) / 7) Explanation of how it works: http://allenbrowne.com/round.html#RoundUp -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - h...

Modify the existing formula
Dear sir, There is 1 data set is assumed running in A1 down, viz.: In A1 down is: 1,2,3,4,5,6,7,8,9, and 10 In F1 down is: 20, 7, 1, 12 and 8 And I have a excel forumula in the cell M1 as below: {=IF(ROWS(M\$1:M1)<=SUM(--(COUNTIF(\$F\$1:\$F\$22,\$A\$1:\$A\$10)>0)),INDEX(\$A\$1:\$A\$10, SMALL(IF(COUNTIF(\$F\$1:\$F\$22,\$A\$1:\$A\$10)>0,ROW(\$A\$1:\$A\$10)-MIN(ROW(\$A\$1:\$A\$10)) +1),ROWS(M\$1:M1))),"")} And then I drag the formula from M1 to M5. Therefore, the formula will give me the result "1" in cell M1, "7" in cell M2 and "8" in cell M3. The formula is extracted t...

How I set up as logical test if my range has to be >=2 but <5
I tried to use the formula "IF" to find different rates, one rate is if the # is >= 2 but <5 and give me the error, please help me One way: =IF(AND(A1>=2,A1<5),rate1,rate2) or, if you have three rates: =IF(A1<2,rateA,IF(A1<5, rateB, rateC)) In article <72F19D81-14B0-41A7-963C-B158D0D7A62C@microsoft.com>, "nsnjlacm" <nsnjlacm@discussions.microsoft.com> wrote: > I tried to use the formula "IF" to find different rates, one rate is if the # > is >= 2 but <5 and give me the error, please help me I'm no...

HELP!!!! RMS
Could anyone tell me if there is any requirement by Microsoft to first become a "certified partner" before being elligible for sitting in RMS - store operations certification exam? in simple words, Is there any Autorization code (given by micorosft to its certifed partners) required at the time of registring forthis course's exam? Or whether I can give this exam as an Independant IT professional? just like MCSD etc. I cant find this information anywhere and prometric testing centres at my locality do not have this information. Your timely help would indeed be appreciat...

Concatenating IF(AND formulas
::undefined::undefinedTo create a cash-flow spreadsheet of balance overdue by 30, 60 and 90 days respectively, as well as those sam increments upcoming in the future, I painstaking built the formula *=IF(AND(Y10>0,Y10<=30),X10,"")*, where X10 is the balance overdue b 0-30 days. The spreadsheet works fine because the columns for 31-6 days, 61-90 days, and >90 days each contain the appropriate values fo Y10, respectively. I copied and modified these four columns to eight to see who had futur balances by the same four increments. The resuslt is a clean spreadshee where eight ...

IF help #2
i have to calulate a Excel Spread sheet on a weekly basis so i know how to pay staff for product sales the file is output from a weird old dos program into a excel document I have 2 sheet inside a Excel document lets call them Sheet 1 and sheet 2 Sheet 1 has 3 columums Date Product and \$Due etc 8/06/06 TABLE 8/06/06 CHAIR 8/06/06 TABLE \$due Columum is not filled and i need this to auto fill Sheet 2 has a list of products in Columum 1 and 2 has the price on it PRODUCT \$DUE Table \$50 I need to do a if statement that if sheet 1 PRODUCT = SHEET 2 PRODUCT THEN sh...

Excel Formula problem
Okay, this is hard to describe without taking a little while so pleas bare with me. I work for an airline ticketing company and we are tryin to combine the data from several spreadsheets onto one main spreadshee which i stupidly volunteered to do. There is one worksheet for each ticketer which list details of the far but the two imporant aspects for this problem are the airline code an the value of the fare. Now, at the moment the airline code appears in column A and ticke value in column D. This data is entered by each ticketer and it is no sorted alphabetically by airline but by date. So e...

Need formula to locate and validate specific text from a string
Hi, I have the following sample data in column A. germany (testing) Germany (cold) Germany Austria (Testing) Austria (cold) Austria (working) Austria (other) China China (alternate) In column B, I want it to show that if the cell to the left has any of the words 'testing' or 'cold' or 'working' it should show "yes" in Column B else show "no". Is this possible? Regards, Vinstream Hello, Try; =IF(OR(ISNUMBER(SEARCH("testing",A1)),ISNUMBER(SEARCH("cold",A1)),ISNUMBER(SEARCH("working&qu...