Help with autofilling column in Excel please

chaddy7604

Can somebody please help me please?
Although I use Excel often I appear to be struggling on what is probably something quite simple.
I have created a really simple 1 page only A4 size spreadsheet, it contains 5 headed columns X 30 rows in length. One of the columns must contain a series of Serial Numbers preceeded by the letter 'H' ie H0001 The number sequence must then increase by one digit for each row in the column H0001, H0002, H0003 etc.

I don't want to do do this using the drag and fill handle method as other people will need to access the file.
I have tried various ways using formulas etc. but I can't seem to be able to wrap my head around it.

I need to set it up so that people without any Excel knowledge can just open the main file from a desktop shortcut to use it, then they just enter a Serial Number into a designated cell alongside the main spreadsheet page and the Serial Numbers will autofill into the column, (probably set up as a macro eventually) which can then be printed out for filing into a log book. The same page can then be just used over and over again for each sheet set of unique numbers.

Thanks in advance.

m00nie

enter h001 h002 underneath each other in there columns then highlight how far down you want to go then, edit, fill, series, autofill

chaddy7604

m00nie wrote: »

enter h001 h002 underneath each other in there columns then highlight how far down you want to go then, edit, fill, series, autofill

Thanks m00nie, I know that works for me thats not an issue.
But if you read my OP it needs to be the input of one serial number for someone who has no knowledge of Excel to generate the whole column without having to access any menus etc

Thanks Chaddy

m00nie

sorry, not fully with it, must have been that last pint :O

how about using vlookup? will pull the information from another table already done

Chippy_Minton

If I understand you correctly try this.

Press Alt-F11 to open Visual Basic editor and paste this code behind the sheet containing the input cell:

Private Sub Worksheet_Change(ByVal Target As Range)

Const inputCell As String = "A1"

If Intersect(Target, Range(inputCell)) Is Nothing Then Exit Sub

Application.EnableEvents = False

If Range(inputCell) <> "" Then
    Range(inputCell).Select
    Selection.AutoFill Range(inputCell, Range(inputCell).Offset(29)), xlFillDefault
End If

Application.EnableEvents = True
End Sub

Enter some data in A1 (the input cell) and it automatically fills in 30 rows. You could add some validation of the input cell data, e.g. starts with H and has 4 digits, but that gives you the basic idea.

More4me

Not very tidy but try this
1) In cell A1 enter your alpha character if any
2) Format cell A2 as text and enter numeric value including leading zeros, four digits in this example.
3) In cell A3 enter formula =text(A2+1,"0000") "0000" corespondse to four digits above.
4) copy formula down as required
5) In cell B2 enter Formula =$A$1&A2 and copy formula down

Hope this helps

Lakeuk

Like More4me, my Example-

Cell A1 enter 1
Cell A2 enter =A1+1
Fill rest of cells in column A with A2 formula

Cell B1 enter ="H"&TEXT(A1,"0000")
Fill rest of cells in column B with B1 formula

Then just hide Column A

jv_patel

Hi

If I understand correctly, then you want someone to enter a single S/N into a cell (the initial S/N for the day say), which then populates a column below...?

If so, then say you set up cell B1 as the input cell (where the initial S/N is entered)

You could now do the following:
Assumptions:-
Initial S/N to be numeric and entered into cell B1
S/N's to be filled in col A from cell A3 down, to be displayed in the format "H0000")

*) Select cells A3 down (or all of col A if nothing is to be in A1 or A2)

*) Right-click, select "Format Cells..."

*) Select "Number" tab, "Custom" at the bottom of the Category list box

*) In the "Type" edit box type (without quotes) "\H00000"
(this means there should be a literal H character, followed by a minimum 5-digit number, left-padded with zero's as necessary)

*) "OK" the dialog

In cell A3 enter the following formula
=IF($B$1="","",$B$1+ROW()-3)

(If you want the cells to start below/above A3 then adjust the offset - the number 3 at the end of the formula)

Now copy this formula down the column

Now if B3 is empty then cols A3 down remain empty
Enter a number into B3 (say 12), then A3 will be H00012, A4 = H00013 etc...

(To be safe you could lock these cells and protect the sheet)

Note that the value entered into B3 must be a number
And remember that the underlying values in column A are numbers - Excel is merely formatting the number to appear as a string

Hope this helps (and makes sense!) ;-)

chaddy7604

Thanks to all you guys above who posted replies, the information was both educational and extremely helpful with solving my problem.
I can't convey how much it means when people actually give their time and effort to help others, I thank you all.:A

Kind regards, Chaddy

rmg1

Just a quick plug for a fantastic website for all your Excel queries. Yes, I am a member, but it's all free and I don't get any points/payment from it.

The web address is http://www.mrexcel.com/board2/index.php

You can ask questions on Excel which can be answered in minutes, depending on how many perople are looking/time of day) and there's a separate board for Access queries.

You have to register (quick, painless and free) and you only get one confirmation e-mail and nothing else (no spam, junk mail, etc)

WestieFan

Sorry to butt in here, but I just want to thank rmg1 for the link to the Excel site.

I am a relative novice with Excel, and have been working on a project for a few weeks but have come to a halt with the final bit. I know what I want to do can be done, just can't figure out how to do it, as it's a bit tricky. I am sure I will find the help I need on that site!

Isn't Excel fabulous, if you know what you're doing :rotfl: