Difficult function question for columns

I would like the create a column that performs the following function:

[If column A=1, then display number in cell B10; If Column A=2, then display 
number in cell B11, If column A=3; then display number in cell B12]

If it is of any importance, in the above example B10:12 have a 
=SUMIF/COUNTIF function in them.

Thanks in advance.
0
Utf
12/21/2009 8:02:02 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
779 Views

Similar Articles

[PageSpeed] 56

AAA1986 wrote:
> I would like the create a column that performs the following function:
> 
> [If column A=1, then display number in cell B10; If Column A=2, then display 
> number in cell B11, If column A=3; then display number in cell B12]
> 
> If it is of any importance, in the above example B10:12 have a 
> =SUMIF/COUNTIF function in them.
> 
> Thanks in advance.


=INDEX(B10:B12,A1)

=OFFSET(B10,A1-1,0,1,1)

=INDIRECT("B"&A1+9)
0
Glenn
12/21/2009 8:15:36 PM
=IF(A1=1,B$10,IF(A1=2,B$11,IF(A1=3,B$12,"whatever you want if it isn't any 
of those")))
--
David Biddulph

"AAA1986" <AAA1986@discussions.microsoft.com> wrote in message 
news:8FC5115F-8787-42B4-9208-0E58CB0FCFAE@microsoft.com...
>I would like the create a column that performs the following function:
>
> [If column A=1, then display number in cell B10; If Column A=2, then 
> display
> number in cell B11, If column A=3; then display number in cell B12]
>
> If it is of any importance, in the above example B10:12 have a
> =SUMIF/COUNTIF function in them.
>
> Thanks in advance. 


0
David
12/21/2009 8:17:48 PM
Perfect! Thank you.

"David Biddulph" wrote:

> =IF(A1=1,B$10,IF(A1=2,B$11,IF(A1=3,B$12,"whatever you want if it isn't any 
> of those")))
> --
> David Biddulph
> 
> "AAA1986" <AAA1986@discussions.microsoft.com> wrote in message 
> news:8FC5115F-8787-42B4-9208-0E58CB0FCFAE@microsoft.com...
> >I would like the create a column that performs the following function:
> >
> > [If column A=1, then display number in cell B10; If Column A=2, then 
> > display
> > number in cell B11, If column A=3; then display number in cell B12]
> >
> > If it is of any importance, in the above example B10:12 have a
> > =SUMIF/COUNTIF function in them.
> >
> > Thanks in advance. 
> 
> 
> .
> 
0
Utf
12/22/2009 2:38:01 PM
Reply:

Similar Artilces:

2K7 question
Is there a way in E2K7 to make a particular message always send a message out in plain text format? Is that just an Outlook setting? Thanks! ...

Question #3
Hi I have some data that I graph. Column A has a date [has data in 12 rows] Column B has a data entry (number) [has data in 1st 6 rows] Column C has a data entry (number) [has data in 1 st 6 rows] Column D has a formula that calculates a % on column B & C [formulas in 12 rows] The graph range I have covers the whole 12 rows but it seems to read the formulas in col D and plots every row Is there something I can add to my formula that stops the graph from plotting it unless there has been an answer returned in it? Formula I have is : =IF(B5+C5<1,"",(B5-C5)/B5) Thanks ...

Registry question
Hello Group, I have an application that saves some configuration data in the registry system (under HKEY_CURRENT_USER\MyApp\ConfData) of the currently logged on user. This way, every user who runs this app will have his own configuration data. When a user runs for the first time this app, the 'MyApp' registry key will be created along with all its subkeys. Now suppose we have 3 users and all of them have already used the app. We will then have the 'MyApp' subtree created for these 3 users. What I want to know is that when the PC admin wants to uninstall the app, how...

Problem cut-n-paste function
I'm having a bizarre problem cut-n-pasting a very simple calculation (ie = A1 � B1) in C1 down the column (ie C2 to Cn). When I cut-n-paste the function in C1, value that displays is the same as the source cell. If I click on the target cell, the correct cells are highlighted and if I hit the enter button, the formula calculates as expected. What is going on? What do I have to do to force the formula to auto calculate? Turn on Automatic Calculation <g>. You do that at Tools/Options/Calculation. On 27 Sep 2004 18:56:34 -0700, jsausten@hotmail.com (Stephen Miller) wrote: >I'm...

Defrag question #3
I have a windows 2000 server that's a file/print server and Exchange 2000 also installed and running on the same box. I was wondering what is the best way if I want to run defrag and scandisk on the disk drive? I guess I'm asking because exchange is running on the box and I don't know if I run defrag and scandisk will I run into problems or cause the database problems. Thanks for your help. John In article <4D7A888F-B293-400C-A77C-DC379DDF4BDF@microsoft.com>, John@discussions.microsoft.com says... > I have a windows 2000 server that's a file/print server and Ex...

ADMT Tool Question
I'm trying to run this tool without success from a Windows 2003 native domain to extract accounts from an NT4 domain. Is this possible? Windows 2003 server Domain: Windows 2003 native mode Forest: Windows 2000 NT4 server sp6a http://www.petri.co.il/active_directory_migration_tool_usage_nt_windows_2003.htm "bobs" <myname@yourdomain.com> wrote in message news:e27m%23Km4FHA.2532@TK2MSFTNGP09.phx.gbl... > I'm trying to run this tool without success from a Windows 2003 native > domain to extract accounts from an NT4 domain. Is this possible? > > Window...

Question about Application behaviour,...
Hi, i have a simple MFC Application, that shows me the System Memory Usage. But after a while the Applications GDI Objects Count grows to something about >8.000 GDI Objects and its Virtual Memory (not much but step by step too) in less than 5 Minutes. I can see with ProcessExplorer that the application does have a high count of page faults. I cant figure out why! The System then starts to react slowly. The Timer invokes every seconds the member function with the code following this text, sets the Position on a progress bar control and raws a transparent text on the surface of the prog...

question on sorting by last name
I have a spreadsheet that has over 5,000 rows and somehow the sort order was removed I need it to sort by column f which is Month/Year and then by column A which is labeled Member Name. The member name column has first and last name and has always been sorted by last name, but for some reason now is sorted by month/year correctly, but by first name instead of last name. How can I make this change and also add the blank lines back between the Month/years going down the spreadsheet. there were also always blank lines splitting up for instance May-03 from June-03 ect. and now all the bl...

How to substitute for a non-existing column in a joined table
Hi, Is there a simpler way than a UNION to return a default value of a joined table for which a corresponding row does not exist? The following example (not a working one, of course) illustrates what I'm after. I'd like to return 'N/A' as c2name if there is no matching row in t2 SELECT t1.c1 (CASE t2.t1pk WHEN NULL THEN 'N/A' ELSE t2.name END) AS c2name, FROM t1 LEFT JOIN t2 ON t2.t1pk = t1.pk Thanks. On 2010-04-21 21:05, bob wrote: > Is there a simpler way than a UNION to return a default value of a joined > table for which a ...

xy scatter plot question
I have xy scatter plot using dates for x axis. I change the min and max on the x axis weekly. The problem is that the lines are "bleeding" over the y axis. How do I make the lines go to the edge of the y axis but not over? (make sense?). thanks I suspect that's how some MS programmer decided to indicate "there's data back there that I cannot show." One way to 'fix' this would be to add a rectangle to the chart from the Drawing toolbar and position it properly. Kind of a pain but it works. Another option would be to set the min. x value equal to...

Question about Status Reason field
I am looking into what the Status Reason field is used for in relation to the Account object. There are two default values, Active and Inactive. I want to add values so that when an Account is inactive, we can state the reason that is inactive. I can add values to the Inactive state without a problem, but then how do I choose any of the values that I defined since when an account is Inactive, I cannot edit any of the fields? I can define a default value, but that again is of no use, because I can't change it. This seems to be a bug or oversight on the part of the CRM developers. You nee...

Project Accounting 9.0 Question
I have been getting familiar with the project accounting module. I understand how to set up contracts, projects and cost categories. I need some clarification re: the assignment of costs to the projects. If we purchase goods or services from a vendor for a project how are these to be processed? Do these have to be booked through the POP receivings window to update project costing? Is there a way to update a project through payables transaction entry? I see a window for setting up units of measure. Can we set up the services we purchase from a vendor independently of the inventory item m...

FORMATING COLUMNS..... HELP
I need to format columns to allow only 7 characters and the rest of the data to go into column B -- JTEFUN "JTEFUN" <JTEFUN@discussions.microsoft.com> wrote in message news:6C5C7A99-E83F-430E-9576-6D1DB57B9311@microsoft.com... >I need to format columns to allow only 7 characters and the rest of the >data > to go into column B > -- > JTEFUN > If you mean that if a user types a lot of data into the one cell and that when they reach 7 characters the rest of the data is automatically inserted into the next cell, then I don't think you can do that....

Report Writer Dater Function
Hello: Does anyone know how to create a restriction in Report Writer to show the most recent date? I want to obtain the most recent date for the Apply to Document Date field in the body of my report. This field is in the Apply Open File (RM20201). Thanks. childofthe1980s ChildOfThe1980s, I would be more than glad to help if you could further explain what report in particular is this for, is it an aging? If so, would you like the most recent apply to date to display on a summary aging? Not sure I am following what you need. MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Busines...

Multi-language support question
We have an MFC application that uses ADO, ATL components to communicate with a SQL Server 2000 database. Our application is currently English only and we are making the conversion to support multiple languages. The text in the application is read in from a table so translating the text itself won't be a big deal programmatically. The text is loaded at log in and we can just autoswitch to the appropriate data based upon who the user is. My concerns are with different character sets, how the different languages impact the OS (how we set the character set in the OS automatically)...

2008 SBS questions... 07-04-10
I am trying to get a 2008 sbs running as efficiently as I can & was wondering about a couple of things. 1) I know it wants to be the DHCP server on a network, but what about for wireless clients? I have a wireless sonicwall TZ-170 for wireless laptops to connect to. They really don't need network access, just internet, so can I leave this running, as long as the addresses it hands out aren't in my same network? 2) Although the bought an SBS, the customer is not & probably won't be running exchange for a while. Can I remove exchange all together? I really see no rea...

DNS Question
Hello All, I am working with a customer that has 2 Small Biz servers in the same office. These are on 2 domains, but the same subnet. I am having trouble getting mail to route between the 2 domains. Each is Small Biz server 2003. Domain A has a public IP of 72.151.19.122 and an internal IP of 192.168.200.78. Domain B has a public IP of 72.151.19.123 and an internal IP of 192.168.200.250. We are using a Netgear firewall and inbound and outbound mail between each of these domains and the rest of the world works fine. They just can't seem to talk to each other. I would think this woul...

Rows and Columns Settings Problem
How do you set rows and columns in a way that when you scrol down/column you can always see a certain row(s)/column(s) -- Message posted from http://www.ExcelForum.com Check out XL Help for "Freeze Panes" In article <JMorgan.1ad5vf@excelforum-nospam.com>, JMorgan <<JMorgan.1ad5vf@excelforum-nospam.com>> wrote: > How do you set rows and columns in a way that when you scroll > down/column you can always see a certain row(s)/column(s). Thank -- Message posted from http://www.ExcelForum.com ...

Question re:clustered column w/3D visual effect
I have Excel 2003 Why wont the 3D chart allow you to drag it more open? There is a large open area between the left side and the axis that looks jerky :) In a plain clustered column chart you can click inside to make the frame appear and drag it larger or smaller as you desire. In the 3D, clicking only allows you to change the angles of the 3D box. Am I missing something? thanks, Meenie This should be a hint to avoid the 3D effects. 3D charts are inflexible, but more important, the 3D effects mask the information in the chart. - Jon ------- Jon Peltier Peltier Technical Services, Inc....

BULK Conditional Formatting
I've read the posts on conditional formatting for cell colour based o another cell's value (eg. set the conditional formatting to "formul is" and then "=A1>0" and set the colour as red / blue / whatever...) however wondering if I can do this for an entire column withou individually changing the conditional formatting for each cell one b one (as there are over 400 rows). Basically I need a formula that reads the contents of column B for th particular row that is active. Can anyone help -- Rob Moyl ---------------------------------------------------------------...

Charting depending on criteria & data series name as a column val
I want to chart some prices as I want to take a look at price trends. My problems, and I can not figure out how to solve them, are: 1. Is it possible to dynamically change the chart depending on certain criteria (product family & selected customers) 2. As the number of customers is variable and they are in one of the columns. Is it possible to plot a series (customer name), depending on the name of a column? 3. I want to chart the data based on the date, but just include the dates available, to prevent periods of time showing no change (e.g. I don't want monthly ticks for the x a...

SERIES function disappear
I can't use SERIES in Excel 2007 (err msg, input function not correct) even i cant find SERIES function at "Insert function" dialog i disable Marco in Excel 2007, is it related? Pls Help, Thank you What were you expecting SERIES to do for you as a worksheet function? In Excel 2003, SERIES didn't exist in a worksheet, but only in a chart. -- David Biddulph "Vimm" <Vimm@discussions.microsoft.com> wrote in message news:1FA6C85E-A9F2-471F-9629-8ED8B9901CB8@microsoft.com... >I can't use SERIES in Excel 2007 (err msg, input function not correc...

Drill Down Function
I'm trying to create a function that will open a detail form based on a field value in a master form. I can do this using a private sub, but I hate replicating the same private sub over and over again each time I want to drill down from a different form or control. I also like being able to run a function directly from the On Double Click event of any given control and form without first having to go to a private sub. The function would have to be told the name of the detail form to open, the name of the key field in the detail form, and the value to search for. Here...

How do I filter rows based upon a column value
I have a spreadsheet that contains multiple agency id's in a column. When generating reports, I would like to filter per agency and display only the rows associated with that agency. Is there a tutorial or sample on how to do this? Hi It sounds like you are looking for Data / Filter / AutoFilter. Have a look here for some basics: http://www.contextures.com/xlautofilter01.html -- Andy. "Jack" <nfr@nospam.com> wrote in message news:eqiU08TVEHA.2988@TK2MSFTNGP10.phx.gbl... > I have a spreadsheet that contains multiple agency id's in a column. When > generati...

CHtmlView question #3
Is there an easy way to get the source of the HTML document? On MSDN online, there's a GetSource(CString& str) listed but that's apparently not implemented in VC++ 6.0. ...