Dynamic Table

Dynamic Table

If I add new data to my Billing Table, my sumif formula will not need to be updated to capture the full data. Within my sumif formula, I can refer to my Billing Table and then use bracketed columns and this will allow the sumif formula to capture all the data even if new data is added to the dynamic table.

Comments

Post a Comment

Popular posts from this blog

Hlookup & Right

Image
HLOOKUP Per the website, "HLOOKUP stands for Horizontal Lookup and can be used to retrieve information from a table by searching a row for the matching data and outputting from the corresponding column. While VLOOKUP searches for the value in a column, HLOOKUP searches for the value in a row." Below, I wanted to return the number of Basis invoices paid on week 1, week 2, week 3, etc. I used the right formula here because the row index number is always one more than the week. Alternatively, on another sheet, I also just used an array constant that allowed me to use just one single formula to output the same result.
Read more

Remove Duplicates

Image
One Way to do this is to use the UNIQUE funciton, which is what I often use. For example, I have several vendor codes for different invoice number. Use the Unique function and it will remove all duplicates and give you only the unique vendor codes. This is helpful when I do entry in the accounting software, especially when there is over 500+ lines of vendor codes. Alternatively, you can use ALT + A + M to remove duplicates, so definitely will start using that more!
Read more

View Excel Workbooks and Formulas

Image
To view any excel workbook and the formulas within their cells, please click on the bottom right icon. Also, once I learned about the FORMULATEXT function so I starting utilizing that at some point so you can see the whole formula without having to click into a cell.  Note the following: 1) All numbers are made up; I often use the RANBETWEEN() function to randomize numbers. It allows you to restrict the randomized number with a min and max value. 2) On the Excel Blogspot article, you can utilize the dropdowns and filters. However, once you open he excel workbook with the bottom right icon, it no longer allows you that option. For example, look at the article below and try the dropdown and filters on the blogspot website vs. opening it in an excel workbook using bottom right icon. Article:  GETPIVOTDATA
Read more