Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
changing cell references
Is there an easy way to change a cell ref without having to go in the cell to
change that ref IE A B 1 sheet2!A5 sheet2!B5 I want to change those cell ref to 1 sheet2!A6 sheet2!B5 Using an other cell to icrease the lookup reference |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
changing cell references
Your question is not too clear fro me. But I think you might find the
INDIRECT function of use. Suppose A1 has the value B2 then =INDIRECT("Sheet2!"&A1) will cause Excel to display the value in Sheet2!B2 best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Sly" wrote in message ... Is there an easy way to change a cell ref without having to go in the cell to change that ref IE A B 1 sheet2!A5 sheet2!B5 I want to change those cell ref to 1 sheet2!A6 sheet2!B5 Using an other cell to icrease the lookup reference |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
changing cell references
I reformulate
A B 1 sheet2!A5 sheet2!B5 I used find and replacle to change cell ref IE find 5 replace 6 in order to change de cell ref is there an easier way ? Thanks for your help "Bernard Liengme" wrote: Your question is not too clear fro me. But I think you might find the INDIRECT function of use. Suppose A1 has the value B2 then =INDIRECT("Sheet2!"&A1) will cause Excel to display the value in Sheet2!B2 best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Sly" wrote in message ... Is there an easy way to change a cell ref without having to go in the cell to change that ref IE A B 1 sheet2!A5 sheet2!B5 I want to change those cell ref to 1 sheet2!A6 sheet2!B5 Using an other cell to icrease the lookup reference |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
changing cell references
If you're trying to change a number of references, you can us 'Find and
Replace'. For example, you're trying to change a lot of A5 references: 1) Select the range of cells you want to change. 2) Select Ctrl-H. 3) Enter what you want to change, ie, A5 4) Enter what you want to replace it with, ie, B6 Press enter. Hope that's what you were shooting for. "Sly" wrote: I reformulate A B 1 sheet2!A5 sheet2!B5 I used find and replacle to change cell ref IE find 5 replace 6 in order to change de cell ref is there an easier way ? Thanks for your help "Bernard Liengme" wrote: Your question is not too clear fro me. But I think you might find the INDIRECT function of use. Suppose A1 has the value B2 then =INDIRECT("Sheet2!"&A1) will cause Excel to display the value in Sheet2!B2 best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Sly" wrote in message ... Is there an easy way to change a cell ref without having to go in the cell to change that ref IE A B 1 sheet2!A5 sheet2!B5 I want to change those cell ref to 1 sheet2!A6 sheet2!B5 Using an other cell to icrease the lookup reference |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
changing cell references
An easier way might be if you constructed your *original* formulas
differently. Say you started with these 2 formulas instead: =INDEX(Sheet2!A:A,A1) =INDEX(Sheet2!B:B,B1) NOW, in A1 and B1 you entered the row that you want your formulas to reference. Say in A1 you enter 5, And in B1 you enter 6. So, to change the row number, simply change the number in A1 and B1. You could also construct your formulas to reference the *same* cell, *if* the row numbers were to be the same for all your formulas. =INDEX(Sheet2!A:A,A1) =INDEX(Sheet2!B:B,A1) =INDEX(Sheet2!C:C,A1) Then, changing the value in the single cell, A1, would change the reference in all your formulas at one time. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Sly" wrote in message ... I reformulate A B 1 sheet2!A5 sheet2!B5 I used find and replacle to change cell ref IE find 5 replace 6 in order to change de cell ref is there an easier way ? Thanks for your help "Bernard Liengme" wrote: Your question is not too clear fro me. But I think you might find the INDIRECT function of use. Suppose A1 has the value B2 then =INDIRECT("Sheet2!"&A1) will cause Excel to display the value in Sheet2!B2 best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Sly" wrote in message ... Is there an easy way to change a cell ref without having to go in the cell to change that ref IE A B 1 sheet2!A5 sheet2!B5 I want to change those cell ref to 1 sheet2!A6 sheet2!B5 Using an other cell to icrease the lookup reference |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
changing cell references
Thank you guys that was of great help
Much appreciated "Ragdyer" wrote: An easier way might be if you constructed your *original* formulas differently. Say you started with these 2 formulas instead: =INDEX(Sheet2!A:A,A1) =INDEX(Sheet2!B:B,B1) NOW, in A1 and B1 you entered the row that you want your formulas to reference. Say in A1 you enter 5, And in B1 you enter 6. So, to change the row number, simply change the number in A1 and B1. You could also construct your formulas to reference the *same* cell, *if* the row numbers were to be the same for all your formulas. =INDEX(Sheet2!A:A,A1) =INDEX(Sheet2!B:B,A1) =INDEX(Sheet2!C:C,A1) Then, changing the value in the single cell, A1, would change the reference in all your formulas at one time. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Sly" wrote in message ... I reformulate A B 1 sheet2!A5 sheet2!B5 I used find and replacle to change cell ref IE find 5 replace 6 in order to change de cell ref is there an easier way ? Thanks for your help "Bernard Liengme" wrote: Your question is not too clear fro me. But I think you might find the INDIRECT function of use. Suppose A1 has the value B2 then =INDIRECT("Sheet2!"&A1) will cause Excel to display the value in Sheet2!B2 best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Sly" wrote in message ... Is there an easy way to change a cell ref without having to go in the cell to change that ref IE A B 1 sheet2!A5 sheet2!B5 I want to change those cell ref to 1 sheet2!A6 sheet2!B5 Using an other cell to icrease the lookup reference |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell Absolute $ References Keep Changing themselves | Excel Discussion (Misc queries) | |||
changing cell references | Excel Discussion (Misc queries) | |||
Changing cell references automatically | New Users to Excel | |||
Changing cell references | Excel Discussion (Misc queries) | |||
Changing Cell References in Formulas | Excel Worksheet Functions |