Forum Discussion
Indirect reference
Here is the file.
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/guestaccess.aspx?docid=19f7368569eb24eeeaa7f4b2a3b3f2a67&authkey=AUquMHxiMXg9Vpao1-QwB8s&expiration=2017-07-13T23%3a00%3a00.000Z
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.
Hello,
he is the link whre to download the file
https://drive.google.com/file/d/0B3Qy6YsLy1ZKaXBRZU5lcHFlSU0/view?usp=sharing
Best regards,
Imed
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".
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