Fill a series that goes A1, B1, C1

[bchez]

If I type A1 in a cell and drag the fill handle, it gives me A2, A3 etc. I want it to be B1, C1 etc Is there a way to do this? Edit custom lists isn't an option.

 

[Eric W]

Assuming you can use a formula, and you enter this on the top row, you can put

=ADDRESS(1,ROW(),4)

in the cell and drag that down. But what do you intend using that list for? There may be another way to solve that problem without such a list.

 

[bchez]

Thanks Eric - That certainly works. I'm not certain why my client wants to do that, but I think she is wondering if using the fill handle to drag and extend series can be manipulated in other ways. So as we enter A1 in a cell and drag the fill handle it becomes A2. She is wondering, is there a way to make that B1 instead of A2? I think the fill handle is very powerful, but it seems kind of limited. I looked on the Home Tab in the Series dialog box (Editing group, Fill, Series) and it didn't allow for much flexibility. Appreciate you weighing in with that formula option though!

 

[Eric W]

I agree the fill handle can be pretty handy, but it is limited. Except for the simplest uses, I can't remember the rules that govern it, so I usually end up writing a formula, then copying and pasting the values.

I recently answered another question where someone was asking how to return the column letter that matched the results of a MATCH. I provided a perfectly correct answer, but someone else came along and asked why they needed it. It turned out they were using INDIRECT to use that first result to look up another value in that column. Which could have been done much easier and more efficiently with an INDEX.

So, if your client is just experimenting with the fill handle, you could direct her to:

https://support.office.com/en-us/ar...et-cells-74e31bdd-d993-45da-aa82-35a236c5b5db

which shows some examples. If there's an underlying reason, then there might be options. Good luck!

 

[bchez]

All good points. Thanks for the tips and direction Eric. Too bad the Series box isn't more like the Custom Sort button. I like that one cause, for example, in a database I can sort by Supervisor and Last Name. The Series box should have an option like Extend the column as a series or Extend the row as a series. I'll be happy to direct her to that URL

 

[FDibbins]

You stillk have not given any indication of exactly why you (they?0 need this progression? What is it needed for?

 

[bchez]

Hi Ford - She wants to know if dragging the fill handle to fill a series is flexible or if it's locked in to the default. For example, when we drag Monday, we know we get Tuesday. If we drag a date, we get the next day. If we use CTRL while dragging, it will have the opposite effect. Her initial question to me was if I have A1 typed in a cell and I drag the fill handle, I get A2. Would there be a way to set it up to get B1 instead. The only way I could tell her was Edit Custom Series.
Thank you Ford.

 

[Rick Rothstein]

Hi Ford - She wants to know if dragging the fill handle to fill a series is flexible or if it's locked in to the default. For example, when we drag Monday, we know we get Tuesday. If we drag a date, we get the next day. If we use CTRL while dragging, it will have the opposite effect. Her initial question to me was if I have A1 typed in a cell and I drag the fill handle, I get A2. Would there be a way to set it up to get B1 instead. The only way I could tell her was Edit Custom Series.

You could always go to a blank sheet, type A1 in cell A1 and then drag across for as many cells as desired, then Copy/Paste(Transpose) the range that was created into the sheet and starting cell where it is eventually wanted.

 

[bchez]

Thanks Rick that's a good option too. Appreciate it!