Formula needed to Autofill Custom Lists

Posted by Kurt on August 06, 2001 8:37 AM

Hello Everyone,

I know you can drag and fill cells using data you created in a custom list. What I would like to do is have Excel fill in the list when a starting point in the custom list is presented.

Example:

Custom List = Q1-01, Q2-01, Q3-01, Q4-01.......

In Cell A1 I type Q2-01, I would like to have a formula that takes that info and continues the list from that point, i.e. Q3-01, Q4-01......

I want to avoid having to manually drag the list out each time I change the starting point.

I hope this presents a good challenge for someone.

Kurt

Posted by Kurt on August 06, 2001 8:56 AM

I think I just answered my own question.

It involves more prep work than I was willing to do,but here goes,

How about putting the list at the bottom of the sheet and writing a H/Vlookup (or whatever) that will give me the next entry in the series.

Let me know what you think.

Mark, where are you when I need you?

Kurt

Posted by Kurt on August 06, 2001 9:16 AM

I used a combination of Offset and Match to get the same result. I made the list at the bottom of the spreadsheet and used match to find the relative position of the first entry, then put the MATCH function within the OFFSET to give me the list value that is next in the series relative to the beginning point.

Thanks for all your help. :)

Kurt

Posted by Aladin Akyurek on August 06, 2001 9:23 AM

Didn't check your own answer. This is then another one.

In A2 enter: =IF(LEN(A1)=0,"",LEFT(A1,1)&MID(A1,2,SEARCH("-",A1)-2)+1&"-"&RIGHT(A1,LEN(I1)-SEARCH("-",A1)))

Aladin

=====

Posted by Kurt on August 06, 2001 9:31 AM

I appreciate you taking your time to do this.

Thanks.

Kurt