Hi experts,

In the attached file Query107, my data entries are in sheet1. Then I made a sheet2 linked to sheet1. In sheet2, when i copy-drag the formula downward there appears zero values, and 1/0/1900 that displayed, especially for cells with a no-value linked in sheet1.

How do I hide this zero and 1/0/2011 values when there is a no-value link found in sheet1. As it is, I have to manually delete these no-values rows before printing.

Please advice.



Kim

Attached Files

Query107 - How to hide cells without value in linked cells.xls (20.5 KB, 10 views)

I noticed that you are not using now R1C1 reference style
There are 2 options, You can use this formula:
=IF(LEN(Sheet1!A4)>0;Sheet1!A4;"")
you might need to change the ";" separator to "," separator, depending on your regional settings.
Or, in excel options-complex-show options for this worksheet : uncheck the option -show a zero in cells with zero value

Hi Catalin,

I use the formula - and you got it right on the spot! (as usual) Thanks a lot!

I also viewed my excel options and much as I looked, i could not find the setting 'complex-show options for this worksheet' and the 'show a zero in cells with zero'. I think this is a more preferred option as i understand this is kind of 'set once-apply to all' setting.

Best regards,

Kim

My mistake, i just noticed you use excel 97, the instructions were for 2007 and up. I never worked vith versions older than 2003, so i'm on unknown teritory... But , if is similar to excel 2003, on tools tab, click options and you should find there this option.