Using a lookup based upon cell attributes

**Barnapkin** · 03-02-2007, 06:58 PM

Lets say that you have three cells. The contents of the cells are 1, 2, 3. The contents 1 and 2 have the strikethrough and 3 does not.

How can you perform a lookup from within another cell to find the cell whose content does not have the strike through? (This could also be a search based on finding the cell that does not have the red background etc...)

Thanks,

John

**Carim** · 03-03-2007, 04:58 AM

Hi,

A regular vlookup() function cannot handle this problem ...
You will need a macro (or an UDF) to test the attribute ...

Please Login or Register  to view this content.

**Barnapkin** · 03-05-2007, 09:54 AM

Thanks,
I was trying to manage this in a way that the users would not have to run a lot of embedded macros.

J

**Carim** · 03-05-2007, 09:57 AM

Difficult to guess what your actual objective is ...
May be an event macro could solve this issue ...
Why don't you upload a zipped copy of your worksheet for a precise answer ...?

**Barnapkin** · 03-05-2007, 12:52 PM

Thank you for your help.

I've attached a starting point for what I'm trying to do.

**Carim** · 03-06-2007, 07:04 AM

Hi,

Attached is a starting point ...
1. Reset button allows you to reformat all your selection area
2. Double-Click on cells to identify and eliminate possible choices
3. UDF NoStrike() in row 13 tracks automatically each selection left ...

**Barnapkin** · 03-06-2007, 10:45 AM

Carim,

Thank you very much for your help.

The file works fine.
It does have a small problem.
When I try to add an additional search row, or change the name of on of the row titles I get a #value where the results were posting in the "yellow" box.

I even went into the macro file, and edited the row title to match the name of the new title or additional search row.

Is there some where else I need to look?

Thanks again.

J

**Carim** · 03-06-2007, 10:47 AM

Hi,

What do you mean by an additional search row ...?

**lshooter3k** · 03-06-2007, 01:18 PM

Hi,

With regards to changing the name of one of the row titles, if you go to Insert > Name > Define and change the title there as well, then this should get rid of the #value error in the yellow boxes.

If you want to add a new search row you'll need to create a 'name' range in the same dialog box as above, and add this to any line in the macros that start 'Set Choices = Union'.

I hope this helps.

Steve

**Barnapkin** · 03-07-2007, 05:40 PM

So far so good.

Thanks steve for the clarification. It works now with newly added rows.

Now for another question (3 actually):
1 - As I add different sort rows, it gets quite tedious that the while page gets cleared when the reset button is pressed. I looked for the button in the macro to try to add it to work on each row, but there isn't anything there that I can identified (If knew all of the VB tricks I wouldn't be here. LOL)
2 - Some sort criteria could have more than one option, so how can the total cells be expanded to include more than one selection?
3 - How does the button get added. There seems to be hidden stuff here, so I'm floundering on trying learn how to do this.

Thank you all again.

**lshooter3k** · 03-08-2007, 09:48 AM

If you go to Tools > Customize, and select the toolbars tab, you will find Visual Basic in the list, tick this to have the toolbar added.
Then if you click the Control Toolbox Icon (it looks like a hammer and spanner) you can create a button. To tell Excel what to do when the button is pressed you need to go to the Visual Basic Editor, and view the code for the sheet that the button is in. From there you need to use the drop down lists at the top of the code window to select the button, and what event to run a macro for. For your spreadsheet, if you go into the VB Editor and double-click Sheet1 in the Project window on the left you should be able to see the code that executes when the button is pressed. Hopefully this will be enough for points 1 and 3.

I've tried to come up with a solution to point 2, which I believe I have done, however I don't know how to get rid of the squares after each line.

Also I've amended the code that's run when you double click on a cell so that it determines whether to add or remove the strikethrough, so you don't have to reset if you make a mistake.

You should also now find that the sheet resets quicker now too.