Copy content and format of a cell in a formula.

Occasional Contributor

So cell A1 says:

Alex

 

if I go to A2 and put in the formula "=A1"

This will give me: Alex

 

However I want to keep the color red and the background color if there is one as well as "bold" or underscore or anything else to do with the formatting. 

 

Is there a way to to that within a formula?

 

4 Replies

@alex2476 

No, formulas don't work that way.

You can use the Camera Tool to create a "live" picture of a cell (or range). It will automatically be updated when you change the value or formatting of the source cell, but it will be just a picture: you won't be able to use it in formulas.

@alex2476 

 

As @Hans Vogelaar has already said, there's not a formula-based way to do what you're asking, especially if the formatting you're wanting to copy with the formula is random or arbitrary.

 

However, if the colors/background/etc --the formatting -- is not arbitrary, but very systematic, i.e., if you're original cell's format is created following rules in the Conditional Formatting way, then you could just use the same rules and Conditional Formatting.

 

And if I could be an Excel consultant for a moment, and ask a question I'd be sure to ask were we meeting face-to-face, that question would be "Why?" Asked more completely, "Why are you wanting to do this in the first place?"

 

Adding more complete context to the question, having viewed many many spreadsheets over the course of recent years, unless formatting is used consistently and in a rule-based fashion, it can make a spreadsheet look chaotic. Even used consistently and in a rule-based fashion, excessive use of text color, bold and varied fonts, background color, etc, can draw attention to itself in a way that distracts from the functionality that people usually want, and can make ongoing maintenance a pain.

 

So that's why I ask. You don't have any obligation to answer, of course, but I do think it's a legitimate question, meant to help, and based on experience helping others design spreadsheets.

So I work on a schedules for films production (commonly called "callsheets"). Now that sheet contains a lot of information and is often very busy looking and written quite small. I like to make a printable blowup of portions of the sheet that I can distribute to certain department.

So for example:
Actor 1 so supposed to be on the make up chair at 7:00 with Suzie
and Actor 2 need to be on the make up chair at 7:00 with Julie and so on.
So I usually color code it because there is no place to put the name of the actual make up artist for each actor. And yes its arbitrary in the sense that Suzie and Julie decide who does the makeup for whom. But then it means that, in the blow up portion, I need to color code it again. its not a huge deal but I like to make is as efficient as possible. I guess I could record a macro that copies/paste and make bigger the first board in a different sheet.

Anyways thanks for the feedback I thought there was a very simple solution to this that I wasn't aware of. And every time I searched I didn't get the answer I was looking for so I tried explain it in more details here.
And if the color coding is a matter of the color corresponding to the make-up artist, that could easily be done by means of Conditional Formatting. Or if it's the actor, same thing.
It IS a simple solution.
https://exceljet.net/lessons/what-is-conditional-formatting
https://www.youtube.com/results?search_query=conditional+formatting+excel