Click a cell value into another cell

[TLFulton]

I built a "Grower's Log" to keep track of germination and harvest times for plants based on when their seeds were planted. On the first page is the seasonal log, designed to be cleared out at the end of each growing season. On the second page is the list of plants and their data. At the moment this is a very short list, but it has potential to be a very long one. In the log, I have the option to choose a plant from a dropdown list in a dynamic range from the data table, that function works fine, and all the rest of the data populates, and the germination and harvest days will count down when a date is entered in the second column of the log. I want to be able to "click" an entry into the log from the data table, basically doing the same thing in reverse. I know this formula will be somewhat complicated, but basically I have to link the cell in the data table to the first blank cell in the corresponding log table, then enter the value that I clicked in the data table into the log, all in one formula. I've exceeded my Excel knowledge, pretty much need everything after = for this one. Also really prefer to do this without a macro, I haven't used any yet.

[#2]

@TLFulton 

One option could be to link the growers log to the plant list. Type "=" and then navigate to the plant list and select the item you want to add.

Another option could be to copy/paste special value the item from the plant list to the grower's log. If you don't want to copy one at a time, you could add a "Select" column to your plant list with an indicator (let's say an "x") as a  data validation dropdown list. Then, make your selection by entering "x" in the new select column, filter your list for your selections, then copy/paste special value over to the growers log.

Edit: Edited to remove my formula suggestion. Since the date in the growers log is hardcoded, using a formula to pick out the selected items in the plant list would cause the date to not match as you add plants to the list (the formula would re-order the list). Sans vba, the only think I can offer at the moment would be to either link the cells, or copy/paste special value from the plant list to the log.

[#3]

I was hoping I could use a formula in the PlantsList, somehow use HYPERLINK with MATCH to send me to the first empty cell in the log, and then IF statements: IF [@PLANTED]="",[@PLANTED]=[@PLANTS], something like that?? I've yet to make it work

[#4]

@TLFulton 

A hyperlink will just jump to a specified location, it won't fill in the destination cell without vba (and while you can hyperlink to a variable cell location using the hyperlink function, it doesn't trigger the necessary event to activate the vba code I think you would need). 

If simply linking the cell or copy/pasting aren't suitable, then I don't see how you accomplish this without vba. If you look at the attached workbook, it should fill in the log when you double click on the plant name from the list. If you right click on the worksheet tab and select view code, then you will see the macro code. You should be able to modify the "Set" statements for your worksheet/table/field names.

[#5]

Hi @TLFulton,

I do not understand why you would "delete" precious dataset that you have collected on plants over a season when you can use that dataset to make comparisons later on to be able to tell you what you did different that created how much yield you had for that particular season and be able to compare the data from previous season to be able to compare the yield from this season of the last season for this time same period last year?  While big corporations are building big databases about their products in order to do that very same thing to get Business Intelligence?

[#6]

There's two tables, the log and the database. The log is just a log that keeps germination and grow times, the notes can be added and amended in the database table at any time, it doesn't get cleared out. No notes are added in the log. What I'm trying to do is make the list work in reverse, so that I can click a plant into the log from the database table, rather than selecting it from the list in the log table, so that if I'm going through the database and decide I want to plant something, I can just click on it and it will send me to the log and and place the item in the first open row. Can you help with that?

[#7]

Hi @TLFulton,

I understand that you want  a dynamic solution for your spreadsheet to make your life easier.

See my article about dynamically updating a power pivot table leveraging power query and the data model:

Leveraging Power Query, and Data Model to Create a Dynamically Updating Power Pivot table | LinkedIn

A direct answer to your question:

"basically I have to link the cell in the data table to the first blank cell in the corresponding log table, then enter the value that I clicked in the data table into the log, all in one formula"

With that logic, then the formula solution you are proposing would have to map each blank cell of the entire Log Table (a memory hog similar to calculated columns), then your formula has to include a Lookup(2/1) function to find the last populated cell to find the next blank cell.

That formula would indeed be complicated as sphagetti code.

My question was geared towards creating a Log Table (where no data is deleted or removed) since its historical data much like the sales table, only you're not selling thing but growing things. (I like people who grow things) to find out and see if there can be an alternative compromise solution that can be extracted from the problem narrative.  I was not intending to make your proposal sound impossible.

cheers

[#8]

You can instead create a searchable drop down list in each of the cell to your Log where when you type the name of the plant the drop down list will filter to the specific plant (much like autocomplete)

Cheers

[#9]

Hi @TLFulton,

Here's a sample of the filtered drop down list, this list is 150 rows long but as soon as you type the first few letters it will filter down to more specific item much like a searchable drop down list that you can put on each cell of your log, or you'll have to do VBA which still involves a ListBox +VBA code, same difference: