move from one column to another after a set condition
 

i have a spread sheet 32 columns in length and if column b = paper then i
want the cursor to move to column h after i have entered details from column
a to column d and then if column b = book then i want the cursor to move to
column m and so forth.
i tried to use the if function in column e but could not get it to work.
Is there a formula or a function that i can use to do this instead of moving
the cursor manually.
I can work this using f5 and putting in the column and row number but i was
wondering if there might be a more productive way of doing it
thank you

Hi

It sounds as though you are trying to do some form of analysed cashbook.
Why not just use a Pivot Table to analyses your data rather than entering
further data across a range of 32 columns?

Take a look at http://peltiertech.com/Excel/Pivots/pivotstart.htm

for information on how to use pivot tables.

Regards

Roger Govier


BAZZA wrote:
i have a spread sheet 32 columns in length and if column b = paper then i
want the cursor to move to column h after i have entered details from column
a to column d and then if column b = book then i want the cursor to move to
column m and so forth.
i tried to use the if function in column e but could not get it to work.
Is there a formula or a function that i can use to do this instead of moving
the cursor manually.
I can work this using f5 and putting in the column and row number but i was
wondering if there might be a more productive way of doing it
thank you

THANK YOU FOR YOUR SUGGESTION BUT I NEED TO ENTER DETAILS IN RESPECTIVE
COLUMNS FOR 7 SECTIONS FOR STUDENTS-
PAPERS-BOOKS-UNIFORM-DANCE-FUNCTIONS-ATTENDANCE-REPORTS AND I WANTED TO MOVE
FROM COLUMN E TO THE RESPECTIVE PRODUCT COLUMN INSTEAD OF MOVING MANUALLY

"Roger Govier" wrote:

Hi

It sounds as though you are trying to do some form of analysed cashbook.
Why not just use a Pivot Table to analyses your data rather than entering
further data across a range of 32 columns?

Take a look at http://peltiertech.com/Excel/Pivots/pivotstart.htm

for information on how to use pivot tables.

Regards

Roger Govier


BAZZA wrote:
i have a spread sheet 32 columns in length and if column b = paper then i
want the cursor to move to column h after i have entered details from column
a to column d and then if column b = book then i want the cursor to move to
column m and so forth.
i tried to use the if function in column e but could not get it to work.
Is there a formula or a function that i can use to do this instead of moving
the cursor manually.
I can work this using f5 and putting in the column and row number but i was
wondering if there might be a more productive way of doing it
thank you

Hi

To jump to different locations following various entries in column B would
need some VBA code and an event change. I am not the best person to advise
on this type of solution.

I was trying to find an alternative for you. What type of data do you need
to enter? Is is similar type of information? Are the column headings important?

What I am wondering, is whether you could enter the relevant data in the 5
columns following column D, regardless of type (papers, books etc.) and use
the Autofilter function to pull up all data belonging to each type.

Regards

Roger Govier


BAZZA wrote:
THANK YOU FOR YOUR SUGGESTION BUT I NEED TO ENTER DETAILS IN RESPECTIVE
COLUMNS FOR 7 SECTIONS FOR STUDENTS-
PAPERS-BOOKS-UNIFORM-DANCE-FUNCTIONS-ATTENDANCE-REPORTS AND I WANTED TO MOVE
FROM COLUMN E TO THE RESPECTIVE PRODUCT COLUMN INSTEAD OF MOVING MANUALLY

"Roger Govier" wrote:


Hi

It sounds as though you are trying to do some form of analysed cashbook.
Why not just use a Pivot Table to analyses your data rather than entering
further data across a range of 32 columns?

Take a look at http://peltiertech.com/Excel/Pivots/pivotstart.htm

for information on how to use pivot tables.

Regards

Roger Govier


BAZZA wrote:

i have a spread sheet 32 columns in length and if column b = paper then i
want the cursor to move to column h after i have entered details from column
a to column d and then if column b = book then i want the cursor to move to
column m and so forth.
i tried to use the if function in column e but could not get it to work.
Is there a formula or a function that i can use to do this instead of moving
the cursor manually.
I can work this using f5 and putting in the column and row number but i was
wondering if there might be a more productive way of doing it
thank you

First of all, TAKE CAPS LOCK OFF! It is considered shouting and is also more
difficult to read.

You will need VBA code to do this. First, select columns A to D, go to
Format|Cells|Protection and uncheck Locked.

Right click on the sheet tab and click View Code to open the VBA editor.
Paste the code below into the editor. The code starts by unprotecting the
sheet, then starting at row 1 locks column E to FF (5 to 32) before checking
the content of column B (2). If B1= paper, it unlocks columns I to L (9 to
12), or if book, columns M to P. You can add your own entries for the rest
of the sheet following the same pattern. At the end, it protects the sheet.

Note: The entry in column B can be any case, but the entries in the code
must be in capitals.

When you use the sheet, use the Tab key after entering data in a cell to
move on to the next available cell. The available cells are the unlocked
ones (columns A to D and others depending on the content of column B on that
row.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Worksheets("Sheet1").Unprotect ' change Sheet1 to suit your sheet name.
For r = 1 To 10 'change range to suit row numbers required
Range(Cells(r, 5), Cells(r, 32)).Locked = True 'lock column E to FF for row
r
Select Case UCase(Cells(r, 2).Value)
Case "PAPER"
Range(Cells(r, 9), Cells(r, 12)).Locked = False 'unlocks columns I to L for
row r
Case "BOOK"
Range(Cells(r, 13), Cells(r, 16)).Locked = False 'unlocks columns M to P for
row r
End Select
Next
Worksheets("Sheet1").Protect ' change Sheet1 to suit your sheet name.
End Sub

--
Ian
--
"BAZZA" wrote in message
...
THANK YOU FOR YOUR SUGGESTION BUT I NEED TO ENTER DETAILS IN RESPECTIVE
COLUMNS FOR 7 SECTIONS FOR STUDENTS-
PAPERS-BOOKS-UNIFORM-DANCE-FUNCTIONS-ATTENDANCE-REPORTS AND I WANTED TO
MOVE
FROM COLUMN E TO THE RESPECTIVE PRODUCT COLUMN INSTEAD OF MOVING MANUALLY

"Roger Govier" wrote:

Hi

It sounds as though you are trying to do some form of analysed cashbook.
Why not just use a Pivot Table to analyses your data rather than entering
further data across a range of 32 columns?

Take a look at http://peltiertech.com/Excel/Pivots/pivotstart.htm

for information on how to use pivot tables.

Regards

Roger Govier


BAZZA wrote:
i have a spread sheet 32 columns in length and if column b = paper then
i
want the cursor to move to column h after i have entered details from
column
a to column d and then if column b = book then i want the cursor to
move to
column m and so forth.
i tried to use the if function in column e but could not get it to
work.
Is there a formula or a function that i can use to do this instead of
moving
the cursor manually.
I can work this using f5 and putting in the column and row number but i
was
wondering if there might be a more productive way of doing it
thank you