Find the value in a row of cells where all but one column is blank

I have a spreadsheet with month columns there will be a date in only ONE month want to be able to calculate 10 days prior in another cell to confirm notification to attendee.  I have attached a sample
Calculate-10-days.xls
eyes59Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dlmilleCommented:
** Edited **
If the row will have nothing, but one date, then you can use COUNTA to determine if there is an entry, then the LOOKUP formula to find that entry and then subtract 10 from it.

Put this formula in C3 and copy down:

[C3]=IF(COUNTA($D3:$O3)=0,"",LOOKUP(1,1/($D3:$O3<>""),$D3:$O3)-10)

See attached.

Dave
Calculate-10-days.xls
0
vb_elmarCommented:
I modified your file, and I added a command button to it.

Please press the command button.
Calculate-10-days.xls
0
dlmilleCommented:
The way the LOOKUP works:

=LOOKUP(1,1/($D3:$O3<>""),$D3:$O3)

The first parameter is looking for a 1, if found, then it uses that INDEX from the lookup to find the value in the $D3:$O3 range of cells (the lookup_vector)

So, that leaves us with the middle part:

1/($D3:$O3<>"")

$D3:$O3 returns an array of values.  $D3:$O3<>"" returns an array of TRUE and/or FALSE's.  The 1/($D3:$O3<>"") returns an array of #DIV!0's or 1's.  #DIV!0 if its FALSE, or 1 if its TRUE.

The lookup then finds the FIRST occurrence of the 1 in that array, bypassing all the #DIV!0's.  In essence, returns an index to the first non-blank, and with the result vector, uses that index to return the actual value.

The rest of the formula is just to subtract 10 as you requested, if a date is found.

Cheers,

Dave
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

eyes59Author Commented:
THANK YOU.  Worked PERFECTLY
0
Anthony MellorChartered AccountantCommented:
:-)
0
dlmilleCommented:
That would work with a little help and find the max date (as opposed to the first date which is what lookup did):

[C3]=IF(COUNTA($D3:$O3)=0,"",MAX($D3:$O3)-10) and copy down
0
Anthony MellorChartered AccountantCommented:
I need to type faster :-)

Congrats - looks like you just hit 1,000,000 !
0
dlmilleCommented:
Thanks.

Lookup is not that complicated as a function re: relative efficiency, though if my head wasn't into these functions all the time, I would have pulled out MAX as well.  Sometimes its the simple solutions, lol.

Dave
0
Anthony MellorChartered AccountantCommented:
I'm still trying to figure out on what sub system you just passed 1,000,000.. very nice anyway.

yes I was wondering why all the effort to deliver the null because he didn't ask for it and that's what makes the answer complex. My first thought was the humble =sum() but elected the slightly "cleverer" max or min... min probably better. I supposed it was because it's a vb solution.

edit: because you used an if I wondered if it could be done without.
0
dlmilleCommented:
Can't be done without an IF if you want blank on a no-date situation.  Any array function that resolves to one result would have been OK - the lookup was just my latest "hammer", while MAX and SUM, MIN, INDEX/MATCH, etc., would work as well.  But I think formulas should be written with error checking and/or returning "" value if there is no valid result.

Cheers,

Dave
0
Anthony MellorChartered AccountantCommented:
yes, certainly tidier.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.