Good morning,
I hope someone will be able to assist me, I am creating a worksheet that looks for a value ( For example Servon ) and second value ( For example EE Estonia ), based on this it would pull data from the relevant worksheet Servon EX EE.
I currently have the formula laid as below but I have exceeded the 64 nest limit and sadly i am not able to think of another way of storing the data, other than have all in 1 Export sheet although this would be massive and not easy to edit with updates.
Below I have put my formula:
=IF(AND(Reference!$AD$12="Servon",Calc!$I$3="EE"),'EE EX Servon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="EE"),'EE EX Dijon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="EE"),'EE EX Lyon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="LV"),'LV EX Servon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="LV"),'LV EX Dijon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="LV"),'LV EX Lyon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="LT"),'LT EX Servon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="LT"),'LT EX Dijon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="LT"),'LT EX Lyon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="NO"),'NO EX Dijon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="NO"),'NO EX Servon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="NO"),'NO EX Lyon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="SE"),'SE EX Lyon'!A2, IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="SE"),'SE EX Dijon'!A2, IF(AND(Reference!$AD$12="Servon",Calc!$I$3="SE"),'SE EX Servon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="FI"),'FI EX Lyon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="FI"),'FI EX Servon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="FI"),'FI EX Dijon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="DK"),'DK EX Dijon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="DK"),'DK EX Lyon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="DK"),'DK EX Servon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="RO"),'RO EX Servon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="RO"),'RO EX Lyon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="RO"),'RO EX Dijon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="HU"),'HU EX Dijon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="HU"),'HU EX Lyon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="HU"),'HU EX Servon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="CZ"),'CZ EX Lyon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="CZ"),'CZ EX Servon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="CZ"),'CZ EX Dijon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="BE"),'BE EX Dijon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="BE"),'BE EX Servon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="BE"),'BE EX Lyon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="AT"),'AT EX Dijon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="AT"),'AT EX Lyon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="AT"),'AT EX Servon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="BG"),'BG EX Dijon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="BG"),'BG EX Lyon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="BG"),'BG EX Servon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="GR"),'GR EX Dijon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="GR"),'GR EX Lyon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="GR"),'GR EX Servon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="PL"),'PL EX Dijon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="PL"),'PL EX Lyon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="PL"),'PL EX Servon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="SI"),'SI EX Dijon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="SI"),'SI EX Lyon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="SI"),'SI EX Servon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="GB"),'GB EX Dijon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="GB"),'GB EX Lyon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="GB"),'GB EX Servon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="LU"),'LU EX Dijon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="LU"),'LU EX Lyon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="LU"),'LU EX Servon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="TR"),'TR EX Dijon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="TR"),'TR EX Lyon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="TR"),'TR EX Servon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="CH"),'CH EX Dijon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="CH"),'CH EX Lyon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="CH"),'CH EX Servon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="HR"),'HR EX Dijon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="HR"),'HR EX Lyon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="HR"),'HR EX Servon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="ES"),'ES EX Dijon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="ES"),'ES EX Lyon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="ES"),'ES EX Servon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="PT"),'PT EX Dijon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="PT"),'PT EX Lyon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="PT"),'PT EX Servon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="DE"),'DE EX Dijon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="DE"),'DE EX Lyon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="DE"),'DE EX Servon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="IT"),'IT EX Dijon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="IT"),'IT EX Lyon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="IT"),'IT EX Servon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="NL"),'NL EX Dijon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="NL"),'NL EX Lyon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="NL"),'NL EX Servon'!A2)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
Would be extremely grateful for someones help.
I hope someone will be able to assist me, I am creating a worksheet that looks for a value ( For example Servon ) and second value ( For example EE Estonia ), based on this it would pull data from the relevant worksheet Servon EX EE.
I currently have the formula laid as below but I have exceeded the 64 nest limit and sadly i am not able to think of another way of storing the data, other than have all in 1 Export sheet although this would be massive and not easy to edit with updates.
Below I have put my formula:
=IF(AND(Reference!$AD$12="Servon",Calc!$I$3="EE"),'EE EX Servon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="EE"),'EE EX Dijon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="EE"),'EE EX Lyon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="LV"),'LV EX Servon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="LV"),'LV EX Dijon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="LV"),'LV EX Lyon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="LT"),'LT EX Servon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="LT"),'LT EX Dijon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="LT"),'LT EX Lyon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="NO"),'NO EX Dijon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="NO"),'NO EX Servon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="NO"),'NO EX Lyon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="SE"),'SE EX Lyon'!A2, IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="SE"),'SE EX Dijon'!A2, IF(AND(Reference!$AD$12="Servon",Calc!$I$3="SE"),'SE EX Servon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="FI"),'FI EX Lyon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="FI"),'FI EX Servon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="FI"),'FI EX Dijon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="DK"),'DK EX Dijon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="DK"),'DK EX Lyon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="DK"),'DK EX Servon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="RO"),'RO EX Servon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="RO"),'RO EX Lyon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="RO"),'RO EX Dijon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="HU"),'HU EX Dijon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="HU"),'HU EX Lyon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="HU"),'HU EX Servon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="CZ"),'CZ EX Lyon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="CZ"),'CZ EX Servon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="CZ"),'CZ EX Dijon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="BE"),'BE EX Dijon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="BE"),'BE EX Servon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="BE"),'BE EX Lyon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="AT"),'AT EX Dijon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="AT"),'AT EX Lyon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="AT"),'AT EX Servon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="BG"),'BG EX Dijon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="BG"),'BG EX Lyon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="BG"),'BG EX Servon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="GR"),'GR EX Dijon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="GR"),'GR EX Lyon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="GR"),'GR EX Servon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="PL"),'PL EX Dijon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="PL"),'PL EX Lyon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="PL"),'PL EX Servon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="SI"),'SI EX Dijon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="SI"),'SI EX Lyon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="SI"),'SI EX Servon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="GB"),'GB EX Dijon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="GB"),'GB EX Lyon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="GB"),'GB EX Servon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="LU"),'LU EX Dijon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="LU"),'LU EX Lyon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="LU"),'LU EX Servon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="TR"),'TR EX Dijon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="TR"),'TR EX Lyon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="TR"),'TR EX Servon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="CH"),'CH EX Dijon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="CH"),'CH EX Lyon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="CH"),'CH EX Servon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="HR"),'HR EX Dijon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="HR"),'HR EX Lyon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="HR"),'HR EX Servon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="ES"),'ES EX Dijon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="ES"),'ES EX Lyon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="ES"),'ES EX Servon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="PT"),'PT EX Dijon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="PT"),'PT EX Lyon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="PT"),'PT EX Servon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="DE"),'DE EX Dijon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="DE"),'DE EX Lyon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="DE"),'DE EX Servon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="IT"),'IT EX Dijon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="IT"),'IT EX Lyon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="IT"),'IT EX Servon'!A2,IF(AND(Reference!$AD$12="Dijon",Calc!$I$3="NL"),'NL EX Dijon'!A2,IF(AND(Reference!$AD$12="Lyon",Calc!$I$3="NL"),'NL EX Lyon'!A2,IF(AND(Reference!$AD$12="Servon",Calc!$I$3="NL"),'NL EX Servon'!A2)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
Would be extremely grateful for someones help.
