Pick cell from chart

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ron F. - 11 Nov 2007 14:43 GMT

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

Reply to this Message

Bernard Liengme - 11 Nov 2007 19:26 GMT

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

Signature

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

>I am developing an estimating spreadsheet that calculates labor hours. I
>have
[quoted text clipped - 11 lines]
> chart
> or is there a easier way??

Reply to this Message

Ron F. - 12 Nov 2007 01:36 GMT

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.

> I have numbers 10,15,20,25,30 in B1:F1 (these are your Degree of
> Difficulty)
[quoted text clipped - 41 lines]
> > chart
> > or is there a easier way??

Reply to this Message

Bernard Liengme - 12 Nov 2007 12:53 GMT

Great. Just email me if more is needed

Signature

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

> Thank you for your help. I have tested your solution and it is working.
> The
[quoted text clipped - 60 lines]
>> > chart
>> > or is there a easier way??

Reply to this Message

Rate this thread: