Blank cell from another page within a worksheet?

[Scoober]

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]

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

 

[Max]

.. 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
---

 

[Teethless mama]

Assuming D5 either contain text or blank then try this:

=REPT('RP1'!D5,1)

 

[Scoober]

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?

 

[Scoober]

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?

 

[Max]

.. 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
---

 

[Scoober]

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]

The "Sub" is a macro ("subroutine"), its not short form for Substitute. You
need to copy n paste it into a regular module, then run it after selecting
the range of formulas. Something like this:

Steps in xl2003 (think it should be similar in xl2007)
In Excel,
Press Alt+F11 to go to VBE
Click Insert>Module
Copy n paste the sub* into the code window (whitespace on the right)
*everything between "Sub .... End Sub"

Press Alt+Q to get back to Excel
In your sheet, select the range of 200? formulas to be changed
Press Alt+F8 to surface the Macro dialog
Double click on "testme01" to run the sub
(Or, select: testme01, then click Run)

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

 

[Max]

Correction, this line:

*everything between "Sub .... End Sub"

should have read as:
*everything between "Option Explicit ... End Sub"
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---