asked on

How can I specify the cell to the left when using Cascading Validatin List formulas?

Hello Experts,

I'm using the formula: =INDIRECT(SUBSTITUTE(O15," ","")) on the cell I want only specific data relating to the cell left of it. The cell left of it points back to the name manager name I created. I have 350+ cells in a column i need this formula applied to but the formula can't reference a specific Cell but rather the cell left of it. So instead of referncing cell O15, it would purely reference the cell to the left of it. I've attached the sheet though I've deleted all sensitive data; and columns O and P have the data validation formulas upto row 15.

Many Thanks Experts,

Ellsworth
Mining-Market-Assignments.3.xlsx

jppinto

Not sure if I understand what you want to do here! The formula that you have on column P is always referencing the column left of it. The row number of column O on the formula is increasing on each row...

ellsworth2000

ASKER

Thanks for helping me again Jppinto,

It does increase but it is off by 1 row. If i click on any cell in column O after row 15 then the name choosen doesn't match up to the table created but rather is off by one. Also I have multiple merged cells further on down the columns which really screws up the numbering and matchin of market names to tables. Does that make sense?

Thanks Jppinto,

Ellsworth

jppinto

You just need to copy the validation from cell P2 to the rest of the rows after 15 using a Paste Special where you past only the Validation.

jppinto
Capture.JPG

ellsworth2000

ASKER

Is there a way to shift the data validation formulas up one cell in column P and have the merged cells ignored during the numbering?

jppinto

Merged rows can be a problem here! You should only put the Validation on thoose merged cells after you have merged all on the same column and on the left column they have the same merges.

ellsworth2000

ASKER

I'm confused on the Merged cells in column O and P. Aren't they already merged that way?

ASKER CERTIFIED SOLUTION

jppinto

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ellsworth2000

ASKER

Perrrrfect! you're aweseom Jppinto!!!!

ellsworth2000

ASKER

Oh hey Jppinto, how do i do the special paste? is there a key combo for it or from one of the drop down menus?