Tab / Enter key behaviour. Auto Wrap?

[beeawwb]

Good afternoon all.

I'm currently working on a spreadsheet that keeps track of cheques tha
come in the mail, and when they have been sent off. There are only
rows needed, so to save space, I repeat the same block of 6 lines fro
row 1 to 36, in A->K

A1-6 contains the sources headers.

****
Cheque #
Cheque Amnt
# of Entries
Date Sent
****

This is repeated every 6 lines. So, sample data of A1-12 is

****
Cheque #
Cheque Amnt
# of Entries
Date Sent
****
****
Cheque #
Cheque Amnt
# of Entries
Date Sent
****

Now, here's what I want to do...

When I'm typing in a cheque, I hit enter after each line. When I pus
enter on Date Sent, I want it to wrap to the next Cheque #.

So, I push enter on B5, it wraps to C2.

If it's the last "Block" on a page (Page 1 goes to K) then jump to th
next "Block", Ie, K5 wraps to B8.

If it's the FINAL block on a page (Which would be K...35) then jump t
the next page (M2), first block.

Is this possible? I want it to work regardless of how many pages I add
They will not (at this stage) go past Row 35 (36 for the blan
seperator), as I'll just keep expanding till I reach column IV.

Thanks for any help you can provide.

-Bob

(PS, some real sample data (A1:E6)


Cheque # 1061 1085 797 1016
Cheque Amnt $51,082.61 $4,570.50 $31.00 $1,157.00
# Of Entries 49 3 1 1
Date Sent 05/Feb/2004 05/Feb/2004 05/Feb/2004 05/Feb/200

 

[beeawwb]

Maybe some coding assitance then? I can write what I think th
psuedocode would look like, but I don't know how the actual code woul
go.

If Enter pushed->
If Row = 35 AND Column = a multiple of 11 (hence finding every end o
page),
Then go to Row 5, Column + 1
Else If Column = multiple of 11
then go to row offset + 3 (3 cells down), -9 (9 cells back)
Else IF OR(Row = 5, 11, 17, 23, 29, 35) Goto Row offset - 3 (go 3 cell
up) , 1 (1 cell across)
Else normal behaviour.

Psuedocode saying, if you are in the final cell of a page, go to nex
page, if in final cell of a section, go to the next section, if in th
final cell of an entry, go to the next entry, otherwise, behave a
normal.

Anybody able to translate that into real code?

Thanks for any help you can provide.

-Bo

 

[Dave Peterson]

This is not an answer to your question.

Just a warning--don't lay out your data that way. You'll find any future
analysis more difficult to accomplish.

I'd use one row per transaction. Put in nice headers.

After you have the data in that format, you'll be able to do a bunch of stuff
(mailmerge/charts & graphs/pivottable summaries/data|subtotal--lots of stuff.)

In fact, you can may your data entry easier by using some builtin
capabilities--Data|Form.

If you try this and find it less than optimal, you could use John Walkenbach's
enhanced data form:

http://j-walk.com/ss/dataform/index.htm

And when you're layout out the data, think granular--if there's a logical reason
to separate the value into multiple cells, do it.

For instance, someday you might want city, state, zip added (or salutation,
first, last name). Put each of these in a separate column.

You can combine the data much easier than separate them.

If you really have to layout the data the way you say, then I still wouldn't.
But after the data is one row per entry, you can use that to create as many
different (pronounced weird!) reports as you want.

 

[beeawwb]

I actually originally had everything laid out as
A = Cheque Number
B = Cheque Amount
C = # Of Entries
D = Date.

That's the way I normally lay things out. Until, when entering large
amounts of data, I had to keep hitting tab, when I was only doing
numeric entry. Ie, I could type one handed if I used the enter key.
Swapping data back would be quite easy. I won't be running *any*
reports on this data, it's simply to keep a backup record of what
cheques I've mailed off. The receipting department keeps the real
records.

So... alternative solution.

Make the Numeric enter key act as a TAB, and after row D (the final
bit) go back to A.

My only concern with this, is my original reason for changing the
layout, that being, I could fit more records per page that way. IE, If
there are 12 columns per page (landscape) I can fit 66 cheques per
page. Is there a way to lay things out in columns A-D, F-I, and then
have it start a new page? (Sort of like an anti-page break. Either
that, or type them all in A-D, but have each new page start in the
remaining available space?

Thanks,

-Bob

 

[Dave Peterson]

Select the range first (A1299 say) and the tab key will follow your order.
(and the enter keys, too)

And if it's just for printing, you could copy the range to word and use its
format|columns to get more per page.

 

[beeawwb]

I can see that working...

I just made a new spreadsheet, and hid columns E->IV. Is there a way to
make the enter key behave like the TAB key?

Also, is there a way to auto link tables in Word from Excel?

-Bob

 

[Dave Peterson]

I have tools|options|Edit Tab|Move Selection after Enter disabled.

When I had the range selected, the cursor moved to the right (until the last
column in the selection).

It'll work ok with move selection after enter set for Right, too.

I'm not sure what you mean by auto link. But you can keep your data in excel
and Insert|object|Create from file (and link to the worksheet) in MSWord.

But you may want to look into a macro approach. This was recently posted by
Eckard Buchner:
http://groups.google.com/[email protected]

based on a discussion in the msword newsgroup:
http://groups.google.com/[email protected]

Both of those are one line in your browser.