conditional formatting and offset()

  • Thread starter Thread starter Peter Horst
  • Start date Start date
P

Peter Horst

Is it possible to use conditional formatting to change the format of a cell
if it is the last cell in a column containing a value? That is, I'd like a
cell to look different if the cell below it is empty. I've tried:

="if(offset(A1,1,0)=0)"

as the condition. I've also tried using "" as the value I'm checking
against.

Thanks much,

Peter
 
Hi
try the following:
Assumptions:
- your range is A1:F20
- text values are skipped

Try the following:
- select the entired range (in this case A1:F20)
- enter the following formula in the conditional format dialog:
=COLUMN(A1)=MATCH(9,9999999E+307,$A1:$F1)
- choose a format
 
Hi Peter,
The quotes certainly don't belong there, the formula your want (untested) is
and the cell that is active when you enter the formula must be A1 since
that is the reference. The entire column should be selected.
Suggest the following conditional formatting formula:

=AND(NOT(ISBLANK(A1)),ISBLANK(OFFSET(A1,1,0)))
 
That seems to have done it, thanks much.

David said:
Hi Peter,
The quotes certainly don't belong there, the formula your want
(untested) is
and the cell that is active when you enter the formula must be A1
since
that is the reference. The entire column should be selected.
Suggest the following conditional formatting formula:

=AND(NOT(ISBLANK(A1)),ISBLANK(OFFSET(A1,1,0)))
Click to expand...
 
Hi David
this could be shortened for conditional formating to
=AND(NOT(ISBLANK(A1)),ISBLANK(B1))


--
Regards
Frank Kabel
Frankfurt, Germany

David McRitchie said:
Hi Peter,
The quotes certainly don't belong there, the formula your want (untested) is
and the cell that is active when you enter the formula must be A1 since
that is the reference. The entire column should be selected.
Suggest the following conditional formatting formula:

=AND(NOT(ISBLANK(A1)),ISBLANK(OFFSET(A1,1,0)))
--
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Is it possible to use conditional formatting to change the format of a cell
if it is the last cell in a column containing a value? That is, I'd like a
cell to look different if the cell below it is empty. I've tried:

="if(offset(A1,1,0)=0)"

as the condition. I've also tried using "" as the value I'm checking
against.

Thanks much,

Peter
Click to expand...
Click to expand...
 
Ok, I guess C.F. formulas to not get adjusted but
you meant A2 instead of B1
=AND(NOT(ISBLANK(A1)),ISBLANK(B1))
 
David
§$$%%$§ Yes i meant that :-)
always get confused with the OFFSET parameter order :-(
 
Hi
change the formula to
=ROW(A1)=MATCH(9,9999999E+307,A$1:A$20)

messed columns/rows
 
For those of us in the US change the comma to a decimal point
=COLUMN(A1)=MATCH(9.9999999E+307,$A1:$F1)
but this will compare the last *numeric* value found to anything in
the selected columns. No check if he next cell is blank.

So all the one's in column A would be highlighted along with
any 1's in the other columns selected.

aa
3
1 (marked)
bb
44
1 (marked)
45
cc
(empty)

If course my formula would mark any cell with content including
spaces or formula that is folllowed by a blank (empty) cell. Maybe
it's more desireable (but unlikely so) with the extra selections.

aa (marked)
(empty)
aa
bb
cc (marked)
(empty)
(empty)
dd (marked)
(empty

Per Franks suggestion using A2 instead of OFFSET(A1,1,0)
My formula could be modified to
=AND(NOT(ISBLANK(A1)),ISBLANK(A2:A$65536)
but that would take forever to calculate

The following might be a compromise, as it would require
at least three empty cells below to be marked.
=AND(NOT(ISBLANK(A1)),ISBLANK(A2:A4))




--
 
With the revision for US of a decimal point instead of a comma
in the number, Frank's new formula worked as advertised to
find the last *number* in the column (not the last value)
=ROW(A1)=MATCH(9.9999999E+307,$A1:$F1
or
=ROW(A1)=MATCH(9.9999999E+307,A:A)

Then tried the formula with COLUMN and it did't work at all, so
I don't know what I tested and deleted the worksheet.

So forget my other replies.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
 
Back
Top