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

[Bommy]

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 an issue before asking but this one has me right stumped

I have gotten a request which I am not entirely sure is totally feasable but I am hoping to be wrong:

The Issue - Once a year there is a report that is generated and previous years it had around 4-5 accounts on it. This year it's over 1000.(Regulations Changes)

We have to make some calculations with the accounts that are in the report(All required info is in the report).The calculator that is used was made in excel and it's rather complex.

Is there a way I could automate filling in the cells in a calulator to calulate it and save it for each account it calculates for? Normally 4-5 accounts is fine but now with over 1000 it's a need to improve or possibly re-work.

[BA_Pete · Accepted solution]

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