Indirect reference

[Deleted]

Hello I have an excel file where I am using for some cells an indirect reference to validate the content of the cell

I have table 1 where the I have two columns classification level 1 and classification Level 2. Classification level 2 changes depending on classification level 1.  for this I used indirect reference to be able to show a list on classification level 2 depending on classification level 1.

I used in data validation the following formula : =INDIRECT(Reference_Indirecte!$A2) for the second line and so on

my problem is that whenver I deleted line in the table 1 the references doesn not change and continue to be the intial line

can you please support.

regards,

Imbrg

[#2]

Hello,

it's a bit difficult to picture what the issue is and what you want to achieve. Could you upload a samle file and explain in context?

[#3]

Dear

I am using the Excel file where you will find the link below.

the field classification Level 2 is changing based on the content of classification level 1 and same thing for Market sub segment which content will change based on market segment.

to realise this I am using data validation and list. the list referefs to another sheet and indirect reference.

This is working fine except when i delete a line in accounts worksheet, the reference to the right line is not working anymore.

exemple : classification Level 2 in line 6 is : =INDIRECT(Reference_Indirecte!$A6)

if I delete this line  (6) then the classification Level 2 in this line is : =INDIRECT(Reference_Indirecte!$A7)

while it should have been modified 

please support me on this issue.

https://schneiderelectric-my.sharepoint.com/personal/sesa353194_schneider-electric_com/_layouts/15/g...

[#4]

Hello,

that link opens the Excel file in Excel online. In order to help I need a file that I can download and open with desktop Excel. Data validation cannot be created or changed in Excel online.

[#5]

Hello,

he is the link whre to download the file

https://drive.google.com/file/d/0B3Qy6YsLy1ZKaXBRZU5lcHFlSU0/view?usp=sharing

Best regards,

Imed

[#6]

Hi Imed

The problem is that you are referencing the data validation in Table1 to another table (Table4). And in Table4 you are referencing back to Table1 (with VLOOKUP()).

So, both tables are synchronized. But if you delete a row in Table1 you break the synchronisation.

To avoid the problem put the VLOOKUP() into Table1:

Insert a column "Classification1" at the position of column I and put the formula there. The reference for data validation changes to INDIRECT($I2).

The same applies to "Market Segment".

[#7]

Hello

Thank you for your reply.

In the solution you are proposing, I need to add a new column between Classification level 1 and classification level 2 and I put the  indirect(I2) in classification level2.

please advise.

Regards

Imed

[#8]

What kind of further advice is needed?

[#9]

Can you please modify your solution in my file because I am not visulasing what is your solution.

regards,

imed

[#10 · Accepted solution]

Here is the file.

[#11]

thank you very much for your support

regards,