• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Merge/Stack Multiple Named Ranges (Across Multiple Worksheets) in a Master Sheet

Status
Not open for further replies.
You're correct...they are conceptual in nature. But the problem is that it takes me more time to get my head around conceptual stuff than to delve in to something hands-on. So uploading a sample workbook means I get to think less about your questions, and spend less time solving them. :)
 
Hi Vinay ,


To address just one of your questions , you can learn about Excel's Table nomenclature from these links :


http://blogs.office.com/b/microsoft-excel/archive/2005/10/28/tables-part-3-using-formulas-with-tables.aspx


http://office.microsoft.com/en-in/excel-help/using-structured-references-with-excel-tables-HA010342999.aspx


http://www.databison.com/table-formulas-in-excel/


Narayan
 
Many thanks Narayan for the links.

Jeffrey, the sample file is uploaded at
http://speedy.sh/wSJrm/Named-Ranges-Sample-Extract.xlsm

Couple of additional queries
- The current VB code is for worksheets/ tables within the workbook - how do I link external workbook/worksheets/ tables in a similar code?
- I am also wondering if it is possible to have a list of available tables - from which the user can select one or multiple or all tables which should be consolidated. Something like a form field to select which tables should be consolidated - by default the option can be all tables. The consolidation/ summary table will be ultimately linked to a pivot table and since all worksheets/ tables have the same layouts, it will make life easy for all.

Let me know your thoughts.

Awaiting your reply.
 
Jeffrey,
In continuance to my above queries,
-How to select only part of a table for consolidation using VBA?
-How to insert "Auto Refresh Pivot Tables" command in VBA?
-How to link an external Worksheet (i.e. in another workbook, maybe closed) using VBA

I also have a cell (formatted as Number - cannot be non Number format) with 2 leading zeros, but when I refer to the cell using "&" and a fixed word - it does not copy using the 2 leading zeros. I have tried formatting the destination cell in various ways but doesnt work - simply refuses to acknowledge leading zeros. Any advise?

Really appreciate your advise - its invaluable.

If anyone else has any thoughts, please feel free to share them.

Thanks!
 
Bump.. Anyone there? Can someone please help me - I am stuck midway since 4 months..

Hoping for some help in the new year at least!
 
Sample File:
http://speedy.sh/EB7B9/Named-Ranges-Sample-Extract.xlsm

Recap of Queries
-The current VB code is for worksheets/ tables within the workbook - how do I link external workbook/worksheets/ tables in a similar code? Can I link an external closed excel file Table using VBA?
-How to select only part of a table for consolidation using VBA?
-How to insert "Auto Refresh Pivot Tables" command in VBA?
-I am also wondering if it is possible to have a list of available tables - from which the user can select one or multiple or all tables which should be consolidated. Something like a form field to select which tables should be consolidated - by default the option can be all tables. The consolidation/ summary table will be ultimately linked to a pivot table and since all worksheets/ tables have the same layouts, it will make life easy for all.
-I also have a cell (formatted as Number - cannot be non Number format) with 2 leading zeros, but when I refer to the cell using "&" and a fixed word - it does not copy using the 2 leading zeros. I have tried formatting the destination cell in various ways but doesnt work - simply refuses to acknowledge leading zeros. Any advise?

Some more queries - which are better served by being within the file - are in the sample file.

Cheers and best wishes for the new year!
Thank you.
 
Hi, juzzvinay!
After being absent from this thread by 4 months now you're hurried because you haven't received any answer in 2 days? Let the people who read this topic take their time, as you did before. Hopefully you won't have to wait 4 months...
Regards!
 
Hi, juzzvinay!

Give a look at this file:
https://dl.dropboxusercontent.com/u/60558749/Merge_Stack Multiple Named Ranges (Across Multiple Worksheets) in a Master Sheet (for juzzvinay at chandoo.org).xlsx

It uses 3 dynamic named ranges for each worksheet Hoja1/2/3:
Table1: =DESREF(Hoja1!$A$2;;;CONTARA(Hoja1!$A:$A)-1;CONTARA(Hoja1!$1:$1)) -----> in english: =OFFSET(Hoja1!$A$2,,,COUNTA(Hoja1!$A:$A)-1,COUNTA(Hoja1!$1:$1))
Table2/3: idem for Hoja2/3

In worksheet Hoja0, summary, this is the only formula:
A2: =SI(FILA()-1<=FILAS(Table1);INDICE(Table1;FILA()-1;COLUMNA());SI(FILA()-1<=FILAS(Table1)+FILAS(Table2);INDICE(Table2;FILA()-1-FILAS(Table1);COLUMNA());SI(FILA()-1<=FILAS(Table1)+FILAS(Table2)+FILAS(Table3);INDICE(Table3;FILA()-1-FILAS(Table1)-FILAS(Table2);COLUMNA());""))) -----> in english: =IF(ROW()-1<=ROWS(Table1),INDEX(Table1,ROW()-1,COLUMN()),IF(ROW()-1<=ROWS(Table1)+ROWS(Table2),INDEX(Table2,ROW()-1-ROWS(Table1),COLUMN()),IF(ROW()-1<=ROWS(Table1)+ROWS(Table2)+ROWS(Table3),INDEX(Table3,ROW()-1-ROWS(Table1)-ROWS(Table2),COLUMN()),"")))

Copy across thru column C and down as required, i.e., until the 1st blank gets displayed (in the example light orange background indicates up to which row the formulas have been copied).

Adapt this method to fit in your actual workbook.

Regards!
 
Thanks so much SirJB7! I will definitely try out you suggestion and let you know how it works. From what I understand this is not automated and I will have to manually add rows in the summary sheet each time new rows are added in the 3 dynamic tables.

I have already implemented a VBA solution to the issue of creating the summary (as suggested by jeffrey) and that works wonderfully. The only issue is that I am unable to customize the VBA code to accept external worksheets and other tweaks and minor issues to improve the usability of the entire workbook.

My apologies for seeming a bit rushed but the issue was pending since August - I would keep checking the thread and forums every few days for possible updates/ solutions but finally thought that in the new year I should bump up the issue. And honestly, I didnt know if anyone would respond or not since it was an old issue. Hence, I had to know if I should await a response or just leave it till I managed to find a solution. I fully appreciate the solutions offered by Jeffrey and Narayan but Jeffrey has recently expressed that he will not be able to help any further for a the next few weeks or so and hence needed to know if I should expect some help or not.
 
Hi, juzzvinay!

It's automated as long as you copy enough formula rows in summary worksheet (light orange shadow). This is a formula only solution, it can be modified with VBA code (macros) for adding/deleting formula rows as rows are added/deleted in child worksheets but you'd change to a .xlsm workbook with macros and if there're no huge changes of rows I think that the formula only is a preferable solution.

Regards!
 
Thanks SirJB7.. Your solution works but it is not suitable in my case as I frequently update the associated worksheets - around 200 a month. Also, since I have more than 8000 rows across worksheets and 40 odd columns the file size is unwieldy as well.

Jeff's solution works just fine - I am only having a problem getting it to read tables from external workbooks. Specifically I get an error when I try to modify this line
Set rngSource = [Mumbai].ListObject.DataBodyRange

to

Set rngSource = Workbooks(C:\Desktop\Test.xlsx).Worksheets(Mumbai).Range(Mumbai).ListObject.DataBodyRange

So it works if the Table is in the same workbook as the summary sheet but not otherwise. Thats where I need help - what is the correct nomenclature to refer to external workbook. Do I need to instruct the macro to open the workbook first? Or make it Active? Or what?

That is the primary issue and most critical to solve.

A smaller issue is how do I tell the macro to only copy a certain portion of the Table (Mumbai in this case)?

Secondly, the data copied currently does not refer to the source data i.e. if I click a cell which has been copied in the Summary sheet, it only shows me the end value. Is there any way it can display the formula used to calculate it? Or can the code be modified to make each cell a hyperlink to the source file?

Similarly, can the source formatting also be copied over with the data? How should the code be modified for that?

Thirdly, what changes need to be made to the code to sort using multiple columns?

Finally, in the source data, I want to put an "Insert" button - to insert rows of the same formatting as the rest of the table - with formulas and formatting (not just a blank row)

In an ideal world I would like to create a userform - when the macro is run, it will generate a popup showing the various tables which are coded by the macro to be copied in the summary sheet and will allow the user to select all or any number of Tables to be summarised in the summary sheet and then display only the tables thus selected. Maybe I am aiming a bit too high.

Please help me out with solutions to these queries. Would really appreciate it. I repeat, the first primary query is critical - the rest would just be helpful and more userfriendly.

Thanks.
 
Hi, juzzvinay!
You didn't forget any question, did you? :eek:
I didn't even read jeffreyweir posts, I just entered after your 2014 post, so I'm afraid I'll have to find some time to read all the thread history and then see if I could be of any help.
Regards!
 
@juzzvinay -
I have had similar data-gathering challenges across my organization. By far the simplest solution I have found has been using Ron de Bruin's free merge add-in here.

Check it out: in Ron's website there are also lots of other examples of similar processes. Let me know how you get on.

- juanito
 
Sorry SirJB7 - I tried to minimize and prioritize the questions but I guess once my curiosity itches, it needs to be scratched.:)

Juanito - Thanks for the suggestion. I have come across the RDB add in earlier and its from there I got the idea of a userform asking which worksheets/ tables should be selected for summary. The issues I had with it were (Please correct me if I am wrong)
1) The summary sheet is created afresh each time - which means it has to be reformatted as my report everytime I summarize. The summary sheet is used as a pivot table which means each time I have to reset all the parameters as well.
2) The sheets to be summarized are in different locations - not in just one location. So again each time I have to manually select all the worksheets and the ranges.

While the add in does the job of consolidating and merging the data beautifully it is good for a one time summary of multiple worksheets. For my usage, an auto updating solution would be more appropriate.

I somehow feel that the solution is very close using Jeffrey's VBA solution but its just not coming in hand. Someone who knows VBA structure well would be able to solve it - I am at my wits end.
 
@juzzvinay: yes, a fully automated approach will be ideal if you can make it work. In my experience and organization, up to now I've found workarounds so that I can use the RDBmerge with a minimum of fuss. YMMV.

- juanito
 
Sir JB7

Hope you have had the time to go through the post. Do let me know if you can help me out.
Looking forward to hearing from you.

Regards
 
SirJB7 - You have provided a solution that I have been looking for as well, thank you. I am new to the forum and I really appreciate the generosity and expertise of all you Excel Ninjas. Might I ask, could this "stacking method" be taken a step further? Is it possible to make the destination ranges automatically expand with the dynamic source ranges?

Suppose that the destination range is a single column and that each source table is a single column. Using your method, we have successfully stacked the three source tables in the single destination column. I would like to make that destination column part of a dynamic table that automatically expands each time a source table expands.

The purpose: Suppose that there are 19 columns of formulas based on each row of your destination column. Suppose that we have a source table for each month of the year and that these source tables can fluctuate between 0 and 500 rows. I must copy your formula and my additional 19 columns of formulas into all 6,000 rows. In my (admittedly limited) understanding, the sheet would have to check all 1,200,000 cells to at least return a "0" or a "". The goal is a dynamic table that would add one row of these 20 columns only after a row had been added to a source table. This would reduce the number of calculations and drastically improve the performance of the workbook (specifically the time it takes to open the workbook). Of course the table would grow as the year moved on and therefore calculation times would increase, but that is to be expected.

So the questions:
1. Is it possible?
2. Is it possible without VBA?
3. If so, how?

Thank you.

SirJB7 said:
Hi, juzzvinay!

Give a look at this file:
https://dl.dropboxusercontent.com/u/60558749/Merge_Stack Multiple Named Ranges (Across Multiple Worksheets) in a Master Sheet (for juzzvinay at chandoo.org).xlsx

It uses 3 dynamic named ranges for each worksheet Hoja1/2/3:
Table1: =DESREF(Hoja1!$A$2;;;CONTARA(Hoja1!$A:$A)-1;CONTARA(Hoja1!$1:$1)) -----> in english: =OFFSET(Hoja1!$A$2,,,COUNTA(Hoja1!$A:$A)-1,COUNTA(Hoja1!$1:$1))
Table2/3: idem for Hoja2/3

In worksheet Hoja0, summary, this is the only formula:
A2: =SI(FILA()-1<=FILAS(Table1);INDICE(Table1;FILA()-1;COLUMNA());SI(FILA()-1<=FILAS(Table1)+FILAS(Table2);INDICE(Table2;FILA()-1-FILAS(Table1);COLUMNA());SI(FILA()-1<=FILAS(Table1)+FILAS(Table2)+FILAS(Table3);INDICE(Table3;FILA()-1-FILAS(Table1)-FILAS(Table2);COLUMNA());""))) -----> in english: =IF(ROW()-1<=ROWS(Table1),INDEX(Table1,ROW()-1,COLUMN()),IF(ROW()-1<=ROWS(Table1)+ROWS(Table2),INDEX(Table2,ROW()-1-ROWS(Table1),COLUMN()),IF(ROW()-1<=ROWS(Table1)+ROWS(Table2)+ROWS(Table3),INDEX(Table3,ROW()-1-ROWS(Table1)-ROWS(Table2),COLUMN()),"")))

Copy across thru column C and down as required, i.e., until the 1st blank gets displayed (in the example light orange background indicates up to which row the formulas have been copied).

Adapt this method to fit in your actual workbook.

Regards!
Click to expand...
 
Last edited:
Hi, Cruiser!

This is a Jethro Tull topic, too old to remember (rock & roll) but still too young to forget (die). And I'm coming back from many weeks at the beach, so let me rock & roll a little and I'll get back to you.

Regards!

PS: But not today, it's CSI time at AXN :rolleyes:
 
Juzzvinay - I recently found this thread. I am sure you have figured this out by now, but I did not see a reply here. I can help with this question if you still need it.
"I also have a cell (formatted as Number - cannot be non Number format) with 2 leading zeros, but when I refer to the cell using "&" and a fixed word - it does not copy using the 2 leading zeros."
 
@juzzvinay
Hi!
Back again to this thread, I have forgotten about it. I'd be able to give a look at it after Tuesday.
Regards!

@Cruiser
Hi!
Same for you. But:
1) Yes, fill the 2^20 rows of summary worksheet with formulas. I wouldn't do it.
2) No.
3) N/A.
Regards!

In both cases turning to VBA would be the logical way. Is that Ok for both?
 
Thanks Sir JB7. Creating a macro free version right now in 2003, but a 2010 VBA version is my next step, so yes, I would definitly like to know how to accomplish it in VBA, if you would care to share.

SirJB7 said:
@juzzvinay
Hi!
Back again to this thread, I have forgotten about it. I'd be able to give a look at it after Tuesday.
Regards!

@Cruiser
Hi!
Same for you. But:
1) Yes, fill the 2^20 rows of summary worksheet with formulas. I wouldn't do it.
2) No.
3) N/A.
Regards!

In both cases turning to VBA would be the logical way. Is that Ok for both?
Click to expand...
 
Status
Not open for further replies.
Back
Top