load data into excel from other sheets

[guthrie]

I have a data source (Sakai) that can export lists of events, as a simple spreadsheet with a few columns, for example (named, ID, event, count, date). The exported data files will have variable number of events depending on activity levels.

Then I build a master spreadsheet where I paste these (three) different data reporting lists into tabs which have named ranges over the data, and then have a few summary tabs (sheets?) which then use these named ranges to produce some nice reports & graphs.

So each time we export new data, we need to open the reporting sheet, make sure the named ranges are big enough for the new list sizes, and then copy & paste the data from the data sheets into the reporting sheet (in the right place, which is into a named range).

Of course what I'd like to do is automate this somehow; to be able to have the reporting sheet import or refer to the data sheets, without needing to edit them. But since the sheets I use for the reports nave named ranges (for example, on one data sheet: visitData, name, date, count, event), I cannot just refer to the sub-sheets with data directly.

I don't want to write VBA code, but if there is some that will do it fairly simply I could try to adapt it.

Thanks for any hints or advice.

[Pete_UK]

You can turn your named ranges into dynamic named ranges, which means they will automatically adjust themselves to suit the amount of data that you have (assuming it is contiguous data). So, for example, assume you have a named range with a Refers to set to:

='Sheet 1'!$A$2:$A$100

Then you can edit this to read:

=INDIRECT("'Sheet 1'!$A$2:$A$"&COUNTA('Sheet 1'!$A:$A))

assuming you have a header in A1. This named range will then automatically adjust to suit the amount of data that you have in column A. Then, refering to your 2nd paragraph above,

... I paste these (three) different data reporting lists into tabs which have named ranges over the data, ...

and your 3rd paragraph would become:

... we need to open the reporting sheet, and then copy & paste the data from the data sheets into the reporting sheet ...

although I'm not sure why you need to copy/paste data into this reporting sheet - is it not part of the workbook which contains the data sheets?

Hope this helps.

Pete