Excel use range name as an argument to excel function

Question

Let suppose I have several named ranges, P2017.01, P2017.02 and so on. These ranges hold values from year 2017 month 01, month 02, and so on. I want to sum the month values one-by-one. I want to construct the names of the ranges, and use these constructed names as a function argument. Example: the prefix 'P2017' is in B3, the index of the month is in C3. I concatenate B3&'.'&C3 and store the value to D3.

In E3 I want to =SUM(INDEX(P2017.01;0;2)), but instead of writing the name P2017.01 I want to acquire the name from the value of cell D3.

What should I write to E3 to do that?

Possible duplicate of Use string value from a cell to access worksheet of same name — Jerry
– Jerry, Commented Nov 16, 2017 at 7:37

Harun24hr · Accepted Answer · 2017-11-16 09:36:13Z

1

Try following formula...

=SUM(INDEX(INDIRECT(D3),0,2))

Comma (,) or semicolon (;) depens on regional settings.

answered Nov 16, 2017 at 9:36

Harun24hr

38.1k6 gold badges24 silver badges47 bronze badges

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

2 Comments

Zoltan Hernyak Over a year ago

Thanks, you gave me the daily brand new excel function today! :)

Harun24hr Over a year ago

Glad to know it helps you.

Collectives™ on Stack Overflow

Excel use range name as an argument to excel function

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related