Ask Your Question
0

Should I use base or calc should be enough for this ?

asked 2019-02-04 08:54:59 +0100

fabe gravatar image

updated 2019-02-05 16:53:25 +0100

Hello guys, this is the situation: I Have an array on Sheet1, as you can see in the first image each products has its own row with some separate info in each column. Sheet1 will be the whole "database".

image description

Now on Sheet2, I'd like a way to list some of the products, may be by typing their names in column B or the row number they sit on Sheet1, to show up. The only detail that could be awkward is that the order of the info column might change from sheet1.

image description

Initially the Dataset could count 200-300 row and 30 columns roughly, but the row count could increase up to 1000-1500

If anyone have some bright suggestion, is very welcome

Cordially

edit retag flag offensive close merge delete

Comments

What/where is the "info column"?

ebot gravatar imageebot ( 2019-02-04 11:43:14 +0100 )edit

And alternatively something very basic for now, let say for example something like this :

in the CELL C2 on sheet2 a formula that would do :

=$Sheet1.C”the row number specified in the A2 cell of Sheet2”

(hope it is clear enough I know it look like a syntax heresy to some but... )

fabe gravatar imagefabe ( 2019-02-04 15:07:38 +0100 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2019-02-05 16:51:14 +0100

fabe gravatar image

For now as a first step I did put in each cell except in A column this formula:

=INDIRECT("$Sheet1."&(SUBSTITUTE(ADDRESS(1;COLUMN();4);"1";"")&$A3)).

basically the =indirect("xxxxxx"&"YYYYY"&zzzz) concatenate all fetched/given value in one character string reference that indirect go, fetch and return in the cell it is

and the (SUBSTITUTE(ADDRESS(1;COLUMN();4) return the Letter of the cell of the current cell fetchrd with the address command.

this way the column order cannot be changed for now but fetching the values on the other sheet is prioritary.

edit flag offensive delete link more
0

answered 2019-02-04 11:25:53 +0100

Lupp gravatar image

updated 2019-02-04 12:45:17 +0100

This looks as if your Sheet2 is expected to display exactly the same information as Sheet1 - or a selection defined by the content of column A, but sorted differently concerning the rows (datasets) and columns (fields).

If so: It depends on the size of the task if it can be done efficiently with a couple of spreadsheets. Basically there exist solutions by formulas and solutions by interactive tools as well.

If not so: Describe the task to more detail. Please edit your original question appending the additional information to do so. (Do not misuse the "Answer" option for this.)

===Edit1 with respect to additional information: 2019-02-04 12:45 CET===
The size of up to 1500 datasets and assumabbly also more than 4 fileds (columns) will also come with requirements concerning many aspects of safety and security, and with probalbly needed future enhancements introducing many additional variants of evaluation of the primary data.
That's clearly a database case then. A sufficiently reliable and efficient solution by spreadsheets would most likely need user programming then, but nonetheless not be more than an amateurish surrogate for a full-grown database.
Since I personally dislike databases I never trained myself sufficiently in desing applications with them. Thus I am not the right one to suggest specific solutions. You surely know, that Base not is a database of its own, but mainly a "connector" to arbitrary database systems. There only is provided a specific database which allows to put everything (structure and data and all) into a wrapped file. Currently there is a period of changing from HsqlDB to Firebird insofar. I don't know the details.

edit flag offensive delete link more

Comments

It is exactly what you have stated and perfectly reassumed in the first place, so I added the average amount of data to be processed in the main question.

fabe gravatar imagefabe ( 2019-02-04 11:58:30 +0100 )edit

You can find the documentation for LibreOffice here. On this page there is a button for Base.

ebot gravatar imageebot ( 2019-02-04 12:52:12 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-02-04 08:54:59 +0100

Seen: 70 times

Last updated: Feb 05