Format Issues (2003)

Topic

New Reply

Lounger’s I have two problems that I’m sure someone can help with:-
1. Conditional Format – I have a column that has dates of a month – ie 1/1/05 through to 31/12/05 and another that contains dates for public holidays – I want to be able to set a conditional format so when a date in the month column equals a date in the public holidays column to conditional format activates, &
2. I have cell that calculates a %, formatted to 0 decimal places, when I add & “text” ie ‘46% of the total’, the cell returns a value of “0.461538461538462 for the total”

any thoughts?

Reply | Quote

Replies

1. For your conditional format, assuming the date list starts at A1, and the Holidays are in a range called ‘Holidays’, in cell A1 use “Format | Conditional | Formula is” and enter

=ISNUMBER(MATCH(A1,Holidays,0))

set the desired format, and copy the format down.

2. For combined values and text in a cell, use the =TEXT(argument,format) function combined with text such as this:

=”This months’ widget inventory value is “&TEXT(A1/B1,”#0%”)&” of the total”

Reply | Quote

Thanks John – Will give it a go

Reply | Quote

You can’t really copy a conditional format down. You have to select all of the cells you want conditionally formatted before you go to Format | Conditional Format.

Reply | Quote

I don’t agree with you, Legare. To make sure, I just repeated my advice above and then copied the format down using the format Painter tool; it works.

Reply | Quote

I don’t agree with you, Legare. To make sure, I just repeated my advice above and then copied the format down using the format Painter tool; it works.

Reply | Quote

I don’t understand.

I copy cond formats down a column just like other formulas/formats. Autofill will even work to copy the formats.

You can also select the cell with conditional format
press the “Format Painter” button and then select the range to put the same format. It works the same as doing the selection first: the format refers to the “cell” or the “relative cell” if based on a formula.

You can also copy – paste special – formats
Steve

Reply | Quote

I don’t understand.

I copy cond formats down a column just like other formulas/formats. Autofill will even work to copy the formats.

You can also select the cell with conditional format
press the “Format Painter” button and then select the range to put the same format. It works the same as doing the selection first: the format refers to the “cell” or the “relative cell” if based on a formula.

You can also copy – paste special – formats
Steve

Reply | Quote

I would agree with John and Steve on this.

Just to add one more thing. In general, you do have to be careful with your absolute and relative cell references when copying conditional formats. John’s conditional format formula doesn’t have any absolute vs. relative reference issues.

Reply | Quote

Yep, I frequently relearn the absolute vs. relative reference lesson the hard way!

Reply | Quote

Yep, I frequently relearn the absolute vs. relative reference lesson the hard way!

Reply | Quote

[indent]

---

you do have to be careful with your absolute and relative cell references when copying conditional formats

---

[/indent]

This is good advice whenever copying a formula, whether it is cell formula, conditional format formula, a validation formula, or even a named formula.

Steve

Reply | Quote

[indent]

---

you do have to be careful with your absolute and relative cell references when copying conditional formats

---

[/indent]

This is good advice whenever copying a formula, whether it is cell formula, conditional format formula, a validation formula, or even a named formula.

Steve

Reply | Quote

I would agree with John and Steve on this.

Just to add one more thing. In general, you do have to be careful with your absolute and relative cell references when copying conditional formats. John’s conditional format formula doesn’t have any absolute vs. relative reference issues.

Reply | Quote

You can’t really copy a conditional format down. You have to select all of the cells you want conditionally formatted before you go to Format | Conditional Format.

Reply | Quote

1. For your conditional format, assuming the date list starts at A1, and the Holidays are in a range called ‘Holidays’, in cell A1 use “Format | Conditional | Formula is” and enter

=ISNUMBER(MATCH(A1,Holidays,0))

set the desired format, and copy the format down.

2. For combined values and text in a cell, use the =TEXT(argument,format) function combined with text such as this:

=”This months’ widget inventory value is “&TEXT(A1/B1,”#0%”)&” of the total”

Reply | Quote

Once a conditional format is created in a cell, it can be copied down, accross or even to specific cells on the sheet. Say the conditional format is already created in cell A1 and you need it in cell C5, E8 and F10. You can copy A1 and then select C5, E8 and F10, use paste special and under paste, select formats. Conditional formatting will be copied as well as any other formatting from A1.
As stated by the other loungers, attention needs to be paid to absolutes and named ranges.

yoyoPHIL

Reply | Quote

Once a conditional format is created in a cell, it can be copied down, accross or even to specific cells on the sheet. Say the conditional format is already created in cell A1 and you need it in cell C5, E8 and F10. You can copy A1 and then select C5, E8 and F10, use paste special and under paste, select formats. Conditional formatting will be copied as well as any other formatting from A1.
As stated by the other loungers, attention needs to be paid to absolutes and named ranges.

yoyoPHIL

Reply | Quote