Solved

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

Posted on 2012-04-09
11
305 Views
Last Modified: 2012-04-09
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
0
Comment
Question by:eyes59
11 Comments
 
LVL 41

Expert Comment

by:dlmille
ID: 37825544
** 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
 
LVL 17

Expert Comment

by:vb_elmar
ID: 37825549
I modified your file, and I added a command button to it.

Please press the command button.
Calculate-10-days.xls
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 37825565
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
 

Author Closing Comment

by:eyes59
ID: 37825603
THANK YOU.  Worked PERFECTLY
0
 
LVL 9

Expert Comment

by:anthonymellorfca
ID: 37825651
:-)
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 41

Expert Comment

by:dlmille
ID: 37825663
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
 
LVL 9

Expert Comment

by:anthonymellorfca
ID: 37825667
I need to type faster :-)

Congrats - looks like you just hit 1,000,000 !
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37825676
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
 
LVL 9

Expert Comment

by:anthonymellorfca
ID: 37825687
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
 
LVL 41

Expert Comment

by:dlmille
ID: 37825704
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
 
LVL 9

Expert Comment

by:anthonymellorfca
ID: 37825710
yes, certainly tidier.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now