Join for FREE
It only takes a minute!
Zeiss - Always on the lookout for something special – Shop now

Welcome to BirdForum.
BirdForum is the net's largest birding community, dedicated to wild birds and birding, and is absolutely FREE! You are most welcome to register for an account, which allows you to take part in lively discussions in the forum, post your pictures in the gallery and more.

Help wanted with Ecxel formula

Reply
 
Thread Tools Rate Thread
Old Wednesday 4th December 2002, 11:47   #1
T0ny
Okillre Member
 
T0ny's Avatar

 
Join Date: Nov 2002
Location: Paston, Norfolk, UK
Posts: 1,091
Help wanted with Ecxel formula

Can anyone with Excel experience assist me, please ? It is not strictly a birdy-type problem, so please send me a PM and I'll let you have my e-mail address, so we don't bore everyone else.

TIA (means Thanks In Advance, Marysan)

Tony
__________________
When in Danger or in Doubt -
Run in circles, scream and shout.
T0ny is offline  
Reply With Quote
Old Wednesday 4th December 2002, 14:48   #2
jeff
Registered User
 
jeff's Avatar

 
Join Date: Oct 2002
Location: Coventry
Posts: 2,254
Go on, bore us. At least post the question you want the solution too, might be able to help?
jeff is offline  
Reply With Quote
Old Wednesday 4th December 2002, 14:54   #3
Steve
Registered user

 
Join Date: Jan 1970
Location: Uk
Posts: 136
Tony
This forum is not just for birding subjects, I would of thought that was obvious, judging by some of the threads alread here.
Steve is offline  
Reply With Quote
Old Wednesday 4th December 2002, 15:03   #4
jeff
Registered User
 
jeff's Avatar

 
Join Date: Oct 2002
Location: Coventry
Posts: 2,254
Yep, seems this is the 'Computers and The internet' section!


Feliz Navidad (that means Merry Christmas)

Jeff
jeff is offline  
Reply With Quote
Old Wednesday 4th December 2002, 15:22   #5
T0ny
Okillre Member
 
T0ny's Avatar

 
Join Date: Nov 2002
Location: Paston, Norfolk, UK
Posts: 1,091
Lightbulb

OK, here goes. I want to create a spreadsheet to print labels, fifteen across by thirteen deep, in numerical sequence, first across then down, in sets of three. i.e. :


1 1 1 2 2 2 3 3 3 4 4 4 5 5 5
6 6 6 7 7 7 8 8 8 9 9 9 10 10 10
11 11 11 12 12 12 13 13 13 14 14 14 15 15 15

and so on, without having to laboriously enter each number individually. I need to create labels from 1 to 4000. I have tried some of the hints from the Office 2000 Bible, which have left me tearing my hair, an activity I can ill-afford to indulge in these days. If anyone has any helpful suggestions, I shall be most grateful.

Tony
__________________
When in Danger or in Doubt -
Run in circles, scream and shout.
T0ny is offline  
Reply With Quote
Old Wednesday 4th December 2002, 16:43   #6
jeff
Registered User
 
jeff's Avatar

 
Join Date: Oct 2002
Location: Coventry
Posts: 2,254
I'm guessing your going to Mail Merge this into Word for your lables, excel is just giving you the data?

If so you could do something like, its a bit raw but should do the trick!:

I've attached the excel file as well:

Sub Auto_Open()
A = InputBox("Input start Number", "Start")
B = InputBox("Input End Number", "End")
j = 1
For i = A To B
C = "A" & j
Range(C).Select
ActiveCell.FormulaR1C1 = i
j = j + 1
C = "A" & j
Range(C).Select
ActiveCell.FormulaR1C1 = i
j = j + 1
C = "A" & j
Range(C).Select
ActiveCell.FormulaR1C1 = i
j = j + 1
Next i
End Sub
Attached Files
File Type: zip test2.zip (17.9 KB, 193 views)
jeff is offline  
Reply With Quote
Old Wednesday 4th December 2002, 20:59   #7
T0ny
Okillre Member
 
T0ny's Avatar

 
Join Date: Nov 2002
Location: Paston, Norfolk, UK
Posts: 1,091
Thnks very much, Jeff, I'll give it a whirl after 'Life of Mammammmamals'

Tony
__________________
When in Danger or in Doubt -
Run in circles, scream and shout.
T0ny is offline  
Reply With Quote
Old Wednesday 4th December 2002, 22:26   #8
T0ny
Okillre Member
 
T0ny's Avatar

 
Join Date: Nov 2002
Location: Paston, Norfolk, UK
Posts: 1,091
Jeff,
My apologies, maybe I didn't explain myself very well.What I had in mind was a spreadsheet where the numbers 1 to 5 appeared triplicated in cells A1 to A15 (ie A1 = 1, A2 = 1, A3 = 1, A4 = 2, A5 = 2, A6 = 2 etc), then dropping down to Row B for 6 to 10 (B1 = 6, B2 = 6, B3 = 6 etc). The formula you so kindly provided actually produces 16 individual sheets, with all the numbers in Column 1 on sheet 16, repeated 3 times - or am I doing something wrong with it ?

Yours, puzzled

Tony
__________________
When in Danger or in Doubt -
Run in circles, scream and shout.
T0ny is offline  
Reply With Quote
Old Thursday 5th December 2002, 08:35   #9
jeff
Registered User
 
jeff's Avatar

 
Join Date: Oct 2002
Location: Coventry
Posts: 2,254
Tony,

I guessed when you said lables you were using the Excel data to import into Word Lables.
Do you mean you just want the data to appear in Excel Cells?
A B C D E .....
1 6 11 16 21 ....
1 6 11 16 21 ....
1 6 11 16 21 ....
2 7 12 17 26....
...and so on........

If so its not possible to do 1-4000 in triplicate (not enough columns in Excel, or at least in Excel 2000 as i'm using) you can only get up to 1280.

i've attached an Excel sheet showing this, maybe i've missunderstood again?
Attached Files
File Type: zip test3.zip (27.6 KB, 147 views)

Last edited by jeff : Thursday 5th December 2002 at 08:38.
jeff is offline  
Reply With Quote
Old Thursday 5th December 2002, 09:06   #10
T0ny
Okillre Member
 
T0ny's Avatar

 
Join Date: Nov 2002
Location: Paston, Norfolk, UK
Posts: 1,091
Sorry again Jeff, it was late last night when I posted, and I made another stupid error - I'd forgotten it was the columns that were headed alphabetically. Let's start again.

A1=1, B1=1, C1=1, D1=2, E1=2, F1=2. G1=3, H1=3, I1=3, J1=4, K1=4, L1=4, M1=5, N1=5, O1=5

then

A2=6, B2=6, C2=6, D2=7, E2=7, F2=7, G2=8, H2=8, I2=8, J2=9, K2=9, L2=9, M2=10, N2=10, O2=10

etc.

Is that possible, please ?

Tony
__________________
When in Danger or in Doubt -
Run in circles, scream and shout.
T0ny is offline  
Reply With Quote
Old Thursday 5th December 2002, 09:15   #11
jeff
Registered User
 
jeff's Avatar

 
Join Date: Oct 2002
Location: Coventry
Posts: 2,254
Tony,

the easiest way to do this would be to enter the data in rows A1-O1 ie 111222333444555
In A2 enter the formula '=A1+5' copy that cell formula and paste it into the range A2:O2-A800:O800
You should now have 1-4000

Attached:
Attached Files
File Type: zip tony.zip (75.7 KB, 202 views)

Last edited by jeff : Thursday 5th December 2002 at 09:20.
jeff is offline  
Reply With Quote
Old Thursday 5th December 2002, 16:46   #12
T0ny
Okillre Member
 
T0ny's Avatar

 
Join Date: Nov 2002
Location: Paston, Norfolk, UK
Posts: 1,091
Jeff,
Thank you very much - that was exactly what I needed. I am eternally in your debt. Sorry I didn't explain myself better earlier.

For those that have followed this saga of sense (on Jeff's part) and stupidity (on mine), the labels are for my collection of 7" 45rpm singles - one label for each side of the sleeve, and one for the record itself.

Tony
__________________
When in Danger or in Doubt -
Run in circles, scream and shout.
T0ny is offline  
Reply With Quote
Old Friday 6th December 2002, 08:54   #13
jeff
Registered User
 
jeff's Avatar

 
Join Date: Oct 2002
Location: Coventry
Posts: 2,254
Tony,

Not a problem, glad i could help, happy sticking. I'm guessing you have 4000 7" singles then.
jeff is offline  
Reply With Quote
Old Friday 6th December 2002, 10:50   #14
T0ny
Okillre Member
 
T0ny's Avatar

 
Join Date: Nov 2002
Location: Paston, Norfolk, UK
Posts: 1,091
Just under - but I'm planning for the future (at least until I've been to the doctor's this afternoon !).

Tony
__________________
When in Danger or in Doubt -
Run in circles, scream and shout.
T0ny is offline  
Reply With Quote
Advertisement
Reply


Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

{googleads}

Fatbirder's Top 1000 Birding Websites

Help support BirdForum

Page generated in 0.29535890 seconds with 26 queries
All times are GMT. The time now is 01:30.