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?
"Pete_UK" wrote:
> 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
>
>
> On Jul 6, 4:54 pm, Jim K. - SGB <JimK...@discussions.microsoft.com>
> wrote:
> > 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!
>
>
>
|
Similar Threads
