May 28 2020 07:54 AM
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
May 28 2020 08:08 AM
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
May 28 2020 08:39 AM
May 28 2020 08:50 AM
May 28 2020 08:53 AM
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.
May 28 2020 08:54 AM
@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.
May 28 2020 09:01 AM
May 28 2020 09:05 AM
@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
May 28 2020 09:21 AM
@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.
Jan 19 2021 06:36 AM