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??
0
Utf
11/11/2007 2:43:01 PM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
656 Views

Similar Articles

[PageSpeed] 20

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 F.@discussions.microsoft.com> wrote in message 
news:0D718E85-E6D0-46F6-94A3-D61F95F3576A@microsoft.com...
>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?? 


0
bliengme5824 (3040)
11/11/2007 7:26:46 PM
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 F.@discussions.microsoft.com> wrote in message 
> news:0D718E85-E6D0-46F6-94A3-D61F95F3576A@microsoft.com...
> >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?? 
> 
> 
> 
0
RonF (10)
11/12/2007 1:36:01 AM
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." <RonF@discussions.microsoft.com> wrote in message 
news:76FF0F8F-A752-41FA-AEBE-1E6D0DDB59C0@microsoft.com...
> 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 F.@discussions.microsoft.com> wrote in message
>> news:0D718E85-E6D0-46F6-94A3-D61F95F3576A@microsoft.com...
>> >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??
>>
>>
>> 


0
bliengme5824 (3040)
11/12/2007 12:53:24 PM
Reply:

Similar Artilces:

Adding static character to a cell
I want to insert a $ in column of cells. Use currency as the format, but I want the $ to stay in the cell so it can be used also when printed out. I hope that's clear? Thanks Frank Frank, The $ in a currency format should also get printed. It is a view of the data, but consistent on screen and print. -- HTH RP (remove nothere from the email address if mailing direct) "Frank" <stratster68@IHATESPAMworldnet.att.net> wrote in message news:%23lA$94VRFHA.3988@tk2msftngp13.phx.gbl... > I want to insert a $ in column of cells. Use currency as the format, but I >...

How to link cell for value in another cell of pivot table?
In Excel 2003, when I link a cell in pivot table, it copies Getpivotdata formular. And when I drag or copy this cell, again it copies the formular. Can you help how to just get the value, then be able to drag to link next cell and its value? thanks. There are instructions here for adding the Generate GetPivotData button to a toolbar, and toggling the feature on and off: http://www.contextures.com/xlPivot06.html JamesChoi wrote: > In Excel 2003, when I link a cell in pivot table, it copies Getpivotdata > formular. > And when I drag or copy this cell, again it copies th...

cell to change colour at given date
I have a cell in a spreadsheet into which I type a date. When the pc clock shows that the date in the cell is more that 30 days old I want the cell to turn red. How would I do that. Basic to many of you I am sure, but to someone who doesn't use this side of Excel, virtually impossible ! (I'm using Excel 97 if that makes any difference - but I presume not) Thanks in advance Tobit LOL - My pleasure. Glad you got sorted, and thanks for the feedback. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL2K & XLXP...

Cell Data to Reflect Active Cell
Help - I'm struggling! Is it possible to switch the data in a particular cell to reflect the active cell as and when I move through a range in my spreadsheet? For example: If Cells: A1 = Apples A2 = Pears A3 = Oranges A4 = Lemons and my active cell is A1 then I would like B5 to = Apples. If I move focus so that the Active cell is now A3 I then I would like B5 to = Oranges and so on. Thanks in advance, Peter "Peter Davies" <peter@sedburypark.co.uk> wrote in news:#QJGurkqFHA.620@TK2MSFTNGP15.phx.gbl: > Help - I'm struggling! Is it possible to switch the...

Updating links
Hi, In one of our office spreadsheets, everytime it is opened it asks do you want to update cells from another spreadsheet. I believe when this spreadsheet was set up, a work sheet was copied from another spreadsheet. I thought that all the references to that other spreadsheet had been removed. Is there anyway to identify (other than manually checking each cell) which cell has a formula linked to the old spreadsheet. Thanks in advance, George Download and install Bill Manville's FindLink add-in, which you can find at: http://www.bmsltd.ie/MVP/ In article <bYd1d.27315$Z14.90...

Chart problem: values in column A doen't show on x-axis
The data in column A in the table I want to use for a chart contain numeric values: 2000 - 2001 - 2002 - ... In the chart these don't show as labels for the x-axis, but as a serie of data in the chart. Any way around this without changing all the values in coumn A int text -- digica ----------------------------------------------------------------------- digicat's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1492 View this thread: http://www.excelforum.com/showthread.php?threadid=49945 ...

How would I fill blank cells with the data from a previous cell?
Need to fill blank cells in a column with the data from the previous non-blank cell in Excel 2000. Used to do this in Lotus. Can't seem to get it in Excel try edit>fill>series (select one) Lotus's is simpler. >-----Original Message----- >Need to fill blank cells in a column with the data from the previous >non-blank cell in Excel 2000. Used to do this in Lotus. Can't seem to get it >in Excel > > >. > One way, assuming you mean you have data blank data blank or something like that and you want the cell with data to fill in the blanks below ...

select cell based on a number in another cell
This is what I want to do I have a cell A1 with the number 40 In another cell I want to use the number 40 to select a cell, something like: =B40+234 Where 40 in B40 is the number typed in A1. how do i do this please? Try this: =INDEX(B:B,A1)+234 -- HTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== <jon.berg@gmail.com> wrote in message news:1133883789.555648.81840@o13g2000cwo.googlegroups.com... This is what I want to do I have a cell A1 with ...

How to create 3 axes chart based on three series of data
...

picking out matched data
I have 2 different sets of data each with 3 columns. I am going to place the 2 sets of data on Sheet 1 of a spreadsheet and Sheet 2 of a spreadsheet. The data in a row across the 3 columns pertains to each other. This is true for the data on each sheet. I need to take the data from sheet 1 and have it match up with any entries that exist on sheet 2, and have it show these matches on sheet 3. The problem is the data in sheet 1 and 2 are not exactly the same. On sheet 1 the data is: Customer Name, Transaction#, Acct#. On Sheet 2 the data is: Customer Name, Reference #, Acct#...

Zooming in chart by 2 click ??
Hello all, Is there anyone who can tell me if it is possible to zoom in a chart by clicking 2 points on that one. Let's say a have a chart from where axis is from 01/01/2005 to 01/01/2006 with 1 point per day (365 points). Can i click 1 point - tht will be the entry point - and click an other one that will be the end of the zoom? Thanks for your help Herve. -- herve ------------------------------------------------------------------------ herve's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27314 View this thread: http://www.excelforum.com/showthread.p...

Select Range of Cells
Within a single column, how would I set a range to be just the cells that have data? I want to lock these cells using a change event, while leaving the remainder of the cells editable. I'm trying to set one range as the entire column, and a second range as cells with data, then using an intersect to lock the cells with data. This should allow for continuous locking of newly used cells. Maybe use SpecialCells(xlCellTypeBlanks) to find the empty cells and lock the column then unlock those -- HTH Bob Phillips "programmingrookie" <programmingrookie@discussions.micr...

sumif color cells with same month
My file have two columns A - Month B - Amount I will change the color to red of certain cells in column B Can I use the sumif function to total column B of those cells with same month and color red ? Many thanks eva cheng You can't sum by color (is there some criteria you could use, such as greater than x value?), but you can sum by month like this: =SUMPRODUCT(--(TEXT(A2:A100,"mmm")="Apr"),B2:B100) -- Best Regards, Luke M "eva cheng" <evacheng@discussions.microsoft.com> wrote in message news:769CE3C9-1043-4D16-B872-E80B07E47C73@m...

Changing Cell Reference in Formula
Excel 2007 I've got this big long formula: =IF(AND(CODE(RIGHT(A2,1))>=188,CODE(RIGHT(A2,1))<=190),1*LEFT(A2,LEN(A2)-1) +(CODE(RIGHT(A2,1))-187)/4,A2)-IF(AND(CODE(RIGHT(B2,1))>=188,CODE(RIGHT(B2,1))<=190),1*LEFT(B2,LEN(B2)-1) +(CODE(RIGHT(B2,1))-187)/4,B2) What I want to do is go through and change all of the A2 to k16, and change all of the b2 to k15. What is the easiest way to do that? Select at least two cells (click on this cell, and ctrl-click on an empty cell) Then use a couple of Edit|Replace's. JimS wrote: > > Excel 2007 > > I've got this b...

Using the contents of a cell in a cell reference
In order to save time it would be useful to create a cell reference that contained a variable. Does anyone know if this can be done and if so what the notation might be? The idea is this: If I normally want to reference a data set from D5:D21 but sometime the length of the data changes an it might be D5:D35 it would be helpful if I could enter 21 or 35 in cell A1 and then reference the data set as D5:D(A1) that way anytime I change the value in A1 my data set would be updated. Thanks in advance Tom I think you want to use =indirect(). A simple example: =SUM(INDIRECT("D5:D&...

How do I stop graphs reading zero in Excel from cells with ""?
Hi. I am making a model in Excel which is takes data updates from an outside source and then processes the information to develop charts on the data. There are a lot of charts so I have developed the model to process the data automatically as the data comes (down the sheet). This processing means that there are formulas in place, waiting for the data to come in. I use a =if(a1="","",<operation>) notation to do it for me. If there is data then the if statement is false and the data can be processed; if the data hasn't arrived yet then instead of getting...

Bad visbility off a cell
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Hi, i am new here. I have a question, when I click in a cell in an excelsheet then this is obscured. I mean the square of the cell is almost invisible. So if there is a lot of figures in this sheet you can not find back the cell where u was working. Has anyone a solution how to change in a better visibiliy? Thanks in advance. Thijs On 3/3/10 8:04 AM, Thijs@officeformac.com wrote: > Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: > Intel Hi, i am new here. I have a question, when...

unlock frozen cells
I opened excell program to add data but it is locked. I can't add nor delete data. None of the functions work. It opens, but is locked. Help It might be that you have a 'Freeze Panes' issue. To unfreeze the windows... In 2003 - WINDOWS > UNFREEZE PANES in 2007 + VIEW > WINDOWS > UNFREEZE PANES -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "Windows 7" wrote: > I opened excell program to add data but it is locked. I can't add nor delete > data. None of t...

Plot Area Changes in Charts
Hi. I have a spreadsheet that has several hundred embedded charts. The data is updated monthly and the charts automatically update themselves. The problem is that when the data is updated on the charts, the plot area reverts to some default size which does not take advantage of alot of the chart size. I don't want to go to each chart every month and manually increase the plot area. Any ideas? Thanks. Jeff G I forgot to mention that the files are excel 2003. "Jeff Gross" wrote: > Hi. I have a spreadsheet that has several hundred embedded charts. The data > ...

what type of information we can enter into spreadsheet cells?
Nina wrote: <nothing> Please use this BIG WHITE SPACE for your detailed question. You can enter ANYTHING you like in a cell...... Is this a homework question possibly? -- Asking a question? Please tell us the version of the application you are asking about, your OS, Service Pack level and the FULL contents of any error message(s) ...

Cell Protection
Arite. Does anybody Know if its posible to protect an individual cell The only options i get are to "protect sheet", "protect workbood" o "protect and share workbook". Im needing a bit of help. Cheers -- college ----------------------------------------------------------------------- college1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1502 View this thread: http://www.excelforum.com/showthread.php?threadid=26642 Hi - first select all cell you DON'T want to protect - goto 'Format - Cells - Protection' and uncheck &#...

Charts That Communicate Progress
I am trying to select charts that will lend themselves well to communicating progress. For example, Pie Charts don't tend to be a good way for communicating progress whereas another type of Chart might be more effective. In other words here is where we were, here is where we are, and here is where we want to be. What would you recommend? Hi, Sounds like you need some sort of Gannt chart. Dave "Workbook" wrote: > I am trying to select charts that will lend themselves well to communicating > progress. For example, Pie Charts don't tend to be a good way for ...

Making pick list conditional on selection from previous pick list
I would like to know how to make a pick list (a "list" of names) conditional on what was picked in the previous column pick list (e.g. of the 4 main areas of a plant (pick list/ column A), there are anywhere from 3 to 5 sections within each main area. I would like to format/program the second pick list (coumn B) to only show the list of sections corresponding to the 'main area' selected in column A. Then I need to do this for a third pick list (specific equipment). If you have an example, it would be appreciated. Thank you. Hi Stewart, See Debra Dalgleish's Depend...

How do I average a range of cells when one cell contains #N/A
I perform a lookup where the results could populate one cell or as many as 15 cells with number results. The cells that do not result in numbers have #N/A. I want to average the fifteen cells but only the cells with numbers. How do I get it to ignore the #N/A when performing the average? =AVERAGE(IF(NOT(ISNA(A1:A15 )),A1:A15)) which is an array formula, so commit with Ctrl-Shift-Enter -- HTH Bob Phillips "hongkonglt" <hongkonglt@discussions.microsoft.com> wrote in message news:5EF99B0B-230F-47FE-A795-56C4E3B84C46@microsoft.com... > I perform a lookup where the re...

Moving from cell to cell is very slow!!
in a machine with all office updates and xp sp2, when using excell (2000) if you move from cell to cell either to edit data or just to format a group of cells it takes about a minute. even in a new empty excel spread sheet. is there a setting or maybe one of the updates cause the program to do a search or something to delay the switching between cells? any help would be appreciated. "Skoal" <Skoal@discussions.microsoft.com> wrote in message news:E1585839-B4AC-4EBB-AA13-222459E306E8@microsoft.com... > in a machine with all office updates and xp sp2, when using excell (...