First off, I've been reading this forum for a couple of months and it has been a major learning tool for me. Thanks to everyone out there!
Problem:
I was recently asked to fill out an Excel template with data from an Access table. Since the Excel template has multiple worksheets that link to the data in the first worksheet, querying raw data from the Access table and then building a PivotTable will not be appropriate. Essentially, upon opening the Excel file, everything should be updated using current and past records based on months.
Example:
Access Table: "Payroll"
Name Amount Month
John 600 April
John 550 May
John 625 June
Sally 700 April
Sally 400 May
Sally 915 June
Rough Excel template:
Name Jan ... April May June ...
John 300 ... 600 550 623 ...
Sally 425 ... 700 400 915 ...
I know this looks A LOT like a PivotTable or a Crosstab but trust me, I need everything to be based off of queries. The Excel file is essentially a hollow shell with formula cells in worksheets that refer back to the first one. The column headers (Jan - Dec) and the row headers (Name) are already supplied in the Excel sheet. If more users exist in the database but are not hard coded into the Excel sheet under the Name column then for all intent and purposes I do not care about them.
So, if I were to build a query in Excel that grabbed Jan data for Sally, I'd have something along the lines of:
(Pseudocode)
SELECT Amount FROM Payroll WHERE Name like A3 and Month like "Jan";
This works just fine! However, there are MANY rows (not just John and Sally) and the rows are split into sections using blank lines, so a simple query that includes everything will not cut it.
Point being... I'm assuming I need to create reusable formula that will let me build totals for each Name. I'm aware that you can apply a formula in Excel to a set of cells and then "drag" the formula in a direction so that the formula applies to other cells (like applying cell1A + cell1B = cell1C and then dragging the formula result cell downward to produce cell
A + cellB = cellC and so on...).
Can I finish one row for John (Jan - Dec) where each cell that represents a total is fed through a query using the Name cell as a parameter, and then "drag down" that cell in order to apply the same formula but this time reference A2 or A3 or A4 (etc)? I know how to link a cell to a query but it's a royal pain modifying the Parameters and then choosing "Use cell as parameter" and then selecting that unique cell. I would need to repeat that process for every single name!
Suggestions or guidance would be MUCH appreciated.
Thanks!
Problem:
I was recently asked to fill out an Excel template with data from an Access table. Since the Excel template has multiple worksheets that link to the data in the first worksheet, querying raw data from the Access table and then building a PivotTable will not be appropriate. Essentially, upon opening the Excel file, everything should be updated using current and past records based on months.
Example:
Access Table: "Payroll"
Name Amount Month
John 600 April
John 550 May
John 625 June
Sally 700 April
Sally 400 May
Sally 915 June
Rough Excel template:
Name Jan ... April May June ...
John 300 ... 600 550 623 ...
Sally 425 ... 700 400 915 ...
I know this looks A LOT like a PivotTable or a Crosstab but trust me, I need everything to be based off of queries. The Excel file is essentially a hollow shell with formula cells in worksheets that refer back to the first one. The column headers (Jan - Dec) and the row headers (Name) are already supplied in the Excel sheet. If more users exist in the database but are not hard coded into the Excel sheet under the Name column then for all intent and purposes I do not care about them.
So, if I were to build a query in Excel that grabbed Jan data for Sally, I'd have something along the lines of:
(Pseudocode)
SELECT Amount FROM Payroll WHERE Name like A3 and Month like "Jan";
This works just fine! However, there are MANY rows (not just John and Sally) and the rows are split into sections using blank lines, so a simple query that includes everything will not cut it.
Point being... I'm assuming I need to create reusable formula that will let me build totals for each Name. I'm aware that you can apply a formula in Excel to a set of cells and then "drag" the formula in a direction so that the formula applies to other cells (like applying cell1A + cell1B = cell1C and then dragging the formula result cell downward to produce cell
A + cellB = cellC and so on...).Can I finish one row for John (Jan - Dec) where each cell that represents a total is fed through a query using the Name cell as a parameter, and then "drag down" that cell in order to apply the same formula but this time reference A2 or A3 or A4 (etc)? I know how to link a cell to a query but it's a royal pain modifying the Parameters and then choosing "Use cell as parameter" and then selecting that unique cell. I would need to repeat that process for every single name!
Suggestions or guidance would be MUCH appreciated.
Thanks!
