# Excel Help

Occasional Contributor

# Excel Help

Hello I am having difficulty with a formula and was hoping someone could help me here. I am trying to reference a cell from a different sheet that has a word in it. I want it to be represented by a number on the next sheet, so I am using this formula =IF('CTQ Pin Gage (1)'!V3="Go","1","2"). The only thing that shows up is 2's even if the cell contains "Go." Any help I could get, I would greatly appreciate! Thanks

12 Replies

# Re: Excel Help

Are you testing for "Go" or "Go." ?

# Re: Excel Help

In my equation? "Go" I am measuring a part of a manufacturing company and they have a go no-go gauge so I am collecting data on that, if that helps.@Kayak2

# Re: Excel Help

Select Formulas - Evaluate Formula and step through the evaluation, I suspect either the formula is testing the wrong cell or the cell gas the wrong value in it

# Re: Excel Help

Unless the Go / No-Go is in a drop down validated (controlled input) cell I would be concerned about if the "Go" is exactly the same. Maybe try something like:
=IFERROR(IF(search("Go",'CTQ Pin Gage (1)'!V3,1)=1,1,2),2)

# Re: Excel Help

I couldn't find the evaluate formulas thing, not sure if that is because there isn't an error and it's just confused? I'm not super technical in Excel so I have no idea.

# Re: Excel Help

@mtarler I'm not sure what a dropped down validated cell is but I tried your formula, and it's doing the same thing that mine did.

# Re: Excel Help

Try doing a simple test in a new spreadsheet with the formula and the cell with the value "Go" in the same sheet

# Re: Excel Help

@MrMcQweefan  Data validation is a tool you can enable on cells to make sure what is entered meets specific criteria.  e.g. if it has to be a number 1-9 or a date or specific names or text from a list of options and when you use a list you can have the option to have a drop down so the user can click on the selection they want.

As for the problem I agree with the other members above that something else is probably wrong like you are pointing to the wrong cell.  It would be easier if you attached the spreadsheet.

a simple test you can do is change the formula to

='CTQ Pin Gage (1)'!V3

and see what it says is in that cell

# Re: Excel Help

@MrMcQweefan  yes you are pointing to the wrong cell

try this:

=IF('All'!V3 = "Go", "1", "2")

that said, you appear to be creating 'sub-tables' and should really consider using pivot tables or the new filter array formulas if you have them, but to get you up and going that should work.  Basically the 'All' is the tab name and you had 'CTQ Pin Gage (1)' which is the tab you are on and cell V3 on that tab is blank.

Hope that helps.

# Re: Excel Help

@mtarler It worked! Thanks so much!

# Re: Excel Help

I’m having difficulty aligning my text to an angle on my iPad and I don’t have access to a computer at the moment