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!

 

[oldbrewer]

if you list say 50 headings for apple, that is your database, apple in A2 and details in B2 to AB26

so select apple in A4, and say you want B4 to be fertiliser (ie B3=fertiliser heading)

=offset($A$1,match(A4,$a$2:$a$2,0),match(B3,$B$1:$AB1,0))

this is clearly very simplistic as your data base is a single row for apple, but say you had orange and lemon also it might be

in a20 =offset($A$1,match(A20,$a$2:$a$4,0),match(B19,$B$1:$AB1,0))

 

[yellowjkjeep]

if you list say 50 headings for apple, that is your database, apple in A2 and details in B2 to AB26

so select apple in A4, and say you want B4 to be fertiliser (ie B3=fertiliser heading)

=offset($A$1,match(A4,$a$2:$a$2,0),match(B3,$B$1:$AB1,0))

this is clearly very simplistic as your data base is a single row for apple, but say you had orange and lemon also it might be

in a20 =offset($A$1,match(A20,$a$2:$a$4,0),match(B19,$B$1:$AB1,0))

Thanks! I will give that a try. I was trying different thinigs and I got it working with

=IFERROR(VLOOKUP(B6,DATA!B6:Q6,2,0),"0")

in cell C6 and then copied that across the row. This works until there is an empty cell in the main database, and then it returns "0" in the remainin cells in that row. I'll provide a picture below. This is going to end up being a pretty extensive database and I'm just starting to build it. I've never used offset and match before so I'll look into those. Again, I appreciate your help. Thanks!

 

[oldbrewer]

when you enter apple in A6 do you want every heading from the lower table in the same order ?

 

[yellowjkjeep]

when you enter apple in A6 do you want every heading from the lower table in the same order ?

It doesn't matter. I can set it up that way.

=IFERROR(VLOOKUP($B6,DATA!$B$6:$Q$8,2,FALSE),"")

Currently I have this and it's working but I've had to manually change column number for each column.

Now I guess I just need to set up the database. I tried the offset and match but didn't understand it very well.

 

[oldbrewer]

assuming you have 1000 rows in your database in A2:A1001 and one of them is daffodil
then =offset($a$1,match("daffodil",$a$2:$a$1001,0),1
will return the cell to the right of daffodil in your database. (obviously daffodil will be in a cell and you will use that cell reference)

you cannot have a dropdown with 1000 choices by the way

still don't really know what your problem is.....

column number is easy - put numbers 1 to say 50 in the top row and instead of 1 in the formula put the cell ref of the one

 

[Peter_SSs]

@yellowjkjeep
1. You say you have (or will have) a large database. If that means you might end up with a lot of OFFSET functions, be aware that OFFSET is a volatile function so could adversely affect your sheet performance if used a lot.

2. There are two problems with the images you are posting:
a) For some of them at least we do not know what the sheet name is so it is hard to correlate your descriptions with the images.
b) We cannot copy the sample data from the images to test with.
Therefore, I'd suggest that you make a small sample data file with some dummy sample data and investigate the link in my signature block below for posting sample data. That way we would be able to overcome both a) and b).

you cannot have a dropdown with 1000 choices by the way

I don't understand that. Even from as far back as Excel 2003 (at least) it has been possible to have that many choices in a Data Validation drop-down.
There have been limits as to how many items will display in the drop-down (currently 32,767 but even in Excel 2003 that was 1000) but the validation can actually include more.

 

[oldbrewer]

I stand corrected. But I would not want such big numbers in a dropdown. OP wants to select "daffodil" and get 20 or 30 cells with info related to daffodil. I get the impression he will use the drop down only a few times a day. But, OP, give us more info.

 

[yellowjkjeep]

@yellowjkjeep
1. You say you have (or will have) a large database. If that means you might end up with a lot of OFFSET functions, be aware that OFFSET is a volatile function so could adversely affect your sheet performance if used a lot.

2. There are two problems with the images you are posting:
a) For some of them at least we do not know what the sheet name is so it is hard to correlate your descriptions with the images.
b) We cannot copy the sample data from the images to test with.
Therefore, I'd suggest that you make a small sample data file with some dummy sample data and investigate the link in my signature block below for posting sample data. That way we would be able to overcome both a) and b).



I don't understand that. Even from as far back as Excel 2003 (at least) it has been possible to have that many choices in a Data Validation drop-down.
There have been limits as to how many items will display in the drop-down (currently 32,767 but even in Excel 2003 that was 1000) but the validation can actually include more.

Thanks for the reply!

I am working on getting those AddIns setup so I share that way, forgive me if this is not allowed, but I have a download link from my website. I have created a page there to share this with y'all until I can get everything setup to share the way you've stated above.

Yes, I want to use dropdowns for the first 2 columns and then have everything auto fill from there. Have a look and I'll be working on the offset/match this evening. Again, thanks for all the help so far. I am definitely an amateur but learning as I go!

http://www.thistinyfarm.com/plant-database-test

 

[yellowjkjeep]

I stand corrected. But I would not want such big numbers in a dropdown. OP wants to select "daffodil" and get 20 or 30 cells with info related to daffodil. I get the impression he will use the drop down only a few times a day. But, OP, give us more info.

You are correct. I would like to use dropdowns. Column A will have about 50 or so drop downs and Column B will have dependant drop downs based on Column A. Column A will be main type of plant, like APPLE, while dependant Column B will be the cultivar, such as ANNA.

I cannot get any of those above mentioned ways to share my worksheet so I have posted it on my website and it is available for download. I am only using 2 sheets at the moment, 'DATA' & 'SHEET2'