Indirect address?

[M1234]

Hi all,

I'm trying to do a sum formula based on certain criteria. I want the formula to say =SUM('Sheet 1'!H83:'Sheet 1'!J83) but I want the J83 part to be based on the week number so that it automatically extends the range to the right when a week is added. e.g. when it's week 7 it should say =SUM('Sheet 1'!H83:'Sheet 1'!M83).

I have a cell that says "Week 1-5" and I'm doing a RIGHT formula and looking that up to give me a column reference as per below:

1 G
2 H
3 I
4 J
5 K
6 L
7 M
8 N
9 O
10 P
11 Q
12 R

I can get it to say =SUM('Sheet 1'!H83:'Sheet 1'!J83) but it keeps on coming up with either a #VALUE or #REF error.

Any help would be much appreciated.

[XOR LX]

Hi,

Try inserting an INDIRECT formula around the address string you create before passing to the SUM.

Regards

[M1234]

Hi,

Try inserting an INDIRECT formula around the address string you create before passing to the SUM.

Regards

It still comes up with the REF error.

I have even tried putting 'Sheet 1'!G83 in one cell and 'Sheet 1'!K83 in another cell and then put =SUM(INDIRECT(((K17)&":"&(K18)))) but it has the same error.

When I evaluate it, it looks as though there are 3 ''' at the start instead of 2.

[M1234]

I've created a mock excel file to show you how I've been unsuccesfully doing it

[Pete_UK]

The only numbers you have on Sheet1 are on row 4, so I don't know why you are trying to add those from row 83. I think this is what you are trying to achieve in I6:

=SUM(INDIRECT("'Sheet 1'!H4:"&(VLOOKUP(RIGHT(D3,1),$C$8:$D$19,2,0)&"4")))

although using RIGHT(D3,1) means you will only be able to get up to week 9.

Hope this helps.

Pete

[M1234]

The only numbers you have on Sheet1 are on row 4, so I don't know why you are trying to add those from row 83. I think this is what you are trying to achieve in I6:

=SUM(INDIRECT("'Sheet 1'!H4:"&(VLOOKUP(RIGHT(D3,1),$C$8:$D$19,2,0)&"4")))

although using RIGHT(D3,1) means you will only be able to get up to week 9.

Hope this helps.

Pete

Hi Pete,

My main sheet was row 83 but I created a mock up to remove the sensitive data but placed it in row 4 instead. Thanks for the help, this works perfectly.

[Pete_UK]

Okay, glad to be able to help.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[XOR LX]

Repeating the Sheet 1 ref is problematic when passed to INDIRECT. Try:

=SUM(INDIRECT("'"&"Sheet 1'!H83:"&(VLOOKUP(RIGHT(D3,1),$C$8:$D$19,2,0)&"4")))

Regards

[martindwilson]

id go for
=SUM(INDIRECT("'"&"Sheet 1'!H4:"&(VLOOKUP(SUBSTITUTE(RIGHT(D3,2),"-",""),$C$8:$D$19,2,0)&"4")))
but im pretty sure this can be done with just an index what is the real layout?