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  Reply With Quote

2. Re: INDIRECT formula cell reference

Any help would be greatly appreciated  Reply With Quote

3. Re: INDIRECT formula cell reference

Bump  Reply With Quote

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  Reply With Quote

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  Reply With Quote

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?  Reply With Quote

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)))  Reply With Quote

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  Reply With Quote

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!  Reply With Quote

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.  Reply With Quote

User Tag List

Tags for this Thread

cell, formula, indirect, sheets, text  Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•