INDIRECT formula cell reference

rlexcel101

New Member
Joined
Apr 11, 2018
Messages
48
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
 
Last edited:
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.
I answered above

I need D6 to change to E6.
 
Upvote 0

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
The reason I haven't replied is that you haven't given what I asked for. If I'm to know what you need I need to know exactly what that formula would look like if it was in the cell to the right in the same format as your opening post.
 
Upvote 0
The reason I haven't replied is that you haven't given what I asked for. If I'm to know what you need I need to know exactly what that formula would look like if it was in the cell to the right in the same format as your opening post.
So..

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

Is my current formula.

-A6:A6 represents the lookup in each sheet
-$B12 represents the criteria on the sheet where formula is
-D6:D6 represents the sum values on each sheet tab

So basically I need the ability for the D6:D6 to become cell references where I can drag and they become E6:E6, F6:F6, and then If dragged down then E7:E7, etc

Does that help?
 
Upvote 0
So column A remains column A as you drag right?

=SUMPRODUCT(SUMIF(INDIRECT("'"&data&"'!A"&ROWS($A$1:A1)+5),$B12,INDIRECT("'"&data&"'!"&ADDRESS(ROWS($A$1:A1)+5,COLUMNS($A$1:A1)+3))))
 
Upvote 0
So column A remains column A as you drag right?

=SUMPRODUCT(SUMIF(INDIRECT("'"&data&"'!A"&ROWS($A$1:A1)+5),$B12,INDIRECT("'"&data&"'!"&ADDRESS(ROWS($A$1:A1)+5,COLUMNS($A$1:A1)+3))))
Thank you!

This worked. My next goal is to make this match off a drop down which I have no clue how lol ideally would like to match the drop down name against the same name on sheet tab.
 
Upvote 0
=SUMPRODUCT(SUMIF(INDIRECT("'"&data&"'!A"&ROWS($A$1:A1)+5),$B12,INDIRECT("'"&data&"'!"&ADDRESS(ROWS($A$1:A1)+5,COLUMNS($A$1:A1)+3))))


So this might get crazy with above but is there a way to do a IF/MATCH type addition to this formula off a drop down?

So in this case:

- In C5 I have my drop down list and each names are identical to the tabs that are being rolled up. Say I just want to choose from drop for that specific name and then a “Total” drop down option as well?
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top