Combine INDEX and MATCH across Worksheets

[filriyadh]

Hi All,

I've been using excel for about 10 odd years now, only recently started to get in to some of the beefy stuff. This site has helped quite some.

Im basically making a roster file for our school clinic so that reports can be generated automatically and there are less hand-written records around.

I am having trouble with the 'Supplies' sheet, where I am attempting to sum up the quantities (column k) adjacent to all occurrences of a supply in Column J sheets 1 through 31. So that we can know how much of x supply was used in the whole month and so on.

Please help. Sorry if I was unclear in my writing.

=INDEX('24'!$J$3:$K$106,MATCH(B5,'24'!$J$3:$J$106,FALSE),2) Returns a value, but that is because sheet 24 has a value for b5, problem is if i use +INDEX and any of the sheets dont have a value for B5, the result is either VALUE or N/A
=SUMIF('1:31'!J5:J105,B6,'1:31'!K5:K105) gives a VALUE! error

I've also tried =INDEX('1:31'!$J$3:$K$106,MATCH(B5,'1:31'!$J$3:$J$106,FALSE),2) with no luck

 

[mvptomlinson]

PS - Thank your Steelers defense for doing so well yesterday. My fantasy football team appreciates it!

 

[T. Valko]

Steelers defense

They're kind of "soft" compared to last year. I'm hoping they can tighten things up!

 

[Joe4]

I moved mvptomlinson's reply from the other thread here to this thread (post #6), and deleted the other thread.

Please do not start new threads on the same topic. All clarifications, bumps, and follow-ups should be posted back to the original thread (see rules 21-23 here):
http://www.mrexcel.com/forum/showthread.php?t=127080