Automatically populate name into formula based on what row it's on?

I would like to use the following formula:

=SUM(COUNTIF(INDIRECT({"'ABC'!8:8","'ABC'!17:17","'ABC'!26:26","'ABC'!35:35","'ABC'!44:44"}),'CBA'!A5))

Where ABC is worksheet 1 and CBA is worksheet 2, and the formula is used in cell C5. Currently, the 'CBA'!A5 portion just tells the formula what to look for in the fields listed prior, but it only works for what is in cell A5 (obviously), and I would like to sort the rows based on another column which would change what is in cell A5. I would like it to automatically change what cell it references to when I go down the column. For example, if I was putting the same formula in cell C11, I would like it to pull the info from A11 instead of A5.

Is there any way to do this? I'm hoping there's just a different way to write that single 'CBA'!A5 portion of the code.

Thanks

Yes that will do that, but what I'm saying if i then sort the rows by a certain column it will change what is in A5, A6, etc. so the result will change as well. I need the value to stay the same, no matter what row the info is in. Does that make more sense? Perhaps maybe there's someway to change just the number of the of the cell of the portion of the code I'm referring to? Like say it is originally on row 5, in cell A5, then I sort it and it ends up on row 7 (A7), the code will automatically change from looking at A5 to looking at A7. Is this possible?

I think the only way to do this is to change the cell references that you use to absolute instead of relative ($A$5 instead of A5) but this will require you entering the formulas manually as it will not be changed when you copy the formula down. Let me know if you need a code that will replace the cell references in your existing formula from relative to absolute.

But won't that still keep $A$5 in the code even if I switch it to another row? I'm looking for it to change to, say, A7 if I switch it from the 5th row to the 7th row. Know what I mean?

it will change if you include the column which has the formula with the sort range try it.
otherwise could you post a sample workbook with your data and expected results?

how do i make a workbook?

A workbook is the excel file that you are asking questions about !!!!!!!...