using text in cell to form part of formula.

[jamie_read]

Afternoon All,

iv searched quite a bit but cant find a work around for the formula i am trying to create and am hoping some one could point me in the right direction.

i am currently using the the following formula:

=SUMIFS(Sheet1!D:D,Sheet1!G:G,"billy",Sheet1!E:E,"Smart")

this works great but i loose the ability to auto fill. i would like to replace the "Smart" with a Cell Reference. like below

=SUMIFS(Sheet1!D:D,Sheet1!G:G,"billy",Sheet1!E:E,INDIRECT(A2)

The formula seems to be accepted but a 0 value is shown.

Thanks for any help you can give

Jay

 

[Norie]

Jay

Why are you using INDIRECT, doesn't A2 on it's own work?

What's actually in A2?

 

[jamie_read]

doh,

i tried A2 and it didn't work - because i left the " in place "A2", i just removed the " and the formula works great. thank you. it was driving me crazy.