Hello and thank you in advance,
I'm currently working on an excel table that is connected to a sharepoint so that it is able to update. The table is about 18,000 rows (but updated and new rows added daily) by 62 columns. Currently I'm using the output in excel to run formulas and determine workloads and equipment orders, an example of code below is what I'm using to determine the number of Desktop Workstations are able to be replaced in each department.
"Count If (This item belongs to the department referenced in the left column, this item is not 'on hold' for any reason, this device has not already been deployed, this is not an existing device, this device is a desktop computer)"
This type of formula is used in a separate table about 900 rows (next to a list of each department) and 16 columns (each deployment item) and it's killing the computer every time it refreshes or a filter is applied. I'm new to SQL but completed a tutorial on CodeAcademy and SQL seems very similar to Excel formulas in that I can understand what I'm seeing when I read it. From what I see online, SQL is much faster but I'm not exactly sure how I can apply a refreshable table inside excel. I'm unable to connect to the SharePoint directly in order to use PowerBI and we're not using an SQL server. If at all possible I would like to avoid using VBA unless I have to. I downloaded a plugin for excel called QueryStorm but I'm looking for any advice from experienced SQL/Excel forum members and hopefully save myself some frustration.
I appreciate any assistance or advice you can provide.