Force Fill Series with Alpha Numeric Cell Entry

[HangMan]

I have a cell that adopts the format 'L1415ACDC00001'. Is there any way to force this to 'Fill Series' when using the drag handle to copy the cell down on a worksheet so that the subsequent cells will update by 1, resulting in:

L1415ACDC00001
L1415ACDC00002
L1415ACDC00003
L1415ACDC00004
L1415ACDC00005
L1415ACDC00006
L1415ACDC00007
L1415ACDC00008
L1415ACDC00009
L1415ACDC00010
L1415ACDC00011
L1415ACDC00012

and so on...

Many thanks

[XOR LX]

Hi,

Not with Fill Series, no. But with this formula in your first cell:

="L1415ACDC"&TEXT(ROWS($1:1),"00000")

Copy down as required.

Regards

[:) Sixthsense :)]

Yes you can do it using Fill Series...

Enter this in a cell

L1415ACDC00001

Select the entered value and extend the selection below. Press Alt+E+I+S and select Autofil option button and Press Ok....

[Izandol]

May you not fill with right-mouse button and choose Fill Series? Or you may use left mouse button and if copy is default, hold Ctrl key while dragging to change to fill.

[Bhuvi]

Hi Hangman,
Excel does this automatically for you , Please remove extra spaces from right of your text by doing "text to columns" "fixed width" and then drag down.

[HangMan]

Hi XOR LX,

That works perfectly, though is it adaptable to accommodate filtered columns, so say for exmple, rows 3 8 and 10 were hidden because of a filter and I wanted the formula to skip the hidden/filtered rows but for the resulting dragged down numbers to still be sequential, resulting in:

Row 1 L1415ACDC00001
Row 2 L1415ACDC00002
Row 3 A different Reference
Row 4 L1415ACDC00003
Row 5 L1415ACDC00004
Row 6 L1415ACDC00005
Row 7 L1415ACDC00006
Row 8 A different Reference
Row 9 L1415ACDC00007
Row 10 A different Reference
Row 11 L1415ACDC00008
Row 12 L1415ACDC00009

Many thanks

[XOR LX]

All,

Please excuse my ignorance for not knowing this!

Regards

[HangMan]

Hi XOR LX,

That works perfectly, though is it adaptable to accommodate filtered columns, so say for exmple, rows 3 8 and 10 were hidden because of a filter and I wanted the formula to skip the hidden/filtered rows but for the resulting dragged down numbers to still be sequential, resulting in:

Row 1 L1415ACDC00001
Row 2 L1415ACDC00002
Row 3 A different Reference
Row 4 L1415ACDC00003
Row 5 L1415ACDC00004
Row 6 L1415ACDC00005
Row 7 L1415ACDC00006
Row 8 A different Reference
Row 9 L1415ACDC00007
Row 10 A different Reference
Row 11 L1415ACDC00008
Row 12 L1415ACDC00009

Many thanks

[HangMan]

Weirdly it won't update on my main sheet, but does so quite happily on a new sheet, trying to figure out why this is!

Many thanks...

[XOR LX]

@HangMan

I believe (though I'm probably wrong again!) that the only way to do that would be to know the criterion for filtering and to write that into the formula.

Regards

[:) Sixthsense :)]

All, Please excuse my ignorance for not knowing this!

Why excuse? In fact, all of us learning from someone

[HangMan]

Is it simply that you can't fill series on a filtered list?

[:) Sixthsense :)]

Is it simply that you can't fill series on a filtered list?

As far as I know, Yes you can't do it with filtered data

[XOR LX]

Yes. I believe the option will be 'greyed out' to you if you try.

Regards

[HangMan]

I tried it filtered and unfiltered and it only works with non filtered data for some reason, apply a filter and it only copies the cell above, rather than filling the series and the little box in the bottom right doesn't appear to give you the option to fill series, when the list is filtered!

[XOR LX]

Then, as I said, using a formula-based solution as I provided, but incorporating the criterion for filtering, will be the only possibility. Or perhaps VBA.

Though obviously both would require an actual workbook example to work on.

Regards

[:) Sixthsense :)]

Oh god I found a new method..... (for me its new) wow lol

Just select the filtered cells>>Enter the stating value in the first cell and Press Alt+; to select the visible cells alone. Press Alt+E+I+S>>Press Enter

[XOR LX]

Sixth, I can't get it to work as you described, as when I select cells in a filtered range the Fill Series option is greyed out.

Am I missing something? Sounds like a wonderful solution, by the way...

Cheers