Link excel sheets with blanks

[phobo]

I have set up a database which only I interact with and a template which pulls data from that which others can use.

The issue I have found though is that when I link a cells blank cells become zeros in the new sheet

e.g. '[Workbook1.xlsx]Sheet1`!A1 = `[Workbook2.xlsx]Sheet1`!A1

If A1 in workbook 1 was just an empty cell then A1 in workbook 2 becomes a zero.

I can fix this with a simple IF statement making these zeros appear as "" however then other formulas get affected with errors.

Eg I have a subtotal which is trying to multiply another cell by "" which gives the #VALUE! error.

Anyone know a way around this? I am aware I can configure the sheet to show zeros and blank but want to avoid that for now as there are other sections that need to show empty zeros.

 

[Trevor_S]

You can apply custom number formats to cells (or ranges of cells). Select the cells, then Format Cells, Number, Custom. There is four parts to the format string, separated by semicolons. The third part is for nil values, leave this blank (i.e. nothing between the two semicolons) and the zero won't display. But cells elsewhere in the sheet will still display zero values.

See https://support.office.com/en-gb/ar...r-format-78f2a361-936b-4c03-8772-09fab54be7f4 for more info on custom number formats.