Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I make a formula which follows a specific cell?
Is there any way to make excel follow the contents of a cell?
I have a league table where the totals change as points are gained and need a third table to collate the points from table one which is an archive whos values no longer chnage and table two which is the current league table where teams (and the respective cells in which the points values reside) move up and down according to results. If you require further info please do not hesitate to ask. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I make a formula which follows a specific cell?
Hi
Use Vlookup Assuming the 2 tables had 5 columns with the first being the team name and the last being the points, then =VLOOKUP(Team_Name,Sheet1!$A:$E,5,0)+VLOOKUP(Team_ Name,Sheet2!$A$E,5,0) With your Team Names starting in A2 on sheet3 it would be =VLOOKUP($A2,Sheet1!$A:$E,5,0)+VLOOKUP($A2,Sheet2! $A$E,5,0) copied down the sheet. Adjust ranges to suit. The 5 in the formula is the column offset from the name where the number of points are held. Adjust this also as you adjust the size of the lookup table. -- Regards Roger Govier "CJ" wrote in message ps.com... Is there any way to make excel follow the contents of a cell? I have a league table where the totals change as points are gained and need a third table to collate the points from table one which is an archive whos values no longer chnage and table two which is the current league table where teams (and the respective cells in which the points values reside) move up and down according to results. If you require further info please do not hesitate to ask. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I make a formula which follows a specific cell?
Name the cells you want, in the search box type name cells or cell reference
and you will se how to do this. If you name a cell say Team1Total then on another sheet you could type =Sheet1!Team1Total and get the result no matter where the named cell is. -- -John Northwest11 Please rate when your question is answered to help us and others know what is helpful. "CJ" wrote: Is there any way to make excel follow the contents of a cell? I have a league table where the totals change as points are gained and need a third table to collate the points from table one which is an archive whos values no longer chnage and table two which is the current league table where teams (and the respective cells in which the points values reside) move up and down according to results. If you require further info please do not hesitate to ask. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I make a formula which follows a specific cell?
On Feb 1, 11:19 am, "Roger Govier"
wrote: Hi Use Vlookup Assuming the 2 tables had 5 columns with the first being the team name and the last being the points, then =VLOOKUP(Team_Name,Sheet1!$A:$E,5,0)+VLOOKUP(Team_ Name,Sheet2!$A$E,5,0) With your Team Names starting in A2 on sheet3 it would be =VLOOKUP($A2,Sheet1!$A:$E,5,0)+VLOOKUP($A2,Sheet2! $A$E,5,0) copied down the sheet. Adjust ranges to suit. The 5 in the formula is the column offset from the name where the number of points are held. Adjust this also as you adjust the size of the lookup table. -- Regards Roger Govier "CJ" wrote in message ps.com... Is there any way to make excel follow the contents of a cell? I have a league table where the totals change as points are gained and need a third table to collate the points from table one which is an archive whos values no longer chnage and table two which is the current league table where teams (and the respective cells in which the points values reside) move up and down according to results. If you require further info please do not hesitate to ask.- Hide quoted text - - Show quoted text - Thak you for your help however this has not helped matters. The Vlookup function works well to collate total points but when a team moves, the value of the points move also and the vlookup formula only calculates the value of the named cell. I need some kind of formula which will follow a specific cell, perhaps i need a formula first which moves the cell first rather than re- arranging the values? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I make a formula which follows a specific cell?
Hi
the vlookup formula only calculates the value of the named cell. I don't understand what you are saying If VLookup finds TeamA on row 6, for example, then it would take the value E6. If the order of the Teams altered, and TeamA was now located in row 12, Vlookup would find it in A12 and take the value form E12. =VLOOKUP($A2,Sheet1!$A:$E,5,0)+VLOOKUP($A2,Sheet2! $A$E,5,0) If the team name is being held in column A of your "results" sheet, then even if you sorted that sheet and the team name order was changed, it would still find the correct team on the other 2 sheets -- Regards Roger Govier "CJ" wrote in message ps.com... On Feb 1, 11:19 am, "Roger Govier" wrote: Hi Use Vlookup Assuming the 2 tables had 5 columns with the first being the team name and the last being the points, then =VLOOKUP(Team_Name,Sheet1!$A:$E,5,0)+VLOOKUP(Team_ Name,Sheet2!$A$E,5,0) With your Team Names starting in A2 on sheet3 it would be =VLOOKUP($A2,Sheet1!$A:$E,5,0)+VLOOKUP($A2,Sheet2! $A$E,5,0) copied down the sheet. Adjust ranges to suit. The 5 in the formula is the column offset from the name where the number of points are held. Adjust this also as you adjust the size of the lookup table. -- Regards Roger Govier "CJ" wrote in message ps.com... Is there any way to make excel follow the contents of a cell? I have a league table where the totals change as points are gained and need a third table to collate the points from table one which is an archive whos values no longer chnage and table two which is the current league table where teams (and the respective cells in which the points values reside) move up and down according to results. If you require further info please do not hesitate to ask.- Hide quoted text - - Show quoted text - Thak you for your help however this has not helped matters. The Vlookup function works well to collate total points but when a team moves, the value of the points move also and the vlookup formula only calculates the value of the named cell. I need some kind of formula which will follow a specific cell, perhaps i need a formula first which moves the cell first rather than re- arranging the values? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using an offset formula for the reference in a relative reference | Excel Worksheet Functions | |||
Can I make a formula in Excel to display result in same cell? | Excel Worksheet Functions | |||
Possible Lookup Table | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
How do I make a cell date specific to input a value on a specific. | Excel Discussion (Misc queries) |