Excel Table Size in Macro
Hi, I download external Excel information and run a macro I created for it. It works great. However, the external info I download grows every week with new rows and when I run the macro, the new rows dont get included in the table. This is part of my macro to turn it into a formatted table: ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$X$125"), , xlYes).Name ="Table1" So, it creates a table and formats it with 125 rows. But next week, there may be 130 rows, not 125 rows. Since the Macro only goes to row 125, the table stops at row 125 and misses the last 5 rows. Question: Other than setting the table size in the macro from "$A$1:$X$125" to something like "$A$1:$X$1200", how can you select all the rows (including the new 5 rows) automatically without making the table so large by using ("$A$1:$X$1200")? Thanks, Wayne.
Autofill cells based on one value of another cell
Hi, Need some help to make my life a little bit easier: I'm making plans for a production facility, and I want to autofill all the cells based on product number. I have all the data I need in another table. Simplified, it looks like this: If I write 1 in B1 then I want the rest of the numbers (persons needed, machine number and planned) appear automatically from the table below. If anyone knows it will help me a lot!! Thanks
Turn Off Calculated Columns
Hello Everyone, I have a workbook with multiple sheets with a different table on each one. A lot of the columns needs consistent formulas and so calculated columns are great for this. However, some of the columns I don't want to have this enabled. It seems that any column that I would like this feature to work didn't work originally, and the columns that I don't want to use it with, it's working with. I've figured out to make an existing column work when it didn't originally, I just have to make sure all the cells in that column have the same type of formula. However, as far as I can tell, once you set it up like that, you can't break it. For example: I have a column that will automatically create a hyperlink to another part of the workbook. When I would create another row in the table, it wouldn't copy that formula. After making sure every cell in that column had the same formula structure, it works now. Elsewhere in the workbook (even in the same table), I have a column that also creates a hyperlink to somewhere else in the workbook. However, not ever cell in these columns has the same formula structures. Yet when I create a new row, it still copies down the formula. Is there a way to stop this from happening?
Combine Data from Multiple Tables to 1 Pivot When Having Duplicate Values
See attached file. I have a table (Table1) with data of number of resources required every month per Product / per Domain / per Team. So a single product can appear many time in the 'product' column. Creating a Pivot for this Table1 is easy so I can see how much effort is required for each product or how much effort invests every Team along the year. I have many products & many department. Due to internal limitations, I have to divide Table1 so each department will get the same table structure but will fill in different required effort per month. So....I have Table2 for Software and Table3 for Mechanics department and can have more tables for other department. I couldn't do it with the Pivot Data Model since 'Product' column contains duplicate values. So - How do I combine all the tables to 1 pivot or to 1 table and from there creating the pivot?
Bi-Directional tables in Excel using Power Query or ADX
Is there a way to make bi-directional tables in excel?? In one sheet there is a full table and in sheet two there is a filtered table. In the example below, the simple problem description is shown. Idea is that users could change the value in table 2, so it is pushed back to table 1. Also when changed in table 1, the value is pushed to table 2. It is easy to use power query but then it is a one-way street. Currently Using Excel 2013 & Power Query. Any help is appreciated. Added simple excel file as an attachment as a basis.
Is there a way to hide a single page in multi-page mail merge document?
Hi! Mail-merge newbie here...I am trying to determine if it's possible to hide a single page with no mail merge data, that is contained in a multi-page mail-merge document. Within Salesforce, we are using a platform called Conga to generate a customer invoice that is to be printed and mailed, after they have completed their order. We have setup the document to contain 3 pages, each of which contains information for a different type of ticket that they can purchase (each of these pages contain static and mail merge data). They can buy any combination of the ticket types. If customer A buys ticket type 1 and 3, but not 2, is there a mail-merge field or code that can be embedded to not have the ticket type 2 page included in the final document export?Taking values from certain rows based on data input
So I'm trying to get a value into the column "Cat A Tied" (C column). This value depends on the number in the "# People Tied With" column (D Column). In this case, cell D2 has the number 3 in it. I want the sum of the first three rows of the B column (not including column headers) and then divide that by 3. However, I want the cell to be dynamic so if I change the number to 5 it will then automatically take the sum of the first 5 rows and divide by 5. Additionally, if I put this value into D3, the second row of my table, I'd want the first number (3 or 5, or any number put in) of rows from that same row onwards instead of from the first row of the data set. Any help would be greatly appreciated 🙂Problem with Pivot tables
Hello, I am facing a problem with data arrangement in a pivot table. When configuring my data base table, the lines are composed of 3 products, while the columns contain information about number, cost etc. One column contains the month, and I have to repeat each month 3 times down to include the 3 products each month. Some other columns involve numbers that are not related to a specific product and therefore I just equally repeat the number 3 times. In the pivot table (which is set to present the months as lines), the sum of this repetition gives me the obvious result of a x3 multiplication, however I would like to receive only the number itself, but without setting the results to “count”, “maximum” or “minimum”, because I want the final line in the pivot table to give me the sum for all months. Prints are attached to this description. Thank you so much Gabriel
