I am developing an estimating spreadsheet that calculates labor hours. I have
built a separate 10 column and 6 row chart with info I want to pull into the
estimating spreadsheet. The colums are labeled as "Difficulty Factors" and
the rows are labeled as "Pipe Sizes". Each intersecting cell is a portion of
a labor hour.
The estimating sheet has 3 drop down lists. I want to build a IF AND
statement that will select a intersecting cell in the chart if certain
criteria from the drop down lists are true.
There are 60 different cells in the chart. Is it necessary to write 60
different IF AND arguments to obtain info from a particular cell in the chart
or is there a easier way??

I have numbers 10,15,20,25,30 in B1:F1 (these are your Degree of
Difficulty)
In A2:A6 I have text a,b,c,d,e, (these are you pipe sizes) - they could just
as easily be numbers. In B2:F6 I have some labour hours (made up numbers)

In C10 I have text "Pipe Size" and in C11 I have a cell that is linked to
the A2:A6 values by Data Validation
In D10 I have text "Difficulty" and in D11 I have a cell that is linked to
the B1:F1 values by Data Validation

In C12 I use =MATCH(C11,A2:A6) to return the position of the chosen pipe
size
So if C11 is b, the Match returns 2 since b is the second item in the list
Likewise in D12 I have =MATCH(D11,B1:F1); If the chosen degree of difficulty
is 25, the MATCH returns 4

In C13 I have =INDEX(B2:F6,C12,D12) This looks at the labour-hours table and
find the row shown in C12 and the column in D12

Now for the trick: I selected B2:F6 (the labour-hour table) and used Format
| Condition Formatting and set this for Formula is =B2=$C$13 and choose a
blue fill pattern. So the cell that matches C13 is now blue

If this is of any interest to you, please send me private message (remove
TRUENORTH. form my email address shown here) and I will forward the file

(By the way: 'chart' means a graph to excellers; yours is a 'table' or
'array')

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Ron F." <Ron wrote in message
...
I am developing an estimating spreadsheet that calculates labor hours. I
have
built a separate 10 column and 6 row chart with info I want to pull into
the
estimating spreadsheet. The colums are labeled as "Difficulty Factors" and
the rows are labeled as "Pipe Sizes". Each intersecting cell is a portion
of
a labor hour.
The estimating sheet has 3 drop down lists. I want to build a IF AND
statement that will select a intersecting cell in the chart if certain
criteria from the drop down lists are true.
There are 60 different cells in the chart. Is it necessary to write 60
different IF AND arguments to obtain info from a particular cell in the
chart
or is there a easier way??

Thank you for your help. I have tested your solution and it is working. The
match and index formulas are new to me, so I will be doing a bit of testing
until I fully understand them. I am working on a more complex situation with
more than 2 matches. I may need additional help.

Thanks for the assistance.

"Bernard Liengme" wrote:

I have numbers 10,15,20,25,30 in B1:F1 (these are your Degree of
Difficulty)
In A2:A6 I have text a,b,c,d,e, (these are you pipe sizes) - they could just
as easily be numbers. In B2:F6 I have some labour hours (made up numbers)

In C10 I have text "Pipe Size" and in C11 I have a cell that is linked to
the A2:A6 values by Data Validation
In D10 I have text "Difficulty" and in D11 I have a cell that is linked to
the B1:F1 values by Data Validation

In C12 I use =MATCH(C11,A2:A6) to return the position of the chosen pipe
size
So if C11 is b, the Match returns 2 since b is the second item in the list
Likewise in D12 I have =MATCH(D11,B1:F1); If the chosen degree of difficulty
is 25, the MATCH returns 4

In C13 I have =INDEX(B2:F6,C12,D12) This looks at the labour-hours table and
find the row shown in C12 and the column in D12

Now for the trick: I selected B2:F6 (the labour-hour table) and used Format
| Condition Formatting and set this for Formula is =B2=$C$13 and choose a
blue fill pattern. So the cell that matches C13 is now blue

If this is of any interest to you, please send me private message (remove
TRUENORTH. form my email address shown here) and I will forward the file

(By the way: 'chart' means a graph to excellers; yours is a 'table' or
'array')

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Ron F." <Ron wrote in message
...
I am developing an estimating spreadsheet that calculates labor hours. I
have
built a separate 10 column and 6 row chart with info I want to pull into
the
estimating spreadsheet. The colums are labeled as "Difficulty Factors" and
the rows are labeled as "Pipe Sizes". Each intersecting cell is a portion
of
a labor hour.
The estimating sheet has 3 drop down lists. I want to build a IF AND
statement that will select a intersecting cell in the chart if certain
criteria from the drop down lists are true.
There are 60 different cells in the chart. Is it necessary to write 60
different IF AND arguments to obtain info from a particular cell in the
chart
or is there a easier way??

Great. Just email me if more is needed
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Ron F." wrote in message
...
Thank you for your help. I have tested your solution and it is working.
The
match and index formulas are new to me, so I will be doing a bit of
testing
until I fully understand them. I am working on a more complex situation
with
more than 2 matches. I may need additional help.

Thanks for the assistance.

"Bernard Liengme" wrote:

I have numbers 10,15,20,25,30 in B1:F1 (these are your Degree of
Difficulty)
In A2:A6 I have text a,b,c,d,e, (these are you pipe sizes) - they could
just
as easily be numbers. In B2:F6 I have some labour hours (made up numbers)

In C10 I have text "Pipe Size" and in C11 I have a cell that is linked to
the A2:A6 values by Data Validation
In D10 I have text "Difficulty" and in D11 I have a cell that is linked
to
the B1:F1 values by Data Validation

In C12 I use =MATCH(C11,A2:A6) to return the position of the chosen pipe
size
So if C11 is b, the Match returns 2 since b is the second item in the
list
Likewise in D12 I have =MATCH(D11,B1:F1); If the chosen degree of
difficulty
is 25, the MATCH returns 4

In C13 I have =INDEX(B2:F6,C12,D12) This looks at the labour-hours table
and
find the row shown in C12 and the column in D12

Now for the trick: I selected B2:F6 (the labour-hour table) and used
Format
| Condition Formatting and set this for Formula is =B2=$C$13 and choose a
blue fill pattern. So the cell that matches C13 is now blue

If this is of any interest to you, please send me private message (remove
TRUENORTH. form my email address shown here) and I will forward the file

(By the way: 'chart' means a graph to excellers; yours is a 'table' or
'array')

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Ron F." <Ron wrote in message
...
I am developing an estimating spreadsheet that calculates labor hours. I
have
built a separate 10 column and 6 row chart with info I want to pull
into
the
estimating spreadsheet. The colums are labeled as "Difficulty Factors"
and
the rows are labeled as "Pipe Sizes". Each intersecting cell is a
portion
of
a labor hour.
The estimating sheet has 3 drop down lists. I want to build a IF AND
statement that will select a intersecting cell in the chart if certain
criteria from the drop down lists are true.
There are 60 different cells in the chart. Is it necessary to write 60
different IF AND arguments to obtain info from a particular cell in the
chart
or is there a easier way??