using text in cell to form part of formula.

jamie_read

New Member
Joined
Oct 12, 2018
Messages
4
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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Jay

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

What's actually in A2?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

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
Back
Top