Dynamic Index Range for multiple rows autofilled

[wlatc]

I have index formula =INDEX($A$2:$F$2,ROW(A1)). I am able to autofill that vertically in column K1 to k6. Then I enter in k7 =INDEX($A$3:$F$3,ROW(A1)) and autofill down. This becomes work intensive with more rows get added or my range changes to A2 to G2 or H2. Is there a way to do this with a dynamically changing range? Then have it autofill the next row?

 

[Eric W]

Welcome to the Board.

Your question is a little unclear as to what you're looking for. But try this formula in K1:

=INDEX($A$2:$F$100,INT((ROWS($K$1:$K1)-1)/6)+1,MOD(ROWS($K$1:$K1)-1,6)+1)

and copy down. If that doesn't work for you, try showing some examples of your data and expected results. You can possible use the HTML Maker in my signature to show a sample of your worksheet.

 

[wlatc]

yes it worked perfectly. Thank you!
I can just change the ending row (F) and the number to offset (6) as I add in more entries.

 

[Eric W]

Yep, that's the gist of it. Glad it works for you!

 

[wlatc]

I don't know what happened, but it seems microsoft has done something now that the formula doesn't work. Do you know how to resolve this? Thank you

 

[Eric W]

Just saying that it doesn't work doesn't give me much to go on. Does it show no data, wrong data, error messages? What does your formula look like now? Can you show a sample of what your sheet looks like now? Possibly use the HTML Maker in my signature to show a sample.

 

[wlatc]

thanks for the help, I guess they put in the new update that it doesn't automatically update when you autofill, theres a new "calculate" button on the bottom of the window

 

[Eric W]

Check to see if you have Calculations set to Automatic. Go to the Formulas tab > Calculation Options > and make sure Automatic is checked.