In a SUMIFS formula I have my sum_range: sheet2!5:5
Is there a way to replace that 5:5 with a formula so my sum_range will change depending on the cells referenced in the formula??
Posted Nov 17, 2017, 7:38 PM UTC
You can use INDIRECT
If you put the reference in A1 (e.g. "5:5") you can do:
INDIRECT("Sheet2!" & A1)
Which will return
Sheet2!5:5
This can also be used with formulas, so if you want to reference the same row on a different sheet, for instance:
INDIRECT("Sheet2!" & ROW() & ":" & ROW())
Which, if the formula is in Row 10, will return:
Sheet2!10:10
I don't think this is what I am looking for. Sorry I am terrible at explaining this - I am new to the finer points of excel.
What I want to do is write my sum_range as sheet1!(formula) and have the formula spit out 5:5 or 6:6 or whatever.
That's what the formula does.
If you want to change what row is being summed up, as in sometimes it's adding up all of Row 5, sometimes all of Row 6, and it's dependent on a formula you create - then my explanation is the method you need.
If you're not trying to change the range that is being summed up then I really think you'll need to provide a sample.
Thanks, I'll give it a shot.
Just replace what's after INDIRECT("Sheet2!" &... with whatever formula you're going to be using to determine the row number. Just be sure to duplicate it, e.g. if the formula is A1/B1 then you'll need to do:
INDIRECT("Sheet2!" & A1/B1 & ":" & A1/B1)
Which, if A1 = 4 and B1 = 2 will return
Sheet2!2:2
Can you provide your current formula. I have an idea, but want to know what you have currently.
My formula is pretty convoluted, the INDIRECT function did solve my issue though. I didn't understand at first but it is exactly what I was looking for.
Great, glad someone could help then.
What do you mean 5:5?
Try the indirect function?
I want my sum_range to be a row from another sheet (sheet1!5:5) for example looks at row 5 from sheet1. However, ideally I want the sum_range to change dynamically so that it doesn't always return row 5, but instead returns a row depending on a formula.
Basically, I want to be able to write sheet1!(formula) for my sum_range.
That's what the indirect function does.
Sheet1 A1 = "B5:B5" for example just to take your example. Then on Sheet2 you in the formula you're trying to write you can do
and that should actually evaluate to
Perfect, it worked! thank you very much!
No worries, can you mark it as a solution?