ArtySin
05-08-2018, 04:07 AM
Guys,
From a list of 70 customers, I have two customers that have multiple site names, the rest just have one site. In cell Invoice!C8 you select the customer name from a customer dropdown list and in data validation for cell Invoice!E16, the site dropdown list, I created a formula:
=IF(Invoice!C8=Customers!E66,ACLE,$R$34)
This works fine, it populates the site dropdown list with the various sites and if there is no match in the ACLE list, it populates the dropdown with the single site that appears in R34. So far so good.
However, I think that the next step will require some VBA and I'm a bit lost with that. If I add a second named list to the data validation formula I get an error. If I replace the ACLE with TSquared in the above formula, again it all works fine but by adding the second I get this error which is how below. The ammended formula is:
=IF($C$8=Customers!$E$14,ACLE,IF($C$8=Customers!$E$66,TSquared,$R$34)
So I suspect it's not possible to have a second named list so I suspect that VBA is the way to go with this.
Any help much appreciated.
ArtySin
22193
From a list of 70 customers, I have two customers that have multiple site names, the rest just have one site. In cell Invoice!C8 you select the customer name from a customer dropdown list and in data validation for cell Invoice!E16, the site dropdown list, I created a formula:
=IF(Invoice!C8=Customers!E66,ACLE,$R$34)
This works fine, it populates the site dropdown list with the various sites and if there is no match in the ACLE list, it populates the dropdown with the single site that appears in R34. So far so good.
However, I think that the next step will require some VBA and I'm a bit lost with that. If I add a second named list to the data validation formula I get an error. If I replace the ACLE with TSquared in the above formula, again it all works fine but by adding the second I get this error which is how below. The ammended formula is:
=IF($C$8=Customers!$E$14,ACLE,IF($C$8=Customers!$E$66,TSquared,$R$34)
So I suspect it's not possible to have a second named list so I suspect that VBA is the way to go with this.
Any help much appreciated.
ArtySin
22193