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.

  • not only content updates automatically, but also format What the values and formats are updated by? macro? VBA? external prog? to avoid blank cells in my source sheet to show as 0 To show or to store? In the first case You may use conditional formatting for zeroes as white-by-white, for example... – Akina Jun 1 '18 at 16:14
  • @Akina Sorry. This might be a very basic question comparing to how you answered. I was previously linking cells by "=", but this only updates the contents, not the format. Also, a blank cell in the source will update the current as 0. Maybe I'm thinking about this the wrong way.. – physlexic Jun 1 '18 at 16:21
  • Oh. I understand "linking" not as reference, but as cells merge. – Akina Jun 1 '18 at 16:23
  • Well. To copy format from source to destination You may use Worksheet_Change procedure (when formula is simple - You know destination, but You are to obtain source address for format copying). To hide zero values You may convert formula '=A1' to '=IF(A1=0,'',A1)'. Of course, it slightly increase data recalculations... – Akina Jun 1 '18 at 16:25
  • Your second question is covered somewhat exhaustively  in Display Blank when Referencing Blank Cell in Excel. – Scott Jun 1 '18 at 18:35
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.

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.

  • IsBlank() will not hide zeroes obtained from space-only, hidden symbols, etc... – Akina Jun 1 '18 at 16:51
  • @Akina I'm sorry but you are completely wrong ;-) =A1 only returns a zero incorrectly when A1 is blank (i.e., there is no formula, no spaces-only, no nothing in A1). =ISBLANK(A1) only returns TRUE if A1 is blank. So =IF(ISBLANK(A1),"",A1) will convert all incorrectly returned zeroes to null strings. "space-only, hidden symbols, etc" are not converted to zeroes so ISBLANK() does not need to worry about "hiding" them. (As they never existed to start off with ;-) ) – robinCTS Jun 4 '18 at 16:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.