Sumproduct SumIF Indirect functions across multiple tabs

[KRE]

I have 12 tabs for each month of the year. I'm trying to return a name that is listed in a cell tied to a date range on my summary page. See below. I know that the reason this formula isn't working is because a name isn't a value but i'm not sure what formula would work?

Each tab is similar as below.

My formula should return Esteban Mendoz in column K of my summary because it matches to the week number.

 

[kweaver]

I think this ugly formula might work (needs the rest of the month abbrv and the range on column I) with a bit more info about the layout, there's likely a much neater VBA formula.

=INDIRECT("'"&CHOOSE(MONTH(C2),"Jan","Feb","Mar")&"'!O"&MATCH(1,--(A2=INDIRECT("'"&CHOOSE(MONTH(C2),"Jan","Feb","Mar")&"'!I1:I10")),0))

I'm not a big fan of INDIRECT as a volatile function.

 

[Fluff]

Hi & welcome to MrExcel.
How about

=LET(h,HSTACK(Jan:Dec!I2:O1000),FILTER(TAKE(h,,-1),INDEX(h,,1)=A2))

 

[KRE]

I think this ugly formula might work (needs the rest of the month abbrv and the range on column I) with a bit more info about the layout, there's likely a much neater VBA formula.

=INDIRECT("'"&CHOOSE(MONTH(C2),"Jan","Feb","Mar")&"'!O"&MATCH(1,--(A2=INDIRECT("'"&CHOOSE(MONTH(C2),"Jan","Feb","Mar")&"'!I1:I10")),0))

I'm not a big fan of INDIRECT as a volatile function.

This didn't work unfortunately or I didn't type it right. What I'm basically trying to do is Look in my summary tab for cell A1 which is "1" and also cell K1 which is "Foreman 1" and find throughout all my month tabs labeled Jan, Feb, etc in the range I2:O7 of each and match the "1" and match "foreman 1" and return Esteban Melendez in my summary sheet.

The formula i tried to replicate from you is listed below...

=INDIRECT(" ' "&CHOOSE(MONTH(A2),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")&"'!O"&MATCH(1,--(A2=INDIRECT(" ' "&CHOOSE(MONTH(A2),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")&"'!I3:I7")),0))

 

[KRE]

Hi & welcome to MrExcel.
How about

=LET(h,HSTACK(Jan:Dec!I2:O1000),FILTER(TAKE(h,,-1),INDEX(h,,1)=A2))

I didn't get this formula to work either
=LET(h,HSTACK(Jan:Dec!I2:O7),FILTER(TAKE(h, ,-1),INDEX(h, ,1)=A3))

I shortened my range to I2:O7 because that's all the bigger the range needed to be. I felt like I typed everything else the same.

 

[Fluff]

Oops, it should be VSTACK

=LET(h,VSTACK(Jan:Dec!I2:O7),FILTER(TAKE(h,,-1),INDEX(h,,1)=A2))

 

[KRE]

Oops, it should be VSTACK

=LET(h,VSTACK(Jan:Dec!I2:O7),FILTER(TAKE(h,,-1),INDEX(h,,1)=A2))

That worked Fluff! Thank you. I copied it across to my other foreman name columns and got it to work by changing my range from O7 to P7 and from P7 to Q7. But when I copied it down referencing A3 instead of A2 and A4 instead of A3 etc I was getting a spill error. If I deleted the row below it then my next row would pop up "Kailey". But I wondered why I got the spill error. Is it because of the "h" in the formula or is it the "-1" and the "1" in the formula

 

[Fluff]

Did you lock the range like

=LET(h,VSTACK(Jan:Dec!I$2:O$7),FILTER(TAKE(h,,-1),INDEX(h,,1)=A2))

 

[KRE]

sure did. It's like it's referencing the cell above and below it. and some come back with #calc!. not all the formulas show the spill either. it's quite random

 

[KRE]

I got the Calc errors to go away but not the spill errors and it's only on the first 12 rows