I have several sheets with tabels using VLOOKUP in adjecent collums.

eg.
=VLOOKUP($A3;'DATA'!$A$6:$DC$283;56) followed by
=VLOOKUP($A3;'DATA'!$A$6:$DC$283;59) etc.

Now I I need to insert extra collums in both the 'DATA', as well as the
VLOOKUP sheets.
However when I insert new collums in the 'DATA' sheets the collumn number in
the VLOOKUP formula stays the same.
I need the collum numbers in the formula to change with the new place in the
'DATA' sheet.

eg.
insert 3 new collums on collumn nr 45
=VLOOKUP($A3;'DATA'!$A$6:$DC$283;56) becomes
=VLOOKUP($A3;'DATA'!$A$6:$DC$283;59)
And =VLOOKUP($A3;'DATA'!$A$6:$DC$283;59) becomes
=VLOOKUP($A3;'DATA'!$A$6:$DC$283;62)

There are just too many to change manually..
Any way to make this work automatically?

You can do Find & Replace (CTRL-H) - highlight the offending columns,
then CTRL-H and:

Find What: ;56
Replace With: ;59
Click Replace All

CTRL-H again, then:

Find What: ;59
Replace With: ;62
Click Replace All

Hope this helps.

Pete

On May 21, 8:43*am, RickJB wrote:
I have several sheets with tabels using VLOOKUP in adjecent collums.

eg.
=VLOOKUP($A3;'DATA'!$A$6:$DC$283;56) followed by
=VLOOKUP($A3;'DATA'!$A$6:$DC$283;59) etc.

Now I I need to insert extra collums in both the 'DATA', as well as the
VLOOKUP sheets.
However when I insert new collums in the 'DATA' sheets the collumn number in
the VLOOKUP formula stays the same.
I need the collum numbers in the formula to change with the new place in the
'DATA' sheet.

eg.
insert 3 new collums on collumn nr 45
=VLOOKUP($A3;'DATA'!$A$6:$DC$283;56) becomes
=VLOOKUP($A3;'DATA'!$A$6:$DC$283;59)
And =VLOOKUP($A3;'DATA'!$A$6:$DC$283;59) becomes
=VLOOKUP($A3;'DATA'!$A$6:$DC$283;62)

There are just too many to change manually..
Any way to make this work automatically?

Thanks Pete,

But to do this one collumn at the time will take days and will be very hard
error-prone.
Is this the only way or is it maybe possible to do this for all collums at
the same time?
I mean when inserting new collumns the collumn numbers in the formula will
automatically add the number of inserted collums.

Any tips?


"Pete_UK" wrote:

You can do Find & Replace (CTRL-H) - highlight the offending columns,
then CTRL-H and:

Find What: ;56
Replace With: ;59
Click Replace All

CTRL-H again, then:

Find What: ;59
Replace With: ;62
Click Replace All

Hope this helps.

Pete

On May 21, 8:43 am, RickJB wrote:
I have several sheets with tabels using VLOOKUP in adjecent collums.

eg.
=VLOOKUP($A3;'DATA'!$A$6:$DC$283;56) followed by
=VLOOKUP($A3;'DATA'!$A$6:$DC$283;59) etc.

Now I I need to insert extra collums in both the 'DATA', as well as the
VLOOKUP sheets.
However when I insert new collums in the 'DATA' sheets the collumn number in
the VLOOKUP formula stays the same.
I need the collum numbers in the formula to change with the new place in the
'DATA' sheet.

eg.
insert 3 new collums on collumn nr 45
=VLOOKUP($A3;'DATA'!$A$6:$DC$283;56) becomes
=VLOOKUP($A3;'DATA'!$A$6:$DC$283;59)
And =VLOOKUP($A3;'DATA'!$A$6:$DC$283;59) becomes
=VLOOKUP($A3;'DATA'!$A$6:$DC$283;62)

There are just too many to change manually..
Any way to make this work automatically?

You might consider using index/match

Eg for: =VLOOKUP($A3;'DATA'!$A$6:$DC$283;56)
the equivalent index/match could be:
=INDEX(Data!BD:BD,MATCH($A3,Data!$A:$A,0))

Any subsequent insertion of new columns in sheet: Data
would not disrupt the formula's return
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---