auto populate based off number entered.

[Jstump]

What formula can i enter in one of the cells that are below 1-6 to make them autopoulate based of the entered number? i want tp be able to enter 1-6 where # is and the the next 3 columns would autopopulate a color where the "" is.

Kane 1.xlsx
	C	D	E	F
1
2
3	Pile types	Lead description	Extension desctription	Cap description
4	1	blue	blue	blue
5	2	green	green	green
6	3	red	red	red
7	4	yellow	yellow	yellow
8	5	brown	brown	brown
9	6	purple	purple	purple
10
11
12
13	#	""	""	""
14	#	""	""	""
15	#	""	""	""
16	#	""	""	""
17	#	""	""	""
Input page

 

[Joe4]

You can put this formula in cell B13 and copy to the rest of the cells in that grid:

=IFERROR(VLOOKUP($A13,$A$4:B$9,COLUMN(),0),"")

 

[Joe4]

Likewise, you could also use the new FILTER function, placing this formula in cell B13 and copy down column B for all your rows:

=FILTER($B$4:$D$9,$A$4:$A$9=$A13,"")

 

[Jstump]

I had to tweak it a bit to make it work for what i needed but thanks! this is what i ended up using =FILTER('Input page'!D4:F9,'Input page'!C4:C9=$B2,"")

Instead of copying and pasting all the way down, is there a way i could change to formula to add it to the collumn D if there is data in column A? This spreadsheet autopoulates numbers in column A based off entering a number in another sheet. for example if i entered 2 on the other sheet there would only be data in A2, A3. if i entered 4000 it would enter data all the way down to row 4002.

Kane 1.xlsx
	A	B	C	D	E	F
1	PIER #	PILE TYPE	STRUCTURE NAME	LEAD DESCRIPTION	EXTENSION DESCRIPTION	CAP DESCRIPTION
2	HP-01	1		blue	blue	blue
3	HP-02
4	HP-03
5	HP-04
6	HP-05
7	HP-06
8	HP-07
9	HP-08
10	HP-09
11	HP-10
12	HP-11
13	HP-12
14	HP-13
15	HP-14
16	HP-15
As-built 1

Cell Formulas
Range	Formula
A2:A4001	A2	="HP-"&TEXT(SEQUENCE('Input page'!B3),"00")
D2:F2	D2	=FILTER('Input page'!D4:F9,'Input page'!C4:C9=$B2,"")
Dynamic array formulas.

Likewise, you could also use the new FILTER function, placing this formula in cell B13 and copy down column B for all your rows:

=FILTER($B$4:$D$9,$A$4:$A$9=$A13,"")

 

[Joe4]

If you set your table up as a table, the formulas will automatically appear in every row, and you won't need to do anything at all.
If not, just pre-populate the formula down for as far as you like. The formula is already programmed to return a blank if there is no match, so it shouldn't hurt anything to pre copy it down.

 

[Jstump]

When i try and populate down it keeps mutliplying and spilling over.

Kane 1.xlsx
	A	B	C	D	E	F
1	PIER #	PILE TYPE	STRUCTURE NAME	LEAD DESCRIPTION	EXTENSION DESCRIPTION	CAP DESCRIPTION
2	HP-01	1		blue	blue	blue
3	HP-02			0	0	0
4	HP-03			#SPILL!
5	HP-04			#SPILL!
6	HP-05			#SPILL!
7	HP-06			0	0	0
8	HP-07			0	0	0
9	HP-08			0	0	0
10	HP-09
11	HP-10
As-built 1

Cell Formulas
Range	Formula
A2:A21	A2	="HP-"&TEXT(SEQUENCE('Input page'!B3),"00")
B3	B3	=FILTER($B$4:$D$9,$A$4:$A$9=$A13,"")
D7:F9,D4:D6,D2:F3	D2	=FILTER('Input page'!D4:F9,'Input page'!C4:C9=$B2,"")
Dynamic array formulas.

 

[Jstump]

My bad, i fixed it. I forgot to add the $ to lock the cells.