Related Posts
ZS first year manager salary for east coast?
Enthusiast
Rising Star
Bowl Leader
How do I know if I am ready for a divorce?
Additional Posts in Excel Genius
Visual Storyteller
Anyone know what these badges are?
New to Fishbowl?
Download the Fishbowl app to
unlock all discussions on Fishbowl.
unlock all discussions on Fishbowl.
Subject Expert
Data from protected files can still be accessed.
Most likely scenario is that formats are not the same - for example, excel can read the number 4 as a number, or as the text "4". If you try to =MATCH() those, it will result in an error, because those two are not the same.
Using fixing functions like =TRIM(), =NUMBERVALUE()* can fix this for you
* Instead of using numbervalue you can just multiply by 1 (add *1 to the reference)
The "multiply by 1" is a great tip. I use it more often that I would like to admit.
In general if you are trying to access a password protected file while the file is closed there is a good chance excel can't open it and access the data. But I have never dealt with this specific example
Yeah I've used a million lookups and index/matches on and from password protected workpapers and never had issues. Not sure what this formatting issue is
Try to use TEXT() or NUMBERVALUE() depending on what data type you need to covert it to in your formula
This worked, I did numbervalue formula and then pasted value on top of the selection. Which then allowed to manipulate the format of the data as needed. Thank you!!
You can also use =clean(trim())
Client shared access to the file and I can’t seem to change the formatting of a code to match another set of data when I know there are a few matches after doing some ctrl f’s
Yeah it’s pretty peculiar, it’s a 6 digit code I am trying to match to a separate sheet with a smaller list of those 6 digit codes.
When I try to use the text formula or copy and paste the formatting it still doesn’t say there’s a match
Your data formats may not match then. Convert everything to the General format. I would anticipate it will work then. I run something similar all the time. This happens pretty commonly. If the format’s match, check for extra spaces in the cells being matched. If that doesn’t solve it the numbers may still be stored as Text. I don’t know the size of the file you’re dealing with, but some of mine have so many data points, adding one more function to a formula I will run 10,000–50,000 times will dramatically increase the time it takes the spreadsheet to calculate the formulae.