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.

 

[JoshuaMars]

Sorry hatman,

Try what exactly?

I meant a code.

 

[hatman]

Forgot to paste in the formula...

=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))

What type of code do you mean?

 

[JoshuaMars]

This is already in the "This Workbook" code area.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.CalculateFull
End Sub

How do I add your code?
=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))

Thanks in advance.

MARSY

 

[hatman]

My "code" is a worksheet formula... simply paste it into the formula bar in the cell where your original formula was... then press Ctrl+Alt+Enter to confirm... it should satisfy everything you have asked so far...

 

[JoshuaMars]

Hatman, thanks for your efforts.

Still didn’t work.

I’m trying to work it out for myself as well. Only way to learn.

If it ok, would you mind if you could break down your formula and see if its what I’m looking for. I might have to fiddle around with it to get what I need.

=SUM(IF(ISNUMBER(SEARCH(RIGHT(CELL("filename",ATP!A1),LEN(CELL("filename",ATP!A2))-FIND("]",CELL("filename",ATP!A2))),'Sports Daily'!B544:B620)),'Sports Daily'!C544:C620,0))

Thanks in advance.

 

[hatman]

=SUM(IF(ISNUMBER(SEARCH(RIGHT(CELL("filename",ATP!A1),LEN(CELL("filename",ATP!A2))-FIND("]",CELL("filename",ATP!A2))),'Sports Daily'!B544:B620)),'Sports Daily'!C544:C620,0))

Working from the inside out...

The Cell() function returns information about the workbook. In this case, I am retrieveing the file/pathname of the workbook (BTW: the file needs to be saved, other wsie, none of this will work). For example:

=CELL("filename",ATP!A1)

will return D:\Documents and Settings\sasurpa\Desktop\[test1.xls]ATP...

LEN(CELL("filename",ATP!A2))

returns the length of the file/pathname = 56...

FIND("]",CELL("filename",ATP!A2))

determines the location of the ] in the string = 53...

RIGHT(CELL("filename",ATP!A1),LEN(CELL("filename",ATP!A2))-FIND("]",CELL("filename",ATP!A2)))

will return the rightmost N characters of the string... in this case N = 56-53 = 3, so the result is ATP: you sheetname. The nature of this set of formulas is that when you change the name of Sheet ATP, the result updates so it always returns the sheet name.

SEARCH(RIGHT(CELL("filename",ATP!A1),LEN(CELL("filename",ATP!A2))-FIND("]",CELL("filename",ATP!A2))),'Sports Daily'!B544:B620)

will determine the location of ATP in each member of 'Sports Daily'!B544:B620. The result (when you confirm with Ctrl+Shift+Enter) is an array of 76 results, on for each cell. When ATP is found in a cell, the result is an integer indicating the start position of the search string. When ATP is NOT found, the result is #VALUE.

ISNUMBER(SEARCH(RIGHT(CELL("filename",ATP!A1),LEN(CELL("filename",ATP!A2))-FIND("]",CELL("filename",ATP!A2))),'Sports Daily'!B544:B620))

will return an array of 76 results, either True or False based on whether the reult of the previous is a number or not.

IF(ISNUMBER(SEARCH(RIGHT(CELL("filename",ATP!A1),LEN(CELL("filename",ATP!A2))-FIND("]",CELL("filename",ATP!A2))),'Sports Daily'!B544:B620)),'Sports Daily'!C544:C620,0)

will return an array of 76 results, either 0 if ATP was not found in the cell, or the corresponding numerical value located in 'Sports Daily'!C544:C620 when ATP was found in the cell.

SUM(IF(ISNUMBER(SEARCH(RIGHT(CELL("filename",ATP!A1),LEN(CELL("filename",ATP!A2))-FIND("]",CELL("filename",ATP!A2))),'Sports Daily'!B544:B620)),'Sports Daily'!C544:C620,0))

simply sums up the numerical results of the 76 member array.

2 things to be aware of: 1) your file must be saved for this to work. 2) You must confirm with Ctrl+Shift+Enter, which (when accepted by Excel) will cause curly brackets {} to appear around the formula, like this

{=SUM(IF(ISNUMBER(SEARCH(RIGHT(CELL("filename",ATP!A1),LEN(CELL("filename",ATP!A2))-FIND("]",CELL("filename",ATP!A2))),'Sports Daily'!B544:B620)),'Sports Daily'!C544:C620,0))}

 

[Peter_SSs]

Joshua

For the cell that contains this formula:
=IF(COUNTA(SHEETS)>=ROW($A1),INDEX(SHEETS, ROW($A1)), "")
1. What cell address is it?
2. In what sheet name?
3. Does it return just ATP (or substitute sheet name) or does it return other text as well as the ATP?

 

[JoshuaMars]

Hatman,
thanks heaps for your efforts.

Still doesnt work and to be quite honest, I cant understand the formula. Hence the reason why I asked.

Im still a novice at Excel, but Im willing to learn but I think that formula is still over my head.

Im sure it will work once I can understand excel more. Im bookmarking this topic for later when I gather more experience at excel.

Peter,
My cell address is C5 where I want this formula.
Sheet name is Centerbet_06-07.
The forumula in this cell is supposed to sum up a column in worksheet "Sports Daily"between C5 and C81 only rows where the word ATP appears in column B

Hope you understand?

MARSY

 

[Peter_SSs]

My cell address is C5 where I want this formula.
Sheet name is Centerbet_06-07.
The forumula in this cell is supposed to sum up a column in worksheet "Sports Daily"between C5 and C81 only rows where the word ATP appears in column B

Hope you understand?

MARSY

I think that I do understand this. However, I don't fully understand about the formula
=IF(COUNTA(SHEETS)>=ROW($A1),INDEX(SHEETS, ROW($A1)), "")
and that is why I asked three questions about it. As yet you haven't answered those three questions - refer my last post.

 

[JoshuaMars]

Peter,

1. What cell address is it?
Answer - C5

2. In what sheet name?
Answer - Weekly

3. Does it return just ATP (or substitute sheet name) or does it return other text as well as the ATP?
Answer ATP.