[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

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
0
ellsworth2000
Asked:
ellsworth2000
  • 5
  • 4
1 Solution
 
jppintoCommented:
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...
0
 
ellsworth2000Author Commented:
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
0
 
jppintoCommented:
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ellsworth2000Author Commented:
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?
0
 
jppintoCommented:
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.
0
 
ellsworth2000Author Commented:
I'm confused on the Merged cells in column O and P.  Aren't they already merged that way?

0
 
jppintoCommented:
What I meant is that you have to make sure that for instance, on row 100, if you have row 100 merged with row 101 on column O, you must be sure that you also have cells P100 and P101 merged so that the row numbers match on the Validation formula.
0
 
ellsworth2000Author Commented:
Perrrrfect! you're aweseom Jppinto!!!!
0
 
ellsworth2000Author Commented:
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?
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now