Jumping columns

[Lolly]

Using Excel 2000 and with three columns, A, B and C for example, after
entering a number in column B, is it possible to jump back to column A in
which there is a vlookup equation, and then jump forward two columns to
column C?

Your solution would be gratefully received.

[David McRitchie]

Hi Lolly,
It is so easy to rearrange columns. What is good for data entry is
usually in the order you want to look at them. Rearranging columns.
http://www.mvps.org/dmcritchie/excel...nd.htm#movecol

However for what you asked...
You can affect how the cursor moves by the Enter key (direction),
Tab key, arrow keys, and prevent entry into cell with cell protection in effect.

An event macro to
go to B in next row after entry in C
go to A in same column after entry in B
go to C after entry in A

I don't know if this will help you or frustrate you but the Event macro
will only move the cursor after entry. If you make a mistake the undo
key will return you the last change and it's previous value...

More information on Event macros in
http://www.mvps.org/dmcritchie/excel/event.htm

Install the following by right click on the sheet tab, then view code.

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Column
Case 3 'After Entry in C next row column B
Target.Offset(1, -1).Activate
Beep '-- may be distracting but intent is to indicate new row
Case 2 'After Entry in B same row column A
Target.Offset(0, -1).Activate
Case 1 'After Entry in A same row Column C
Target.Offset(0, 2).Activate
End Select
End Sub

If you make a mistake use the Undo key Ctrl+Z to return from your last change was made.

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Lolly" <Lolly@discussions.microsoft.com> wrote in message news:DC976DB6-78E9-444E-958E-50704166170D@microsoft.com...
> Using Excel 2000 and with three columns, A, B and C for example, after
> entering a number in column B, is it possible to jump back to column A in
> which there is a vlookup equation, and then jump forward two columns to
> column C?
>
> Your solution would be gratefully received.

[Lolly]

Hi David,
Thank you for that detailed information. However it doesn't fully answer my
query as the data columns being used are fixed for all the many users and we
therefore cannot rearrange columns. The example columns of A, B and C are
actually in the middle of other fields of information. When a number is
entered in column B we want a cooresponding name from a maser list to
automatically appear in column A and then for the cursor to jump to column C.
We already have the correct vlookup equation in column A. The Master List is
on Sheet1 and our records are being entered on Sheet 2. I hope this gives a
better idea of what is required.
Sorry, but I must admit I did become confused with the macro instructions
and feel they do not accurately cover what we require in the one row on each
record's data. There are about 20 colums in each row and each row needs to be
completed before entering the data for the next record. These records relate
to data collected from bird banding.

"David McRitchie" wrote:

> Hi Lolly,
> It is so easy to rearrange columns. What is good for data entry is
> usually in the order you want to look at them. Rearranging columns.
> http://www.mvps.org/dmcritchie/excel...nd.htm#movecol
>
> However for what you asked...
> You can affect how the cursor moves by the Enter key (direction),
> Tab key, arrow keys, and prevent entry into cell with cell protection in effect.
>
> An event macro to
> go to B in next row after entry in C
> go to A in same column after entry in B
> go to C after entry in A
>
> I don't know if this will help you or frustrate you but the Event macro
> will only move the cursor after entry. If you make a mistake the undo
> key will return you the last change and it's previous value...
>
> More information on Event macros in
> http://www.mvps.org/dmcritchie/excel/event.htm
>
> Install the following by right click on the sheet tab, then view code.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Select Case Target.Column
> Case 3 'After Entry in C next row column B
> Target.Offset(1, -1).Activate
> Beep '-- may be distracting but intent is to indicate new row
> Case 2 'After Entry in B same row column A
> Target.Offset(0, -1).Activate
> Case 1 'After Entry in A same row Column C
> Target.Offset(0, 2).Activate
> End Select
> End Sub
>
> If you make a mistake use the Undo key Ctrl+Z to return from your last change was made.
>
> ---
> HTH,
> David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
> My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
> Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
>
> "Lolly" <Lolly@discussions.microsoft.com> wrote in message news:DC976DB6-78E9-444E-958E-50704166170D@microsoft.com...
> > Using Excel 2000 and with three columns, A, B and C for example, after
> > entering a number in column B, is it possible to jump back to column A in
> > which there is a vlookup equation, and then jump forward two columns to
> > column C?
> >
> > Your solution would be gratefully received.
>
>
>

[David McRitchie]

Hi Lolly,
You only mentioned 3 columns not 20, but if you look at my
event.htm page you should be able to modify what was supplied
to limit it to certain rows. In any case why not try the macro and try to
modify it. From what you describe, you will have to use a macro.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Lolly" <Lolly@discussions.microsoft.com> wrote in message news:C2520C7C-7BFB-41C3-93DF-58E2C72CE96F@microsoft.com...
> Hi David,
> Thank you for that detailed information. However it doesn't fully answer my
> query as the data columns being used are fixed for all the many users and we
> therefore cannot rearrange columns. The example columns of A, B and C are
> actually in the middle of other fields of information. When a number is
> entered in column B we want a cooresponding name from a maser list to
> automatically appear in column A and then for the cursor to jump to column C.
> We already have the correct vlookup equation in column A. The Master List is
> on Sheet1 and our records are being entered on Sheet 2. I hope this gives a
> better idea of what is required.
> Sorry, but I must admit I did become confused with the macro instructions
> and feel they do not accurately cover what we require in the one row on each
> record's data. There are about 20 colums in each row and each row needs to be
> completed before entering the data for the next record. These records relate
> to data collected from bird banding.
>
> "David McRitchie" wrote:
>
> > Hi Lolly,
> > It is so easy to rearrange columns. What is good for data entry is
> > usually in the order you want to look at them. Rearranging columns.
> > http://www.mvps.org/dmcritchie/excel...nd.htm#movecol
> >
> > However for what you asked...
> > You can affect how the cursor moves by the Enter key (direction),
> > Tab key, arrow keys, and prevent entry into cell with cell protection in effect.
> >
> > An event macro to
> > go to B in next row after entry in C
> > go to A in same column after entry in B
> > go to C after entry in A
> >
> > I don't know if this will help you or frustrate you but the Event macro
> > will only move the cursor after entry. If you make a mistake the undo
> > key will return you the last change and it's previous value...
> >
> > More information on Event macros in
> > http://www.mvps.org/dmcritchie/excel/event.htm
> >
> > Install the following by right click on the sheet tab, then view code.
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > Select Case Target.Column
> > Case 3 'After Entry in C next row column B
> > Target.Offset(1, -1).Activate
> > Beep '-- may be distracting but intent is to indicate new row
> > Case 2 'After Entry in B same row column A
> > Target.Offset(0, -1).Activate
> > Case 1 'After Entry in A same row Column C
> > Target.Offset(0, 2).Activate
> > End Select
> > End Sub
> >
> > If you make a mistake use the Undo key Ctrl+Z to return from your last change was made.
> >
> > ---
> > HTH,
> > David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
> > My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
> > Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
> >
> > "Lolly" <Lolly@discussions.microsoft.com> wrote in message news:DC976DB6-78E9-444E-958E-50704166170D@microsoft.com...
> > > Using Excel 2000 and with three columns, A, B and C for example, after
> > > entering a number in column B, is it possible to jump back to column A in
> > > which there is a vlookup equation, and then jump forward two columns to
> > > column C?
> > >
> > > Your solution would be gratefully received.
> >
> >
> >

[Lolly]

Hi David,
Thanks once again for your suggestions. Unfortunately I'm having problems
creating a Macro. Using your example I added this:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Target.Column
Case 1 'After Entry in C same row Column E
> Target.Offset(0, 2).Activate
> End Select
> End Sub

With about 20 columns, I want to jump from Column C to Column E after
entering a letter in Column C, or just pressing Enter in Column C.
Each time I try this the following error message appears: Compile error,
Syntax error and the line Target.Offset (0, 2) is highlighted.
I've had a look at your Events page, but fear I cannot understand most of it.
Hopefully, you can help me further.
With grateful thanks in advance.
"David McRitchie" wrote:

> Hi Lolly,
> You only mentioned 3 columns not 20, but if you look at my
> event.htm page you should be able to modify what was supplied
> to limit it to certain rows. In any case why not try the macro and try to
> modify it. From what you describe, you will have to use a macro.
> ---
> HTH,
> David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
> My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
> Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
>
> "Lolly" <Lolly@discussions.microsoft.com> wrote in message news:C2520C7C-7BFB-41C3-93DF-58E2C72CE96F@microsoft.com...
> > Hi David,
> > Thank you for that detailed information. However it doesn't fully answer my
> > query as the data columns being used are fixed for all the many users and we
> > therefore cannot rearrange columns. The example columns of A, B and C are
> > actually in the middle of other fields of information. When a number is
> > entered in column B we want a cooresponding name from a maser list to
> > automatically appear in column A and then for the cursor to jump to column C.
> > We already have the correct vlookup equation in column A. The Master List is
> > on Sheet1 and our records are being entered on Sheet 2. I hope this gives a
> > better idea of what is required.
> > Sorry, but I must admit I did become confused with the macro instructions
> > and feel they do not accurately cover what we require in the one row on each
> > record's data. There are about 20 colums in each row and each row needs to be
> > completed before entering the data for the next record. These records relate
> > to data collected from bird banding.
> >
> > "David McRitchie" wrote:
> >
> > > Hi Lolly,
> > > It is so easy to rearrange columns. What is good for data entry is
> > > usually in the order you want to look at them. Rearranging columns.
> > > http://www.mvps.org/dmcritchie/excel...nd.htm#movecol
> > >
> > > However for what you asked...
> > > You can affect how the cursor moves by the Enter key (direction),
> > > Tab key, arrow keys, and prevent entry into cell with cell protection in effect.
> > >
> > > An event macro to
> > > go to B in next row after entry in C
> > > go to A in same column after entry in B
> > > go to C after entry in A
> > >
> > > I don't know if this will help you or frustrate you but the Event macro
> > > will only move the cursor after entry. If you make a mistake the undo
> > > key will return you the last change and it's previous value...
> > >
> > > More information on Event macros in
> > > http://www.mvps.org/dmcritchie/excel/event.htm
> > >
> > > Install the following by right click on the sheet tab, then view code.
> > >
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > Select Case Target.Column
> > > Case 3 'After Entry in C next row column B
> > > Target.Offset(1, -1).Activate
> > > Beep '-- may be distracting but intent is to indicate new row
> > > Case 2 'After Entry in B same row column A
> > > Target.Offset(0, -1).Activate
> > > Case 1 'After Entry in A same row Column C
> > > Target.Offset(0, 2).Activate
> > > End Select
> > > End Sub
> > >
> > > If you make a mistake use the Undo key Ctrl+Z to return from your last change was made.
> > >
> > > ---
> > > HTH,
> > > David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
> > > My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
> > > Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
> > >
> > > "Lolly" <Lolly@discussions.microsoft.com> wrote in message news:DC976DB6-78E9-444E-958E-50704166170D@microsoft.com...
> > > > Using Excel 2000 and with three columns, A, B and C for example, after
> > > > entering a number in column B, is it possible to jump back to column A in
> > > > which there is a vlookup equation, and then jump forward two columns to
> > > > column C?
> > > >
> > > > Your solution would be gratefully received.
> > >
> > >
> > >
>
>
>