Vlookup Problem on Excel

  • Thread starter Thread starter worzell
  • Start date Start date
W

worzell

Hello!

I have a spreadsheet with mulitple sheets. On sheet2 some of the cells
in the rows are updated by vlookup from sheet1 and some are manually
keyed. The problem is that when sheet1 is changed the manually typed
cells don't change correspondingly i.e. sheet2 looks like this

A B C
1 Vlookup1 Vlookup1 keyed1
2 Vlookup2 Vlookup2 keyed2
3 Vlookup3 Vlookup3 keyed3
4 Vlookup4 Vlookup4 keyed4

If the information for Vlookup2 is changed on sheet1 and that
particular field moves off of sheet2, sheet2 looks like this:

A B C
1 Vlookup1 Vlookup1 keyed1
2 Vlookup3 Vlookup3 keyed2
3 Vlookup4 Vlookup4 keyed3
4 keyed4

And I need it to look like this:

A B C
1 Vlookup1 Vlookup1 keyed1
2 Vlookup3 Vlookup3 keyed3
3 Vlookup4 Vlookup4 keyed4
4

Any ideas on how I can get this to sync, I thought it was maybe a data
sort issue but now not so sure.

Any help gratefully accepted.

Many Thanks,

Anthony
 
You are selecting two cells Vlookup2 to move and
saying 'move up'. So accordingly what excel is doing is 100% correcct.
If you want to look like what you say, then after moving
those two celss, delete cell 'keyed2' and select 'Move cells up'.

Sharad
 
Sorry not sure if I was totally clear. I will try and give a bit of
background and hopefully make more sense!!!

Although what Excel does is technically correct, as sheet1 is updated
so regularly it is not really practical to move rows up/delete rows in
sheet2 manually, as they could in fact go down as well as up or
disappear entirely.

Basically it is a spreadsheet for work based on a lead's probabilty of
becoming a live job. All lead are held on sheet1 but the % on sheet1
has to be over 75% in order for the lead to qualify for a place on
sheet2. Obviously these percentages change regularly so a lead's place
in the table will also change i.e.:

A B C
1 Vlookup1 Vlookup1(100%) keyed1
2 Vlookup2 Vlookup2( 90%) keyed2
3 Vlookup3 Vlookup3( 90%) keyed3
4 Vlookup4 Vlookup4( 80%) keyed4

If Vlookup1 becomes only 80% probable and Vlookup4 becomes 60%
probable on sheet1 then sheet2 will look like this:

A B C
1 Vlookup2 Vlookup2( 90%) keyed1
2 Vlookup3 Vlookup3( 90%) keyed2
3 Vlookup1 Vlookup1( 80%) keyed3
4 keyed4

And the whole thing comes out of sync so it is kind of impossible /
impractical to try and correct it all manually each time. Would it be
possible to somehow link cell C1 to cell A1 so that it shifts in sync
or something along those lines????

Hope this makes it a bit clearer, please let me know if you need more.

Many Thanks,
 
Back
Top