# Thread: INDIRECT formula cell reference Thanks: 0 Likes: 0

1. ## INDIRECT formula cell reference

Hi I am having trouble referencing the cell in below formula vs. text.

=SUMPRODUCT(SUMIF(INDIRECT(""&data&"!"&"A6:A6"),\$B12,INDIRECT(""&data&""!"&"D6:D6")))

data represents the line for sum values and name range of sheets (D6)..A6 is lookup on sheets and B12 is criteria.

The formula works but I have to go through ad manually change text vs dragging

2. ## Re: INDIRECT formula cell reference

Any help would be greatly appreciated

Bump

4. ## Re: INDIRECT formula cell reference

this is an example to combine indirect() and offset(), see if you can adopt it for your problem

ABC
15Sheet2
210
315
420
525

Sheet1

Worksheet Formulas
CellFormula
A1=SUM(OFFSET(INDIRECT(\$C\$1&"!\$A\$1"),ROW(A1)-ROW(\$A\$1),0,1,5))

ABCDE
111111
222222
333333
444444
555555

Sheet2

5. ## Re: INDIRECT formula cell reference

I have the formula which works in pulling correct values I just have my reference as texts and trying to use a cell reference so I can drag formula

6. ## Re: INDIRECT formula cell reference

What do you want the formula below the original one to be doing if you drag it down one cell? Using A6:A7, A7:A7, something else?

7. ## Re: INDIRECT formula cell reference

Maybe this one which does A6:A6, A6:A7 etc as you drag:

=SUMPRODUCT(SUMIF(INDIRECT("'"&data&"'!A6:A"&ROWS(\$A\$6:A6)+5),\$B12,INDIRECT("'"&data&"'!D6:D"&ROWS(\$A\$6:A6)+5)))

8. ## Re: INDIRECT formula cell reference

Originally Posted by steve the fish
Maybe this one which does A6:A6, A6:A7 etc as you drag:

=SUMPRODUCT(SUMIF(INDIRECT("'"&data&"'!A6:A"&ROWS(\$A\$6:A6)+5),\$B12,INDIRECT("'"&data&"'!D6:D"&ROWS(\$A\$6:A6)+5)))
thanks

I inputted this formula and pulls but it’s still not dragging.

So D6 is basically one month but if I drag it stays as D6 when I need E6

9. ## Re: INDIRECT formula cell reference

Still struggling trying to get “D6:D6” to reference as cells and drag across vs as text

Would really appreciate the help!

10. ## Re: INDIRECT formula cell reference

You need to be answering the question in post 6. I presume you are dragging right. So what is the formula you want in the cell next to the original to be? Put it here in exactly the same way as you did for the original formula but the cell to the right. I would need to know what needs to change from cell to cell.