Auto expand table based on amount of rows of data provided

**AMoreno** · 10-19-2018, 12:53 PM

Hello,

Asking for anyone who can offer any suggestions in being able to auto expand a table based on data added to cells next to it , if possible.

In our example. we are bringing over data from a report by using power query that is mashing together all of our data and placing the data based on the call center and exporting the new table created into another workbook to its corresponding call center sheet name. This information is then being used as a lookup to other files in a table directly next to it and contains formulas. So the question is, once the new data is queried in and dumped into the sheet... how can I have our other table with formulas to expand its rows based on the number of rows that is in the table that was queried in from power query.

If there are any links or examples to use, it would be appreciated.

Maybe a helper column but I have looked around for awhile now.

I have attached a sample workbook with a basic mock up of how my data is being used. (There are 90 centers so there will be 90 sheets with the same layout)

Thanks,
Ant

**Tsjallie** · 10-19-2018, 02:25 PM

This simple macro resizes the table to the number of rows of the source table.

Please Login or Register  to view this content.

**AMoreno** · 10-19-2018, 03:32 PM

Thanks for the attachment. It seems to be work so far so ill keep checking.

Just a few questions how to work this: @Tsjallie

How would I be able to do this for all my other tabs. Would this be correct? Rename all tables and source tables on each sheet... and then copy and paste the vba code above (however many times needed including the =nothing 's) and just have to change sheet name for the new objects and change the tables names that are being used on that sheet? Correct?

Or do I have to redefine each object for each new sheet of tables I am looking at?

Thanks,
Ant

**Tsjallie** · 10-19-2018, 04:58 PM

Depending on how your workbook is organized you could use the Workbook_SheetChange event which fires whenever a the content of a cell in any sheet changes.
This event gets the sheet as parameter, but you'll need to be able to determine the names of the source and target tables.
Can you upload the workbook?

**AMoreno** · 10-22-2018, 12:47 PM

@Tsjallie

The structure of our workbook is very similar to the one already given. I have mocked up a new file with the power query load in and the table next to it that would contain formulas and copied it over to a couple of other sheets to be used. (Test1, Test2, Test3, Test4) All the tables have been uniquely identified. PQ tables are the source of info and the F tables are the formula tables that need adjusted as there are new employees.

Wondering if this could information could be used with your Workbook_SheetChange event and how to use it for the remaining tabs?

Or if anyone has any other suggestions.

The file loaded contains the macro that Tsjallie has created shown above.

AFTER testing it with the power query table, the macro provided by Tsjallie does not recognize the power query tables as a source. Is there another way to call/look at that table from power query? I am assuming the power query table is not able to be used as a list object.

Thanks,
Ant

**Tsjallie** · 10-23-2018, 08:34 AM

Hi Ant,
Ideally there would be one workbook containing the full Power Query table which you can distribute to the call centers.
Then each call center table would pickup the data relevant for that call center.
The source file(s) for the Power Query would be in some central location.

Thinking about such a setup setup brings up some questions:
How big (rows and columns) is the source file for the Power Query?
Does each call center use it's own workbook or do they have their own sheet within one workbook?
Do the call centers all use their own portion of the Power Query table? If yes, what are the filter criteria?
Are the call centers in a networked environment?

**AMoreno** · 10-23-2018, 11:53 AM

Awesome @Tsjallie, thanks for getting back.

Okay yes that seems to make sense. Almost similar to the file already given but instead you are saying to have queries in another workbook and each sheet with two tables (which one references from the query source file in the other book and the other has formulas that references the new first table).

Ill explain the use of our workbooks so far.

Original source files 5000, 5001, 5002, 5004. each call center has its own sheet in each file. Each source file is a different account number. Salary, OT, etc.

Power query- grabs the 6 uniques columns of each call center sheet in each of the original source file and eliminates duplicates based on criteria needed for matches of salary. So each sheet has its own query as of now.

Salary report (main workbook)- power query for each sheet is dumped into its corresponding sheet. each sheet also has a formula table. this table columns include each source file account and looks for the matches for each account and month and must match based on the unique values from the query table from power query and then pulls data values of salary from each source file that matches the power query unique values.

All the unique values are used for each call center. The only ones references for the formula table are employee id, title, and month. each column in the formula table has a different formula that matches each source file based on the column and matches the month from the source file to pull salary values, looks like below

7/18 7/18 7/18 7/18 8/18 8/18 8/18 8/18
5000 5001 5002 5004 5000 5001 5002 5004

Your questions.

1. The data pulled from the power query includes the same 6 columns each month, the rows will grow month to month as more employees worked in the call center. The reason is because the source file grows after the unique columns as each month a new column is added for the new months salary data is entered.

2. No all the call centers have their own sheet within a workbook. As of now each sheet has its own query.

3. Yes each sheet uses its own query. Its based on (Employee Name,FT/PT,Title,Employee ID,Hr. Rate,Alloc.) 6 values that are unique to each center

4. Im not for sure what you mean but I do not think so.

So based on that idea for now we could set it up and have the power query run on another workbook and save that. And then have the file given to you, and make turn each query table into a regular table that references back to the new power query file. (Each sheet would have its own query.) So that the table next to it will have the formulas that look at the new table next to it for references back to the source values. My question, how would we get the new first table to grow based on the query table on the other workbook?

Maybe there is a process we skipped or had missed that would be easier.

Also if a mock up file with real values would be better please let me know and ill make another one. I figured we are more worried about the function of not having to drag down formulas on 94 different sheets and how to use the power query information correctly. The formulas are working and are pulling information correctly, we are trying to figure out how to adjust the tables as the year goes by and each month is added.

Thanks,
Ant

**AMoreno** · 10-23-2018, 01:45 PM

Here is a workbook with the same fields as we are using that we need help on.

-Left table, reference to query in another workbook from power query.

-Right table, looks for matches on left table to pull from the 4 source files for each account. The header column is hidden on this table as each column cannot have the same titles.

Each sheet is its own call center. The table on the left is the information that needs to be referenced from the power query and updated monthly to include updated names and other unique values. The table on the right (as of now only has 3 months but would include all 12 months) includes formulas that we would use the table on the left table for matches to reference to the 4 source files I mentioned above for the salary values for each account. This table needs rows adjusted as new employees will drop in the table on the left. As noted the columns on the left and right tables will stay the same.

So both tables will possibly need continuous updates only on the number of rows month to month. Figure some type of count would be needed but not for sure how to go about it.

ps. this file does not include the previous macro..sorry

Thanks,
Ant

**Tsjallie** · 10-23-2018, 03:05 PM

Also if a mock up file with real values would be better please let me know and ill make another one.

Yes, I would appreciate a file with real data (but anonymized and also with the formulas in the F-tables.
If you can upload such a file that would help. Also a copy of the report (also anonymized) you're using with Power Query will help. A portion of abt. 100 rows will do.

**AMoreno** · 10-24-2018, 04:49 PM

@Tsjallie

Okay sorry for the hold up. It took awhile to get some things cleaned and mocked again. We liked your idea, so I tried to set it up as it would hopefully be easier for your work.

And time purpose, we only have 3 months worth of formulas in the f tables, they would need to go out for 12 months in real time but well adjust later.

As known each center has its own query, and each center does not have to have all 4 accounts because if no one in that center has OT then nothing will be there. So what happens is the query goes through all files and the merges sheets that are included in the call center (we will have to merge more sheets into the query if a specific call center later has OT or another account). If any call center had multiple accounts once merged then they would be appended and remove the duplicates (this give us the power query source). This will make more sense once you see the files. remember each center does not have all 4 accounts.

There will be 5 workbooks attached!!!

Files;
-5000
-5001
-5002

All salary information- 3 of the files. salary, ot, etc. The months values are what is used to match and bring in the values to F tables (formulas)
The 5004 column/file only has one cost center so it was NOT included. and the adj column is another file that we have managers enter on another file so that was also NOT included.

Files;
-pq_source

This is where the query grabs the unique values from the 500X's files. Merges files if call centers appear in other accounts, if so, then appends and removes duplicates. These are the unique values that the F tables would use for matching the unique values in each of the 500X' files. We would want to reference this file back to adjusting tables files so we can use your macro.

Files;
adjusting_tables

This file is what we would use and something you suggested that may work.
The table on the left is the information we want the power query to go to with the six unique columns. The table on the right, are the formulas being used.

As mentioned; the rows change by cost center some having more than others. However, if power query information can be somehow referenced into the table on the left of the adjusting table then hopefully the other table may adjust as well with the macros you mentioned.

If you have any other questions, please let me know.
Thanks in advance and for taking the time out of your day with this.

Thanks,
Ant

**Tsjallie** · 10-26-2018, 04:21 PM

Hi Ant,
I'm uploading the adjusting_tables.xlsx.

Added a sheet with 2 parameters. These point to file flocations. Change these to your needs as they're now set to my locations.

I only made changes to the sheet 006020-000-Administration.
In that sheet I entered a table that integrates the PQ-query and the F-table.
This solves yr initial problem of keeping the number of rows in sync. So no need for that macro.
Trade-off of using PQ is that - though you can have the location of the pq_source file flexible by using a parameter - you cannot
use a parameter to point at the right sheet in the pq_source file. I think that should be done with a named range of which the scope is the worksheet.
But I haven't found a way yet to make PQ use such a local named range. PQ appears to only understand global named ranges

That means - if we don't find a workaround for that - you'll have to make query for all the sheets. It's not difficult, but just very labourious.

To make your life a little bit easier (

) I added some suggestions to improve the formulas (make 'm more flexible).

Hope it's somewhere in the right direction.

**AMoreno** · 10-26-2018, 04:42 PM

Okay, thanks Tsjallie!

I will have to take a better look at this next week when my other co-worker comes in and we will talk about this as a possible solution.
And yes, we have already made query specific to each sheet and that did take awhile to get all together.

So with that example, our initial idea would work, but how were you able to pull the power query into the actual table without the headers? Like how were you able to integrate the query and table to say set? That part would almost be our solution, as in our file we had to hide the header column in the F tables so they would align.

Also thanks with the formula help. I have only used indirect and named arranges in practice and not in real time, so I will also have to look more into on how those are worked.

You will hear from me soon! Once again, I will look at this more next week and thanks for your effort.

Thanks,
Ant

**Tsjallie** · 10-26-2018, 04:56 PM

Initially I made the Querytable thru PQ.
Then I extended it to host the columns of the f-formula.
After that I hid the table headers and the row they were in.
Finally I added new column headers (not part of tthe table).
Actually I did the same as you did with the original f-table.

**AMoreno** · 11-02-2018, 12:15 PM

@Tsjallie

Thank you. We are meeting today to begin as we have decided to go with this process.

However, I do have a few questions about setting up the parameters and the meaning of the named arranges.

1. For the PQsourcePath and GLfilesPath, is just using the file path up to the folder where this file is contained correct? With four accounts, I am assuming ill have to create another parameter for each one.

2. I understand the first two arranges. But could you explain the GLsourceFile arrange? Because it is referenced for our formulas. I see that it is replacing it with the centers number to find a file match but could you break down the formula of what each part is doing?

="'" & SUBSTITUTE(GLfilesPath & "\","\\","\") & "[" & '006020-000-Administration'!G$10 & ".xlsx]"

With that, Im assuming I would need 4 different GLfilesPath, one for each account? Then needing a GLsourceFile for each of those?

With the formula. Would the initial cell reference be in the first row and first column of our formulas? and also the back slash substitutions?

The GLsourceFile is what is giving me the hard time from understanding what all is being done, besides that I believe I understand what the parameters and formulas are trying to do.

Thanks,
Ant

**Tsjallie** · 11-02-2018, 04:19 PM

1. For the PQsourcePath and GLfilesPath, is just using the file path up to the folder where this file is contained correct?
With four accounts, I am assuming ill have to create another parameter for each one.

PQsourcePath is currently not used. I intended to use that to get PowerQuery open the right file from the right location and then read the right sheet.
Unfortunately PQ isn't that smart.
You only need one GLfilesPath which points to the folder containing the GL-files assuming all these files are in that folder.

2. I understand the first two arranges. But could you explain the GLsourceFile arrange? Because it is referenced for our formulas.
I see that it is replacing it with the centers number to find a file match but could you break down the formula of what each part is doing?
="'" & SUBSTITUTE(GLfilesPath & "\","\\","\") & "[" & '006020-000-Administration'!G$10 & ".xlsx]"
With that, Im assuming I would need 4 different GLfilesPath, one for each account? Then needing a GLsourceFile for each of those?
With the formula. Would the initial cell reference be in the first row and first column of our formulas? and also the back slash substitutions?
The GLsourceFile is what is giving me the hard time from understanding what all is being done, besides that I believe I understand what
the parameters and formulas are trying to do.

The GLsourceFilePath can be with or without a backslash at the end. If you would enter the path with a backslash then the Substitute-function removes the second one (added by GLfilesPath & "\").
The part '006020-000-Administration' is Excel confusing you. Actually GLsourceFile is looking at cell B1 of the sheet it's used in.
Just copy the formula of cell G11 in sheet 006020-000-Administration and copy it to cell G5 in the sheet 006030-000-Administration.
If you then evaluate the formula you will see that it looks at the sheet 006030-000-Admninistration. Including the typo

**AMoreno** · 11-09-2018, 01:50 PM

@Tsjallie Okay that makes sense. Thanks for going into the explanation.

So we have been able to get the right sources needed for the power query to update the rows correctly into the table. However, the formulas are not returning any of the values that were matched. It is only returning a zero as in accounting format with "-" when I remove the quotations from my iferror part. I have not been able to figure this out. Wondering if you could take a look into it. I did not know if it had something to do with the indirect or something else. The GlfilesPath has been updated to the 5000 account .xlsx file and only for that column.

So for the 5001.xlsx file would have its own path for the 5001 column

Thanks,
Ant

**Tsjallie** · 11-11-2018, 03:41 PM

Hi Ant, I'll take a look at it.
In the meantime can you upload the current workbook, so I can see any changes you made.

**AMoreno** · 11-15-2018, 02:08 PM

@Tsjallie

Hello,

Okay so I only made a few changes that I would hope that would work.

*All files used were the ones originally attached all together, all kept in one folder.

-So in the parameters I updated the GlfilePath with the 5000 file. (Have not made other path for other accounts yet)
-In the power query- advanced editor- I adjusted the source to the pq_source file to update the names.

Once completed no numbers were being pulled over.

So in pq_source file I had added an extra row (which is why there is an extra line... testing it) to the cost center working on and ran the query to make sure it was working properly, which it did and brought over the name and expanded the table as needed. However, the formulas used are not returning any value but a zero.

Thanks,
Ant

**Tsjallie** · 11-22-2018, 04:48 PM

Hi Ant,
to keep you posted I'm uploading a file showing the direction I would go.
It's grossly the same as the earlier file, but in this version the GL Files (5000, 5001 and 5002) are merged into one table AllAccounts.
This makes the lookup formulas easier and more flexible as they no longer need the INDIRECT-constructs to look at the right table.

However manually creating a workbook with 94 sheets like this will be awfully labourious and error prone.
Also any changes or additional cost centers will need manual labour.
I think the workbook is going to be usefull, but it should be created by a (set of) macros.
Are you familiar with macros (vba)?

**AMoreno** · 11-26-2018, 01:53 PM

Hello Tsjallie,

Thanks so much for your work and the detail you put into it. Really makes it easy to understand.
This seems to make sense, I will go over this with my co-worker to see when and how we can get working on this.

To answer your question: Beginner Level, I know how to use macros and have created several that I use often but those were done with the help of the recorder and piecing different parts together from the internet. But all was done on my own, so with some guidance I am able to understand. I have never taken any classes on vba so writing from scratch is not possible at this time.

What suggestions do you have where all macros might be needed? Several macros for how many possible steps would you think?

Thanks,
Ant

**Tsjallie** · 11-30-2018, 06:43 AM

Hi Ant,
created a workbook with a procedure to create the CostCenter Reporting Workbook from scratch.
For me a good opportunity to dig more into power query

and should save you a lot of work.
Have a look at it and just run the procedure.
If - like you say - you have done some basic things with vba then it shouldn't be too hard to follow with the procedure is doing.
More detailed info is on the ProcCenter sheet.
Don't forget the change the folder paths to your needs.

**AMoreno** · 12-04-2018, 11:29 AM

@Tsjallie

Thanks so much. I will be reviewing the workbook during this week. Also glad you were able to learn some new tricks. I have also learned a lot from just working on this and how I can use some things we did in the past with other projects, so thanks again. I will get back to you to let you know how it all comes out or if we have any questions.

Thanks,
Ant

...started to look through the file, THANK YOU so much for your time and effort you put into this (I really cannot say it enough). Will get back to you later this week!!!

**AMoreno** · 12-06-2018, 05:21 PM

@Tsjallie

I have a few question about how this file is ran. So far, I have been able to follow what has been done and been able to adjust references as needed.

1. In a picture attached. The pq source as noted turns the info that has blanks into underscores. However, when the formula for matching over to the GL match, a lot of them are not matching. However, this is because the pq source after the digits, any word broken up was given a underscore. So when matching over the GL match, the match is not the same as the name. Ex. community_based = community based. Which in excel terms does not work due to the extra space so no match occurs. Fortunately, the language (wording- administration, community based) can be matched with the 3 digits before. Ex. 000 = admin, 050= Community based, 080=support, etc... How can we adjust the formula as the only match needed can be used on the left first 9 digits/10 characters? As the centers and groups would have the same numbers. Ex.- they all have 10 characters- 000000-000,006000-000,006000-050
I was not for sure how to include a left function into it or would it be better to make another helper column for it or simply adjusting the formula?

2. If we wanted to add a total column for each month, would adding a sum column for each month in the template. Would this come over into the new workbook correctly? I do not see any other adjustments that would need to be done to the macros for this to work, is this correct? If not, my only thought would be in the Sub CreateCostCenterWorkbook() towards the end when referencing the columns 1 : 72.

3. Similar to question above. The adjustments are done by each financial manager each month, which is done in another file. If we put in a formula for a each adjustment to reference another file. Would this come over to the new workbook correctly? I imagine it would, as it copying over the template but I wanted to double check first?

Thanks,
Ant

**Tsjallie** · 12-10-2018, 05:53 PM

Q1:
this formula for the column [Chk] will substitute both "-" and " " by "_"

Formula:

Please Login or Register  to view this content.

Q2:
You can add a Total column for each month in the template, but the macro copies the formula in the first column to all the cells in
columns 1 - 72 (or 84).
So the macro should also copy the formula from the first Total column to all other Total columns.
I will get back to you on that.

Q3:
Not sure what you mean.
Can you upload such an Adjustment file. Just with some dummy data, so I can check the structure.

**Tsjallie** · 12-11-2018, 05:47 PM

Further to Q2:
The attached new version has a Total column for each month in the template and the macro now copies the formulas of the first row to all the cells in
columns 1 - 84 (see code snippet below).

Please Login or Register  to view this content.

**AMoreno** · 12-12-2018, 04:52 PM

Okay thanks! I will check all this on Friday (and thanks for putting in the [Chk] formula as well)! Its been a busy finals week for me, one left to go!!!

Question 3:

For the adjustment file attached. At the end of each month, each of our financial managers are in charge of entering adjustments. These adjustments can differ for many reasons that include lower levels coding a value to the wrong cost center, wrong account, and etc. So month to month these changes need to be accounted for so we can match the GL. So attached is a mock up file, this file currently has almost 1000 entries at this time (so its commonly used), but for time sake I only included two examples. Originally, we were only having the amount come over in the adjustment column regardless of the accounts (even though it is an option given to choose, we could not get the adjustment to fall over to the right account which is why we created the adj column) that have been changed and just making sure the values are matched by the same criteria as the others. Really if anyone needed to find the exact account, any of the financial managers could go back to the file and research the problem themselves based on their own adjustments.

The structure of the file is not dependent on anything, so it can be adjusted for better use for this new process. This file was made at the beginning when we first started so some of the fields such as the date are used differently.

Even so, I think I could write in a formula to all twelve adjustment columns and the macro should be able to just copy over template, correct? I am basing that on the fact on what I noticed, I believe you added the sum columns and adjusted the number of columns that needed to be copied over from the sub.

Thanks,
Ant

**Tsjallie** · 12-13-2018, 03:34 PM

As far as I can see, there are two ways to properly incorporate the Adjustments file:

Handle the Adjustments file together with the GL Accounts
For that the structure of the Adjustments file should be exactly the same as the GL files.
Because the Adjustments file has a column GL Account (1st column) that column should be added to GL files to. Not sure if that's possible, but it would save you changing/extending the macro. The the GLFiles query would need a minor adjustment to import the GL Account column instead of creating it.
Handle the Adjustments file seperately
Alternatively the Adjustments file could be imported thru a separate query and handled in some way (there are more than one solution). But that would need changing and extending the macro.

Obviously (I think), option 1 would be the easiest.

Auto expand table based on amount of rows of data provided

Re: Auto expand table based on amount of rows of data provided

Re: Auto expand table based on amount of rows of data provided

Re: Auto expand table based on amount of rows of data provided

Re: Auto expand table based on amount of rows of data provided

Re: Auto expand table based on amount of rows of data provided

Re: Auto expand table based on amount of rows of data provided

Re: Auto expand table based on amount of rows of data provided

Re: Auto expand table based on amount of rows of data provided

Re: Auto expand table based on amount of rows of data provided

Re: Auto expand table based on amount of rows of data provided

Re: Auto expand table based on amount of rows of data provided

Re: Auto expand table based on amount of rows of data provided

Re: Auto expand table based on amount of rows of data provided

Re: Auto expand table based on amount of rows of data provided

Re: Auto expand table based on amount of rows of data provided

Re: Auto expand table based on amount of rows of data provided

Re: Auto expand table based on amount of rows of data provided

Re: Auto expand table based on amount of rows of data provided

Re: Auto expand table based on amount of rows of data provided

Re: Auto expand table based on amount of rows of data provided

Re: Auto expand table based on amount of rows of data provided

Re: Auto expand table based on amount of rows of data provided

Re: Auto expand table based on amount of rows of data provided

Re: Auto expand table based on amount of rows of data provided

Re: Auto expand table based on amount of rows of data provided

Re: Auto expand table based on amount of rows of data provided

Thread Information

Users Browsing this Thread

Similar Threads

Bookmarks

Bookmarks

Posting Permissions