Forum Discussion

Bommy's avatar
Bommy
Frequent Visitor
2 years ago
Solved

Best way to approach? Auto Fill Premade Excel Calulator - Possible?

I am just looking for a bit of possible guidance to this issue - Any potential resources or guidance would be wonderful 🙂  Normally I like to find answers or even a potential idea of how to resolve...
Need Help
Tips and Tricks
  • BA_Pete's avatar
    BA_Pete
    2 years ago

    Hi Bommy ,

     

    Yes, I suppose it's technically feasible by generating a complete dataset in Power Query, then creating pivot tables that you can reference for each of your entry fields which can be filtered by account number, but to do it 1,000 times? No, not operationally feasible.

    You could do some clever stuff with VBA possibly, but I'd never recommend VBA for an enterprise solution, it's just too niche.

    My overall recommendation would be to build the calculator in Power BI. Yes, it will take time to do, but it will continue to work just fine even if you have 100,000 accounts, so an investment for the future.

     

    Pete

BA_Pete's avatar
BA_Pete
Icon for Super User rankSuper User
2 years ago

Hi Bommy ,

 

Yes, I suppose it's technically feasible by generating a complete dataset in Power Query, then creating pivot tables that you can reference for each of your entry fields which can be filtered by account number, but to do it 1,000 times? No, not operationally feasible.

You could do some clever stuff with VBA possibly, but I'd never recommend VBA for an enterprise solution, it's just too niche.

My overall recommendation would be to build the calculator in Power BI. Yes, it will take time to do, but it will continue to work just fine even if you have 100,000 accounts, so an investment for the future.

 

Pete