"Dynamize the list of names"

Hello, good morning. I wanted to know if it was possible to make the list of names dynamic in Excel using dynamic formulas for recalculating the range that automatically adapts to the addition or removal of new rows between the various headers. Since there are multiple headers in the list, I wanted to know if it was possible to dynamically recalculate the range between two headers.

For example:
a1=carbohydrates 'header

a2:a100 contains the list of carbohydrate names

a101=proteins header

a101:a200 contains the list of protein names

In the case of adding or removing a row in one of the two lists, can the calculation be made dynamic so that it calculates everything that is added or removed between the two headers?

Let me give you an example: if a row is removed from the carbohydrate list, the range will become A2:A99, so the second header will be in row 100. The same applies if a row is added. Therefore, the formula should search for the headers and then define the range between the two headers.

Please create the formulas for Excel in Italian.

"Thank you for the suggestion, I followed your instructions."

Hello Glenn Kennedy,

Let me explain my idea. I have two Excel sheets, one used as a data collection called "DATABASE" which contains the sheet "DATABASE ALIMENTI," and the other is "Macronutrition calculator PRO1," which contains the sheet with the links from the "DATABASE ALIMENTI" sheet.

The reason for my request to make these formulas and name lists dynamic is as follows:

Currently, the lists and formulas are static. So, if I wanted to add data, I would have to manually modify the lists and update the formulas. However, if I can make them dynamic, it will avoid adjusting these values every time.

Within the Macronutrition calculator PRO1 file, you will find a pivot table called "NUOVI ALIMENTI." Here, through a macro in the Macronutrition calculator PRO1 file, I add the NEW FOODS to the DATABASE file.

Once the formulas and name lists are made dynamic, I could sort the database by the respective headers. Here are the headers and name lists:

FONTI_PROTEICHE ='DATABASE ALIMENTI"! $A$984:$A$1490
FONTI_GLUCIDICHE ='DATABASE ALIMENTI"! $A$3:$A$982
ALCOOL ='DATABASE ALIMENTI'!$A$2326:$A$2338
PRODOTTI VARI ='DATABASE ALIMENTI'!$A$2295:$A$2324
DOLCI ='DATABASE ALIMENTI'!$A$2249:$A$2293
UOVA ='DATABASE ALIMENTI'!$A$2241:$A$2247
FORMAGGI ='DATABASE ALIMENTI'!$A$2198:$A$2239
LATTE_E_YOGURT ='DATABASE ALIMENTI'!$A$2174:$A$2196
PESCE ='DATABASE ALIMENTI'!$A$2091:$A$2172
FRATTAGLIE ='DATABASE ALIMENTI'!$A$2074:$A$2089
CARNI_TRASFORMATE_SALUMI ='DATABASE ALIMENTI'!$A$2023:$A$2072
FRUTTA ='DATABASE ALIMENTI'!$A$1929:$A$2021
VERDURA_E_ORTAGGI ='DATABASE ALIMENTI'!$A$1827:$A$1927
LEGUMI ='DATABASE ALIMENTI'!$A$1795:$A$1825
FONTI_LIPIDICHE ='DATABASE ALIMENTI'!$A$1492:$A$1793

I will carry out the sorting of the database using the macro present in the Macronutrition calculator PRO1, which I mentioned, which will be activated every time data is entered into the DATABASE file in the DATABASE ALIMENTI sheet.

I still have to create this part of the macro, but the idea is to take the value from the row present in NUOVI ALIMENTI and move it to the header it refers to, which will be determined through this formula:

=SE(E(E2339>F2339;E2339>G2339);"FONTI GLUCIDICHE";SE(E(F2339>E2339;F2339>G2339);"FONTI PROTEICHE";SE(E(G2339>E2339;G2339>F2339);"FONTI LIPIDICHE";"")))

I hope I have been clear with my request. If you have any doubts or further requests, please feel free to ask.

The file you provided earliI apologize for the confusion earlier. The file I provided earlier was indeed a file to understand if dynamic ranges could be obtained. Upon analyzing my original files, the ones I have provided to you, I will need multiple formulas to properly manage the data.

I will need a dynamic formula to handle the data in the list of names and a formula to manage the links in the Macronutrition calculator PRO1 file, specifically in the DATABASE ALIMENTI sheet.

Additionally, the reference "END" used by you should be removed and made to coincide with the header of the "NUOVI ALIMENTI" table. Furthermore, the list should not be extracted but rather modifiable within column A.

To help us to come up with a better solution, maybe you should tell us what you are trying to achieve. Are you creating formulas linking to the lists? If so, please show us what your actual workbook looks like.

Here are my original files. https://www.excelforum.com/excel-for...ml#post5934939

Yes, I need to dynamically manage both the lists of names and the links to the database file.

The issue with the "Macronutrition calculator PRO1" sheet containing links to the "DATABASE ALIMENTI" sheet is as follows:
If I add or delete a row from the "DATABASE ALIMENTI" sheet in the "DATABASE" file,
these changes are not updated with the following formula in the "Macronutrition calculator PRO1" sheet.

=SE('C:\Users\Utente\Desktop\Macronutrition Calculator\DATABASE\[DATABASE.xlsm]DATABASE ALIMENTI'!A2<>"";'C:\Users\Utente\Desktop\Macronutrition Calculator\DATABASE\[DATABASE.xlsm]DATABASE ALIMENTI'!A2;"")

I used your formula to create data validation directly in the "DATABASE" file on the "DATABASE ALIMENTI" sheet, but if the sheet is closed, these formulas do not work.

=INDICE('C:\Users\utente\Desktop\Macronutrition Calculator\DATABASE\[DATABASE.xlsm]DATABASE ALIMENTI'!$A:$A;CONFRONTA("FONTI GLUCIDICHE";'C:\Users\utente\Desktop\Macronutrition Calculator\DATABASE\[DATABASE.xlsm]DATABASE ALIMENTI'!$A:$A;0)+1):INDICE('C:\Users\utente\Desktop\Macronutrition Calculator\DATABASE\[DATABASE.xlsm]DATABASE ALIMENTI'!$A:$A;CONFRONTA("FONTI PROTEICHE";'C:\Users\utente\Desktop\Macronutrition Calculator\DATABASE\[DATABASE.xlsm]DATABASE ALIMENTI'!$A:$A;0)-1)

In the file, I have added some formulas to retrieve the data, but the issue with the links to the database file still remains.