• 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.
J

juzzvinay

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


I would appreciate help in a problem which I am facing - despite all efforts of searching multiple blogs/ forums (including my favorite, chandoo.org), I am unable to find a non-VBA solution.


Briefly, I work for a small company with offices in Mumbai (HO), Bangalore and Kochi. There is a common xlsx file for expenses with 3 sheets (Mumbai, Bangalore, Kochi) with user rights from each location to add their expenses to their office sheet. So far we have been manually copying all records from all three sheets to a seperate Master Sheet to tabulate the firm-wide expenses.


However, I want to insert a 4th sheet in the expenses document which automatically copies all data (without repeating the headers) onto the Master Sheet and ensures that we are able to monitor expenses real time as the expenses are recorded by every location. (Of course there are other applications for the same principle, i.e. incomes can also be arranged in the same way, multiple bank accounts can be consolidated in the same way, etc.)


Now, this implies that the data is dynamic and is constantly growing. Secondly, the sheets all share identical layout including the master sheet.


What I want is that the Master sheet should automatically link/ copy each data item from Mumbai (say 25 rows of data), Bangalore (say 60 rows of data) and Chennai (say 10 rows of data) and stack them one on top of the other - so Mumbai data from row 1-25 on Master sheet, Bangalore data from row 26-86 and Chennai data from row 87-97. And thereafter as more and more rows of data are added from the 3 locations, the Master sheet will incorporate all the entries (both old and new). This sheet can be used for further consolidation of financials for the company as well as deeper analysis using pivot tables.


Some of the steps I have taken are,

I have created Named Ranges for each location i.e. Mumbai, Bangalore, Chennai - Tried naming a 4th Named Range - Master which Refers to=Mumbai,Bangalore,Chennai but to no avail - whether multiple worksheets are the problem or non contiguous ranges are the problem I dont know.


VBA solution I have found is to create a temporary range which is a union of the three ranges but I would prefer to do it without VBA as multiple users will handle the document and are not as proficient/ comfortable.


Alternatively, a solution is to use multiple range/sheet inputs in Pivot tables - however, I am not able to get it to work as the page fields do not reflect all the column headings the way it reflects when using a single input/sheet range. So solutions from this angle would also be welcome.


Somehow I feel the solution is within reach but for some small errors/ lack of understanding by me.


Would appreciate if someone can guide me.


I did find a similar solution on this forum itself (http://chandoo.org/forums/topic/combine-two-dynamic-range-from-different-worksheet-into-a-third-worksheet) but it uses an additional column of formula on the Master sheet. I just think the combination of multiple dynamic named ranges would be a far more elegant and simple solution for others as well. Of course if it is not possible I would love to understand whether the issue is with the ranges being non-contiguous, being dynamic or their positioning on multiple worksheets or anything else.


Another similar issue - http://www.mrexcel.com/forum/excel-questions/23135-combining-named-ranges-into-one-master-range-validation.html


If a sample worksheet is required, I will be happy to put it up.


Thanks!
 
Hi,


As required, the sample workbook is attached below.

http://speedy.sh/F3Ffe/Named-Ranges-Sample-Extract.xlsx
 
Hi Vinay ,


A straightforward way of doing this would be to use a formula like this :


=IF(ROWS($A$2:A2)<=ROWS(M),INDEX(M,ROWS($A$2:A2),COLUMNS($A$2:A2)),IF(ROWS($A$2:A2)<=ROWS(M)+ROWS(B),INDEX(B,ROWS($A$2:A2)-ROWS(M),COLUMNS($A$2:A2)),IF(ROWS($A$2:A2)<=ROWS(M)+ROWS(B)+ROWS(Ch),INDEX(Ch,ROWS($A$2:A2)-ROWS(M)-ROWS(B),COLUMNS($A$2:A2)),"")))


This will insert data from named range M at the top ; next will be data from the named range B , and at the bottom will be data from the named range Ch.


Narayan
 
Narayan


You are seriously GOOOOD!! Here I have been struggling with this since a couple of days making absolutely no headway and you have a solution in a half hour. Thanks man!


Wow - though I am sure it will take me sometime before I fully understand the straightforward way.


Anyhow, it works but there is one issue - I need to manually insert, in the Master sheet, an equivalent number of rows to the new rows inserted in the three worksheets M,B and C. Any way out? I really shouldnt be complaining because this will still save tons of time and effort, but dil maange more!


Any reason why we cant create a Master named range which refers to the 3 existing named ranges?


Thanks again
 
Hi Vinay ,


I haven't understood you ; is this insertion of rows a one-time issue , or will it be required regularly ?


As I understand it , suppose you have the following rows in the three sheet tabs M , B and Ch :


M - 15 rows

B - 35 rows

Ch - 10 rows


On your Master tab , you have these same rows of data in this order viz. 15 rows of data from M , 35 rows of data from B and 10 rows of data from Ch.


Now if there is a fresh addition of data as follows :


M - 10 rows

B - 20 rows

Ch - 5 rows


This addition will be on the individual sheets , at the bottom of the existing data.


Why do want to add 35 new rows on the Master tab ?


Aren't you supposed to now have your consolidated data as follows :


M - 25 rows

B - 55 rows

Ch - 15 rows


Basically Excel formulae cannot do the work of operations such as inserting and deleting rows ; macros ( VBA code ) will be required.


So even if we can create a named range which is a consolidation of 3 non-contiguous ranges , it will still not allow you to insert new rows in a worksheet.


Narayan
 
Narayan,


Got it - so if it has to be truly automatic, then macros will be required.


As of now, the insertion of rows will be required regularly i.e. for every new row added in either M,B or C, a row needs to be added in the Master sheet.


Whether the addition of rows is at the bottom or from the top, unless I am doing something wrong, the number of rows in the Master sheet remains constant at the old number of rows i.e. 60 in your example. I checked - the named ranges are dynamic and accomodate the new entries. Only the master sheet doesnt.


If you dont mind

A) Is the named range as a consolidation of 3 non contiguous multi worksheet dynamic named ranges possible? Or is not possible at all? Even if we have to insert new rows in the Master worksheet each time, it is the same as your current solution, correct?


B) What would the easiest solution if macros can be used to automate row insertions?


Thanks
 
Hi Vinay ,


I am still not clear on why you should be inserting rows in the Master tab ; let me explain.


I assume that when you add fresh data to the individual sheet tabs M , B and Ch , you will be doing so at the bottom i.e. after the existing data.


On the Master tab , the insertion of the formulae is a one-time effort ; you can copy the formulae down to even 1000 rows ; if there is a total of 60 rows in the M , B and Ch tabs , then only 60 rows out of these 1000 will display data ; the rest will display blanks.


As and when you add fresh data to the individual tabs M , B and Ch , the data will appear on the Master tab as follows : all data from M ( old + new ) , followed by all data from B ( old + new ) , followed lastly by data from Ch ( old + new ). This will repeat whenever fresh data is added to the tabs M , B and Ch.


Thus , you will never be required to insert rows on the Master tab , provided you have copied the formulae down ; if not , all you have to do is copy the last row of formulae down to as many more rows as you wish.


All that is needed is to ensure that the named ranges M , B and Ch are dynamic , and expand to include fresh data.


Narayan
 
Ok. Got it. I was copying it only for exact requirement as the total/ subtotal rows come in at the bottom. Also, the entry was being done bottom-top i.e. newest data on top. Anyhow, that can be managed.


Named ranges are dynamic and include fresh data, so no issues there as well.


For my understanding,

A) Is the named range as a consolidation of 3 non contiguous multi worksheet dynamic named ranges possible? Or is not possible at all?


B) What would the easiest solution if macros can be used to automate row insertions?


C) Am I doing something wrong/ missing something with the multiple input ranges in Pivot Tables?


Thanks!
 
Hi Vinay ,


I really cannot say whether the answer to your first question is impossible ; let me check it out and I'll post back.


Regarding your second question , the easiest solution using macros would be to have a Worksheet_Change macro in each of the sheets M , B and Ch ; whenever a complete row of data is inserted , it can be transferred to the Master tab , and a new blank row can be inserted ; the problem is to manage the rows on the Master tab , since data from each tab has to be inserted in the right place , but I am sure it can be managed. Otherwise you can have a macro which runs once all data entry is completed , and transfers the entire set of data from all the 3 tabs ( M , B and Ch ) onto the Master tab , overwriting the earlier data on it.


In a formula-based solution , there is a problem in deleting rows , since the formulae on the Master tab will generate errors ; with a macro-based solution , this can be taken care of.


Regarding your point about pivot tables , I have not understood your question ; you are not really processing any data here , which is what pivot tables are good at ; all you need to do is just copy data as is ; again this is relevant only if we are able to get one single range from combining the 3.


Narayan
 
Vinay - are we talking about different workbooks here? Or is everything in the same workbook, but the file is shared across a network. If everything is in one workbook, then this question is similar to http://chandoo.org/forums/topic/creating-a-priority-table-from-multiple-tables


At that post, I suggested that we have a summary table that a macro clears any time the Summary tab is activated, and then the macro copies the info from the other tabs into the blank Summary table. Good thing about this is that you can then point a pivot at that summary table, and do business intelligence stuff.


If the files are in different workbooks, then you could use SQL to amalgamate the data to a pivot table. THere's an example of this at http://blog.contextures.com/archives/2010/08/30/macro-creates-excel-pivot-table-from-multiple-files/ that could likely be easily amended to suit your needs.
 
Narayan,


Sure - let me know when possible regarding the first question.


The macro solution sounds good - the first option seems quite efficient. Even if the new data is inserted in any order, I can always sort it (or even that can be automated).


Regarding pivot tables - one of the reasons why the multiple ranges need to be consolidated is so that we can analyse the same using pivot tables. Hence I tried to create the pivot tables using the inputs as they currently are i.e. in different worksheets. However, I wasnt able to get it to work. If the pivot tables can use multiple worksheet input then creating the consolidated range might not be required at all. Thats what I meant.


Thanks
 
Jeffrey,


Thanks so much for the input - the worksheets are all in the same workbook and hence the link shared is appropriate. Of course that is entirely macro based which I dont know much about and hence am still trying to understand it. Would you be able to suggest a VBA solution to my existing problem?


Also, if the files are in different workbooks, would the macro solution work then as well? Or would we require a separate solution for it?


Do let me know. Thanks
 
Vinay - how do you share the workbook across the different offices? Is it a shared workbook sitting on a network drive? Or do you have sharepoint server installed? Or something else?
 
Jeffrey, its a shared workbook using Google. Everyone edits their respective location data (i.e. on their sheet) at the end of the week.
 
Hi Narayan/ Jeffrey


Follow up to this query - how many sheets can be consolidated using the formula given by Narayan? Need to consolidate 12 sheets now and the formula is becoming too long and the file size is also shooting up.


Any suggestions? VBA solution is also fine.


Thanks
 
Hi, juzzvinay!

Consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you. Give a look at the green sticky posts at this forums main page for uploading guidelines.

Regards!
 
Hi SirJB7


I have already uploaded a sample file - http://speedy.sh/F3Ffe/Named-Ranges-Sample-Extract.xlsx - the solution suggested by Narayan works well. The only issue is that due to the high number of entries (~8000) across more than 10 worksheets is leading to a long, complicated formula and therefore perhaps a larger file size (250-500kb file is becoming 25MB) which is leading to slow performance and perhaps higher chances of file getting corrupted while saving, etc.


Hence, I am wondering if for the same issue, maybe a macro based solution would be simpler and easier to manage. But I am not at all well versed in macros and hence need some inputs and pointers.


I have tried to follow the forum guidelines. In case I am inadvertently not following any guidelines please accept my sincere apologies and advise any corrections required.
 
Hi, juzzvinay!

Oh, I now understand, I only read your previous post, not the whole topic.

Regards!
 
When you say "It's a shared workbook using Google" what exactly do you mean? i.e. is it still an Excel file capable of running VBA, but just shared using a Google sharing service?


I'd suggest again the approach I took at http://chandoo.org/forums/topic/creating-a-priority-table-from-multiple-tables


Why don't you download the book I posted there, take a look, and see if it will work in your situation. I suggest you store the downloaded sample book from that post in the same place where users are currently accessing the file, and see if it works for all of them before looking further into this approach.


At that post, I suggested that we have a summary table that a macro clears any time the Summary tab is activated, and then the macro copies the info from the other tabs into the blank Summary table. Good thing about this is that you can then point a pivot at that summary table, and do business intelligence stuff.
 
Hi Jeffrey,


Been a bit busy since a couple of weeks so couldn't get around to setting up the excel with VBA. Finally got it done today and it works beautifully.


I had a few minor queries though and would appreciate if you could take me through them.

1)I have set up Summary table and have to each time go to the VBA project and press F5 to refresh the Summary table. How should I automate it - with a button or something? How do I use the macro in the sheet provided by you?

2)Each time the sheet is refreshed, I have set it to sort using one column - managed that by trial and error till I figured it out. However, I dont understand how to sort using multiple columns. Also

.SortFields. _

Add Key:=Range("Summary[[#All],

what does the #All mean?

3)If the old sheet was 1000 rows, then on rows 1002 and 1003 I had some formula for analysing the data - sum, subtotal, etc. If the new summary sheet is 1020 rows, the formula rows get totally wiped out. How should I prevent that? Or is the only alternative to put the total and other formula on top of the header rows?

4)Secondly in some places, I would like the source formatting to also be copied to Summary sheet. In some places that is not required. What changes should I make to the VBA code for the same?

5)The summary sheet is around 5000 rows long and about 50 columns wide - so if I go through the summary and want to make changes, it becomes difficult to remember each change to be made in the source worksheets. Is there any way each cell on the summary table is linked to the source cell in the corresponding worksheets and I can just click on it and reach the source cell, make the changes and move on.

6)How can I automate the process of adding a row in the source worksheets with a "Insert Row" button? How should I code it?

7)Would it possible for you to go through the code and comment step by step so I can understand what each step does? Difference between .ListRows.Add.Range and .HeaderRowRange.Offset(1) in plain speak?

8)Finally, do you have any resource where I can read up and learn a bit more about VBA coding?


Sorry for the long query - but once I started writing the queries kept rolling in.


Thanks in advance Jeffrey.
 
Sorry forgot about

9)Do I always need to save the file as XLSM file or can I save it as XLSX and still use the VBA/ Macro?


VBA is completely new for me so pardon me if my questions are too simple.
 
Ok. I didnt think a sample file would be needed as most of the queries are conceptual in nature. Will upload a sample before and after file shortly.
 
Status
Not open for further replies.
Back
Top