I have a spreadsheet with many many worksheets & on each of those worksheets many many postcodes.
I am looking for a way where I can have a list of postcode stored once somewhere (in excel, word or whatever) & then when we type postcodes into the Excel spreadsheet & click whatever to start the macro or run the code it will refer to where I have the postcodes saved & then highlight any that match on the worksheet page.
Is this possible, anyone know how? Appreciate any advice?
Hi, could you maybe post a sample workbook with only a few sheets, include a mock-up of your solution and explain a bit further what you want to achieve? That'll be much easier to work with.
cheers
you could just use conditional formatting with list on same sheet say col f1:f20
formula is option
=OR(A1=INDEX(F1:F20,0))
if your list is on another sheet say sheet2 col a use a named range instead
insert/name /define
type in a name and in the refers to box put
=Sheet2!$A$1:$A$20
then use
=OR(A1=INDEX(yourname,0))
Last edited by martindwilson; 12-16-2009 at 04:45 PM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Martin, as mentioned to you on another topic I can't follow your words, your too advanced on Excel for me, please can you type in a more idiot proof way?
I have attached a sample as requested.
Columns F, G & H (labeled Sectors) have various postcodes entered into them.
Then I would have a separate sheet called something like clientrequests & on this I would store postcodes.
Then once postcodes have been typed into Columns F, G & H,
I then click a start, run macro or whatever & it flags up any of the postcodes that are stored in the clientrequests sheet.
However one thing to beware of is that on the spreadsheet I also have some code that will highlight duplicate postcodes in various colours.
So to differentiate the clientrequest postcodes highlighted should be in black? Also it would need to be able to be switched on & off, leaving them unmarked once details have been noted.
Any ideas?
i think you could stiil do this with cf try attached
1. created a dynamic named range on sheet 2 called pclist (click on insert/names/define to see it)
2.created a dropdown in c1
3 conditional format as =AND($C$1="is in list",OR(F4=INDEX(pclist,0)))
Last edited by martindwilson; 12-17-2009 at 11:11 AM.
That seems to work nicely on the sample sheet :-)
However I can't see what is running it IE how do I get that onto my main spreadsheet.
It isn't a macro & View Code doesn't reveal anything?
Also where you have put the postcodes on sheet 2 I would like to be able to name this clientrequests
Can you advise further. Thanks
it is done using conditional formatting only, no code.
OK I have no idea what conditional formatting is, how to edit it or how to copy it into my main spreadsheet.
Can you advise please?
you seem to have excel 2007 not 2003 as in your profile so cf is done slighly differently.
its not easy to describe in text you need to have a look at how its done.
http://www.contextures.com/xlCondFormat02.html pre 2007
http://www.cpearson.com/excel/cformatting.htm pre2007
http://www.free-training-tutorial.co...ormatting.html 2007
http://techblissonline.com/condition...in-excel-2007/
Have had a good look at the links given & whilst they have opened me up to understanding a bit about conditional formatting their not really enabling me to achieve what I wish.
What I wished to achieve was almost there with the sample martinwilson uploaded. But as I said I couldn't copy that into my spreadsheet...
Basically I need to be able to highlight postcodes that show up on my regular weekly worksheet that match those that are listed on one of the worksheets.
Any more ideas anyone how to achieve this?
it works ,you must be putting it in the wrong place.
have you got the real thing to look at? just the post codes you can delete everything else.
Hi Martin
I agree it does work in the sample sheet you did, but what I can't do is copy your sample over to my main spreadsheet.
I have no idea where to go, what to copy & paste or any other idea how to do this?
well you cant copy it as it is cell specific,
But if I knew what to copy I could change the cells to relate to mine?
Simon, reading through your responses in this thread then I really think that you should learn basic Excel before dabbling in VBA
Hope that helps.
RoyUK
--------
For Excel Tips & Solutions, free examples and tutorials why not check out my web site
Free DataBaseForm example
What I want to achieve has been shown in the sample upped by Martin.
The only thing is I do not know how to copy this information into my spreadsheet, if someone could advise on that I would be sorted.
:-)
ok heres the steps
i used a dynamic named range to make things a bit more flexible
when formula is entered in conditional formatting its uses F4
thats because its the first one in the range you are applying the format to
if you then copy cell f4 and paste special "formats" to any cell or range of cells it will auto adjust itself for the cell it's in
note i have used slightly difeen names than the first example i gave you
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks