SUM Range in a different workbook

[wist]

Hello,

having issues figuring out how to sum a range from a table in a different workbook. I need to sum the AR part but the number of rows will vary from file to file so cannot just put SUM(B1:B2) ..I need to specify B2 dynamically because that one will change (B1 will not)

AR	100
	200
AP	10
	20

<tbody>
</tbody>

If INDIRECT was working from closed files it would be no problem but it's not. Any ideas?

Thanks

 

[Yongle]

One way

1. Put this formula (after amending the file path) into cell A1 in a new sheet
=SUM('C:\Test\subfolder\[Master Workbook.xlsx]Sheet1'!B$1:B1)

2. Copy it down to match the number of rows in use in the other sheet (plus extra rows to allow for new items)

3. Column A now contains all possible values

4. This formula gives you sum(B1:B5)
=INDEX(A:A,5)

5. Make it dynamic by putting a formula to calculate last row (or input value) in a cell (D1 in this example)
=INDEX(A:A,D1)

 

[wist]

One way

1. Put this formula (after amending the file path) into cell A1 in a new sheet
=SUM('C:\Test\subfolder\[Master Workbook.xlsx]Sheet1'!B$1:B1)

2. Copy it down to match the number of rows in use in the other sheet (plus extra rows to allow for new items)

3. Column A now contains all possible values

4. This formula gives you sum(B1:B5)
=INDEX(A:A,5)

5. Make it dynamic by putting a formula to calculate last row (or input value) in a cell (D1 in this example)
=INDEX(A:A,D1)

thank you Yongle this definitely works and I might use this option but I have to perform this calculation with several workbooks so ideally I would be looking for a formula that would do it directly without adding the extra sheet and sum. Thank you

 

[Yongle]

You could do it with VBA
- and the VBA could be triggered when you change a cell value or by a button etc

Range("[COLOR=#0000cd]E1[/COLOR]").Formula = "=SUM('C:\Test\[Master Workbook.xlsx]Sheet1'!B1:B" & Range("[COLOR=#ff0000]D1[/COLOR]") & ")"

VBA creates a dynamic formula based on the value in another cell

example if cell D1 value is 70, then this formula is placed in cell E1:

=SUM('C:\TestArea\[Master Workbook.xlsx]Sheet1'!B1:B70)

 

[wist]

You could do it with VBA ...

thank you, yes that would be the easy but ugly way :D