Filling in multiple data entries from value of single cell

[yellowjkjeep]

Hello everyone. I have been searching for a way to do this and don't even know the right questions to ask. I have always been able to figure things out without help, except Google, but this has me stumped. What I am trying to do is develop a plant database that will show all plants I will be installing for a client's project and what I'd like to do is have the details auto-populate based on the input of a selected plant. Things like harvest dates, fertilizer recommendations, annual or perennial, water requirements, etc. I'll attach some screen shots.

Thanks a lot guys and gals. I have spent 4 hours trying to figure this out. I've tried IF & VLOOKUP and I don't know what else to do. I can make a data sheet for each plant variety or a table but I can't figure it out at all. I know I don't expect anyone to do the work for me, but if you could just point me in the right direction, I'd sure appreciate it!

 

[Peter_SSs]

As you have quite a lot of columns, I would definitely avoid the OFFSET option if at all possible - and I think it is.

In your sample file you have a formal table on 'Sheet2' and it is called 'Table4'. If that differs in your real file you will need to adjust that in my formulas below.
I have assumed that in Sheet2 you want to return all columns from 'DATA' and in the same order.

So, here is a small section of DATA

Excel Workbook
	A	B	C	D	E	F	G	H	I
2			LIFE CYCLE	SYSTEM PLACEMENT	USDA HARDINESS ZONES	SOURCE	TYPE	SIZE	COST
3
4
5	PLANT	Variety	Life Cycle	SYSTEM Placement	USDA HARDINESS ZONES	Source	Type	Size	Cost
6	APPLE	Anna	Perennial	Understory	4-10	The Arbor Gate	Productive	30 feet	$ 19.99
7	AVOCADO	ARAVAIPA	Perennial	Understory		The Arbor Gate	Productive	10 FEET	$ 45.00
8	BLACKBERRY	PRIME ARK FREEDOM	Perennial	SHRUB		GROW ORGANIC	Productive	5 FEET	$ 8.00
DATA

Try this.
1. In Sheet2, add a new column between B & C & remove the Data Validation that will most likely get copied from column B when you do that. Label the column 'Index'
2. Put the formula shown in C6 below making sure the range in 'DATA' is covered. I have used down to row 1000. That formula should flash-fill down the table.
3. The 'Index' column can now be hidden if you want.
4. The formula shown in D6 needs to get copied not only down to the bottom of that column but then dragged right across all columns.
5. You will see that cells that were blank in 'DATA' show up as "0" here. Two options ..
a) File -> Options -> Advanced -> Display options for this worksheet -> Remove the tick from 'Show a zero in cells that have zero value' -> OK, or
b) Use the longer formula shown in the final screen shot below.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
5	PLANT	VARIETY	Index	LIFE CYCLE	SYSTEM PLACEMENT	USDA HARDINESS ZONES	SOURCE	TYPE	SIZE	COST
6	APPLE	Anna	2	),"",INDEX(DATA!C$5:C$1000,Table4:]))]Perennial	Understory	4-10	The Arbor Gate	Productive	30 feet	19.99
7	AVOCADO	Aravaipa	3	Perennial	Understory	0	The Arbor Gate	Productive	10 FEET	45
8	BLACKBERRY	Prime Ark Freedom	4	Perennial	SHRUB	0	GROW ORGANIC	Productive	5 FEET	8
9	BERRIES	Goji Berry	#N/A
10	MULBERRY	Pakistan	#N/A
11	SATSUMA	Brown Select	#N/A
12	PEACH	Red Baron	#N/A
Sheet2

If you want to use the formula to hide unwanted zero values instead of Settings, then this formula in D6 and down/across.
I have also hidden the Index column here as described above.

Excel Workbook
	A	B	D	E	F	G	H	I	J
5	PLANT	VARIETY	LIFE CYCLE	SYSTEM PLACEMENT	USDA HARDINESS ZONES	SOURCE	TYPE	SIZE	COST
6	APPLE	Anna	),"",INDEX(DATA!C$5:C$1000,Table4:]))="","",IF(ISNA(Table4:]),"",INDEX(DATA!C$5:C$1000,Table4:])))]Perennial	Understory	4-10	The Arbor Gate	Productive	30 feet	19.99
7	AVOCADO	Aravaipa	Perennial	Understory		The Arbor Gate	Productive	10 FEET	45
8	BLACKBERRY	Prime Ark Freedom	Perennial	SHRUB		GROW ORGANIC	Productive	5 FEET	8
9	BERRIES	Goji Berry
10	MULBERRY	Pakistan
11	SATSUMA	Brown Select
12	PEACH	Red Baron
Sheet2

 

[yellowjkjeep]

THANK YOU! This works beautifully! I'm going to have to study how this works and all the formula you provided so I can repeat it in the future, but thank you for your help!

 

[Peter_SSs]

THANK YOU! This works beautifully! I'm going to have to study how this works and all the formula you provided so I can repeat it in the future, but thank you for your help!

You are very welcome. Glad to help.