Format Question

[dantheram]

If i have a cell where the value contained in it is changing (=OFFSET(A2,$A$24,$A$25,10,1), can i tell those cells to have different formats depending on the returned data.

I have %, currency and numbers to display in the offset cells.

 

[Ruddles]

You can make a cell's format change automatically depending on the value in it or depending on the value in another cell using conditional formatting.

Is that what you want to do?

 

[dantheram]

Yes exactly, how do you achieve this?

 

[dantheram]

bump

 

[Ruddles]

What's the address of the cell and what ranges of values should decide whether it should be formatted as percentage, currency or number?

And which version of Excel are you using?

 

[dantheram]

Here is the equation in the cells where i require dynamic formatting - =OFFSET(A2,$A$25,$A$26,10,1).

i'm using excel 2003.

 

[Ruddles]

I'm afraid the formula doesn't help. You want the format of the cell to be determined by its value, yes?

If so, I'll need the address of the cell and what ranges of values should decide whether it should be formatted as percentage, currency or number.

Have a read of this: http://www.cpearson.com/excel/cformatting.htm.

 

[dantheram]

I'm afraid the formula doesn't help. You want the format of the cell to be determined by its value, yes?

If so, I'll need the address of the cell and what ranges of values should decide whether it should be formatted as percentage, currency or number.

Have a read of this: http://www.cpearson.com/excel/cformatting.htm.

Their is no range of values, here is the problem. That formula will either return a % or currency. I want excel to format these numbers correctly in the cell containing the formula.

Basically i have data which is dynamic and the cell formatting is remaining the same regardless.

 

[Ruddles]

Okay, I'm not 100% with you... when I asked you if you wanted to change the format of the cell depending on the value in it or in another cell, you said "Yes, exactly".

Do you want the cell containing the formula to copy its format from another cell?

In short, what is it that actually decides what the format of this cell should be?

When the formula returns a value, how is Excel to know whether it's a percentage or currency?

 

[dantheram]

Okay, I'm not 100% with you... when I asked you if you wanted to change the format of the cell depending on the value in it or in another cell, you said "Yes, exactly".

Do you want the cell containing the formula to copy its format from another cell?

In short, what is it that actually decides what the format of this cell should be?

When the formula returns a value, how is Excel to know whether it's a percentage or currency?

ok,

cell 1 contains "12%", the cell with the OFFSET looks this up and displays "0.12".

Then i change the OFFSET value and cell 2 is referenced instead of cell 1. Cell 2 contains "£30". the OFFSET cell then displays "30".

I simply want the the formatting of the OFFSET cell to keep the oringinal formatting regardless of its own cell format.