Auto-fill Address(es) of Cells that meet a manually entered criteria

Hello all,

I've been searching around on this forum and others all afternoon and I can't seem to find a solution to my Excel woes.

I am trying to setup a column that will auto-populate with the cell address of any cells that match the given value within a set of rows and columns. These addresses will then (hopefully) be used to fill in some portions of existing and new formulas used in other parts of the document.

So, here's the basic layout of what I'm working with: C19:AB65 are filled out with one of the following four values: "Yes", "No", "Sometimes" and "N/A". I want to fill column D with the cell address of each instance of "Yes" (e.g. D1 would return "$A$5", D2 would return "$L$13", etc until all instances of "Yes" are accounted for). Column D will be printed as a reference to which instances were marked as "Yes" and (if I can work it out) to populate portions of other formulas on separate pages of the workbook. The former condition is actually more important in the short run and the latter would only be a handy addition down the road.

First and foremost, I know there are probably better ways to approach my goals, but I am inheriting the .xls from someone else and would prefer not to redo everything from the ground up.

I have been seeing a lot of examples combining ADDRESS and MATCH, but these all seem to only return the first instance, min instances or max instances. I need to catch all instances of the given value.

Anyway, I hope that I have made my issue clear (a day of excel tends to make my head feel a little mushy). I can create an example .xls, but the setup is actually pretty straight-forward, so I'll leave it with my example unless specifically requested.

Thanks much for any advice!

I'm using Windows XP and Excel 2007

This looks like a job for a macro with a for loop, (if you are into macros)...

By the way, column D seems to be within the range containing your yes/no's. Is that a typo?

Originally Posted by pichon

This looks like a job for a macro with a for loop, (if you are into macros)...

By the way, column D seems to be within the range containing your yes/no's. Is that a typo?

Hello pichon, thank you for your response. Yes... Column D was a typo... let's try AD instead .

I'm about to try out your script, but just wanted to thank you in advance. I'll update in a bit about how it turns out.

Thanks!

Hi Pichon,

First, thank you for your macro, it works beautifully. However, it isn't exactly the functionality that I am looking for, but I guess I left a few key points out of my request. Let me give you a bit more detail:

The workbook will actually be filled out by other people, so ideally, I would like to have the workbook automatically populate the cell address without needing to prompt the user (also not needing them to click any buttons or run the macro manually). I could see having a "refresh" button on the page that they could use, but I would prefer it to auto-magically happen on its own.

Also, I'm sure this is super easy, but my VB knowledge is very minimal; I'd like to get a relative cell address instead of an absolute (AB15 instead of $AB$15).

Other than that, I can see that the macro is pointing me in the right direction. Thanks for taking the time to help me :-)

One more update: I set up a "refresh" button to run the macro. I think this could work for my purposes as long as we can get rid of the two prompts. I imagine this would be pretty easy, but I tried tinkering with the macro and keep getting a "400" error. The starting cell will always be C19 and the results column will always be AD.

Also, It looks like the Macro doesn't clear column AD before writing the data. If it could wipe the column before repopulating, that would probably be best in case any of our "yes"es turn in to "no"s.

Thanks!

Hi martindwilson,

To be honest, the only reason I'm headed down the macro route is because that is what pichon suggested :-). If you have any other methods that might steer in another direction, I'd be happy to see them!

Thanks :-)

Attached is a quick mock-up of what I'm working with and what I'm looking to accomplish.

All of the Yes/No/Maybe cells will be filled out by hand. I want the orange cells to display the cell address for each of the Yes conditions. I would like for this list to dynamically update, however here's another little speed bump I ran in to today: There needs to be a "notes" section next to the address generated with the address.

It would be ideal if these notes could stay in the same row as the cell address that it is related to. With the macro solution that pichon provided, any time that the macro is run to update the list, the notes stay in the same cell, but the list of addresses updates and everything gets mixed up.

Thanks!

Aw geeze... sorry, I uploaded the wrong file. Let's try this one. :-)

Sorry to have vanished, had to get the doc ready for use on Monday. I wound up just using a manual field that the end-user has to fill out for the cell address. Not the most elegant solution, but it will work for my purposes.

Thanks for the help, I think I will look over the macro option again when this project is over to see if I can find a way to get the exact results I was looking for.