copy graph from a Worksheet based on a field in another Worksheet

[Jim K. - SGB]

Here goes! Hope this makes since and I'll try to be as basic as possible.
Excel 2003
I have a Workbook with the following worksheets:
Form-Print
Work-Sheet
January
February
March
(Etc. a sheet for each month)

1-The 'Work-Sheet' tab has user data that is input monthly for the prior
month and several calculated columns based on that input.
2-All the 'Month' tabs (12 in all) have formulas to extract the data for
only that month from the 'Work-Sheet' tab, put it into columns based on the
month and year, and has a graph of this data for all the years. All this is
done automatically in that 'Months' tab with no user input within that tab.
3-The 'Form-Print' tab is strictly an easy to read document based on a given
"month-year" field that is input by the user. This is given to top
management at the end of each month showing that particular months info as
well as its history from prior years including the graph for that month.

I have gotten all the info in the text portion of the 'Form-Print' tab
for the month with no problems. All this info comes from only the
'Work-Sheet' tab. My problem is the fact that I need to also get the graph
from the correct 'Month' tab for the data as well as automatically size it ti
a specific size. I have found nothing to allow me to get the graph from the
matching 'Month' tab based on the "month-year" input by the user on the
'Fornm-Print' tab.

I prefer to get this done strictly with formulas and/or copy functions.
I have no experience with VB what-so-ever and would have to be baby-stepped
through it for VB to work.

If you have an easy way for me to get this done I'd be forever greatful!

 

[Pete_UK]

You said that your Month tabs get the data from the Work-Sheet tab
according to the month, so why can't you have similar formulae in the
Form-Print sheet which get the data from the Work-Sheet tab according
to the Month which is specified in a cell in the sheet Form-Print? You
put a different month in the Form-Print sheet, and the data
automatically adjusts.

Then the graph can be derived from the data in that sheet, and can be
sized etc to suit.

Give us some examples of the formulae you have now and we might be
able to suggest how they would need to be altered.

Hope this helps.

Pete

 

[Jim K. - SGB]

Pete,
Here is the formula I use to get specific data from the 'Work-Sheet' tab.

=INDEX('Work-Sheet'!M3:M404,MATCH(I1,'Work-Sheet'!AB3:AB404,0))

The Index function allows me to use the same formula in all areas I need and
I only have to change 1 set of criteria (the cell range M3:M404). The lookup
fields remain the same.

The problem I have is the fact that the graph contains much more
information than what exists on the 'Form-Print' tab. It also contains all
prior year data that is automatically populated within the 'Month' tab for
that month. This graph already exists and is automatically updated as new
records are added to the 'Work-sheet' tab.

If necessary, I can copy this spreadsheet to a new name, delete any
proprietary data info, input a couple years bogus info, and then send or
attach the workbook if that is possible. Or, if there is a way, to put a
screen shot of some of it here?