Consulting

Results 1 to 2 of 2

Thread: Solved: INDIRECT Reference

  1. #1
    VBAX Tutor SJ McAbney's Avatar
    Joined
    May 2004
    Location
    Glasgow
    Posts
    243
    Location

    Solved: INDIRECT Reference

    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.

  2. #2
    VBAX Tutor SJ McAbney's Avatar
    Joined
    May 2004
    Location
    Glasgow
    Posts
    243
    Location
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •