Why can't I just use the = sign in a function, and not use >=?

M

meghantrus

When I perform this function, after (2007,3,5) the cell output is
"0".
I need for the information that is displayed on (2007,3,5) to be
permanent and not
change to "0" when (2007,3,5) has passed.
=SUMIF(A1,"="&DATE(2007,3,5),Summary!F14)

I can't use this formula because I am displaying accumulating data
every week and it will be overwritten with the new data and not
preserve the old information.
=SUMIF(A1,">="&DATE(2007,3,5),Summary!F14)

Thanks for any suggestions you can give me!
 
D

Don Guillett

This will sum rng D if dates in rng A are as desired.
=SUMIF(rngaA,DATE(2007,3,5),rngdD)
 
M

Martin Fishlock

Hi:

You can't preserve the value on day this an accumulating total

there are a couple of work arounds but they depend on the data.

I would suggest using an autosave effect in VBA to pick up the value and
dropit into the specific cell as in:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Sheet1.Range("A1") = DateSerial(2007, 3, 5) Then
Worksheets("Sheet1").Range("B1") =
Worksheets("summarySheet1").Range("F14")
End If
End Sub


you need to modify this as the date is hard coded in the formula and the
cell answer is b1 on sheet1 for which I didn't know where you wanted it.
 
M

meghantrus

Hi:

You can't preserve the value on day this an accumulating total

there are a couple of work arounds but they depend on the data.

I would suggest using an autosave effect in VBA to pick up the value and
dropit into the specific cell as in:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Sheet1.Range("A1") = DateSerial(2007, 3, 5) Then
Worksheets("Sheet1").Range("B1") =
Worksheets("summarySheet1").Range("F14")
End If
End Sub

you need to modify this as the date is hard coded in the formula and the
cell answer is b1 on sheet1 for which I didn't know where you wanted it.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.







- Show quoted text -
Click to expand...



Thank you very much for your help!
I think this might work for what I want to do.
I do not have experience with vba code, so I am getting errors when I
try to save it.
This is what I have entered:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If SheetPlan.Range("A1") = DateSerial(2007, 3, 5) Then
Worksheets("Plan").Range("B43") =
Worksheets("Summary").Range ("E14")
End If
End Sub


I would like to do this for numerous dates and display the data in
different cells.
Do I have to create a new workbook for each instance of code for a
different date?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If SheetPlan.Range("A1") = DateSerial(2007, 3, 6) Then
Worksheets("Plan").Range("B43") =
Worksheets("Summary").Range ("F14")
End If
End Sub


Another question...
Can I grab the information from a cell in a pivot table and display
it?
Do you know the syntax?
Here is what I have...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If Sheet11.Range("A1") = DateSerial(2007, 3, 7) Then
Worksheets("Sheet11").Range("D43") =
Worksheets("summarySheet12").Range ("GETPIVOTDATA("mnemonic",Summary!$A
$2,"status","NOK"))
End If
End Sub

Thanks and Cheers!
 
M

Martin Fishlock

Hi:
SheetPlan should be worksheets("Plan")

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If SheetPlan.Range("A1") = DateSerial(2007, 3, 5) Then
' XXXXXXXX
Worksheets("Plan").Range("B43") =
Worksheets("Summary").Range ("E14")
End If
End Sub

for multiple dates try an if statement:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
dim dc as date ' hold the date from the worksheet
dim sCellAddress as string ' hold the address of the cell

dc=worksheets("Plan").Range("A1")

If dc= DateSerial(2007, 3, 6) Then
sCellAddress="B43"
elseif dc= DateSerial(2007, 3, 7) Then
sCellAddress="B44"
elseif dc= DateSerial(2007, 3, 8) Then
sCellAddress="B45"
elseif dc= DateSerial(2007, 4, 1) Then
sCellAddress="B46"
End If
Worksheets("Plan").Range(sCellAddress) =
Worksheets("Summary").Range ("F14")

End Sub

But I would rather use a lookup table in the workbook say on sheet called
'lookup' with vba where in your lookup table you have

date destination_cell destination_worksheet
=date(2007,3,6) B43 Plan
=date(2007,3,7) B44 Plan
=date(2007,3,8) B45 Plan
=date(2007,4,1) B46 Plan

you can then do:


Const csz_wsLookupName As String = "lookup"
Const csz_rLookup As String = "A:C"
Const ci_ColCell As Long = 2
Const ci_ColSheet As Long = 3
'Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
' Cancel As Boolean)

Private Sub Workbook_BeforeSave( _
ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim dc As Date ' hold the date from the worksheet
Dim sz_CellAddress As String ' hold the address of the cell
Dim sz_Sheet As String ' hold sheet name
Dim a ' answer
dc = Worksheets("Plan").Range("A1")
a = Application.VLookup(CLng(dc), _
Worksheets(csz_wsLookupName).Range(csz_rLookup), _
ci_ColCell, False)
If Not (IsError(a)) Then
sz_CellAddress = a
a = Application.WorksheetFunction.VLookup(CLng(dc), _
Worksheets(csz_wsLookupName).Range(csz_rLookup), _
ci_ColSheet, False)
sz_Sheet = a
Worksheets(sz_Sheet).Range(sz_CellAddress) = _
Worksheets("Summary").Range("F14")
End If

End Sub
 
M

meghantrus

Hi:
SheetPlan should be worksheets("Plan")

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If SheetPlan.Range("A1") = DateSerial(2007, 3, 5) Then
' XXXXXXXX
Worksheets("Plan").Range("B43") =
Worksheets("Summary").Range ("E14")
End If
End Sub

for multiple dates try an if statement:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
dim dc as date ' hold the date from the worksheet
dim sCellAddress as string ' hold the address of the cell

dc=worksheets("Plan").Range("A1")

If dc= DateSerial(2007, 3, 6) Then
sCellAddress="B43"
elseif dc= DateSerial(2007, 3, 7) Then
sCellAddress="B44"
elseif dc= DateSerial(2007, 3, 8) Then
sCellAddress="B45"
elseif dc= DateSerial(2007, 4, 1) Then
sCellAddress="B46"
End If
Worksheets("Plan").Range(sCellAddress) =
Worksheets("Summary").Range ("F14")

End Sub

But I would rather use a lookup table in the workbook say on sheet called
'lookup' with vba where in your lookup table you have

date destination_cell destination_worksheet
=date(2007,3,6) B43 Plan
=date(2007,3,7) B44 Plan
=date(2007,3,8) B45 Plan
=date(2007,4,1) B46 Plan

you can then do:

Const csz_wsLookupName As String = "lookup"
Const csz_rLookup As String = "A:C"
Const ci_ColCell As Long = 2
Const ci_ColSheet As Long = 3
'Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
' Cancel As Boolean)

Private Sub Workbook_BeforeSave( _
ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim dc As Date ' hold the date from the worksheet
Dim sz_CellAddress As String ' hold the address of the cell
Dim sz_Sheet As String ' hold sheet name
Dim a ' answer
dc = Worksheets("Plan").Range("A1")
a = Application.VLookup(CLng(dc), _
Worksheets(csz_wsLookupName).Range(csz_rLookup), _
ci_ColCell, False)
If Not (IsError(a)) Then
sz_CellAddress = a
a = Application.WorksheetFunction.VLookup(CLng(dc), _
Worksheets(csz_wsLookupName).Range(csz_rLookup), _
ci_ColSheet, False)
sz_Sheet = a
Worksheets(sz_Sheet).Range(sz_CellAddress) = _
Worksheets("Summary").Range("F14")
End If

End Sub

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.












- Show quoted text -
Click to expand...



Thank you Martin. The If Stmt worked great.
One more question...
How do I reference a cell that is part of a pivot table instead of
using this...Worksheets("Summary").Range("D14")
The issue is that the data may change positions, depending on the
amount of data, so referencing a value of a pivot table would be
better. This is my pivot table location that is currently the same as
D14...
=GETPIVOTDATA("mnemonic",Summary!$A$2,"status","NOK")

Below is what I have so far...

Many Thanks!

Meg



Sub Auto_Open()

Dim dc As Date ' hold the date from the worksheet
Dim sCellAddress As String ' hold the address of the cell


dc = Worksheets("Plan").Range("A1")

If dc = DateSerial(2007, 3, 5) Then
sCellAddress = "B42"
ElseIf dc = DateSerial(2007, 3, 6) Then
sCellAddress = "C42"
ElseIf dc = DateSerial(2007, 3, 7) Then
sCellAddress = "D42"
ElseIf dc = DateSerial(2007, 3, 8) Then
sCellAddress = "E42"
ElseIf dc = DateSerial(2007, 3, 9) Then
sCellAddress = "F42"
ElseIf dc = DateSerial(2007, 3, 10) Then
sCellAddress = "G42"
End If
Worksheets("Plan").Range(sCellAddress) =
Worksheets("Summary").Range("D14")

End Sub
 
M

Martin Fishlock

regarding the pivot table I think this will work:

=Worksheets("Summary").Range("A2" _
).PivotTable.GETPIVOTDATA( _
"mnemonic","status","NOK")

Well this one worked for me:

a = Worksheets(1).Range("A3" _
).PivotTable.GetPivotData("amt", "name", "a")
 
M

meghantrus

regarding the pivot table I think this will work:

=Worksheets("Summary").Range("A2" _
).PivotTable.GETPIVOTDATA( _
"mnemonic","status","NOK")

Well this one worked for me:

a = Worksheets(1).Range("A3" _
).PivotTable.GetPivotData("amt", "name", "a")



--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.












- Show quoted text -
Click to expand...

That worked! THANK YOU!!
 
M

meghantrus

That worked! THANK YOU!!- Hide quoted text -

- Show quoted text -
Click to expand...


I have another question for you...

If the date in cell A1 does not equal one of the dates specified in
the code, a run time error occurs.
Currently, I have A1 =TODAY(). Can I prevent this somehow in the code
to ignore the date in A1 if
the current date is not specified in the code? My macro is called
Auto_Open in order to run every time it is opened.
Thanks!

If dc = DateSerial(2007, 2, 9) Then
sCellAddress = "B54"
ElseIf dc = DateSerial(2007, 2, 16) Then
sCellAddress = "C54"
ElseIf dc = DateSerial(2007, 2, 23) Then
sCellAddress = "D54"
ElseIf dc = DateSerial(2007, 3, 2) Then
sCellAddress = "E54"
ElseIf dc = DateSerial(2007, 3, 9) Then
sCellAddress = "F54"
ElseIf dc = DateSerial(2007, 3, 16) Then
sCellAddress = "G54"
End If
Worksheets("Plan").Range(sCellAddress) =
Worksheets("Summary").Range("A2").PivotTable.GetPivotData("mnemonic",
"status", "Postponed")
 
Top