Home/ Questions/Q 2205
Next
Answered
computing
  • 2
Asked: 2021-10-27T23:19:08+00:00 In:

Solved How Do Make a Validation List With Multiple Columns

  • 2

I have data that I need included in the drop down list in multiple columns. If I try to add all of the columns to the list formula I get the below error..

“You may not use reference operators (such as unions, intersections, and ranges) or array contrants for Data Validation criteria”

This is what my formula said..
=’Fixture Glossary’!$B:$B,’Fixture Glossary’!$E:$E,’Fixture Glossary’!$H:$H,’Fixture Glossary’!$K:$K,’Fixture Glossary’!$N:$N

Share

    1 Answer

    1. Best Answer
      OK, I wrote this formula to create a single column of data from 3 separate columns of varying lengths. I wrote it for your Columns B, E & H. You will need to extend it to add Columns K & N:

      =IF(B1<>””,B1,IF(INDIRECT(“E”&ROW;()-COUNTA(B:B))<>””,
      INDIRECT(“E”&ROW;()-COUNTA(B:B)),
      IF(INDIRECT(“H”&ROW;()-SUM(COUNTA(B:B),COUNTA(E:E)))<>””,
      INDIRECT(“H”&ROW;()-SUM(COUNTA(B:B),COUNTA(E:E))),””)))

      If you put this in Row 1 of an empty column, it will grab all data from Column B until it runs out and then grab data from column E until it runs, then grab data from Column H.

      You will note that each time it moves to the next column, you have to SUM the COUNTA from all the previous columns. It uses that SUM to get itself back to Row 1 of the current column.

      As long as you drag that down far enough to exceed the total number of pieces of data in all 5 columns, you will have a single list that can then be used for your drop down.

      You may need adjust for Column headers, etc.

      Let’s say we put that formula in Column P…

      Your Source formula for the Drop Down can either include the entire Column e.g. P:P – but that will include any blanks at the bottom of the long list – or you can use the same concept to have the Source list stop at the bottom of the actual data in the long list by using this as the Source:

      =INDIRECT(“P1:P”&SUM;(COUNTA(B:B),COUNTA(E:E),COUNTA(H:H)))

      Click Here Before Posting Data or VBA Code —> How To Post Data or Code.

      message edited by DerbyDad03

      • 0