How do you combined an address and sum to another worksheet?

[new2de]

Hi guys,

I spent ages browsing the net to no avail.

I'm trying to get this to work:

=SUM('Permit 17-18'!C49:ADDRESS(39,COUNT(C49:N49),4)) but Excel doesn't like it.

Address bit is basically E49. How on earth do I get Excel to accept E49 so it processes: =SUM('Permit 17-18'!C49:E49)

Thanks!

 

[Joe4]

Welcome to the Board!

When building a range dynamically, you need to encapsulate it in an INDIRECT statement.

Try:

[COLOR=#333333]=SUM(INDIRECT("'Permit 17-18'!C49:" & ADDRESS(39,COUNT(C49:N49),4))[/COLOR]

See here for more details on INDIRECT: http://www.cpearson.com/excel/indirect.htm

 

[new2de]

Welcome to the Board!

When building a range dynamically, you need to encapsulate it in an INDIRECT statement.

Try:

[COLOR=#333333]=SUM(INDIRECT("'Permit 17-18'!C49:" & ADDRESS(39,COUNT(C49:N49),4))[/COLOR]

See here for more details on INDIRECT: http://www.cpearson.com/excel/indirect.htm

Thank you Joe. With your reply I was able to derive the formula I needed! Also thanks for the link!

 

[Joe4]

You are welcome!
Glad I was able to help.