I have an Excel worksheet that works as template for a questionnaire. My aim was simply to copy the worksheet into a new one while preserving the formatting and other stuff, and I could then just fill it out.

For example, say I gather some customer polls each year so that I have worksheets 2010, 2011, 2012 and so on.

My possible dilemma comes when I want to gather all that data together into one sheet and create some graphs out of them. Say I have gathered the data from years 2010, 2011 and 2012 and made some fancy graph out of them to show how customer opinion has changed over the years. Now, what if I add another worksheet for the year 2013?

Is there a simple way to automatically include this new data into the graphs dynamically, so that it gets updated without need for my input?`

Bear in mind that VBA and macros are out of the question as this Excel sheet is supposed to work on Mac 2008 version.

  • 1
    From the requirements you have outlined there is likely no way to help you. Excel is not able to intuitively know what to do with data when you add more sheets unless VBA or macros are used. In order for the chart to display the data, it needs to know where the data is, thus requiring input from you. – CharlieRB Jun 3 '13 at 14:16
  • 1
    CharlieRB is correct, however you could make some empty sheets for the next 10 years or so and include empty references for the information needed from them sheets and then simply set the graph to only show data that exists. – Kevin Anthony Oppegaard Rose Jun 3 '13 at 14:42

You may be able to use something what Excel calls a 3-D reference. However this very much depends on the type of data and functions you use. Create a 3-D reference to the same cell range on multiple worksheets

You are limited to functions like sum and count. If you have tabs like 2010, 2011, and 2012. Simply create a sheet infront of and behind those so that your formula will look something like =SUM(StartDate:EndDate!B2)

Otherwise you could define your sheets as named ranges and then consolidate those ranges into a Pivot Table. Consolidate multiple worksheets into one PivotTable report

If you could share some sample data and what you would like it to look like I can help further.

I Hope this helps.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.