Return row # from column A if "Value" = X or Y if condition "yes" is met.

[TheFireBat]

Hey guys new to POSTING in the forum. I'm usually pretty good at googling the results I need and scanning either Mr. Excel, Excel Forum, or stack overflow to get my answers respectively... I am self-taught and also reach out locally to my other excel friends when in need. THIS problem is driving me up the wall.

HERE is a LINK to an example sheet: https://drive.google.com/open?id=0B3...TFGbno4aHQ4NjQ

I have everything set so that in column A on sheet 1 a value of either X, Y, or "" will be returned based on a date range showing me tabled data for a promotion on another page by referencing a row number.

(For context X means the row references a promotion running between 2 dates EX: 6/16/16 - 6/16/17
Y means the row references a promotion starting after a date but with a currently undetermined end date EX:6/16/16 - ""
Finally "" or "Blank" just means there isn't a current or open promotion for this row and it should be ignored.)

Again THIS much of what I'm working on works perfectly, however, I'm trying to rig up a button to toggle showing "Y" that way it will display only Xs. I have been able to show X's ONLY with this formula =MATCH("x",Sheet1!$A$1:$A$98,0)

by trying to add Ys into the equation I have only been successful of returning a value of "TRUE" I've used things like =Match(OR("x","y"),Sheet1!$A$1:$A$98,0) and everything I can think of up to terribly awful abominations like

=IF(MATCH("x",Sheet1!$A$1:$A$98,0)+ROW(Sheet1!A1:A98)-1,MATCH("x",Sheet1!$A$1:$A$98,0)+ROW(Sheet1!A1:A98)-1,MATCH("y",Sheet1!$A$1:$A$98,0)+ROW(Sheet1!A1:A98)-1)

All this in an effort to return the row number if a value of X or Y is met in column A on sheet one. I havne't even had the chance to mess with the IF Yes / No toggle.

If you guy's could help me get THIS figured out I would be eternally grateful.

-Mike

Please Login or Register  to view this content.

[sandy666]

I cannot open anything from Google so....

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

or

If it is a Google Sheet try here: For Other Platforms(Mac, Google Docs, Mobile OS etc.)

[TheFireBat]

I cannot open anything from Google so....

Attach a sample workbook.

-Thanks Sandy!

I never thought someone might not be able to access a file shared via google! I've uploaded the file as requested. I appreciate your assistance!
-Mike

[sandy666]

so you want to show only values for X or X and Y on sheet2?

but
for X only

Sheet1
A2: =IF(AND(PDate>=B2,C2="-"),"Y",IF(AND(PDate>=B2,PDate<=C2,C2<>"-"),"X",""))
it will show X or Y
Sheet2
D3: =IF(Sheet1!A2="X",ROW(Sheet1!A2),"")
It will show row number for X

both drag down

what you want to do with Y?

[sandy666]

Ok, now you can select what you want: X, Y or both

Let me know if it works for you

[TheFireBat]

This is great work Sandy!

I really appreciate the help! I have never seen the IFNA command.
I like seeing something new... I usually use IFERROR, I'll study up on the differences.

I am very curious as to HOW this works. I see the code in Promo Summary column D has changed but still seems to have matched and ROW.
I see that E1 is now Operating as a switch. returning the value typed or ? to return ANY OTHER value. (Which is PERFECT!)
I also see thatColumn D is referencing Column C on the same page, but I don't understand how the helper column operates?
Could you help me understand the values C is returning and how this "Helper" is causing the desired end result?

I really appreciate you going above and beyond, I just want to understand what I couldn't figure out. (As I tried SOOOO many things.)
Once I have a better understanding I'll be able to use similar code in the future unassisted.

Thank you ever so much again!
-Mike

[sandy666]

here is updated file, some cosmetic correction

[sandy666]

IFNA vs IFERROR
IFNA works with #N/A error only but is shorter than IFERROR and was applied to Ex2013 and up, that's all
btw. IMHO #N/A is not an error, this is info: Not Available

Helper: checking for non-blank cell and counts characters defined by cell E1

returning the value typed or ? to return ANY OTHER value

any single characters (all characters: x and y and whatever will be there) but there is assumed only x and y so ? means x and y
shorter: x count x, y count y, ? count x and y

=IFNA(MATCH(ROWS($C$3:$C3),$C$3:$C$55,0),"")
ROWS($C$3:$C3) = 1
ROWS($C$3:$C4) = 2
etc,,,
$C$3:$C$55 = {0;0;0;1;1;1;1;1;1;1;1;1;2;2;2;2;2;2;2;2;2;2;"";"";"";"";"";"";"";"";"";"";"";"";"";"";0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}
so MATCH lookin' "1" in range for the first position of this value in range and like you see first "1" is on 4 position
the next value "2" is on 13 position
and so on...

Notice, I changed undefined ranges to defined because less troubles with shifting values
You need to know when and where and how you can use defined or undefined ranges

I am not a good teacher, sorry

and on the end: if it works for you (file from post #7) mark thread as SOLVED (go to the top of thread, right side, Thread tools)

[TheFireBat]

Thanks again!

I added a REP POINT. I also marked the thread as solved. Thank you for explaining! That helps a lot. Like I said I didn't just need a solution I need to educate myself! That was awesome and I appreciate it very much. I tried to figure this problem out for some time between projects at work but an elegant solution such as this I was quite far from. Also I chuckled at your "Office Version" by your name... I'm using the same version LOL!

-Mike

[sandy666]

You are welcome and thanks for feedback and mark thread solved