Match & address

[DarthBrute]

I am trying to get a cell to locate the text within a reference cell using a wildcard. But I need to search about 29 different sheets and get the address (sheet name & cell #). Yes I know I can use Ctrl+F to find the data. But this will be part of a much larger program.

I have designated names for the search areas. (DIV1######, # = the name of the tab, only 1st 4 tabs have been named.)

=MATCH(C6&"*",DIV1230530)

That is what I started with. I then tried ADDRESS but I didn't quite understand it. I tried combining different named areas for the lookup_array but I kept getting an #N/A return value.

I have a workbook attached.

[FlameRetired]

Looking at it.

Need help.

1. @how many rows/columns in largest sheet of real data will there be?
2. Please update your profile RE: Office version(s) and location. Members tailor solutions with those things in mind.

[DarthBrute]

Right now just Sheet1. That's my test sheet to find those names across the 29 sheets that there will be. Each sheet represents a geographical area. And each can be updated accordingly in the future. Really I just want to find the text listed in Column A on Sheet1 and give its location next to it.

[FlameRetired]

Thank you for updating your profile.

But I need to search about 29 different sheets and get the address (sheet name & cell #). Yes I know I can use Ctrl+F to find the data. But this will be part of a much larger program.

That's why I asked. If I have interpreted correctly it's important.

[DarthBrute]

Basically, its part will be a search box, Type it in and it finds its location.

[Glenn Kennedy]

Hi. is this what you wanted? Happy to explain. It's a bit involved, so i don't want to explain until it's right!!

It uses an old in-built Excel 4 macro, to return a dynamic list of worksheets, so enable macros on opening. Formulae in Q and D are array formulae. Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[DarthBrute]

That is perfect. Thank you! I've never worked with arrays. How does that differ from a normal formula?

[Glenn Kennedy]

Mmmm
Think of them as formulae within formulae that calculate multiple rezults, hold them in memory snd the use all of the values in another formula.

I've also used 2 named ranges (ctrl f3 to view). I'm out for the night, starting now. I'll explain how it all hangs together in the morning, if you want/need an explanation.