Conditional data validation using excel lists

Question

I am building a workbook to manage the expenses of a home renovation business. The table named 'Jobs' contains a 'status' column, which can contain various options such as 'Job started', 'Job complete', 'invoice sent' etc.

I intend to have another table called 'expenses', where I can add expenses, but only to jobs that have status set to 'Job started'. To do this, I would have a column with List Data Validation, but I don't know how to generate the list of jobs that only have the appropriate corresponding status.

Expenses table:

Jobs table:

Misc lists:

Edit for clarity: In the sample shown, when I click on items in the Job Site column of the 'Expenses' table, I would like to see 'Werribee', 'Croydon South' and Pascoe Vale South' in the list (since their status is 'Job started')

Which column from the Jobs table do you want to add. you could use a simple formulae like =SUMIF(I2:I12,"Job started",F2:F12) — nightcrawler23
– nightcrawler23, Commented Jan 9, 2017 at 1:38

Keshan Nageswaran · Accepted Answer · 2017-01-10 07:19:32Z

1

You can use this formula.

in Expense table you A2 put this.

=IFERROR(INDEX(Jobs!$A$2:$A$999,SMALL(IF(Jobs!$I$2:$I$999="Job started",ROW($1:$100)),ROW()-1)),"")

basically this looks up to worksheet Job, A2:A999 and checks if its status is "Job started", If its validation is positive, it copies the value from the range A2:A999 which has the "Job started", paste this on each cells on Expense table.

Don't forget this is a array formula so press Ctrl+Shift+Enter after copying the formula.

edited Jan 10, 2017 at 7:19

Keshan Nageswaran

8,2064 gold badges30 silver badges45 bronze badges

answered Jan 9, 2017 at 3:04

user7254740

1111 gold badge6 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

nightcrawler23 · Accepted Answer · 2017-01-09 02:59:33Z

0

In an empty column (say F) beside the expenses table use the below 2 formula

for first row

F2 = 1/AGGREGATE(14,6,1/((JOBS!$I$2:$I$14="Job started")*(ROW(JOBS!$I$2:$I$14)>1)*ROW(JOBS!$I$2:$I$14)),1)

and next row onward

F3 = 1/AGGREGATE(14,6,1/((JOBS!$I$2:$I$14="Job started")*(ROW(JOBS!$I$2:$I$14)>F2)*ROW(JOBS!$I$2:$I$14)),1)

Drag down F3. Notice the >1 in F2 and >F2 in F3 and onward. This will give all row numbers that have "Job started" as status in the Jobs table

Once you have the row numbers, you can use Index(Jobs table, row num, column num) to get other data for those rows

Make sure the Jobs table range that you input in the formula starts form the first row i.e. A1:J7 as shown in your image

Example

edited Jan 9, 2017 at 2:59

answered Jan 9, 2017 at 2:16

nightcrawler23

2,0761 gold badge14 silver badges22 bronze badges

Collectives™ on Stack Overflow

Conditional data validation using excel lists

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related