Thread: Displaying following dynamic ranges to create an automatically updated spreadsheet ?

Hi everyone,

New to the forum, it is also the first time that I use excel for professional reasons (excel 2003). Here is my problem, help would be highly appreciated.

I am currently carrying on a mapping of all development projects in an country. For that purpose, I build a simple excel database with these projects and information about them (name, sectors, location, amounts, dates, etc..).

My problem comes from the fact that projects have to be filled up by financing organizations themselves. I can only check the data after them. Thus, I would like my database to have a dedicated sheet for every organization, in order to avoid that they change/delete/create data of other organizations. That why I created several identical sheets for every organization (say X/Y/Z/...)

But of course, the final need is to have a master compilation spreadsheet with all projects together (called "ALL Projects"). And I would like this spreadsheet to be filled up and updated automatically when changes are done and projects added by organizations in their sheet.

So I thought about displaying a succession of several dynamic named ranges (called "X_Projects", Y_Projects", etc...) into the master spreadsheet.

But this raises two questions:

- To create these dynamic ranges, I used INSERT-NAME-DEFINE and used the code OFFSET with COUNTA (detailed in the example database in the X sheet). Is this code really working for big ranges including numbers, text, but also dates and list selection?

- If yes, then how to display these defined dynamic ranges following each other in the master spreadsheet, for it to be automatically updated. Again, data to be displayed are the same for every project.

- If no, would you have any other suggestion for me? I looked for this for hours, and am completely stuck. Links to tutorials or other posts are welcomed too.

Please find enclosed a light example of my database, to better understand what I mean. example.xls

Thank you very much guys!
Best regards

Dear Richard,

Thank you for your time. This is for sure a first step.

Indeed, all the individual sheets will have always the same layout for every organization (i.e. columns in the same position). The aim of this is to force the organizations to share information with a compulsory common standard.

However, I can not know in advance the number of projects they have or plan, and it will change at every update, once a project finished or a new one planned. So, with your solution, I will have to check the number of rows and adapt my master table after each filling, giving me extra-work. That is what I wanted to avoid by referring to automatic updates, and why I thought about dynamic ranges, as I read that once "new items are added, the range will automatically expand"… Is it feasible?

Thank you again,
Best regards

Ok,

So I will opt for the macro solution, even if I haven't big experience on this. But everyone needs to begin one day. Do you know some good tutorials where I can learn codes for such tasks ? I have the whole weekend to look into.

By the way, what do you mean by "builds the summary sheet from scratch each time you run it". Does this means that the summary sheet will only be built (so updated) when the workbook is opened ? Creating an update button could be the solution, right ?

Thank you again Richard,
Best,

Dear Richard,

Thank you for your help.

I used the codes of the pipelines case.

So here is the code, adapted to my file and with some minor changes:
- Summary is not the first sheet
- All the sheets are not taking into account for the Summary, as I have plenty of sheets (summary, charts, mapping, codes...). Only those whose names end with "Proj." are considered.
- The name of the original sheet is not repeated into the summary sheet first colomn, the layout is exactly the same, so the macro is a bit lighter.
- Paste Special Values only instead of simple copy, in order to stick to the summary format.

Code:
[Option Explicit
Sub UpdateAllProjectsList()
Dim x As Long, llastrow As Long
Range("All_Projects").Offset(1, 0).ClearContents
Application.ScreenUpdating = False
For x = 1 To Sheets.Count
If Sheets(x).Name Like "*Proj." Then
If Sheets(x).Range("B5") <> "" Then
llastrow = Sheets(x).Range("B4").End(xlDown).Row
Sheets(x).Range("B5:Y" & llastrow).Copy
Sheets("ALL Projects").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
End If
Next x
Sheets("ALL Projects").Range("A1").Select
End Sub]

That's it, thank you again for your help Richard,
See u