Results 1 to 5 of 5

20030216, 08:26 #1
 Join Date
 Oct 2002
 Location
 RossonWye, Herefordshire, United Kingdom
 Posts
 212
 Thanks
 0
 Thanked 0 Times in 0 Posts
First Cell to Left Not Blank (Excel 97SR2)
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

20030216, 11:38 #2
 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 97SR2)
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 ctrlshiftenter):
=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 ctrlshiftenter):
=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

20030216, 16:00 #3
 Join Date
 Oct 2002
 Location
 RossonWye, Herefordshire, United Kingdom
 Posts
 212
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: First Cell to Left Not Blank (Excel 97SR2)
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

20030217, 00:12 #4
 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 97SR2)
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/ ctrlshiftenter)
=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

20030217, 10:44 #5
 Join Date
 Oct 2002
 Location
 RossonWye, Herefordshire, United Kingdom
 Posts
 212
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: First Cell to Left Not Blank (Excel 97SR2)
Hi Steve
Spot on! Many thanks... and I think I even understadn why it works!
Regards
Peter