Reference expanding range to autopopulate multiple rows

Hi - I am trying to find a solution when a user inserts a new row in a list, that will be referenced to automatically populate multiple rows in a separate list. The part on the right should autopopulate as users add additional rows on the left (e.g. asset numbers). Additionally, the asset attribute and asset value are repeating and the same for each new asset number. Any thoughts?

Excel Prob.png

(This is my first question so hopefully I'm doing it right! Thanks for your understanding and patience!)

Hello and welcome to the forum.

Column D can be populated with this:

D2 =IFERROR(1/(1/INDEX(A$2:A$1000,ROUNDUP(ROWS(A$1:A1)/3,0))),"")

Where do the values in columns E and F come from?

I see now, they are just repeating.

Try these:

D2 =IFERROR(1/(1/INDEX(A$2:A$1000,ROUNDUP(ROWS(A$1:A1)/3,0))),"")

E2 =IF(D2="","",CHOOSE(MOD(ROWS($1:1)-1,3)+1,"Blue","Big","Cold"))

F2 =IF(D2="","",CHOOSE(MOD(ROWS($1:1)-1,3)+1,45,20,5))

Drag all of the formulas down as far as needed.

Wow, thank you so much.

The values in Column E & F are actually being referenced from a separate spreadsheet. Would that also be possible to do?

You're welcome. Thanks for the rep!

Sure, change the hard-coded values to the cell references that the values are in.

For example, if Blue, Big, and Cold are in Z1, Z2, and Z3, the formula in E2 can be changed to this:

=IF(D2="","",CHOOSE(MOD(ROWS($1:1)-1,3)+1,Z1,Z2,Z3))

Thank you! So simple and yet I struggled all morning with it.

Okay, I have just discovered that not all attributes will be the same. I could be wrong, but would an INDEX/MATCH function work better for this approach?

Screen Shot 2017-12-12 at 16.16.43.png