Hello,
I've used the following formula successfully to pull data from multiple sheets and provide the total on an analysis worksheet. Example of worksheets and formula.
=DSUM(INDIRECT("'"&A2&"'!D:D"),$B$1,INDIRECT("'"&A2&"'!D:D"))
Example of one of the individual sheets holding original data: [TABLE="class: grid, width: 500, align: center"]
<TBODY>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Merchant Name[/TD]
[TD]Name[/TD]
[TD]Date[/TD]
[TD]Number[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Louise[/TD]
[TD]James[/TD]
[TD]05-01-13[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Louise[/TD]
[TD]Lou[/TD]
[TD]09-08-12[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]
Merchant Name is the same name as the tab for the individual sheet. Hence the INDIRECT argument.
Example of Analysis Sheet Running above formula:
[TABLE="width: 500, align: center"]
<TBODY>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Merchant Name[/TD]
[TD]Number[/TD]
[TD]Date[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Louise[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Julie[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]
Example of what I want to see using the Transpose formula (Analysis sheet):
=TRANSPOSE(INDIRECT("'"&A2&"'!C:C"),$C$1,INDIRECT("'"&A2&"'!C:C")) (My theory that currently doesn't work. I'd like to be able to pull data across worksheets and yield the transposed data into the corresponding row of the same merchant row.
[TABLE="class: outer_border, width: 500, align: center"]
<TBODY>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Merchant Name[/TD]
[TD]Number[/TD]
[TD]Date[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Louise[/TD]
[TD]4[/TD]
[TD]05-01-13[/TD]
[TD]09-08-12[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Julie[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]
Let me know what you guys think. Is it possible to do?
Thanks!!![ROFL :rofl: :rofl:](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f923.png)
![ROFL :rofl: :rofl:](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f923.png)
I've used the following formula successfully to pull data from multiple sheets and provide the total on an analysis worksheet. Example of worksheets and formula.
=DSUM(INDIRECT("'"&A2&"'!D:D"),$B$1,INDIRECT("'"&A2&"'!D:D"))
Example of one of the individual sheets holding original data: [TABLE="class: grid, width: 500, align: center"]
<TBODY>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Merchant Name[/TD]
[TD]Name[/TD]
[TD]Date[/TD]
[TD]Number[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Louise[/TD]
[TD]James[/TD]
[TD]05-01-13[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Louise[/TD]
[TD]Lou[/TD]
[TD]09-08-12[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]
Merchant Name is the same name as the tab for the individual sheet. Hence the INDIRECT argument.
Example of Analysis Sheet Running above formula:
[TABLE="width: 500, align: center"]
<TBODY>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Merchant Name[/TD]
[TD]Number[/TD]
[TD]Date[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Louise[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Julie[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]
Example of what I want to see using the Transpose formula (Analysis sheet):
=TRANSPOSE(INDIRECT("'"&A2&"'!C:C"),$C$1,INDIRECT("'"&A2&"'!C:C")) (My theory that currently doesn't work. I'd like to be able to pull data across worksheets and yield the transposed data into the corresponding row of the same merchant row.
[TABLE="class: outer_border, width: 500, align: center"]
<TBODY>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Merchant Name[/TD]
[TD]Number[/TD]
[TD]Date[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Louise[/TD]
[TD]4[/TD]
[TD]05-01-13[/TD]
[TD]09-08-12[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Julie[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]
Let me know what you guys think. Is it possible to do?
Thanks!!
![ROFL :rofl: :rofl:](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f923.png)
![ROFL :rofl: :rofl:](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f923.png)