I want to display blank linked cells as blank, not zero filled 4

[RBP]

Can anyone help with this?
I know how to link cells content from one sheet to another (copy > paste > Paste Options > Link Cells )so that sheet-1 r1c1 displays in sheet-2 r1c1( or wherever), but if sheet-1 r1c1 is blank (empty of all content) how do I stop sheet-2 r1c1 from filling with a "0"? It works if you put a space char in the linked from cell, but that's a pain in the rear.

The data is a mix of text, and dates, no more than 30 - 40 chars. Any fixes/suggestions would be gratefully received.
Thanks

 

[yogia]

Hi RBP:

Let us say you are lnking to cell A1 of Sheet1, then you can use ...

=IF(LEN(Sheet1!A1),Sheet1!A1,"")

The formula would produce a formula blank if the cell being linked to is blank. Would this do?

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[AnotherHiggins]

Or
[tab][COLOR=blue white]=If(isblank(Sheet1!A1),"",Sheet1!A1)[/color]

Or
[tab][COLOR=blue white]=If(Sheet1!A1="","",Sheet1!A1)[/color]


As for linking from one spreadsheet to another, I think it is easier to just do this:[ul]
[li]In the sheet where the link will be, select the cell you want to be linked and press [highlight]=[/highlight][/li]
[li]Navigate to the sheet that has the data on it[/li]
[ul][li]You can [Ctrl]+[ignore][Tab][/ignore][/li]or
[li]Window > Sheet1[/li][/ul]
[li]Click on the cell you want linked[/li][/ul]

That's it.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[RBP]

Thanks for swift responses.
Response 1, works fine after a little doctoring of names etc.
Response 2 has worked, but now does not,it's a bit flaky, I have to Copy Paste, Paste link for it to work, and that then gives me the wayward "0" problems.
Cheers RBP

 

[xlbo]

there's pretty much no need to ever do the copy/paste link. If you have 2 excel workbooks, as John has explained, simply typing = in the cell and then navigating to the other workbook and selecting a cell is the best, fastest and easiest way to link data

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

Hi RBP,

there are 2 ways you can suppress zero values in cells ( whatever way they are generated ), and that is to, either:
a) do menu command Tools/Options/View and untick the Zeroes checkbox
or:
b) select the relevant cells and create a custom number format of:
#,##0;-#,##0;;@
You can adjust the number format of the numeric parts as required ( adding decimal places, or having the negative part have a different colour, or whatever ). Custom number formats have 4 parts:
format_for_positive_numbers;format_for_negative_numbers;format_for_zeroes;format_for_other_contents




Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[RBP]

Thanks Geoff & GlenUK

Geoff, re your response.

"there's pretty much no need to ever do the copy/paste link. If you have 2 excel workbooks, as John has explained, simply typing = in the cell and then navigating to the other workbook and selecting a cell is the best, fastest and easiest way to link data."

I did try this originally, ( I am linking between cells in the same workbook, just two different sheets)
Unfortunately it did not work, all the seemed to happen on the linked to cell was that Excel inserted and displayed the formula into the cell, rather than the content value of the linked from cell. (could be operator failure though, it has happened in the past!) Thanks RBP.

 

[xlbo]

if you link to cell A1 in sheet2 then you should get a formula in the cell that looks like

=Sheet2!A1

This should then always refelct what is in cell A1 on sheet2

Whilst the formula resides in teh cell, the VALUE should be what is in the referenced cell. Only way I can think that thgis wouldn;t work is if you have your calculation set to manual

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question