Find Data From Multiple Worksheets

Rich9016

Board Regular
Joined
Dec 21, 2004
Messages
220
Hi Guys,
I thought I remembered this formula from another post I started and I thought I could re-create it but I seem to be doing something wrong. I am using the following formula:

=SUMPRODUCT(SUMIF(INDIRECT(AO583:AO584&$C$10),A583,INDIRECT(AO583:AO584&"!"&CELL("Address",F$12))))

I kept the range short for the example, so AO583:AO584 is where I have listed the names of all of the worksheets I have.

Basically I'm trying to extract a value from different parts of a large number of worksheets. It worked before on something else I was working on but I can't seem to get it to work right now. Can anyone see if I'm doing anyhing wrong?

C10 is the cell I want the formula to look at first
A583 is the cell it should match
F12 is the cell which contains the information I'd like to extract

I hope that makes sense. I'm not sure if I've put the right information. If it doesn't work I'll just have to deal with it but if anyone sees anything obvious please let me know.

Thank you!
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Hi,

A guess:

=SUMPRODUCT(SUMIF(INDIRECT(AO583:AO584&"!$C$10"),A583,INDIRECT(AO583:AO584&"!"&CELL("Address",F$12))))
 

Rich9016

Board Regular
Joined
Dec 21, 2004
Messages
220
that doesn't seem to do it. it leaves me with a zero. ugh, it's going to be something small and stupid that i forgot to put in...
 

Rich9016

Board Regular
Joined
Dec 21, 2004
Messages
220
oh! i think i made a stupid mistake. this formula should give me a number, but i'm looking for it to return text. i guess that's why i got 0. if i were to change it so that it returns text instead, does anyone know how?
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
You post a formula with SUMPRODUCT and SUMIF, and then you say you want to return text???

Please explain, with words, what you actually want to do.
 

Forum statistics

Threads
1,141,678
Messages
5,707,782
Members
421,527
Latest member
Tamiwsw

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
Top