need dynamic cell address for mid level data validation dead end

[Polymorpher]

Column D is full of either "y" or "n".

i want to mark all cells in rows A and B as invalid if the value of D on their row is "n"

Example.

A1=Bla B1=Tra D1=n
A2=Bla B2=Tra D2=y

A1 and B1 are valid
A2 and B2 can marked as invalid

I was attempting to use something like =IF("y"<>ADDRESS(ROW(),3),TRUE,FALSE)
when i failed miserably - couldn't get the damn if to use the custom crafted address.


Or how do i make:

=OFFSET(ADDRESS(ROW(),5),0,2)
work....

[Punnam]

Hi,

Can you please upload a sample workbook without any confidential data , showing your requirement manually .

Punnam

[Polymorpher]

sample.xlsx here we go

[Punnam]

Hi ,

I am not sure about Data Validation .

This could be a other way bit similar to your required result .

Punnam

[Polymorpher]

I have a way to mass delete invalid cells marked by data validation.

I don't have a way to delete stuff matching that criteria by other means.

if you can delete the non matching cells via your method - sure.

[Punnam]

HI,

The attachment in my previous post was not uploaded .
Check this attached file

Punnam

[Polymorpher]

if you are going to build a support structure you might as well do a simpler one:

=IF(INDEX($D$1:$D$15,ROW())="y",OFFSET(INDEX($D$1:$D$15,ROW()),,-3),"")

...for column A obviously. I can also make the offset dynamic but you get the point.

Point is all of this is avoided when using data validation.

[Polymorpher]

this whole thing would unravel if i knew how to make something like this work:

=OFFSET(ADDRESS(ROW(),5),0,2)

[Punnam]

@ Polymorpher,

I think your requirement is not clear to me As per my understanding
You need to remove the Invalid data in Sheet1 & Get the address of all the valid cells in sheet1

if that is the case see the revised sheet .

Punnam

[Polymorpher]

The address i am able to get... However I can't use it within other functions.

Try - filling 10 rows of column A with numbers only. and try to use that address:
=SUBSTITUTE(IF(D1="y",(CELL("address",A1)),""),"$","")
with a function:
=SUM(SUBSTITUTE(IF(D1="y",(CELL("address",A1)),""),"$",""):A5)

I need a method that gets the addresses in such dynamic ways but can also use them inside other functions directly.
That way it would most probably work in data validation as well.

[Punnam]

@ Polymorpher ,

I think its time,
More attention is required other forum members .Post Dummy Post saying "BUMP" so that other member can also take part in this.
"=SUM(SUBSTITUTE(IF(D1="y",(CELL("address",A1)),""),"$",""):A5)" need a indirect function to total it
Punnam

[Polymorpher]

Bump

I have seen a guy use a string to craft such dynamic addresses and use them within regular functions but i lost the document that had it.

=INDIRECT(IF(D5="y",(CELL("address",A8)),0))

works, now i'll grind to make something usable with data validation.

[sktneer]

You can try the formula given below for data validation and circle the invalid data. In the data validation --> Allow --> Custom --> Paste the formula given below in the formula box --> Error Alert --> Style --> Information --> OK.
Now select your data set and click on Data Validation -> Circle Invalid Data

Please Login or Register  to view this content.

Is this something you can work with?

[Polymorpher]

Jesus it looks so simple, and yet i was so far away. Thats where the know-how kicks in i suppose.

So yeah that does it.

[sktneer]

Glad I could help. Thanks for the feedback.