Forum Discussion
Click a cell value into another cell
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?
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?
- 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 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:
https://www.linkedin.com/pulse/leveraging-power-query-data-model-create-dynamically-pivot-torralba/?trackingId=cDnh9lz5av8674ji06mWrA%3D%3D
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
