Linking worksheets

[Liz]

Hi,

I am working on a large workbook with many worksheets. The main worksheet
that i have is where i enter all my info on. The heading for the columns are
the months of the year. on the rows are the dollar amounts. The dollar
amounts I have them flowing into my summary sheets. Right now I am having to
change the summary formula every month because I need the new month to
reflect. I need to know if there is a formula ormacro that I can put in
that will change to current month I am working on automatically.

Any help you can give me I would appreciate.

Thanks

 

[Otto Moehrbach]

Liz
Your explanation is a bit hazy. You have a Main sheet into which you
enter data. That sheet has months across the heading row and dollar amounts
down the columns. That's clear. I take it that you want to enter a dollar
amount in some column and you want this value to be reflected in the summary
sheets. What summary sheets? Are they named the months? Do you want the
dollar amount to go to the same sheet who's name is in the header of the
column into which you entered the dollar amount (if the sheets are named
months)? Into what column and what row? If the summary sheets are not
related to the header of the column into which you entered the dollar
amount, how do you find the right summary sheet? HTH Otto

 

[Liz]

In the main sheet I have the dollar amounts broken down monthly in different
categories but the amounts I need for the summary are cumulative sales 07 and
Cumualtive Forecast sales 08.

In the summary sheet i have the headings as YTD Plan and YTD 07. right now
I am getting the figures over by putting = then going into the main sheet and
hitting the current month. I want for this to change automatically as I
enter the data (if possible) I hope you understand what I am trying to do.
Thanks

 

[Otto Moehrbach]

Liz
You have to understand that no one knows your business but you and the
people you work with. To get help with Excel, you have to use generic
terminology. For instance, you say:
"...I have the dollar amounts broken down monthly in different
categories...". This means something to you because you built the workbook
and you have it in front of you. Without that, it means very little.
Say things like:
I have 5 columns with headers in row one of this, this, this, this, and
this.
I don't know how you would explain "broken down .... in different
categories", but try.
You say:
"but the amounts I need for the summary are cumulative sales 07 and
Cumualtive Forecast sales 08." Where are these values? How can they be
found?
You say "putting = ". I take this to mean that you are typing an equal sign
in some cell. What cell? What is the significance of that cell? In what
column is that cell?
You say: "putting = then going into the main sheet and hitting the current
month." I understand the mechanics of building a formula, but I don't
understand what constitutes the "current month". You said that your data is
broken down "monthly" but you are clicking on one cell. Is this a summation
cell? Where is it?
I sincerely want to help you but I have to understand what you have, what
you want, and how you want to get there.
If you are reluctant to divulge your data, simply fake the data. I need
just the layout of your data. HTH Otto

 

[Liz]

Example:
Main Data Sheet
Column: 17
A B C d

Region Reps Jul Aug etc(all months
accross)
18 Allied
Cumulative 07 25,000 42,000
Forecast 08 28,000 50,000

Summary: Columns 4
A b c d
Region Reps YTD Plan YTD 07
18 Allied 28,000 (jul) 25,000 (jul)

I hope you can understand what I am trying to do now. As I enter the
information in the main data sheet for aug I want the summary sheet column c
and d to change automatically with the aug amounts I am putting in.

Thanks

 

[Otto Moehrbach]

Liz
I understand your example, I think. What you want can be done only with
VBA (programming). I wrote up a little macro to do just your example. It's
not going to help you much because, as I said, it works for only the one
example you provided. That macro is below. Basically, this macro will take
any numbers you enter in any column from C to N, in rows 3 and 4, and copy
them to cells C2 and D2 in the Summary sheet. That's all it does and it
does it automatically. Place this macro in the sheet module for the Main
Data sheet.
I'm sure that you have more regions than Region 18 in both sheets and I'm
sure that you want this little macro to do its thing with any region in
which you enter values in the months columns. But to write a macro to handle
all regions, you will have to provide the layout (that word again) of how
the regions are laid out in each of the sheets.
For instance, from your example I get that a region in the Main Data sheet
covers 3 rows and the row that holds the region number in Column A is the
first row and you enter data in the next 2 rows. Is that correct? Are
there any blank rows between regions?
In the Summary sheet I think you have 1 row for each region. Is that right?
Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Not Intersect(Target, Range("C3:N4")) Is Nothing Then
If Application.CountA(Range(Cells(3, Target.Column), Cells(4,
Target.Column))) = 2 Then
With Sheets("Summary")
.Range("C2").Value = Cells(4, Target.Column).Value
.Range("D2").Value = Cells(3, Target.Column).Value
End With
End If
End If
End Sub