[Solved] Creating a List - Not drop down

Discuss the spreadsheet application
Post Reply
amyhart420
Posts: 16
Joined: Sun Sep 25, 2016 12:36 am

[Solved] Creating a List - Not drop down

Post by amyhart420 »

So I've gotten stumped on my current project. I am trying to create a list of items based on which are marked with an "X".

Example:
Arrows X
Waterskin
Trail Rations
Torch X
Theives' Tools X

Would show as:
Arrows
Torch
Theives' Tools

I've tried IF and it skips cells not aligning the list correctly. Then I tried versions of LOOKUP and MATCH and it only list the first or last option. Any help will be appreciated.
Last edited by amyhart420 on Mon Oct 10, 2016 9:44 pm, edited 1 time in total.
OpenOffice X 3.3.1 on Windows Vista
User avatar
Zizi64
Volunteer
Posts: 11489
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Creating a List - NOT DROP DOWN

Post by Zizi64 »

Do you want Filtering the original list?
Or do you want use the results in another formulas?
Why you put the X at end of the string data?

Can you upload an axample .ods file with the manually filled-in source- and target cell range?
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
amyhart420
Posts: 16
Joined: Sun Sep 25, 2016 12:36 am

Re: Creating a List - NOT DROP DOWN

Post by amyhart420 »

Sorry I'm not sure what you mean by filtering.
No this is going to be the final formula in order to put the information onto the designed character sheet, so it won't be used in another formula.
Sorry I was attempting to give an example of what I was talking about.

Attached is the file for better understanding.
Attachments
example.ods
(9.57 KiB) Downloaded 159 times
OpenOffice X 3.3.1 on Windows Vista
User avatar
Villeroy
Volunteer
Posts: 31346
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Creating a List - NOT DROP DOWN

Post by Villeroy »

Attachments
DataPilot.ods
(18.23 KiB) Downloaded 160 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31346
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Creating a List - Not drop down

Post by Villeroy »

And of course, drop down lists lets you select the wanted items into the target list instead of marking them with a letter "X".
Attachments
example2.ods
(10.73 KiB) Downloaded 156 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
amyhart420
Posts: 16
Joined: Sun Sep 25, 2016 12:36 am

Re: Creating a List - Not drop down

Post by amyhart420 »

Okay so the Data pilot is definitely not what I'm looking for. That is selecting a group and sorting it. I need to select the group and then list only those items in the selected cells without skipping any cells.

The drop down doesn't work in this case because I am trying to use this current issue to transfer selected data to a final sheet.

The current project I'm working on is about 12 sheets, 2 of which are calculation space, 2 of which for conversions, 5 for information, 1 for shopping, and 1 as a information fill in, and the final character sheet. The only sheets in which will be changed after the project is finished will be the information fill in and the shopping. Then the final character sheet will be printed.

The current step is part of transferring the completed shopping list into their final page. I have the items shopped for set up on the shopping page and then on the information fill in page I have where they select the items to equip and then sort remaining items into the bag they desire, using drop down list. Then on one of the calculation pages I have the chart that list each item and IF they selected Backpack (for example) it puts an X in the corresponding cross cell.

Now I just need to set up on the final character sheet, that IF there is an X in the backpack column it list the corresponding item.

The issue with IF functions is that I can't find a way to make it not skip cells when filling in the items. IE. Using my example file the second and third item cells in the table would be blank because the waterskin and Trail rations do not have X's.

I thought a LOOKUP or MATCH would work but it only gives me the first or last "X"ed item, based on my sorting.

I don't mind a long step process or having to use multiple sheets to hide excess calculations, I just need to figure out some way to reach the final look I am trying to get without having to make the user use any other sheet besides the fill in or shopping sheet.

EDIT: Here's screenshots of the actual sheet areas.
Image
Image
Last edited by amyhart420 on Mon Oct 10, 2016 10:23 am, edited 1 time in total.
OpenOffice X 3.3.1 on Windows Vista
User avatar
Zizi64
Volunteer
Posts: 11489
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Creating a List - Not drop down

Post by Zizi64 »

Sorry I'm not sure what you mean by filtering.
example_FILTER.ods
(10.94 KiB) Downloaded 127 times
You can filtrering the data by "X", and then you can copy the data of the filtered cellrange (manually) into an another position.

Data - Filter - Autofilter...


or you can copy the filtered data automatically into the desired target cell range:
Data - Filter - Standard filter...
Standard Filter.png
Last edited by Zizi64 on Mon Oct 10, 2016 10:48 am, edited 1 time in total.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
amyhart420
Posts: 16
Joined: Sun Sep 25, 2016 12:36 am

Re: Creating a List - Not drop down

Post by amyhart420 »

Okay I see what you did there. I'm gonna try and see if I can get that to work.
OpenOffice X 3.3.1 on Windows Vista
User avatar
Lupp
Volunteer
Posts: 3703
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Creating a List - Not drop down

Post by Lupp »

There is a reoccurring shortcut circuit here concerning questions like this one. As you say yourself the sheets you are designing are for use by others. Those "others" are supposed to be uneducated concerning spreadsheets, datakeeping, and everything except typing, being fast, and doing as the boss said. They surely must not intervene concerning the functionality of the means they have to work with. Typically spreadsheets made for use in such a surrounding have lots of "protected cells" in "protected sheets", and of automatisms to suppress any deviation from the plotted path. Those concerned with accessing them for input/update of data are now expected in addition to be unable to learn how to break this "protection" (which is easy in fact).

It is clearly the best advice in the described situation to develop and establish a database solution. Database systems are explicitly made to reliably work under the described conditions. The end application needs a thorough design by someone knowing in detail how the workflow should be and what they have to achieve and what means are at hand. These commonly are professionals. On a small scale it may be done by a very clever and experienced guy reading through the manuals and everything. Spreadsheets can never meet the typical needs for the scene. From the beginning there are missing the complete and reliable manuals you would need, e.g.

Looking at your "Bags" it seems clear that your intentions are of the described kind.

If you want to be the "very clever guy", and you finally decide you can isolate the processes under discussion from general data keeping, and implement it in spreadsheets, you may read through this recent topic: viewtopic.php?f=9&t=85497 . There is also an attachment discussing and demonstrating - in short - the three approaches concerning the requested task I can think of:
1. A solution by standard formulae reqiring helpers.
2. A solution based on functions not yet available in AOO Calc and also not completely in LibO Calc, and thus relying on user code ("macros").
3. Filtering (as you got already advised).

Please note that everything you do is at your own risk, and that insecure keeping of data and unreliable processes may endanger businesses.
The "solutions" I discuss are made to allow for experiments. They are surely not directly applicable for real world tasks.

If you want to test the part only working in recent LibO, consider to download and expand a PortableApps package of it.
Your signature is showing an outdated version of AOO.
On Windows 10: LibreOffice 25.2.4 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
amyhart420
Posts: 16
Joined: Sun Sep 25, 2016 12:36 am

Re: Creating a List - Not drop down

Post by amyhart420 »

This project is to create a character sheet for a game that myself and my friends play. The character creation process can take a hour for a single character, and that is for more experienced players due to the amount if time and Calculations of total scores and abilities. This sheet is designed in order to speed up that process by completing all of the calculations and filling in automatically so only the basics must be selected, so far I've dropped the time down to about 15 minutes which is a lot more manageable.

This is only intended to be on my own computer and filled in with myself nearby so I don't have to fear my players attempting to change other protected sheets.

I haven't used a database because I am a lot more unfamiliar with them and so that will make creating this project a lot more difficult. As well this part is one of the final pieces of the project, thus attempting to turn it into a database would destroy 2 months of hard work.

As far as the filtering I just tried to work it in and it worked for sorting out just the items with the "X" but when attempting to transfer it to the final character sheet it was still locked into the item listed cell. It made it so if the items were changed it still showed the other item that was selected. (IE. Locks into the words showed in A1 and A5 not first cell showing and second cell showing.) Another problem was if the items were changed or added the filtering did not auto update. Perhaps I missed a step somewhere in the filtering process to get it to work...

And this is the only version I have. My computer is really old and until I can get a new charger for my laptop I can't use an updated version.
OpenOffice X 3.3.1 on Windows Vista
User avatar
Lupp
Volunteer
Posts: 3703
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Creating a List - Not drop down

Post by Lupp »

I hadn't read thoroughly enough through the long thread, obviously. Sorry!
As I see your intentions now this might actually be a task for spreadsheets. It's about optimising in a creative style which I also did Calc based successfully.
However, for this specific task I would again suggest you find a way to run a recent LibO. Look at http://downloadarchive.documentfoundati ... /portable/. It's just about 123MiB of download (and a few MiB for the offline help in addition).
As your computer is running Vista it surely can connect a 1GiB thumbdrive. Allowing for some extra time to load software modules from a slow device first, it will work.
On Windows 10: LibreOffice 25.2.4 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
amyhart420
Posts: 16
Joined: Sun Sep 25, 2016 12:36 am

Re: Creating a List - Not drop down

Post by amyhart420 »

So I finally ended up finding a solution that involved about 80 cells for each bag option and a slow if and lookup function that slowly removes part of the search range and finally gets down to a single list of each item.

If anyone would like to see the final result just ask and I will update.
OpenOffice X 3.3.1 on Windows Vista
Standinghawk
Posts: 28
Joined: Mon Oct 03, 2016 2:45 am

Re: [Solved] Creating a List - Not drop down

Post by Standinghawk »

Please, Update. I would like to see how you did it. It might help me somehow. Thanks.
Using OpenOffice 4.1.2
with Windows 10.0.14393.105
Post Reply