Data Validation with a twist

[zadams14]

Ok, after two days of banging my head against my desk I turn to you, I have a data validation list drop down that references 52 columns and it works great. My problem is i need a cell to reference the data validation drop down and select the next selection from the drop down.......?

Thanks,

Zach

 

[pplstuff]

Google cascading combo box, or cascading data validation.

Essentially, you'll make named ranges based on the first selection. Your second data validation list will be =indirect(a1)
where a1 is the first data selection and it's value is a named range.

 

[JoeMo]

If the validation drop down is fed from a list, use the Index/Match functions to return the next value down the list.

 

[Vidar]

Hi

Does your data validation drop down get it's list from a range in your workbook?
If so, you could do an INDEX/MATCH formula.
=INDEX,ListRange,MATCH(DropDownItem,ListRange,0)+1)

Vidar

 

[theBardd]

Assuming I read it correctly, something like

=INDEX(Sheet2!A1:AZ1,MATCH(D1,Sheet2!A1:AY1,0)+1)

where Sheet2!A1:AZ1 is the values i your DV list, and D1 is the DV cell.

 

[zadams14]

Assuming I read it correctly, something like

=INDEX(Sheet2!A1:AZ1,MATCH(D1,Sheet2!A1:AY1,0)+1)

where Sheet2!A1:AZ1 is the values i your DV list, and D1 is the DV cell.

That worked great.

Next question, now that INDEX works and shows the proper result how would i use that outcome to then lookup the column that references the new calculation and add the data in that row for the remaining columns moving forward?

New calc = Jan 8 (2nd column)

Jan 1 Jan 8 Jan 15 Jan 22 Jan 29 etc
8 10 10 20 8

Add data from all rows for Jan 15 column to 52nd column

Thanks,