Excel 2007 : How to make a figure show a value in another cell dependent on a cell with text

**Syzaghi** · 09-22-2011, 12:05 PM

How to make a figure show a value in another cell dependent on what line of text is chosen in an another cell?

In Cell A (Sheet1) I have a drop down menu with "Stock" " Sales "Advertising"

The drop down menu is on sheet2,
(Stock= Sheet2!A1)
(sales=Sheet2!A2)
(Advertising=Sheet2!A3)

in cell B is blank but will have a numerical value added when needed.

I have three columns C, D, E
C is STock
D is Sales
E is Advertising

If A1 = "stock" and B1 has a value typed in then C1=B1
but if B1 is left blank then C1 should not have anything in it or equal zero
and using the formula underneath C1 contains #VALUE when B1 is left blank or "Advertising" or "Sales" is selected in A1

=IF(FIND("Stock",A1)>0,SUM(B1))
This formula does the calculation but #VALUE! appears if "Stock" is not selected,
how can I stop this or equal C1 to 0 if "Stock" is not selected.

I have been racking my brains for nearly 24 hours and searched many forums please someone put me out of misery and help or at least tell me if I am going about the whole conundrum the wrong way.
Thanks in advance.

**Andrew-R** · 09-22-2011, 12:10 PM

Hello and welcome to the forum - unfortunately you need to read the forum rules, because rule #1 says that threads must have titles descriptive of the problem, not generic requests for help. If you edit your post and sort out the title then I'll be happy to help you out with your formula.

Thanks.

**Syzaghi** · 09-22-2011, 04:19 PM

Originally Posted by Andrew-R

Hello and welcome to the forum - unfortunately you need to read the forum rules, because rule #1 says that threads must have titles descriptive of the problem, not generic requests for help. If you edit your post and sort out the title then I'll be happy to help you out with your formula.

Thanks.

Sorry Andrew, Rookie mistake! I have changed the title and I will read the rest of the rules asap!

**Andrew-R** · 09-22-2011, 04:41 PM

No problems - just notice that the rules also say not to quote entire posts

As for your formula - I think all you need in C1 is =IF(A1="Stock",B1,"")

If you want to be thorough then when working with strings it's a good idea to use the TRIM function to make sure that they have no leading or trailing spaces and the UPPER function to put them all into upper-case (or the LOWER function to put them all into lower-case, if you prefer). So that would give you =IF(UPPER(TRIM(A1))="STOCK",B1,"")

And actually your original formula wasn't that far out, the FIND function finds one string within another and returns the character position that the searched for string starts at. The trouble is that if it can't find the string it returns an error, which is why you were having the problem.

The fixed version of your original formula would be =IF(ISERROR(FIND("Stock",A1)),"",B1)

Hope this helps.

**Syzaghi** · 09-23-2011, 06:57 AM

Thank you Andrew for taking your time to help me. The formula worked perfectly and i'll make my Titles shorter

Excel 2007 : How to make a figure show a value in another cell dependent on a cell with text

LinkBack

Thread Tools

Rate This Thread

Display

How to make a figure show a value in another cell dependent on a cell with text

Re: Please help with this formula

Re: Please help with this formula

Re: How to make a figure show a value in another cell dependent on a cell with text

Re: How to make a figure show a value in another cell dependent on a cell with text

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions