Tables
12 TopicsTurn 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?7.8KViews0likes7CommentsFilter complex table to simple table
Hi, I have very big and complex table with loads of headers and information . Sadly I don't know what steps do I need to take ? But these are requirements Basically table consist of serial numbers with failed and pass and which stage failed and what it done to pass test . what I want do to create another table to summaries only failed ones and show only test failed for each test and why failed and what they did . thanks1.1KViews0likes1CommentExcel 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.13KViews0likes6CommentsTaking 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 🙂3KViews0likes5CommentsPivot tables connected
Hello, I created two pivot tables in different worksheets of a same workbook, but I am facing some problems when I try to group or ungroup only one of them. Because both tables have time as lines, whenever I try to group the days into months in one pivot table, the other one gets automatically grouped, which is a problem for the graphing results I am showing. How can I have the freedom of grouping and ungrouping one pivot table without altering the other one? Thank you1.9KViews0likes5CommentsProblem 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 Gabriel2.8KViews0likes6CommentsCombine 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?6.5KViews0likes2CommentsExcel Table New Row Formula Problems
Hello everyone, I'm trying to build a spreadsheet to track my finances better. I tried to make it as dynamic and user friendly as possible so that others in my family can use it as well (they're not Excel savvy). Since I wanted the areas and categories of tracking to be dynamic, I used tables, which pulls from other tables in other sheets (1 sheet per month, 3 tables per month for different areas of personal finance). The problem I'm running into is when I insert a new row into the tables, some tables won't copy the formulas correctly from the row above and some tables will. Some new rows will reference the cell in the bottom left corner of the table, while others will copy just fine. Entertainment1 shows the new row behaving correctly and moving the formulas down as it should Housing2 and Savings1 show the new row pulling formulas from incorrect locations1.4KViews0likes0CommentsExcel Table AutoExpansion not extending all formulas to the new row when I add a new value
Using Excel for O365. I have large table with many columns. When I add a new value in a column at bottom of the table, only about 50% of the formulas propogate to this new row. Not sure how to fix it.13KViews0likes1CommentAutofill 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!! Thanks12KViews0likes3Comments