vlookup query, conditional formatting, security

MKB

New Member
Joined
Sep 22, 2006
Messages
46
Some random questions, can't find anything that answers these specifically so apologies if they have already been answered.

I work with an awful lot of vlookup queries, however sometimes when vlooking up from one workbook to another, when I drag for a table array it doesn't work, I get no broken lines and nothing fills my vlookup function wizard. The only way I have around this is to copy all of the data in a sheet into a blank sheet in the same workbook I am using (I lookup from many workbooks). It seems to occur randomly, I assume this is down to some formatting somewhere but for the life of my I can't find consistent instances of this to diagnose.

Conditional formatting - is there any way to see what cells have any conditions assigned to them without having to click and go through every single cell?

Security - I have a document that only myself and a few other people should edit, that many can access. Tools > Options > Security gives me the option to require a password to modify which is exactly what I want, however this happens as a user opens the document. I ideally want them to have to enter a password to save it when they're done.

On a different problem I'm locking a lot of cells and only allowing a few columns to be unlocked (with the parameters of select unlocked cells). I'm also allowing auto filter, but on potentially a huge amount of data, the Find function is key but this doesn't seem to work on locked cells. Is there any easy way round this?

Many thanks, :)
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Hi,
Your VLOOKUP question I don't really understand - can you clarify?
For conditional formatting, you can use the Edit->Goto, Special... option.
For security, why do you need a password to save rather than to open? If you do, you would need code.
For the Find, if you use the FindAll option, it will list the cell references rather than trying to select them.
 
Upvote 0
You might try entering the VLookup Formulas without the function wizard - not sure but I've never had this problem (I also rarely use the function wizard).

FYI, if you use an awful lot of vlookup formulas you might want to become proficient with Index() and Match() as an alternative method that might reduce memory usage and calculation time - be careful to use the correct type argument for Match (false for exact matches).

http://www.mrexcel.com/tip021.shtml
 
Upvote 0
Well if you go insert > Function > Vlookup, it asks for lookup value which is fine. Then next box is table array, where I'd click and drag a box around the table I wish to lookup to. In random instances Excel doesn't populate the values when I try to select the table array in another worksheet - it just acts as though I'm highlighting cells and not selecting them as part of a formula.

Thanks for the formatting option, password to save rather than open is going to stop me getting asked queries on it really although I suppose it's no big deal really, I can live without that just wondered if there was a way.

FindAll works a treat, thanks :)
 
Upvote 0
For the Vlookup, I've heard of similar issues with the RefEdit controls. I'd agree that skipping the wizard is probably the best bet.
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top