Hi,

I am trying to find a way to leave a blank cell on my summary sheet that is
populated from another sheet within the worksheet.

i.e.
Summary page - A1 is populated from worksheet1 from D5

If D5 says 'Yes' then 'Yes' appears in A1 of my summary sheet

My question is how can I leave A1 blank if D5 is blank on worksheet1?

At the moment if D5 is Blank I get a '0' in A1 on my summary sheet which
does not visually very nice.

Thank you for your help in advance.

Opps i forgat to tell you that the forula in A1 is currently =RP1!D5

"Scoober" wrote:

Hi,

I am trying to find a way to leave a blank cell on my summary sheet that is
populated from another sheet within the worksheet.

i.e.
Summary page - A1 is populated from worksheet1 from D5

If D5 says 'Yes' then 'Yes' appears in A1 of my summary sheet

My question is how can I leave A1 blank if D5 is blank on worksheet1?

At the moment if D5 is Blank I get a '0' in A1 on my summary sheet which
does not visually very nice.

Thank you for your help in advance.

"Scoober" wrote:
.. the formula in A1 is currently =RP1!D5

One usual way: =IF(RP1!D5="","",RP1!D5)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---

Thanks that works perfectly.

Can I ask if it is possible to add this formula to a number of cells in one
go that are already populated with the original and incorrect formula, so I
don't have to go back and enter your formula to some 200 cells?

"Max" wrote:

"Scoober" wrote:
.. the formula in A1 is currently =RP1!D5

One usual way: =IF(RP1!D5="","",RP1!D5)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---

.. if it is possible to add this formula to a number of cells in one
go that are already populated with the original and incorrect formula,
so I don't have to go back and enter your formula to some 200 cells?

In a spare copy of your file,
try running the sub below on the selected formulas range

Option Explicit
Sub testme01()
'Amended from a Dave Peterson posting
'Sub will change cells with formulas such as: =RP1!D5
'into: =IF(RP1!D5="","",RP1!D5)
'Select the range with the formulas to change.
'You can include empty cells and cells with values,
'but don't include any cells with formulas that shouldn't be changed.
'Note: Save your file first.
'If sub doesn't work correctly, you can close without saving.

Dim myFormula As String
Dim NewFormula As String
Dim myCell As Range
Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "Please select a range with formulas!"
Exit Sub
End If

For Each myCell In myRng.Cells
With myCell
myFormula = Mid(.Formula, 2)
NewFormula = "=if(" & myFormula & "="""",""""," & myFormula & ")"
.Formula = NewFormula
End With
Next myCell

End Sub

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---

I have seen this done a long time ago. I have 2007 and cannot seem to find
sub which i think is short for substitute. Sorry to pull teeth but would you
know how to find sub on 2007?

"Max" wrote:

.. if it is possible to add this formula to a number of cells in one
go that are already populated with the original and incorrect formula,
so I don't have to go back and enter your formula to some 200 cells?

In a spare copy of your file,
try running the sub below on the selected formulas range

Option Explicit
Sub testme01()
'Amended from a Dave Peterson posting
'Sub will change cells with formulas such as: =RP1!D5
'into: =IF(RP1!D5="","",RP1!D5)
'Select the range with the formulas to change.
'You can include empty cells and cells with values,
'but don't include any cells with formulas that shouldn't be changed.
'Note: Save your file first.
'If sub doesn't work correctly, you can close without saving.

Dim myFormula As String
Dim NewFormula As String
Dim myCell As Range
Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "Please select a range with formulas!"
Exit Sub
End If

For Each myCell In myRng.Cells
With myCell
myFormula = Mid(.Formula, 2)
NewFormula = "=if(" & myFormula & "="""",""""," & myFormula & ")"
.Formula = NewFormula
End With
Next myCell

End Sub

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---

Assuming D5 either contain text or blank then try this:

=REPT('RP1'!D5,1)


"Scoober" wrote:

Opps i forgat to tell you that the forula in A1 is currently =RP1!D5

"Scoober" wrote:

Hi,

I am trying to find a way to leave a blank cell on my summary sheet that is
populated from another sheet within the worksheet.

i.e.
Summary page - A1 is populated from worksheet1 from D5

If D5 says 'Yes' then 'Yes' appears in A1 of my summary sheet

My question is how can I leave A1 blank if D5 is blank on worksheet1?

At the moment if D5 is Blank I get a '0' in A1 on my summary sheet which
does not visually very nice.

Thank you for your help in advance.

Yes that works! :)

Can I ask if it is possible to add this formula to a number of cells in one
go that are already populated with the original and incorrect formula, so I
don't have to go back and enter your formula to some 200 cells indiviually?

"Teethless mama" wrote:

Assuming D5 either contain text or blank then try this:

=REPT('RP1'!D5,1)


"Scoober" wrote:

Opps i forgat to tell you that the forula in A1 is currently =RP1!D5

"Scoober" wrote:

Hi,

I am trying to find a way to leave a blank cell on my summary sheet that is
populated from another sheet within the worksheet.

i.e.
Summary page - A1 is populated from worksheet1 from D5

If D5 says 'Yes' then 'Yes' appears in A1 of my summary sheet

My question is how can I leave A1 blank if D5 is blank on worksheet1?

At the moment if D5 is Blank I get a '0' in A1 on my summary sheet which
does not visually very nice.

Thank you for your help in advance.