obtaining the cell values for Row and Column Refereces

[Gealer]

I have this formula =SUMIFS('Staff Manager DL'!$Q:$Q,'Staff Manager DL'!$L:$L,******,'Staff Manager DL'!$N:$N,'Working Patterns'!I3,'Staff Manager DL'!$J:$J,'Working Patterns'!K$1))

Where there is ****** I need to obtain this information from 2 different cells.

I have the Column Ref in K2 (lets say that is A)
Then in J3 I have the row value (lets say 3)

So I need the ******** to say A3 and then the 3 will be the variable that will change when I drag it down.

 

[Gealer]

I have tried the concatenate(K2,J3) but this is still not working!?

 

[Gealer]

Does anyone have any idea on this?

 

[gaz_chops]

Try

=SUMIFS('Staff Manager DL'!$Q:$Q,'Staff Manager DL'!$L:$L,INDIRECT(K2&J3),'Staff Manager DL'!$N:$N,'Working Patterns'!I3,'Staff Manager DL'!$J:$J,'Working Patterns'!K$1))

 

[Norie]

You could use INDIRECT but the row number will not increment as you drag the formula down.

=SUMIFS('Staff Manager DL'!$Q:$Q,'Staff Manager DL'!$L:$L,INDIRECT(K2&J3),'Staff Manager DL'!$N:$N,'Working Patterns'!I3,'Staff Manager DL'!$J:$J,'Working Patterns'!K$1))

Which cell are you going to put the formula in?

 

[gaz_chops]

Think this works if you drag down,



=SUMIFS('Staff Manager DL'!$Q:$Q,'Staff Manager DL'!$L:$L,INDIRECT($K$2&$J$3+ROW(A1)-1),'Staff Manager DL'!$N:$N,'Working Patterns'!I3,'Staff Manager DL'!$J:$J,'Working Patterns'!K$1))

 

[Gealer]

Thanks for the reply.

In the end I managed to get around it with =SUMIFS('Staff Manager DL'!$I:$I,'Staff Manager DL'!$K:$K,INDEX($A:$G,$J3,K$2),'Staff Manager DL'!$F:$F,'Working Patterns'!$I3,'Staff Manager DL'!$B:$B,'Working Patterns'!K$1)

I substituted the A with an 1 (b=2 ect) and that worked as well.