Im sure the answer is out there!!!!

[JoshuaMars]

Hi there Excel users,

Im trying to complete this formula.

Im sure its easy for someone out there with more expertise then me.

=SUMIF('Sports Daily'!B390:'Sports Daily'!B466,"=*ATP*",'Sports Daily'!C390:'Sports Daily'!C466)

What Im trying to work out is the ATP area of it. ATP is in a cell that comes from a workshet name.
=IF(COUNTA(SHEETS)>=ROW($A1),INDEX(SHEETS, ROW($A1)), "")

So basicly, the ATP is a workshet name, and if i change the name of the worksheet, the cell changes and so does the SUMIF formula.

HELP me please anyone. Im lost.

 

[hatman]

Your question is not all that clear... but are you maybe looking for the Indirect() function here?

 

[Peter_SSs]

Joshua

I also am finding your requirement hard to understand. However, perhaps you could try this:
=SUMIF('Sports Daily'!B390:B466,"*ATP*",'Sports Daily'!C390:C466)

 

[JoshuaMars]

Thanks Peter,

My formula is now as follows.
=SUMIF('Sports Daily'!B390:B466,"=*ATP*",'Sports Daily'!C390:C466)

What Im trying to do is SUM the total of Cells B390 to B466 in the rows with the word ATP in it in the worksheet titled Spors Daily.

The cell with the word ATP has the current formula.
=IF(COUNTA(SHEETS)>=ROW($A1),INDEX(SHEETS, ROW($A1)), "")

As the word ATP is also the name of a workshet.

But what I would like to is change the top formula it so that if I change the ATP workshet to another name it also changes the ATP in the top formula.

At the moment, the seceond formula automaticly changes.

I was thinking that maybe I somehow join both formulas.

HELP!!!!!!!

 

[hatman]

Is THIS what you are looking for? Trying to count all cells that contain the Name of sheet ATP no matter what it gets changed to?

=SUMIF('Sports Daily'!B390:'Sports Daily'!B466,"=*"&RIGHT(CELL("filename",'ATP!'!A1),LEN(CELL("filename",'ATP!'!A1))-FIND("]",CELL("filename",'ATP!'!A1)))&"*",'Sports Daily'!C390:'Sports Daily'!C466)

 

[JoshuaMars]

hmmmm, close. I hope this is the right explaination.

Trying to count all cells that contain the Name of sheet "x" in the sheet "Sports Daily" no matter what sheet "x" it gets changed to.

At the moment Ive worked out
=SUMIF('Sports Daily'!B390:B466,"=*x*",'Sports Daily'!C390:C466)

Is it possible to be a formula?

Thanks in advance.

 

[hatman]

Yup, that's what my proposed solution does... assuming that the sheet is named ATP at this moment...

EDIT: NOPE! I got over-confident, and forgot that you can't use wildcards in the Countif() formula... i'll post something more useful in a sec.

 

[hatman]

Try THIS

=SUM(IF(ISNUMBER(SEARCH(RIGHT(CELL("filename",'ATP!'!A1),LEN(CELL("filename",'ATP!'!A1))-FIND("]",CELL("filename",'ATP!'!A1))),'Sports Daily'!B390:B466)),'Sports Daily'!C390:C466,0))

confirmed with Ctrl+Shift+Enter rather than just Enter.

 

[JoshuaMars]

I tried to enter this code but it came up with a syntex error.

I already have acode in the worksheet. How do I add another?

Thanks

 

[hatman]

Sorry, I see I added a couple of extra !'s in there... try this EXACTLY... and be sure that your workbook is saved... otherwise this won;t work.

Not sure what you mean by "acode".