Related Posts
Visual Storyteller
Additional Posts in Excel Genius
Anyone know what these badges are?
Visual Storyteller
New to Fishbowl?
Download the Fishbowl app to
unlock all discussions on Fishbowl.
unlock all discussions on Fishbowl.
That was just a preview…
Sign Up to see all discussions
Sign Up to see all discussions
- Discover what it’s like to work at companies from real professionals
- Get candid advice from people in your field in a safe space
- Chat and network with other professionals in your field
Sign up in seconds to unlock all discussions on Fishbowl.
"" is an unusual email domain. If correct, please proceed to the next step
Messaging rates may apply
This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
New to Fishbowl?
Download the Fishbowl app to unlock all discussions on Fishbowl.
Share
Embed this post
Copy and paste embed code on your site
Preview
Download the
Fishbowl app
Scan your QR code to download
Fishbowl app on your mobile
Sign up for free to view this conversation on Fishbowl
Sign up for free to continue using Fishbowl
Messaging rates may apply
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.