[SOLVED] Collect cell data and display in new cell.

[pherriot]

Hi Everyone.
Especially to Villeroy, Keme, Roblyd, FJCC, RusselB and Luppe. Apologies if I missed anyone.

We have (Agents created themselves) a large excel file full of Agent tips and templates, abbreviations, mini calculators (formula in 2 cells)....all to help them do their job. Im keen for the Agents to see we can use their hard work in a way that doesnt mean we change the look and feel of their original file....I was hoping to achieve a similar mechanism to a HTML frame, where selecting from the menu on the left changes the page info in the "main" frame window. See below:

So I left what I assumed was the easy part to the end...a simple link to the location the excel to bring the data into the cell in "Sheet1.C2"

I have the usual Data Validation with INDEX and MATCH working to have menus change according to the first selection and then I have SUBSTITUTE to recover and combine the selected results (Sheet1.C2), all thanks to the above Gentlemen. I cannot believe the difficulties I am having in collecting the texts from the appropriate sheets and cells...what I assumed was the easy part. This would be much better respresented in an HTML frame but I am keen for the Agents to maintain their look and feel as they created it, and also want them to continue adding to the file as they go. See below for screen for what I imagined was simple:

...as always, any help is appreciated and thanks to the core volunteers and moderators who help on a daily basis.
Cheers.
P

Edit: Moved from Calc Tutorials sub-forum; Tutorial forums are not for questions. [robleyd, mod]

[Lupp]

Sorry.
I tried to understand, but did not succeed, even after downloading the attachment and looking inside.

This is not about Excel and not about html, and not about lots of different topics. It's about Calc, and the subject should set additional limits.

So, what should "Collect cell data and display in new cell" mean? Please tell us where the data are to take from, what kind of selection probably is needed, and in what way the selected data should be diplayed (in the other cell).

Anyway: A spreadsheet cell is supposed to display one "thing". Basically cells showing results are supposed to contain calculating formulae. If the data are numbers (as it originally was mandatory with spreadsheets) that's rather clear. If the data are texts much less support is available, and things get complicated. If the data are of different types no strict "collection" is possible in a single cell.

An accumulating standard function for texts is not implemented in any version of AOO, not to speak of your very old one.
LibreOffice has working implementations of accumulating TEXTJOIN() and CONCAT() since V 5.4.

[Villeroy]

Like the vast majority of spreadsheet users, you try to turn a calculator into a database. And you do it badly because the structure does not follow any rules

[pherriot]

Hi Lupp.
Sorry I was trying to not leave a long convoluted question. In response to your question:

Lupp: So, what should "Collect cell data and display in new cell" mean? Please tell us where the data are to take from, what kind of selection probably is needed, and in what way the selected data should be diplayed (in the other cell).

Collect cell data refers to Sheet 1 A2 + B2 contents (which change depending on drop down menu selection in those cells)

display in new cell refers to Displaying the above metioned "Collected" Data. In a new or the same cell, I thought best a new cell to keep it clean!

data is taken from the additional sheets (Inbound_Admin_Tasks, Outbound:Admin_Tasks, Inbound_Voice and Outbound_Voice) and and references (Inbound_Admin_Task1, Inbound_Admin_Task2, Inbound_Admin_Task3, Inbound_Admin_Task4, Outbound_Admin_Task1, Outbound_Admin_Task2, Outbound_Admin_Task3, Outbound_Admin_Task4, Inbound_Voice_Task1, Inbound_Voice_Task2, Inbound_Voice_Task3, Inbound_Voice_Task4, Outbound_Voice_Task1, Outbound_Voice_Task2, Outbound_Voice_Task3, Outbound_Voice_Task4)

How is the data to be displayed? In its current state, so this could be 6 cells or it could be 1 cell, which is why I went for named references so that in the worst case scenario I could go to the data rather than have it brough to the cell. It will be raw text strings as it is work instructions on how to complete a task.

[pherriot]

Like the vast majority of spreadsheet users, you try to turn a calculator into a database. And you do it badly because the structure does not follow any rules

Thanks for the feedback Villeroy, please see that what "the vast majority" are trying to do is provide ways to use applications for users who would never had used it traditionally, which eventually helps in eveolving the tool. I would suggest to you that the reason why so many add-ons have been applied to Excel and Libreoffice calc are precisely because of the vast majorty. I can see how it appears I am using it not as a calculator or even a database, however if for example I were to use each sheet with VLOOKUP, the cell contents become unimportant and as I understand it each sheet becomes its own little mini-array.....which is using the tool correctly I think.

...anyway, even though your answer does nothing to help, I still genuinely appreciate your time.
Cheers, Paul.

[Lupp]

I still dont't understand. Have a look into the attachment, please.
(You needn't hurry. I will be off for a while anyway.)

[Villeroy]

Thanks for the feedback Villeroy, please see that what "the vast majority" are trying to do is provide ways to use applications for users who would never had used it traditionally, which eventually helps in eveolving the tool.

Datbases are evolving and calculators are evolving like street cars and aeroplanes do.
If someone would "evolve" spreadsheets in this direction he either had to fork into another software because many new features would be contradictive to the existing calculator nature or he had to create something insanely complicated. In the first case you would develop a bad variant of yet another database program (like MS Works and other extinct products). In the second case the monster would leave behind all compatibility with similar applications and nobody would use it because of its complexity and because import of existing data into the new functional context would be too difficult.

Calc and Excel provide a lot of database functinality but first of all, you have to learn how to use them. You can not dump arbitrary data in arbitrary places and then hope that some existing feature is able to make sense of it.

[pherriot]

Hi Lupp.

I have included a more detailed explanation in the cells you requested.

Essentially I have no resrictions with regard to cell sizing and a maximum of 19 cells to be displayed at any one time. It will always be 19 cells whether empty cells or not.

„Resource“ selection (A2) + „Areas“ selection (B2) refers to a sheet and named reference in this document. The cells after the named reference and counting down 19 rows to be displayed either here or in a new cell. Sometimes there will not be 19 cells of data but pulling in empty cells is not an issue as we will format the data into 19 cells or less. All items will be listed, its then up to the Agent to decide to use the steps or not. We will not be omitting any data at all.

I understand that we are forcing Calc to do something it was not intended to do, hence there are no formatting requirements to make it look beautiful….simply displayed raw text (steps on how to successfully do their work).

...looking forward to your reply.
Thanks in advance.
Paul.

[Lupp]

I would suggest you take very seriously what Villeroy told.

If you insist on completing your project based on Calc, you should learn lots of things about spreadsheets and then thoroughly redesign your sheets. Spreadsheets mimicking small databases in a sense should regard the most basic rules for DB, at least, among them that tables should be normalized (in a specific sense.) If you actually decide to disregard this concerning the segmentation of your data columns for urgent reasons, you need at least to do it in a clear and hence unchanged way. The best (and final thing) I can do for you is to supply the attached example.

Once again: Make sure first you can achieve the needed basic functionality and implement it! Design everything with this goal in mind! Any considerations concerning formats or output presentation (prettyprint) come last.
If your project file is working and you are discontent concerning the output, you may ask another more specific question.

[pherriot]

Hi Lupp.
You rasie a good point, I have far superior knowledge on Web Design than Excel and DBs. So we are considering moving to a simple HTML frames mechanism. However that brings its own problems with regard to Inranet security and how the company polices that. We are still decinding....until now we have been working with raw text and now we have a very crude COntents page (with names reference links) and a "Home" button to take you back there. The tool still has the same functionality but is very primitive.
...to see your version working is amazing! I looked over the functions and unfortunatley when you nested 6 different ones in the data sheets for (I think) self checking for data entries....something short circuited in my brain. I feel I will not attain enlightenment Master-Yoda. However I am taking it home with me this weekend to drool over
...I will try my hardest to correctly investigate, compile and phrase my question back to you so that your honesty, time spent and hard work do not go unaprreciated.

Lupp, I hope you have a wonderful weekend and next time you are Mallorca (Playa de Palma), Im buying you a nice cold beer.
Cheers.
Paul.

[pherriot]

...incredible. Your example has self checking for sheet and content info, it has "info found" section, it has auto fill of content found too. Youve even thought to include conditional formatting with a ISNA function to alert for "no info found".

I get it now what you meant by creating a formatted approach to storing the data, and at least trying to adhere to some sort of DB rules. I have taken this back to the floor and am discussing reformatting our knowledge DB. Ill come back with our final design and examples, its the only way I know how of honouring your help Lupp.
Be right back....

[pherriot]

OK, its taken me this long ot understand to a level where I can do what I want with the tool.
Drop down referring to each other, sheet and cell checks for content, offset array referencing for data etc.

Im on clean up now.
Can anyone help me insert the ISNA function inside the array?
=OFFSET(INDIRECT($C$43);1;0;30;9)
Cheers.
Paul.

[Lupp]

Can anyone help me insert the ISNA function inside the array?
=OFFSET(INDIRECT($C$43);1;0;30;9)

The formula looks basically as if taken from my example, but there only one similar formula occurs:

Code: Select all

{=OFFSET(INDIRECT($H$3);1;0;19;1)}

This formula is entered for array evaluation, and I don't understand for what intended functionality a subexpression using ISNA() should be included there. Need info.
Anyway, ISNA() is well suitable creating CF in many cases. Using it in formulae designed to return data depending on whether or not an expression returns the #N/A error, is mostly inefficient. For related cases LibreOffice for a long time now (V 4.0 or higher) has the IFNA() function.

[pherriot]

Hi everyone.
Sorry its been so long, but here is the "almost" finished tool (just some basic clean up and filling the library to do.

So in essence this Open Office calc file was created to bypass corporate design, legal, IT and mamangement processes thus providing a much needed tool in a realistic timeframe.
It behaves exactly like a framed web browser, in that you select from menus and the display pain is filled in with the desired content.

I hope someone else can find this as usefull as we did.

....finally all the credit goes to Lupp, without who this would have fallen flat.
Cheers.
P