Calc: Automatically fill text if in a cell a code is filled in

English
#1

In Calc I am trying to automate some things.
I made a sheet in my spreadsheet called “drop down menus”.
In this list there are a few colums

Column A: Code
Column B: Release
Column C: Wholesale price
Column D: Consumer price

Now I want to automate that if in my sheet named Products select the Code (made a drown menu for this) it automatically fills in the Release in to another cell.

Here a screenshot of the Products sheet

image
image754×178 4.69 KB

So when I select MFR001 under Catalog# in the first sheet, it should automatically fill in “Product” with the release title coming from my column B in my drop down sheet.

I’ve been trying various IF functions, but get stuck.
So any help would be great :slight_smile:

#2

Why did you paste an image instead of attaching an example file?

#3

VLOOKUP.

1 Like
#4

Because I am new here and wasn’t aware I could attach files instead of images.
But here you go.
Untitled 1.ods (11.4 KB)

#5

thanks, this works :slight_smile:

Though, find something strange. At some point in the list, when I use the code
=VLOOKUP(E2,$‘drop down menus’.$A2:$D176,2) it doesn’t correlate anymore.

I have update my example to show. In Drop down menu there are 148 lines it should correlate. But at some point it starts to show rather strange outcomes.
Untitled 1.ods (16.6 KB)

For example if I choose code MFRC005 this should show the text “| Annea Lockwood & Maze - Bayou Borne”, but it shows “Jos Smolders - Textuur 1”.

#6

See also:
disasl118585_AndSomeSuggestions2.ods (18.0 KB)

1 Like
#7

Thanks @Lupp going to check how you did that with all columns.

#9

@Lupp unfortunately still get the same error as with my previous one.
I have pasted my full list of produts in the LookupTable in your document, and get the same result as I have in my own document.
See my updated version here
disasl118585_AndSomeSuggestions2.ods (20.2 KB)

#10

Add the function fourth argument ,0

`=VLOOKUP(E2;$'drop down menus'.$A2:$D176;2;0)`
1 Like
#11

Ahh great, together with @Lupp his info this works :slight_smile:

Cheers

#12

Two last questions, I hope.
For the Price it now doesn’t take in the correct prices.

I use this code: =IF(E21="","",VLOOKUP($E21,LookupTable.$A$2:$D$101,IF(F21,3,4,)))
Similar as to how @Lupp had it, but with the corresponding columns in my original file.

And is there a way to not have the “IsWholesale” in a column, but somehere else in the document, for example in the address field of my invoice and refer to it?

See my example on how my invoice normally looks (without private data of course)
example.ods (40.1 KB)

#13

My stupid error. (I think the original sample was ordered.)
See:
disasl118585_AndSomeSuggestions3.ods (28.0 KB)

If your lookup table has items below row 101 you need to adapt ranges.
That’s a general issue with spreadsheet usage of this kind.
I think it was already suggested to use named ranges. You then need to not append new rows to the table, but to insert them above the current limit.

Concerning “wholesale”: I assumed the distinction was needed per product, and therefore used a reference relative in row. If you want to have the wholesale attribute for the complete invoice in one place, use a cell anywhere and refer to it absolute.

2 Likes
#14

Wow, you both really helped me a lot.
It all works now, and I learned a bit more about how this work.
Will have to play with it more often in other spreadsheets, but this is great.

Thanks @LeroyG and @Lupp

1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.