LibreOffice Calc - Drop Down List Other Cell Conditional Cell Value

English
#1

I’m trying to setup a Time Zones list. I’ve got a set of Time Zone Columns in a separate spreadsheet - One for PST, One for MST, One for CST and One for EST. I’ve got a drop down list created for the four timezones: PST, MST, CST, EST. If I choose PST, I want the appropriate row (value) selected from the other spreadsheet.

Example: Spreadsheet:

PST MST CST EST
01 02 03 04

01 is Cell A3, 02 is cell B3, 03 us cell C3, 04 is cell D3

Spreadsheet is Zones

So, if PST is selected from the drop down list, I’d like the result to be (value) Zones.A3. If MST is selected from the drop down list, I’d like the result to be (value) Zones.B3. And so on.

I’d like to then be able to copy this formula down in the destination sheet so that each cell would then adjust depending on the selection from the drop down.

What I would like to know is how to set this up appropriately for it to work?

#9

Credit to Joshua4 for the Switch Solution.

#2

Here is an old-school solution. Modern folks might do it a little differently.

Notice that here the entire table of time zone names and relative number values has been selected and turned into a Named Range, TimeZones (don’t use just Zones for quirky reasons, it seems). Then the time zone names have also been selected and turned into a Named Range, TimeZoneTitles, even though it is part of the whole table. (You might be able to use OFFSET to do this from TimeZones, but let’s keep it simple and just create another Named Range.)

Next, the B5 picker cell droplist is populated by going to Data>Validity then selecting Cell Range and entering TimeZoneTitles. That puts the PST, etc., into the droplist.

Then in C5 HLOOKUP (horizontal lookup) is used to go across the entire zone table, find the matching title that has been selected from the droplist, and give the 2nd cell value down in the table, the one right below the picked title. Notice that since the zones are not in alphabetical order you have to have FALSE at the end of the HLOOKUP argument list.

SimplePickForZones.ods (9.3 KB)

#3

Cracked the code on this. Based on this question and answer:

LibreOffice Calc - Drop Down List Other Cell Conditional Cell Value - English - Ask LibreOffice

My code (by comparison):

=IF($E$3=“PST”,Zones.$A3,IF($E$3=“MST”,Zones.$B3,IF($E$3=“CST”,Zones.$C3,IF($E$3=“EST”,Zones.$D3))))

Haven’t tried Joshua4’s solution yet. Yours came in b4 mine did, so the “solution” is yours. I was looking for a comparable HLookup solution but couldn’t find it. So, Thanks.

[For clarification’s sake, “$E$3” is the Drop Down list.]

#4

:slight_smile: It’s not a race, but thanks for the cred. Really, this is meat-and-potatoes sort of stuff.

If you are going to hard-code, consider the SWITCH function:

SWITCH($E$3,"PST",Zones.$A3,"MST",Zones.$B3,"CST",Zones.$C3,"EST",Zones.$D3)

Do take a look at using lookup functions though…LOOKUP, HLOOKUP, VLOOKUP, and nowadays coupling INDEX with MATCH. Unlike some things (such as array formulas), lookups are pretty easy to master and have lots of power.

1 Like
#5

It’s not a race, but, in the interest of fairness, didn’t seem right to give it to myself. I like the Switch code. It’s simpler, more elegant than mine. What I need to find is a good reference on LibreOffice Calc code, comparable to the ones out there for Excel. Too much of the code is different enough that getting a book on Excel would just come up short. Looking for answers here are often situation specific and as such require tweaking for other situations. Knowing the code is a help. Been awhile since I’ve gotten into it. Another idea that might help is a Libreoffice Calc or general “cookbook”. MrExcel had a great book along these lines that works really well for Excel.

Just looked at the manual for Calc 7.1. It acknowledges that HLookup exists. Real helpful.

1 Like
#6

As I see it, almost anything you put into a sheet in Calc will be identical to Excel. The only standing exceptions are

  1. that for globalization you use the “;” instead of the “,” to separate function arguments…but that isn’t necessary in the English version of Calc, and
  2. that you use “.” instead of “!” to separate the sheet name from the cell address in a reference like your Zones.$A3.

Calc has styles, which Excel doesn’t, and Calc has the STYLES function. Calc has REGEX, which I’ve never seen in Excel…but might now be there in Excel 360. So, I just use ExcelJet and Mr. Excel online all the time.

Now, as for macros, very different. The book on that is Andrew Pitonyak’s book, www.pitonyak.org/oo.php. It’s a little dated, and a little clunky, but at some point it is a touchstone for almost any LO macro work. Also, a person will want to install MRI and learn how to use it: https://github.com/hanya/MRI/releases.

#7

Broken:

HLookup_DropDown_Broken_Example.ods (17.3 KB)

#14

Excel has cell styles but nobody uses them.

1 Like
#15

How is it broken? HLOOKUP of CST in row #2 of named range TimeZones always returns 22:00.

Possibly you want this:
HLookup_DropDown_Fixed_Example.ods (16.3 KB)

#16

This all might’ve been made clearer had I uploaded the original spreadsheet I was working with instead of just explaining it. I didn’t. That’s on me.

In Joshua4’s spreadsheet is the instruction:

“Now just use $C$5 where ever you need to.”, so, I did.

If anything, put it on me for being too dumb to understand what that meant.

Aside from that, the Switch and Conditional_If solutions are less complex by comparison. Sure, it’s a lot on one line, but, only once.

Once I grasp either Conditional or Switch, it’s easy to implement. I went a step further and tried to incorporate this solution in Josua’s last example:

https://ask.libreoffice.org/uploads/short-url/qVJs1rQlo1SaqZl5ibAf9vtkLUx.ods

couldn’t figure it out.

Asked the question here:

and got a simple solution from @Hrbrgr .

Hrbgr figured out something I was having trouble with, how to properly interpret the formulaic information in column A to achieve the desired results.

@joshua4 's got good answers, but, for me, translating them into usable variations hasn’t worked for me. And I spent several hours trying. Conditional If and Switch work for me. Not as condensed as Joshua4’s other solutions, but, I can wrap my mind around them and more easily adapt them to my needs.

If that ruffles feathers, my apologies.

Something I worked out that’s a little bit of everything. The conversion rate depends on a website for reference. Thankfully, there’s no shortage of conversion sites out there, so, if one goes down, another won’t be too far away. For that matter, same for Time Zone conversion sites. [Note: By the time I figured out that PTSD is just MST by another name (same for MSTD and CSTD) I was too far along. Trying to change it for that instead just broke it again. So, I left well enough alone.]

Conditional_IF_DropDown_Done_ODS.ods (26.4 KB)

1 Like
#8

Tried to get the HLookup Solution to work and haven’t so far. Until that’s resolved, the other two: Conditonal IF and Condtional Switch Work.

See attached:

Conditional_IF_DropDown_Example.ods (16.4 KB)
Switches_DropDown_Example.ods (23.2 KB)

#10

HLookup_DropDown_Repaired_Example.ods (45.4 KB)

There were several things that got broken. Look this revised version over carefully if you are wanting to start learning some slightly more powerful techniques. Also note the use of conditional formatting to show that it is the next day in the Philippines.

#11

Works, but, is only as good as the reliability of the web page. Hard coded is more work, but, more reliable.

Back at ya: Exchange Rate (Though same problem)

HLookup_DropDown_Repaired_Example_Peso_Rate.ods (29.4 KB)

#12

The sample I gave doesn’t actually depend on the website. That is just there as a hyperlink to confirm values. It is a cross-check.

The sample sheet attached does a simple handling of exchange rates. Of course, don’t invest based on the sample spreadsheet attached! It is for technical demonstration only.

HLookup_DropDown_Repaired_Example_Peso_Rate.ods (23.0 KB)

#13

Joshua4’s shown me the more complex ways to get things done. If you understand complex, necessary to modify it, then, Joshua4’s method is the way to go. If not, Switch (also Joshua4) and Conditional IF are much simpler, comprehensible, modifiable ways to go.

Joshua4, I appreciate the effort, but, it adds layers of complexity requiring comprehension that may not be common.

#17

I totally understand. If you are interested, check out a Leila Gharani video on HLOOKUP or something when you have a break some day. [Just don’t worry about “New Feature!” videos, since they are usually about Excel 360 and often do not apply to Calc.]

https://www.youtube.com/c/LeilaGharani

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