Super Stuck! - Recall values from another sheet... huh!

  • Thread starter Thread starter Flinch56
  • Start date Start date
F

Flinch56

I am big time stuck and need some tips...

I have one sheet that has dates and corresponding dollar values paid o
that particular date.

I need to have a separate workbook, an income statement, collect th
sum of all of the dollars that had dates that say fit within the mont
of march. Of course, I need to be able to do this for each month for
years.

If anybody has any help, you would be preventing a guy with a full hea
of hair from going prematurely bald... thanks
 
Doddle for a Pivot Table if your data is set up correctly. Assuming it is in
database format with all your dates in Col A and your other data in say Cols
B:D, then in a helper column E do the following:-

Assuming your dates start in A2, in E1 put a heading 'By Month', and in E2 put
this formula and copy down as far as your data goes:-

=DATE(YEAR(A2),MONTH(A2),1)

Now, select all the data, do Data / PivotTable and PivotChart report, drag the
'By Month' field up into the page fields and drop whatever data you need to show
by month into the other fields until you have the report correct.

Now show the Pivot Table toolbar, select the 'Show pages' option and choose 'By
Month' although it should in fact be the only option anyway. Hit OK and it will
create a separate sheet for evry month in your date range automatically.

If you want a routine that will rename them all to the correct month then that
is easy too.

Sub ChngName()

On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
With ws
.Activate
.Name = Application.Text(.Range("B1"), "mmm-yy")
End With
Next ws

End Sub

A bit crude in that it will also rename any other sheets, but it will be easier
to find those and fix them, than rename all the others. Assumes that your date
field appears in B1 on each of the sheets.
 
Even bigger doddle if I read the bl00dy question properly:-

Just activate both workbooks and do Window / Arrange

In the workbook you want the summary to appear, Do Data / PivotTable and
PivotChart report, and then use the range selector to select all the data in
your source sheet. Now just follow the wizard.

Make sure you drag your date field to the far left, then right click on any of
the dates and select Group and Outline. Months should already be checked, so
also select Years and hit OK. Now just drag in your other data.

For a good intro to PivotTables

http://peltiertech.com/Excel/Pivots/pivotstart.htm

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



Ken Wright said:
Doddle for a Pivot Table if your data is set up correctly. Assuming it is in
database format with all your dates in Col A and your other data in say Cols
B:D, then in a helper column E do the following:-

Assuming your dates start in A2, in E1 put a heading 'By Month', and in E2 put
this formula and copy down as far as your data goes:-

=DATE(YEAR(A2),MONTH(A2),1)

Now, select all the data, do Data / PivotTable and PivotChart report, drag the
'By Month' field up into the page fields and drop whatever data you need to show
by month into the other fields until you have the report correct.

Now show the Pivot Table toolbar, select the 'Show pages' option and choose 'By
Month' although it should in fact be the only option anyway. Hit OK and it will
create a separate sheet for evry month in your date range automatically.

If you want a routine that will rename them all to the correct month then that
is easy too.

Sub ChngName()

On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
With ws
.Activate
.Name = Application.Text(.Range("B1"), "mmm-yy")
End With
Next ws

End Sub

A bit crude in that it will also rename any other sheets, but it will be easier
to find those and fix them, than rename all the others. Assumes that your date
field appears in B1 on each of the sheets.
Click to expand...
 
Thanks for all the help guys, I've been able to get the right values t
the right place, but is there a way to make the pivot table aut
update, particularly when my data is changed or added. If it has to d
with VA, please keep in mind I know nothing about it. Thanks
-Brando
 
You can use the Worksheet_Activate event to refresh the pivot table when
the sheet it's on is activated.

Right-click the pivot table's sheet tab, and select View Code
Paste the following code onto the worksheet module:

'==============================
Private Sub Worksheet_Activate()
ActiveSheet.PivotTables("PivotTable1") _
.PivotCache.Refresh
End Sub
'===============================

Choose File>Close and return to Microsoft Excel
 
Back
Top