Linking Sheets shows 0's for blank cells

[magmike]

I have a workbook on the server, that links to multiple files from
different users.

I used Paste Link to acheive this. Because the number of rows can
increase, I selected the columns in use, and paste linked those.
However, where there are blank fields, the new file shows zeros. Is
there a way to link without showing zeros for blank cells?

Note - all cells are formatted as text, and when I select Ignore Blank
Cells in the Paste Special box, the Paste Link button is not
available.

Thanks in advance!

magmike

 

[Gord Dibben]

To ignore the blanks and not return zeros you must manually create a trap in the
formula.

Assume your formula is similar to this..='[12months.xls]1stQ'!A2

You need to add the trap.

=IF('[12months.xls]1stQ'!A2="","",'[12months.xls]1stQ'!A2)

which cannot be done through Paste Link.

You could run a macro on the sheet with the links.

Sub Blank_Link_Trap()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = True Then
If Not cel.Formula Like "=IF(*" Then
myStr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=IF(" & myStr & "="""",""""," & myStr & ")"
End If
End If
Next
End Sub


Gord Dibben MS Excel MVP

 

[magmike]

To ignore the blanks and not return zeros you must manually create a trap in the
formula.

Assume your formula is similar to this..='[12months.xls]1stQ'!A2

You need to add the trap.

=IF('[12months.xls]1stQ'!A2="","",'[12months.xls]1stQ'!A2)

which cannot be done through Paste Link.

You could run a macro on the sheet with the links.

Sub Blank_Link_Trap()
Dim myStr As String
Dim cel As Range
    For Each cel In Selection
        If cel.HasFormula = True Then
            If Not cel.Formula Like "=IF(*" Then
                myStr = Right(cel.Formula, Len(cel.Formula) - 1)
                cel.Value = "=IF(" & myStr & "="""",""""," & myStr & ")"
            End If
        End If
    Next
End Sub

Gord Dibben  MS Excel MVP

I have a workbook on the server, that links to multiple files from
different users.

I used Paste Link to acheive this. Because the number of rows can
increase, I selected the columns in use, and paste linked those.
However, where there are blank fields, the new file shows zeros. Is
there a way to link without showing zeros for blank cells?

Note - all cells are formatted as text, and when I select Ignore Blank
Cells in the Paste Special box, the Paste Link button is not
available.

Thanks in advance!

magmike- Hide quoted text -

- Show quoted text -

Could this be done automatically after the update occurs? On protected
sheets? In a protected workbook? Without the user having to do
anything?

 

[Gord Dibben]

According to the initial post, you are the user who is doing the updating by
selecting from the other books and paste-linking to a single book.

Which books and sheets would be the protected ones?

I would say what you want is possible with enough code but need much more detail
about which books would be opened and which sheets and ranges would be copied to
the single book before running the Blank_Link_Trap code.

I'm not much of a coder so we would need help for this.


Gord

To ignore the blanks and not return zeros you must manually create a trap in the
formula.

Assume your formula is similar to this..='[12months.xls]1stQ'!A2

You need to add the trap.

=IF('[12months.xls]1stQ'!A2="","",'[12months.xls]1stQ'!A2)

which cannot be done through Paste Link.

You could run a macro on the sheet with the links.

Sub Blank_Link_Trap()
Dim myStr As String
Dim cel As Range
    For Each cel In Selection
        If cel.HasFormula = True Then
            If Not cel.Formula Like "=IF(*" Then
                myStr = Right(cel.Formula, Len(cel.Formula) - 1)
                cel.Value = "=IF(" & myStr & "="""",""""," & myStr & ")"
            End If
        End If
    Next
End Sub

Gord Dibben  MS Excel MVP

I have a workbook on the server, that links to multiple files from
different users.

I used Paste Link to acheive this. Because the number of rows can
increase, I selected the columns in use, and paste linked those.
However, where there are blank fields, the new file shows zeros. Is
there a way to link without showing zeros for blank cells?

Note - all cells are formatted as text, and when I select Ignore Blank
Cells in the Paste Special box, the Paste Link button is not
available.

Thanks in advance!

magmike- Hide quoted text -

- Show quoted text -

Could this be done automatically after the update occurs? On protected
sheets? In a protected workbook? Without the user having to do
anything?

 

[magmike]

According to the initial post, you are the user who is doing the updating by
selecting from the other books and paste-linking to a single book.

Which books and sheets would be the protected ones?

I would say what you want is possible with enough code but need much more detail
about which books would be opened and which sheets and ranges would be copied to
the single book before running the Blank_Link_Trap code.

I'm not much of a coder so we would need help for this.

Gord

To ignore the blanks and not return zeros you must manually create a trap in the
formula.
Assume your formula is similar to this..='[12months.xls]1stQ'!A2
You need to add the trap.
=IF('[12months.xls]1stQ'!A2="","",'[12months.xls]1stQ'!A2)
which cannot be done through Paste Link.
You could run a macro on the sheet with the links.
Sub Blank_Link_Trap()
Dim myStr As String
Dim cel As Range
    For Each cel In Selection
        If cel.HasFormula = True Then
            If Not cel.Formula Like "=IF(*" Then
                myStr = Right(cel.Formula, Len(cel.Formula) - 1)
                cel.Value = "=IF(" & myStr & "="""",""""," & myStr & ")"
            End If
        End If
    Next
End Sub
Gord Dibben  MS Excel MVP
I have a workbook on the server, that links to multiple files from
different users.
I used Paste Link to acheive this. Because the number of rows can
increase, I selected the columns in use, and paste linked those.
However, where there are blank fields, the new file shows zeros. Is
there a way to link without showing zeros for blank cells?
Note - all cells are formatted as text, and when I select Ignore Blank
Cells in the Paste Special box, the Paste Link button is not
available.
Thanks in advance!
magmike- Hide quoted text -
- Show quoted text -

Could this be done automatically after the update occurs? On protected
sheets? In a protected workbook? Without the user having to do
anything?- Hide quoted text -

- Show quoted text -

The master file (Leads.xls) is the only one with protection. There are
five sheets in this file and they are all protected as is the
workbook. Those sheets are named for and linked from Tracy.xls,
Ed.xls, Keith.xls, Nicole.xls and Mike.xls. The five seperate files
are not protected in anyway.

In the individual files, there may be, for example, 200 rows and 3
columns. Any cells in the individual files not containing data,
display as a blank cell, as would be expected. When someone opens the
master file (Leads.xls) a box comes up that asks the user if they want
to update, and of course, they choose to update. At that point, I
assume it is reaching out to the individual files (each named for the
salesman) to get the latest data. However, any of the cells in the
three colums, including those in rows without any data at all, display
as a "0".

I hope that answers all of your questions.

magmike

 

[Gord Dibben]

When you open Leads.xls you get just the standard "this workbook contains links
to other data sources" then the choices to update or not.

Is that correct?

If so, Master.xls already has the links formulas waiting for updating with new
data from the other 5 workbooks.

I would suggest you open Leads.xls and run the macro on each of the 5 sheets to
add the Blank_Link_Trap to your links.

Make a copy of Leads.xls first to experiment with.


Gord

 

[magmike]

When you open Leads.xls you get just the standard  "this workbook contains links
to other data sources"  then the choices to update or not.

Is that correct?

If so, Master.xls already has the links formulas waiting for updating withnew
data from the other 5 workbooks.

I would suggest you open Leads.xls and run the macro on each of the 5 sheets to
add the Blank_Link_Trap to your links.

Make a copy of Leads.xls  first to experiment with.

Gord








- Show quoted text -

But can I have it do that automatically, or do I have to go to each
sheet and go the macro toolbar and select the macro to run? I'm hoping
for seemless use of this file as the other users aren't as computer
savvy.

 

[Gord Dibben]

I guess I just don't understand what's happening with your linked cells to other
books.

I am thinking that you would do this just once to add the 0 trap in Lead.xls
linked cells.

Then when you open Lead.xls it will ask to update and pick up the data from the
other workbooks.

That's the purpose of linking.............you don't have to re-build the link
formulas every time you open the workbook.

Your users do nothing but open Lead.xls and say yes to "update links".

Are you needing to re-build the link formulas each time and for why?


Gord