Can I have an Excel sheet with only 2 columns? 6

[DougP]

We need to take inventory one time, to upload to out MRP package.
I want to have the first column be part number and the second column be quantity.
Can we key in a part number in cell A1 hit enter key it would move to the cell "B1" , key in Quantity hit enter it would move to the cell "A2" key in next part number it would move to B2, key qty. etc etc. So it would go back and forth by itself. Can you limit the columns and make it do that?

can this be done in Excel?
I know it works in Access like that.

TIA

DougP, MCP, A+

 

[KenWright]

Tools / Options / Edit tab / Move Direction after Enter - Right

Select A1:B100, enter data and hit enter and so on.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[TonyJollans]

Hi Ken,

I was going to reply to this saying that Excel does not do it the same way as Access (which is true but not helpful). I guess I knew, but did not realise the implications of, the fact that cell movement on Enter differs depending on whether you are within a multi-cell selection or not - a star for pointing it out.

Doug,

Every Excel Worksheet has 256 columns but you can, if you want hide columns 3 through 256 so it looks like there are only 2. You'll still have to select the cells you want to work with to do as Ken shows, though.


Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[RoyVidar]

Another option, if the users will accept using the Tab key in stead of Enter:

Uncheck the locked property of the cells (Format | Cells - Protection tab) of your range, then protect the sheet (Tools | Protection | Protect Sheet).

Tab will then move the cursor within only the unprotected cells, in the order you describe.

Roy-Vidar

 

[DougP]

Well we are using a barcode scanner which has CR as a suffix to each scan. Not actually keying it in but I did not want to confuse the issue and have this thread run 3 diff'nt ways. I can re-program it to add a TAB but would rather not.

Ken,
I got the Enter key to move Right but I don't know what you mean with
"Select A1:B100, enter data and hit enter and so on."
It still goes to C1

Tony,
If I hide the other columns it just stays at B1 will not go down to A2


DougP, MCP, A+

 

[DougP]

PS this is Excel XP (2002)
How do I un-hide the other columns now that they are hidden?
TAB or Enter does the same thing, Protected or Not it just stays in B1

DougP, MCP, A+

 

[byundt]

Ken,
Great trick with selecting the cells before entering the data! Have a star.
Brad

 

[RoyVidar]

Unhiding - select from the rightmost column (header/column letter), drag to the right. Right click the column letter, select unhide.

I guess we all assumed manual entering of the information, where those suggestions would normally work. Manually hitting Tab in either of the suggestions, at least on my setup (a2k and xp) moves first to B1, then A2, B2, A3... - or is this unique only when using Norwegian regional settings/keyboard?

In xp you can also uncheck the "Select locked cells" when protecting the sheet. On manual tests here, that will also change cursor position as you describe (with both Tab and Enter (using KenWrights suggestion)). I haven't the foggiest I'm afraid what happens when using a barcode scanner, though.

KenWright's suggestion of selecting the input area, is very usefull for manually entering data only in the selected area. When selecting an area in Excel, one of the cells is the "active cell", which one can move within the selected area using either Tab or Enter - but, could it be that you'd need a programmatic approach here, or use Access?

Roy-Vidar

 

[DougP]

RoyVader
I'm missing something. It moves to the right but keeps going
I hightlighted the cells A1 to B100
Click tools Options Edit TAB,
The Move direction was already set to Down so I set it to Right
It seems to be a default setting now for all sheets. Thats OK
But it is not doing what you guys says it is suppose to do.
I am doing it manually now.
12331 Enter (it moves to B1)
23 Enter (it moves to C1)

122 Tab (it moves to B1)
12 Tab (it moves to C1)
same thing
I have SP3 if that makes a difference.

DougP, MCP, A+

 

[Gavona]

Worksfor me in xl97 wish I'd known about it before - a star from me.

Thanks

Gavin

Thanks,

Gavin

 

[jwigh]

Select A1 > Select columns A:B > Start entering you values. They will appear in A1, B1, A2, B2, A3 ...
The lue is to have the columns selected when you enter the values.

 

[KenWright]

Doug, assuming you have already done the Tools / Edit bit, then simply select say A1:B20 using the mouse (and at this point you now leave the mouse alone). Cell A1 will be your activecell whereas A1:B20 with the exception of A1 will appear to be grey. Now type something and hit enter (Don't you touch that mouse now.....) and the cursor will move right. All with the exception of B1 will now be grey and B1 will be the active cell. Type something and hit enter (If you touch that mouse it will bite....) and the cursor will now move to cell A2 and so on.

If you want to do this with non-contiguous cells then just select them all individually, and select the first one again at the end (trust me), then give it a name using Insert / Name, and then simply select the name from the name box and follow the same steps as above.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[KenWright]

Mind you, it may be easier to combine mine and Tony's responses Do the tools / edit / move right bit from my post and then do the Hide columns bit from Tony's post and that way you won't have to do any selecting at all.

You can even touch that mouse again and it won't bite.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[01Patrik]

The by far easiest way if you have Excel 2002 and later ( a lot of suggestions, I know, but still) must be to use a combo of Ken's and Roy's ideas, as follows:

1/ Select the area (example: Both columns A and B). Right-click, "format cells". In the "protection" tab, uncheck the "lock" checkbox. Click OK.

2/ Follows Kens instruktion (below)

Tools / Options / Edit tab / Move Direction after Enter - Right

3/ Tools / Protection / Protect sheet / uncheck all but "select unlocked cells". (More specific, make sure "Select locked cells" is UNchecked)

Done! Now pressing enter will give exactly the result you want, including starting over when you hit the end of the unlocked area.

// Patrik

 

[01Patrik]

(if you're using Excel 2000 or previous, then the 3rd part won't work. Then you'll have to use code to achieve the same result, meaning that you'd be much better of using one of the other guy's inctructions.

// Patrik

 

[sawedoff]

I hate to be redundant, but, Ken - the contiguous cells trick deserves a lot of stars. Thanks.

Sawedoff