Excel Cell Formats

[timwescott]

Hello

Is there anyway that the cell formatting as well as the cell contents can be put in to a new cell reference aside from using the copy command?!

Example. Cell A1 has the letter 'A' in it, which is red and bold. Cell A3 contains the formula ="DC"&A3&"BKP001" but the cell is not formatted. What I would like is the formatting as well as the contents of A1 to show in A3 when the cell is included.

Is this possible and if so, how?!


Thanks
Tim

 

[xlbo]

not possible within a formula I'm afriad. you can individually format pieces of text strings but not formulae - not for font & colour anyway

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[timwescott]

Humm, what a shame as it's gonna be a pain in the a**e to format dozens of cells with the same format that already appears in a single cell.

Can anyone write me a formula that is the same as doing a Copy, Paste Special and selecting the Formats option or any of the other options in that area?!?

 

[xlbo]

you cannot write a formula that is the same as doing Copy/Pastespecial - As I have already stated, a formula cannot do what you require

The only way you could do this is to use code to copy the data and then format it. If you want to do that, then please post in the VBA forum Forum707

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[CBasicAsslember]

If the formats don't need to change once set you can use the format painter, which is a lot faster then copy/paste special.

 

[xlbo]

That still won't work if the cell is a formula as opposed to a text string

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AnotherHiggins]

As Geoff has stated, a formulaic answer is not possible.

If you want all populated cells in column A to have the same formatting, then just select the entire column, then format the cells.

If there are certain criteria that determine if a cell should be red (or whatever formatting you want), then have a look at Conditional Formatting (Format > Conditional Formatting).

Short of that, you're stuck with VBA.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[xlbo]

John - still won't work for what the OP requests I'm afraid. From what I understand, they want a result like:

DCABKP001

which is just not possible if the result is a formula - it would need to be converted to a pure text string to be able to get this format - no amount of conditional formatting or format pasting will work.

If it is the whole cell that needs to be bold - well that is a different story....still couldn't pick it up from the original cell without VBA though

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[timwescott]

xlbo - (Geoff)

You are exactly right in what I want to achieve. There are other characters/groups of characters I would like to format in the same way, but I appreciate that it can't be done using a formula.

Thanks for all the other suggestions and comments.


Regards
Tim

 

[xlbo]

no probs - as I suggested earlier, this can be done by VBA code - the only issues are that you lose the formulaic link to the original data and whether you are comfortable using VBA...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AnotherHiggins]

Well there's your problem right there, Geoff: you actually read the posts before answering.

I should know better than to respond that early in the morning.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[xlbo]

lol - I have a several hour head start on you John - mid afternoon here

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question