Type: Posts; User: Swayzy; Keyword(s):

Page 1 of 3 1 2 3

Search: Search took 0.01 seconds.

  1. Replies

    Re: Help with VBA coding

    Yes, never reference more range than needed. The issue grows alot when you reference 1 000 000 rows 10 000 Times.

    If you need an expandable range then try to use Excels formal tables instead
  2. Replies

    Re: Payment Terms - Cash Flow Forecasting

    I did a similar tool at work for cash flow management, you will want to use a macro. Formulas can do things like this but they will be long an not transparent.

    My version had for example among...
  3. Replies

    Re: Auto-Range and Scaling of Chart Axes

    Is the code residing in a standard module? The worksheetchange event indicates that it should be inside a worksheet object

    Also the me. Sheets reference dosent seem to be declared nor set. If you...
  4. Re: Obtain separate monthly totals from a year's daily data.

    Sumifs works completely fine for this.
    Add the value column as the sumrange then use the datecolumn for the first criteria range. For the first criteria use the bigger or equal signs inside quotes...
  5. Re: Excel Table Exceeds Rows - Options for Pivot Tables

    Write the data to a text file and add to a data model. You can even split the table into multiple files and have them in One folder then import the folder.

    Overall google a bit on Powerpivot and...
  6. Re: How to use SUMIFS on two different data tables?

    I just thought that I've already explained a possible solution quite clearly in my comments, this was just live a example. But anyway this is breakdown:

    1 Create formal Excel tables
    2 Name tables...
  7. Re: How to use SUMIFS on two different data tables?

    Without seeing the actual data structure it would be hard to give a good recomendation. There are alot of ways to get to but depending on circumstances the solution might differ quite a bit. For...
  8. Replies

    Re: Performing a SUM within a COUNTIFS

    Dont exactly understand what you need but I think if you use an extra column or two you can get what you want. For example you probably need to use the first part with checking the f column as a...
  9. Re: How can I make a macro-enabled workbook look like an app?

    You can hide stuff like the toolbar, formula bar etc so that the window look alot more clean. So just google excel widow properties or something like that
  10. Re: How to use SUMIFS on two different data tables?

    Glad I could help!!
  11. Re: How to use SUMIFS on two different data tables?

    I made a simple mockup file. Here you should be able to follow the example and see why this is a very optimized structure as long as you dont try to build a big table with the sum functions that use...
  12. Replies

    Re: arrays verses worksheet for math

    Arrays and collections in vba is faster for calculations. They are a good way to if you need to do alot of manipulation with lots of cells/data points.

    So you dont use them much until you have...
  13. Re: How to use SUMIFS on two different data tables?

    When a user selects a year to look at and you have the data in separate ranges/sheets then you need to alter the referenced named ranges. So if a user switches from 2018 to 2019 then the lookup...
  14. Re: How to use SUMIFS on two different data tables?

    If you need the data in separate tables I would advise you to use the real table function (ctrl + t). Doing so makes it easier to reference them and the ranges auto expand when you enter new data....
  15. Re: How to use SUMIFS on two different data tables?

    Depending on how many individual sumifs you need I would suggest you try Indirect function for the ranges. If you are using proper tables just build the columns you need to ref in text and add the...
  16. Re: Index Match Return Second Column, not first

    Personally I like using sumifs and dates Excel understands. By using sumifs then it is much easier to use a criteria for summing.

    So in your case I would replace the dates if they arent in the...
  17. Re: Combine Dropdownlist with a search funktion

    Use a Powerpivot to handle your data then on the actual pivot table you can use a slicer.

    It is though unclear wether you want a box that you want to in real time fliter available companies as...
  18. Replies

    Re: How to use WeekNum in a SumIf

    Great! For future reference I think your first solution might work if you use sumproduct instead of sumifs.
  19. Replies

    Re: How to use WeekNum in a SumIf

    Why not just use sumifs and define it to sum between the dates that makes the desired week? So if you want to sum the current week you use today inside weekday and then subtract that from today to...
  20. Re: Can someone help me with this? Probably a beginner level formula.

    I can highly recommend the Book powerbi and powerpivot by Rob collie and Avichal Singh. It is really really good and it gave me alot of new skills. Learn this Book and you Will set yourself far above...
  21. Re: Can someone help me with this? Probably a beginner level formula.

    Power pivot would be the best way to handle both the complex question (query) and the amount of tables. It really is a no brainer. Also the flexibility of a pivot tables when tour boss says "now show...
  22. Replies

    Re: Excel Analytics for Dummies

    Powerpivot would be a good way and there is tons of YouTube vids om creating data models and the accompaning pivot tables.
  23. Re: Add a column to calculate remaining budget

    Look into using a power pivot were you Link actuals data with budget. Search YouTube for something like power pivot budget vs actuals. And you can then very easily do percent measure showing budget...
  24. Re: Bill Of Material Forecast Based on Timing of Material Change

    If you want direct help to solve the problem I would require that you upload (and share through Dropbox or similar) dummy workbooks that have the desired structure and outcome. That way I can see...
  25. Combining data from same looking tables but store data.


    This is my first real dab into PowerPivot, PowerQuery and data moddeling so go easy on me:)

    So this is the setting, I need to combine staff data into one big pivot table that can be...
Results 1 to 25 of 56
Page 1 of 3 1 2 3