Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Move a Column to rows | Excel Discussion (Misc queries) | |||
move column headings from row to column | Excel Worksheet Functions | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions | |||
How do I get a column heading to show up at the top and not move . | Excel Discussion (Misc queries) | |||
How can I sort an entire spreadsheet from a list | Excel Worksheet Functions |