Create a named range of cells BUT variable in reference to a cell value

Hi,

Can you give a more specific example, including the cells in question and what your desired result would be?

Regards

Hi again,

When you say you want to "name column-D with the corresponding name in column-A" what precisely do you mean? What is the "correspondence" here? Is D1 supposed to "correspond" to cell A2 in some way, and E1 to A3?

"if the values under column-A change, so does the name range."

That's not a problem, and quite usual, to set dynamic, offset ranges which will respond to changes in those ranges. But your original request seemed to be suggesting rather that you wanted the actual names of the Named Ranges to change, and I'm not sure what you mean exactly by that, nor why you'd particularly want it.

Regards

XOR LX... thanks for the support.

I believe I made a poor job at explaining myself, hahaha!! Check the attached picture, please.
now I realize that the cells D1:J1 are causing a confussion... those are just labels for the columns.
What I'd like to do is Naming the column (hope the attached picture makes sense).

RangeName1.jpg
RangeName2.jpg

Thanks!!

May I ask why you want to change the name? Sounds like quite an unusual request, I have to say, especially since you don't appear to be interested in changing any actual values within those ranges. Or am I missing something?

Regards

Always the "why" is the most important question... ;-)

• I'm creating a basic "customer contacting log" tool to keep track of the interaction between a supplier and its clients.
• I want to create two data validation drop-down fields (somewhere else in the workbook).
• One will show the company names (referring to Column A) and the second one will show only the contacts in those companies... that will be part of a Call-Log and will have thereafter other fields such as "date" and "purpose of the call".
• The second data validation drop-down will have an "indirect(NameRange)" formula tied to the resulting name from the first drop-down one (in this case the value of N2)... and THAT's why I need to have the columns Named.
• Please see the attached picture for a basic view.

RangeName3.png

The Company and Contacts will come from another table (kind of flat log style).
Table on C1:J6 is already a byproduct of those flat tables.

I'm trying of making everything as non-manual as possible.

No, still totally confused I'm afraid!

Why can't you just create your multiple Named Ranges (seven in your example: Company01 to Company07) so that e.g. Company01 is D2:D6, Company02 is E2:E6, etc. (these can be made to be dynamically offset depending on the number of names in each column)?

So then N2 is a drop-down list containing these Named Ranges and N3 lists the Contacts for whichever Named Range is selected in N2.

If that's not what you want, then I apologise but I can't see what else it could be.

Regards

Then... could you show me how to do what you're suggesting?
(perhaps my method is not the most efficient + I'm not familiar with the offset... the multiple named ranges need to be created dynamically as the number of companies will also grow)

At the end, I need (the end-user) to be able to drop-down select the company and then have the second drop-down to only show the contacts belonging to that company.
FYI... here's the draft file DemoBook1.xlsx

Thanks for your patience!

Ok, but you already seem to have the correct drop-down set-up with your INDIRECT. So I'm assuming that bit's ok and that you just need to make each range dynamic in case of additions/deletions?

Regards

But then this is precisely what kvsrinivasamurthy gave you in Post #5. Can you please clarify your reply in Post #7?

Regards

Hi,

I think I'm understanding a bit better now.

Have you tried the Create from Selection (Formulas/Name Manager) option? For example, if you insert a new row above your current names, and enter the desired Company Names, then highlight D1:J6 (or however many rows is necessary) and use Create from Selection with just the Top row box checked, then all those required Named Ranges are automatically generated.

Hope that helps?

Regards

Hi,

I think I'm understanding a bit better now.

Have you tried the Create from Selection (Formulas/Name Manager) option? For example, if you insert a new row above your current names, and enter the desired Company Names, then highlight D1:J6 (or however many rows is necessary) and use Create from Selection with just the Top row box checked, then all those required Named Ranges are automatically generated.

Hope that helps?

Regards