KeyValue cell insertion without macro?

[Digika]

I have a Sheet2 with data references

Code: Select all

[Key]      [Value]
Potatoe      10$

I have a working table Sheet1, which has some space inside with similar layout

Code: Select all

[extra stuff]
[keyCell] [valueCell]
[extra stuff]

keyCell uses references to form a drop-down list with absolute references populated from Sheet2; Key Column. What I want also is to tie second column sister cell to populate Value as well automatically on drop-down Select for Key

Is this possible without macro?
I've looked at this:
viewtopic.php?f=13&t=101766
but my data has to be dynamic from cells that needs to have easy-of-access editability for user

[Villeroy]

Just as a reminder: You have a little database application installed. This is a 100% database task.

Anyway:

[Digika]

Thanks i will check your example later.

You have a little database application installed. This is a 100% database task.

I cant use database, the data has to be presented in humaj-readable format for easy addition for people who never saw Excel in their life but know how to click mouse and press buttons on weird machine called keyboard

[Villeroy]

If they never saw Excel, how should they be able to handle a spreadsheet? A spreadsheet is extremely diffcult to use while looking simple.
Everybody can enter data into a database form. We do it when posting to this forum, while online shopping, online banking, every time we type something into an input box we use a database.

Just have a look at this: viewtopic.php?f=9&t=104741
Or even this one: viewtopic.php?f=13&t=104042 where somebody used a form 10 years long and forgot how he made it.

[Digika]

Just as a reminder: You have a little database application installed. This is a 100% database task.

Anyway:

is it does not work across sheets?

Code: Select all

=VLOOKUP(D30;Data.$A$2:Data.$B$100;2;0)

I need to use relative D30 because I might want to duplicate rows in a table. The "Data" sheet has fixed columnes and rows so they can be abs. However, I'm getting proper result returned in the formula preview but in the cell itself in the table I have raw formula code displayed (???)

123.png (2.29 KiB) Viewed 2497 times

i wonder if it is related to the fact I have some cells merged

[Villeroy]

The cell has number format code @ which turns it into a text entry cell which treates all input as text even when input is numeric or starts with a =.
[Tutorial] Ten concepts that every Calc user should know
Do not apply many formatting attributes to your spreadsheet draft until it works as expected. And in particular: Never merge any cells!
However, I can tell you that your spreadsheet will never be finished. Data entry in spreadsheets by untrained users is a never ending story of failure.

[Digika]

And in particular: Never merge any cells!

Yeah I guess VLOOKUP is not gonna work for me in OO, gonna have to return back to Excel.
Alright, well, thanks for all the help!

[Villeroy]

Would you please share with us whatever you get done with MS Excel? In 3 decades I have never seen a spreadsheet document where this actually works in a fool proof way.

[Digika]

Are you saying this wont work in Excel either? I thought with my issue it was an OO limitation.

Okay, then I'm gonna have to use script/macro, either a button that fills all value cells using the selected key on click or on "drop-down selected" event.
Any chance you have some of these examples lying around?

[Villeroy]

It is completely impossible to tell anything about your problem as long as you send us a picture of a document instead of posting the document itself.
Merging cells is a purely cosmetic feature with very limited use and lots of pitfalls.
Lets say you have the word "Amount" in A1 and number 12.99 in B1.
Now you merge the 2 cells.
The program asks if you want to merge the contents as well and you answer "Yes".
Now you have the 2 cell contents merged as a single text in cell A1 and separated by a space character. A1 is "Amount 12.99" and B1 is blank. Of course, you can not look up the values that used to be in the 2 unmerged cells because by aswering "Yes" you allowed the program to spoil your data. If you answered "No", the values would remain separated but B1 would be covered by the expanded cell A1.
I did not use Excel in 20 years, however back then it did exactly the same thing. If you try to mis-use a spreadsheet as a poor database surrogate, you have to abstain from all the formatting bling-bling until it does what it is supposed to do. Too much formatting makes your document unmaintainable and incomprehensible.

[Digika]

Fair enough, I appreciate the input.
Clearing format to Default/All seems to fix it for now. As I understand from the VLOOKUP help page, to address another value in further column I just need to increase column index, for example, if I have Column A with key names and Columnds B, C, D have paired values, then increasing column index

Code: Select all

=VLOOKUP(D30;DataSheet.$A$2:DataSheet.$B$100;>>>>>>>>2<<<<<<<<;0)

lets me reference values from different columns.
Question: is there a way to constrain search to column pair? Right now I have to specify ranges like A:B or A:C if I want to cover more value columns. Now, I'm SUSPECTING Calc does some optimizations behind the scenes and actually only loads 2 columns since we have columnindex as a contraint but since I cant profile it and be sure I'd like to know if there is a way to constraint it pre-emptively just because I will forever bug me.

[Zizi64]

Question: is there a way to constrain search to column pair?

Do you want get two or more results from the function VLOOKUP()?
Then you must use two or more VLOOKUPs, and you can concatenate the results - but that concatenated result will be quite useless for the other calculations...