Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    First Cell to Left Not Blank (Excel 97-SR2)

    Hi Folks

    I'm trying to identify the address of the FIRST cell to the LEFT of the current cell that has something in it! See the example below. Assume we start A1. In row 2 I have identified the answer I want, now all I need to do is figure out the formula to achieve it.

    Date Date Date Date Date Date Date Date
    None B2 E2

    Whether ANYTHING at all appears in row 2 is primarily dependendent opon what date appears in the corresponding cell above it in row 1. If something should appear in row 2, I then want to work backwards to the last cell on the left that has something in it in order to establish what dates applied to that cell. I've got all the date manipulation stuff working, just can't see how to identify the first cell to the left that has something in it.

    Regards
    Peter

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: First Cell to Left Not Blank (Excel 97-SR2)

    If I understand correctly. You want to find the 2nd to last column in Row2 that has something in it.

    Try this ARRAY formula(ALL ONE line, confirm with ctrl-shift-enter):

    =INDEX($2:$2,MAX(IF(LEN(OFFSET($A$2,0,0,1,MAX(IF(L EN($2:$2)<>0,COLUMN($2:$2)))-1))<>0,COLUMN(OFFSET($A$2,0,0,1,MAX(IF(LEN($2:$2)< >0,COLUMN($2:$2)))-1)))))

    If you want the DATE (from Row1 corresponding to that value(ALL ONE line, confirm with ctrl-shift-enter):
    =INDEX($1:$1,MAX(IF(LEN(OFFSET($A$2,0,0,1,MAX(IF(L EN($2:$2)<>0,COLUMN($2:$2)))-1))<>0,COLUMN(OFFSET($A$2,0,0,1,MAX(IF(LEN($2:$2)< >0,COLUMN($2:$2)))-1)))))

    It finds the column in the entire row2 of the cell whose contents whose contents have a length <>0. It then defines a range in row2 that is 1 column less than the last cell. It then looks in this "subrange" for the last column that has a value. It then extracts this value using INDEX.


    Steve

  3. #3
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: First Cell to Left Not Blank (Excel 97-SR2)

    Hi Steve

    Thanks for the feedback, appreciated. I think however we type at crossed purposes, my poor explanation of the problem and not your solution I suspect. (I was rushing out this morning.) The attached spreadsheet extract hopefully demonstrates the issue better.

    Essentially row 1 has a list of user selectable dates. In row 2, in the corresponding cell, if the date they selected in row 1 meets some test criteria, I am displaying text that describes the range of dates that the data that appears below applies to. If there is valid data in the cell immediately to the left then the existing formulas in A2:I2 will work since I know what date was selected in the cell immediately to the left.

    My problem comes when the date in the cell immediately to the left isn't valid. What I then need to do is walk backwards through the cells on the left until I find the first cell that does have a valid date in it. Also keep in mind that there may be no valid dates to the left. Because the dates in row 1 are user selectable, the formula employed needs to be flexible enough to cope with changes in the dates fields.

    Hope I'm explaining myself a little better this afternoon.

    Regards
    Peter
    Attached Files Attached Files

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: First Cell to Left Not Blank (Excel 97-SR2)

    I didn't spend a lot of time trying to understand your spreadsheet, so I didn't add it to your formula. I leave that to you.

    Add a formula snippet similar to that below as part of your formula. OR You could just create an intermediate row with these formulas in them and use that row's results in your calcs, whatever is easier for you

    This formula in col B and copied across the columns will give the DATE (from row 1) of the cell to the left of B that is not a null string. It is an ARRAY formula (confirm w/ ctrl-shift-enter)

    =INDEX($1:$1,MAX(IF(LEN($A$2:A$2)<>0,COLUMN($A$2:A $2))))

    As you copy it across the columns the columns expand to expand the range So in Col I (for example) this formula will be entered. Notice $A$2 is locked on Col and row so does not change, but A$2 will change columns (to H$2) as the formula is copied down the columns to Col I, so the range checked is always A to the col to the left of the current cell:

    =INDEX($1:$1,MAX(IF(LEN($A$2:H$2)<>0,COLUMN($A$2:H $2))))

    It essentially gets the LARGEST column number of the cols from A to the column to left of the cell with the formula. This Largest column number is used to index row 1 to get that date.

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: First Cell to Left Not Blank (Excel 97-SR2)

    Hi Steve

    Spot on! Many thanks... and I think I even understadn why it works!

    Regards
    Peter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •