Excel Help

%3CLINGO-SUB%20id%3D%22lingo-sub-1423986%22%20slang%3D%22en-US%22%3EExcel%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1423986%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20I%20am%20having%20difficulty%20with%20a%20formula%20and%20was%20hoping%20someone%20could%20help%20me%20here.%20I%20am%20trying%20to%20reference%20a%20cell%20from%20a%20different%20sheet%20that%20has%20a%20word%20in%20it.%20I%20want%20it%20to%20be%20represented%20by%20a%20number%20on%20the%20next%20sheet%2C%20so%20I%20am%20using%20this%20formula%20%3DIF('CTQ%20Pin%20Gage%20(1)'!V3%3D%22Go%22%2C%221%22%2C%222%22).%20The%20only%20thing%20that%20shows%20up%20is%202's%20even%20if%20the%20cell%20contains%20%22Go.%22%20Any%20help%20I%20could%20get%2C%20I%20would%20greatly%20appreciate!%20Thanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1423986%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1424013%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1424013%22%20slang%3D%22en-US%22%3EAre%20you%20testing%20for%20%22Go%22%20or%20%22Go.%22%20%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1424029%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1424029%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20my%20equation%3F%20%22Go%22%20I%20am%20measuring%20a%20part%20of%20a%20manufacturing%20company%20and%20they%20have%20a%20go%20no-go%20gauge%20so%20I%20am%20collecting%20data%20on%20that%2C%20if%20that%20helps.%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F367901%22%20target%3D%22_blank%22%3E%40Kayak2%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1424133%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1424133%22%20slang%3D%22en-US%22%3ESelect%20Formulas%20-%20Evaluate%20Formula%20and%20step%20through%20the%20evaluation%2C%20I%20suspect%20either%20the%20formula%20is%20testing%20the%20wrong%20cell%20or%20the%20cell%20gas%20the%20wrong%20value%20in%20it%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1424190%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1424190%22%20slang%3D%22en-US%22%3EUnless%20the%20Go%20%2F%20No-Go%20is%20in%20a%20drop%20down%20validated%20(controlled%20input)%20cell%20I%20would%20be%20concerned%20about%20if%20the%20%22Go%22%20is%20exactly%20the%20same.%20Maybe%20try%20something%20like%3A%3CBR%20%2F%3E%3DIFERROR(IF(search(%22Go%22%2C'CTQ%20Pin%20Gage%20(1)'!V3%2C1)%3D1%2C1%2C2)%2C2)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1424204%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1424204%22%20slang%3D%22en-US%22%3E%3CP%3EI%20couldn't%20find%20the%20evaluate%20formulas%20thing%2C%20not%20sure%20if%20that%20is%20because%20there%20isn't%20an%20error%20and%20it's%20just%20confused%3F%20I'm%20not%20super%20technical%20in%20Excel%20so%20I%20have%20no%20idea.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1424209%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1424209%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3BI'm%20not%20sure%20what%20a%20dropped%20down%20validated%20cell%20is%20but%20I%20tried%20your%20formula%2C%20and%20it's%20doing%20the%20same%20thing%20that%20mine%20did.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1424228%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1424228%22%20slang%3D%22en-US%22%3ETry%20doing%20a%20simple%20test%20in%20a%20new%20spreadsheet%20with%20the%20formula%20and%20the%20cell%20with%20the%20value%20%22Go%22%20in%20the%20same%20sheet%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1424246%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1424246%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F682400%22%20target%3D%22_blank%22%3E%40MrMcQweefan%3C%2FA%3E%26nbsp%3B%20Data%20validation%20is%20a%20tool%20you%20can%20enable%20on%20cells%20to%20make%20sure%20what%20is%20entered%20meets%20specific%20criteria.%26nbsp%3B%20e.g.%20if%20it%20has%20to%20be%20a%20number%201-9%20or%20a%20date%20or%20specific%20names%20or%20text%20from%20a%20list%20of%20options%20and%20when%20you%20use%20a%20list%20you%20can%20have%20the%20option%20to%20have%20a%20drop%20down%20so%20the%20user%20can%20click%20on%20the%20selection%20they%20want.%3C%2FP%3E%3CP%3EAs%20for%20the%20problem%20I%20agree%20with%20the%20other%20members%20above%20that%20something%20else%20is%20probably%20wrong%20like%20you%20are%20pointing%20to%20the%20wrong%20cell.%26nbsp%3B%20It%20would%20be%20easier%20if%20you%20attached%20the%20spreadsheet.%3C%2FP%3E%3CP%3Ea%20simple%20test%20you%20can%20do%20is%20change%20the%20formula%20to%3C%2FP%3E%3CP%3E%3CSPAN%3E%3D'CTQ%20Pin%20Gage%20(1)'!V3%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Eand%20see%20what%20it%20says%20is%20in%20that%20cell%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1424259%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1424259%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1424276%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1424276%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F682400%22%20target%3D%22_blank%22%3E%40MrMcQweefan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1424307%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1424307%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F682400%22%20target%3D%22_blank%22%3E%40MrMcQweefan%3C%2FA%3E%26nbsp%3B%20yes%20you%20are%20pointing%20to%20the%20wrong%20cell%3C%2FP%3E%3CP%3Etry%20this%3A%3C%2FP%3E%3CP%3E%3CFONT%3E%3DIF('All'!V3%20%3D%20%22Go%22%2C%20%221%22%2C%20%222%22)%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3Ethat%20said%2C%20you%20appear%20to%20be%20creating%20'sub-tables'%20and%20should%20really%20consider%20using%20pivot%20tables%20or%20the%20new%20filter%20array%20formulas%20if%20you%20have%20them%2C%20but%20to%20get%20you%20up%20and%20going%20that%20should%20work.%26nbsp%3B%20Basically%20the%20'All'%20is%20the%20tab%20name%20and%20you%20had%20'CTQ%20Pin%20Gage%20(1)'%20which%20is%20the%20tab%20you%20are%20on%20and%20cell%20V3%20on%20that%20tab%20is%20blank.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3EHope%20that%20helps.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1424314%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1424314%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3BIt%20worked!%20Thanks%20so%20much!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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

11 Replies
Highlighted
Are you testing for "Go" or "Go." ?
Highlighted

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 

Highlighted
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
Highlighted
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)
Highlighted

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.

Highlighted

@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.

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

@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

Highlighted
Highlighted

@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.

 

Highlighted

@mtarler It worked! Thanks so much!