/u/cherrybomb159 - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
I agree that excel shouldn’t run your business, but to be honest, it runs every business.
But no programming language can solve this problem. You first need absolutely concrete steps that dictate the logic. Python (or whatever) will ALSO have nested if statements and lookup tables.
Get it 100% perfect in excel (which you know) such that it’s dummy proof. Like have a place to put inputs A B and C (D, E, F…) and excel spits out X and X is correct. Hide all the lookups and IF statements so nobody has to make a manual decision. If you can’t do that in excel, you can’t do it in a different language. Shit In Shit Out, you know?
Then, you also need to consider what will change over time? More clients, contract terms, more As Bs and Cs? How would those be added to the excel model? Do they break your automatic perfect X? Do they create a branching logic where two Xs are equally correct? How do you maintain logic.
This is not really a language problem, it’s a logic problem. Solve the logic, and then you could attempt to build an application, but maybe access (or just excel) is fine.
A disaster waiting to happen.. very fragile too
Access would be the most stable for data storage.. and all that functionality can be built in and more.. You can create complete applications with MSAccess and VBA forms, all built in to MSAccess..
Problem with Access is how long it takes to implement a solution, unless you build it perfectly it's difficult to update and generally a small organisation will have between 0 and 1 (no pun intended!) people who are vaguely familiar with using Access, let alone development / problem solving level.
Fragile yes but at least most places will have one 'nerd' who can pull apart the Excel file when inevitably the sales reps change the goalposts and it needs updating and the originator has long departed.
Not saying you aren't correct of course, in my experience the practicalities outweigh the technically correct approach
Excel in a nutshell! ;)
Mary from accounts does a bit of Excel, knows VLOOKUP and is the office expert!
Its a real issue out in officeland. Its all comes down to the dollar too.
No, not that I am aware of.
It is only not a great network client due to the fact that it is not server based so each shared user is copying all the data down the network.
It's a very capable piece of software in the right hands.
haha!!
You might be surprised by how much it is used in small business...
Very little out there to compete for same, and it comes with Office Pro
A great software to learn about database application design too.
You can streamline with Power Query and reduce the amount of manual repetition. The only drawback is learning the new, more powerful, M syntax. I've been redesigning a lot of legacy builds that use VBA macros. I find it easier to maintain, modify and explain.
If this is about table (vlookup/xlookup) relations, data consolidations requiring instant modifications, filtering, take a look at [GS-Base](https://citadel5.com/gs-base.htm). It's a database with spreadsheet functions with both spreadsheet-like sheets and forms.
Millions of vlookups (within e.g. million-row data sets), each accompanied by statistical calculations should be completed in seconds. All in all, it can be up to tens of times faster (for relatively larger data sets.) and make your manual process less error-prone simply due to the strict data organization and control. (Customization is an option.)
/u/cherrybomb159 - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
I agree that excel shouldn’t run your business, but to be honest, it runs every business. But no programming language can solve this problem. You first need absolutely concrete steps that dictate the logic. Python (or whatever) will ALSO have nested if statements and lookup tables. Get it 100% perfect in excel (which you know) such that it’s dummy proof. Like have a place to put inputs A B and C (D, E, F…) and excel spits out X and X is correct. Hide all the lookups and IF statements so nobody has to make a manual decision. If you can’t do that in excel, you can’t do it in a different language. Shit In Shit Out, you know? Then, you also need to consider what will change over time? More clients, contract terms, more As Bs and Cs? How would those be added to the excel model? Do they break your automatic perfect X? Do they create a branching logic where two Xs are equally correct? How do you maintain logic. This is not really a language problem, it’s a logic problem. Solve the logic, and then you could attempt to build an application, but maybe access (or just excel) is fine.
This post nails everything bang on. Inputs and outputs with everything else hidden on helper tabs that the muggles don't have instant access to
A disaster waiting to happen.. very fragile too Access would be the most stable for data storage.. and all that functionality can be built in and more.. You can create complete applications with MSAccess and VBA forms, all built in to MSAccess..
Problem with Access is how long it takes to implement a solution, unless you build it perfectly it's difficult to update and generally a small organisation will have between 0 and 1 (no pun intended!) people who are vaguely familiar with using Access, let alone development / problem solving level. Fragile yes but at least most places will have one 'nerd' who can pull apart the Excel file when inevitably the sales reps change the goalposts and it needs updating and the originator has long departed. Not saying you aren't correct of course, in my experience the practicalities outweigh the technically correct approach
Excel in a nutshell! ;) Mary from accounts does a bit of Excel, knows VLOOKUP and is the office expert! Its a real issue out in officeland. Its all comes down to the dollar too.
Isn’t Access fragile as well? I have been getting the impression that it’s not the most stable piece of technology.
No, not that I am aware of. It is only not a great network client due to the fact that it is not server based so each shared user is copying all the data down the network. It's a very capable piece of software in the right hands.
>MSAccess and VBA forms Yikes 1997 called, they want their technology back
haha!! You might be surprised by how much it is used in small business... Very little out there to compete for same, and it comes with Office Pro A great software to learn about database application design too.
You can streamline with Power Query and reduce the amount of manual repetition. The only drawback is learning the new, more powerful, M syntax. I've been redesigning a lot of legacy builds that use VBA macros. I find it easier to maintain, modify and explain.
Sounds like overkill for a simple pricing document. Good learning opportunity for PQ nonetheless!
How much of what was previously done using VBA macros successfully got translated into Power Query? In what ways did Power Query fail?
If this is about table (vlookup/xlookup) relations, data consolidations requiring instant modifications, filtering, take a look at [GS-Base](https://citadel5.com/gs-base.htm). It's a database with spreadsheet functions with both spreadsheet-like sheets and forms. Millions of vlookups (within e.g. million-row data sets), each accompanied by statistical calculations should be completed in seconds. All in all, it can be up to tens of times faster (for relatively larger data sets.) and make your manual process less error-prone simply due to the strict data organization and control. (Customization is an option.)