Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Pick cell from chart
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?? |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Pick cell from chart
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?? |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Pick cell from chart
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?? |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Pick cell from chart
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?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Random pick of a cell | Excel Discussion (Misc queries) | |||
Automaticall pick up info from a cell from a shet t oanither sheet , in the same cell, same book | Excel Worksheet Functions | |||
How to pick every third cell? | Excel Discussion (Misc queries) | |||
VBA: how to assign or pick a value in other Cell | Excel Discussion (Misc queries) | |||
Bubble chart will not pick X series | Charts and Charting in Excel |