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

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

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

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

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

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