Where did formula 1 get its name from?

Dragging a formula down but letting the the sheet refrence change each time

• QUESTION: I want to drag a formula down that has refrences to different sheets i am working on. up to about 50 sheets or so. The formula I am using so far is =SUM('sheet1'!$AP$23) The cell AP23 has a total from each sheet and the formula is for a table with all the total from all the sheets. Thats why AP23 has the $ signs. Is there any way to make the 'sheet1' change when i drag the formula down? so that the next formulas would be =SUM('sheet2'!$AP$23)=SUM('sheet3'!$AP$23)=SUM('sheet4'!$AP$23)and so on. The totals in the sheets will be constantly changing, is there a way of automatically sorting the sheet so when the totals change the table with all these formulas will put the sheet with the highest total at the top and lowest at the bottom? or will this need to be done manually? Thanks for your help ANSWER: there is no provision into use relative SHEET address. Since you have numerical sheet names, you can calculate the sheet name =SUM('sheet1'!$AP$23) would become =Indirect("Sheet"&row(A1)&"!AP23") the sum is unnecessary since you are linking to only one cell. since "!AP23" is now a string argument rather than a cell reference, it doesn't need the $ signs to make it absolute. It only become a cell reference when it is evaluated by the Indirect function. You can now drag dill this down a column. Assume you formulas are in cell A11:A50. In M1 you could put =Large($A$11:$A$50,row(A1)) then drag fill this down until you have filled 40 cells. This will give you an ordered list. row(A1) returns the row of the cell referenced. In this case, it returns a 1 since A1 is in row1. when you drag fill it down the column, the A1 will Become A2 and row(A2) will return a 2. So this is a method of feeding progressive numbers to both the Indirect function to concatenate the sequential sheet number and to the Large function to tell it you want the 1st (largest number), then the 2nd (2nd largest number) and so forth. ---------- FOLLOW-UP ---------- QUESTION: I have a few more problems for you! If i fully explain what im doing then you might get a better understanding of where im coming from! I am creating a football prediction spreadsheet for the forth coming football season (maybe you call it soccer!) Anyway, the 50 or so sheets are going to be of peoples predicted scores, with a cell (AP23) with the total amount of points they have at any time. 5 points for correct score and 2 points for corrct result. I will enter everyones predictions in to all the workheets. I have s seperate worksheet for the actual results of the matches which i will fill in all season after the games. I have a formula, which im quite proud of(=IF(AND(ISBLANK(Results!D2),ISBLANK(Results!E2)),0,IF(AND(ISBLANK(D2),ISBLANK(E2)),0,IF(AND(D2=Results!D2,E2=Results!E2),5,IF(AND(D2=E2,Results!D2=Results!E2),2,IF(OR(AND(D2>E2,Results!D2>Results!E2),AND(D2<E2,Results!D2<Results!E2)),2,0)))))), that will input the 5 points,2 points or 0 points depending on prediction versus actual reult. These points are then added together in AP23. I then have another worksheet that im/you are working on the will collate everyones totals and put them in order of who has the most points. This will be printed out on a weekly basis for bragging rights! Still with me?! As i've said, i have 50 worksheet titles sheet1 to sheet50, but when i start enerting peoples predictions i will be renaming the worksheets with the persons name. will the formula you gave me previosly change when i rename the worksheets? Is there a way of adding this name into a formula as well as the points total, so that in the same cell it would read, for example [Paul Collins 52]? or [Sheet1 52] as it would read before the name changes? because currently i have a cell with what will be the persons name but currently just reads sheet1 or whatever and the adjacent cell has the formular you gave me. does this makes sense? Sorry if this is overlly complicated and seemingly pointless to you!! But i am very gratefull for your help Paul

• Answer:

  The formula I gave you only works if the sheets have numbers. The formula will not adjust if you rename the sheet because the sheet name is a string and not an actual reference to a sheet. What you could do is this in the first cell where you want the link, put in ="Sheet1"&row(A1)&"!$AP$23 then drag fill this down the column for 50 rows. Now select those 50 cells and do Edit=>copy without moving the selection do Edit=>paste special and select values. Now the first cell will display =Sheet1!$AP$23 With those 50 cells still select, do Edit=>Replace and in the replace what put in an Equal sign (=). In the replace with box put in another equal sign (=) and click OK. this will replace the equal sign with an equal sign, but when it does it,will re-evaluate each cell and turn the text string into an actual formula. this will refer to the 50 sheets. Now when you rename the sheet, the sheet name will change in the formula. Alternatively, if you don't care about the formula until you change the sheet name to match the persons name. you can use Assume B9 has Paul Collins and in C9 you want to refer to his sheet which is now Named Paul Collins. =Indirect("'"&B9&"'!$AP$23") this will show a #Ref error until the a sheet named John Exists. You can do this =if(iserror(Indirect("'"&B9&"!'$AP$23")),0,Indirect("'"&B9&"!'$AP$23")) to display a zero or to make the cell appear blank =if(iserror(Indirect(B9&"!$AP$23")),"",Indirect(B9&"!$AP$23")) If you want to show [Paul Collins 52] ="["&B9&" "&Indirect("'"&B9&"'!$AP$23")&"]" You can combine this with the other formulas - so mix and match to achieve exactly what you want. If that hasn't given you want you need or the tools to get what you need, then just post a followup with the specifics of where I missed the boat/misunderstood or if I was unclear.