INDIRECT Reference

[SJ McAbney]

I have the following formula:


=INDIRECT(CONCATENATE("'Business Won'!$Q7$",COUNTA('Business Won'!Q:Q)+5))

It doesn't work on a sheet with a space in its name.

When I was testing it on a sheet called Sheet1, it worked fine.

Any ideas how to get around this? There's going to be quite a few formulas like this.

[SJ McAbney]

Solved it myself. Was nothing to do with the INDIRECT() function and my function - which was actually:


=SUMPRODUCT((INDIRECT(CONCATENATE("'Business Won'!$Q$7:$Q$",COUNTA('Business Won'!Q:Q)+5))=B10)*(INDIRECT(CONCATENATE("'Business Won'!$B$7:$B$",COUNTA('Business Won'!Q:Q)+5))=B10)*(INDIRECT(CONCATENATE("'Business Won'!$D$7:$D$",COUNTA('Business Won'!Q:Q)+5))="V"))

now works. I hadn't included the +5 part. :rolleyes: