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

  • Thread starter Thread starter Combat High
  • Start date Start date
C

Combat High

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.
 
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
can help you. HTH Otto
 
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.
 
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
 
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. :(
 
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!
==============================================


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. :(
 
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.?
 
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.?
Click to expand...

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.
 
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.
Click to expand...

....Yes, I had to format the row as "Text" - otherwise "1/0" created a date.
:(
 
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!
-------------------------------------------------------------------

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.
Click to expand...

....Yes, I had to format the row as "Text" - otherwise "1/0" created a date.
:(
 
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?
 
Combat High wrote:
..
Well I saw some things on LOOKUP (and MATCH) that is *almost* perfect
Click to expand...
for me but, for example, I need the LOOKUP_VALUE to be an ARRAY which
Click to expand...
fails when I try.
Click to expand...

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
Click to expand...
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?
Click to expand...

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

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?
 
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 said:
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
Click to expand...
probably an easy way to arrange all the variables but I have no clue.
Click to expand...
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.
Click to expand...

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
 
Back
Top