Use cell value as part of a reference to a cell range in a formula

Question

My problem is similar to that in this quesion: Row in formula change based on value in another cell, i.e. I want to use the value of a cell as reference to another cell in a formula of yet another cell.

However, I want the value of a cell to only be the part of a cell range. Let me give you an example:

A1 holds worksheet-a

In C1 I'd like to have a forumla that results in =SUM('worksheet-a'!B:B)

Based on the other question, I thought that something like =SUM(INDIRECT(A1)&"!B:B") might do it. Sadly, it does not.

Do you have any ideas?

Thank you!

Jirka Š. · Accepted Answer · 2013-06-27 07:02:12Z

1

For me it works in this way

=SUM(INDIRECT(CONCATENATE(A1;"!B:B")))

or with ampersand

=SUM(INDIRECT(A1&"!B:B"))

answered Jun 27, 2013 at 7:02

Jirka Š.

3,4382 gold badges17 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

msallge Over a year ago

Thank you. Your comment helped me drill down on the issue. Apparently the - within the worksheet name caused the problem. I could just avoid worksheet names with a minus.

Collectives™ on Stack Overflow

Use cell value as part of a reference to a cell range in a formula

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related