Avatar of mattclarified
mattclarified
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Can't find known cell values in excel spreadsheet

Hi,

I have an excel spreadsheet that has a column of simple functions in it, =a1-b1, =a2-b2, etc.

Whenever you try to search the worksheet for values that are known to be in these fields it will not find them, regardless of whether you search by value or formula.

I have tried recreating the values and the formula's, but as it is quite an extensive spreadsheet I haven't recreated the whole spreadsheet (Last resort!)

If anyone has any ideas or anything to check please let me know

Thanks
M@
Microsoft Excel

Avatar of undefined
Last Comment
mattclarified

8/22/2022 - Mon
JP

I have not been able to get excel to search a formula for text if that is what you are trying to do. VBA can do it. If you just want to see where the formulas are you can press Ctrl + ~ to toggle between formulas and values.
Patrick Matthews

mattclarified,

I cannot reporduce the behavior.  Can you upload a smallish sample that does exhibit that behavior, along with the work steps you are using?

Patrick
StephenJR

What exactly are you searching for? Can you give some specific examples?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
mattclarified

ASKER
I have narrowed it down now, it seems to only happen on the one sheet.  The reason it would not find the value when using "look in: Values" was because of the comma separator.  It seems on this sheet, whenever you use the "look in: Formulas" option it will not find anything.
The data is pretty basic, I can't upload the spreadsheet as the data is sensitive, but have included some screenshots.

Thanks
M@ screenshot1 Formula's
StephenJR

I think we need actual data, dummy will do.
mattclarified

ASKER
The problem is, if I copy it, it's fine, but the spreadsheet is too big to sit there copying bits out to another spreadsheet unless it is the final option!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
StephenJR

Like Patrick I cannot replicate this unfortunately.
ASKER CERTIFIED SOLUTION
mattclarified

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
mattclarified

ASKER
Closing question as no one could help being as the problem was quite bizarre. Ended up rebuilding the sheet in another file