1

Is there a way to link cells so that not only content updates automatically, but also format (fill, border, font, etc.)?

Also, is there a way, other than doing each cell individually, to avoid blank cells in my source sheet to show as 0 on my current sheet? I have a big file with a lot of random blank cells that I don't want to link as 0.

Thanks.

physlexic
  • 111

2 Answers2

1

As other Users have explained, the only way to have a "linked" cell that auto-updates its format when the source cell's format changes is to use VBA.

However, if the only thing you wish to do is directly mirror a source cell without any other calculations (e.g., you just want to do =A1 but also have the formats update), there is a way to make it look like that's what's happening.

Just four simple steps:

  1. Select the source range
  2. Copy
  3. Select the top left cell of the destination range
  4. Paste as a Picture Link (Home → Clipboard → Paste → As Picture → Paste Picture Link)


The following screenshots show how to create a picture link at D11 linked to D2:E3 and how it looks when the picture is deselected:

Screenshot during picture link creation Screenshot after picture link creation

Notes:

Unfortunately you can't actually use the values from the picture for any further calculations.

However, there is a work-around for this. Just directly link the cells underneath the picture to the source as well. In my example above, you would enter =D2 into cell D11 and ctrl-enter/fill/copy-paste that formula into D11:E12.

The only issue with this work-around is that blank source cells will show through the picture as 0s if the source cell's fill is set to No Fill.

To work-around this 0 issue, either set the destination cells' font colour to the fill colour (use white for no fill), or, if you actually require the destination to actually be "blank", use the formula =IF(D2="","",D2) instead of =D2.

robinCTS
  • 4,407
0

If you know VBA, you could set up a macro that runs when any of the cells in a range are changed, and apply the same formatting to another range. If not, depending on the details of what you're trying to do, you could potentially use conditional formatting that applies to both ranges. If you clarify what you need, I'll update my answer.

As for the blank/0 issue, you can use the IF and ISBLANK formulas. For example:

=IF(ISBLANK(A1),"",A1)

will return an empty string if A1 is blank, otherwise it will return the value of A1.

Greg K
  • 202