Excel 2007 Extracting data for charts from multiple worksheets?

[Mechta]

I'm doing a college materials assignment which requires the results of a number of tensile tests (typically 3 to 6 tests per chart) to be plotted in scatter charts (variable data in both X & Y axis). The data is in about twenty worksheets and the charts on separate worksheets within the same workbook.

I'm finding extracting the data to be longwinded and wonder if you can help with any of the following:

1. When selecting the data for a series it keeps jumping back to the worksheet with the chart on it rather than staying on the one from which I have to get the Series name, X axis data and Y axis data. What can I do to hold it on that worksheet until I have all three boxes filled?
2. If assigning a name to a column of data (to try and speed up data collection) is there some sort of wildcard to tell it to take data from the first reading to the last, given that the number of readings (and therefore the number of worksheet rows) varies from one test to the next? So far I can only find ways for taking data from columns of identical length.
3. Is there a place for writing strings which allows data to be collected from multiple worksheets?

Any other methods of quickly extracting data into charts would be much appreciated.

[bnt]

re 3, you could type the ranges in manually if you can learn the notation. For example, the range Sheet2!A1:D10 refers to the range A1:D10 on page Sheet2.

Also, re 2, all I can think of is to use a Macro, since there you can select a contiguous block based on a single cell using CurrentRegion e.g.

Code:

ActiveSheet.Range("a1").CurrentRegion.Select

Then create the named range from that selection, and a graph that uses the name should update automatically (in theory).
There's also a newer feature, Structured References, which looks like it could be useful - but I have no experience with it yet.

[Mechta]

Hi BNT,

Thank you for your reply. I haven't used Macros in Excel for about eight years, so I guess its time to re-learn them... A quick scan of the Structured References suggets that is what I need, I will have a go tomorrow.

[Spurlash2]

Mechta,

Info on Dynamic Named Ranges can be found HERE.

If you want to extract 20 sheets worth of data to one sheet (along the lines of a Dashboard presentation), you could highlight your data, right click/copy. Go to your main sheet, right click a suitable cell, and Paste Special/Paste Link from the contextual menu that springs up. This means that when you input a figure on a data sheet it will automatically be mirrored in your main sheet. So no tedium there to worry about.

Other EXCEllent sites (did you see what I did, there?) HERE, and HERE.

[Mechta]

Hi Spurlash2,

Thank you for those links. They have been very informative, although I have still to find a solution to no. 1 (jumping back to the chart worksheet when going to the next column of data).

I'm beginning to think that I am actually at the start of a very steep learning curve with Excel despite having used it off and on for years...