I have a spreadsheet with 2 tabs. On Tab1 I have a cell with a value “Y” at A2 & A4. What I would like to do is to check all cell values for column A in Tab1 and if the cell value is equal to “Y”, then paste the rest of each corresponding row to one single row in Tab2. Tab2 does not have the Column with “Y” values so the cells range to copy from [using Tab1 Row1 data] is Tab1 B2 to D2 and that data will paste into Tab2 A2 to C2. And the same for any other cells in the A column that contain a Y value in Tab1. Sample added and strictly dealing with numbers and text. And I want the other sheet/tab to have all the modified data, updating itself automatically whenever something on the first tab changes.
Sample.ods (8.8 KB)
Assuming this requires basic, please provide some code and a link to a tutorial on how to use basic in calc. Thanks!
Hi and welcome. Your question has some detail, but unfortunately it falls kind of short of giving enough information to go on. Can you edit your question and include a mock up ODS (spreadsheet file) of what you want? What is the structure of the data on Sheet1/Tab1? Are you doing some sort of number crunching (using a spreadsheet to manipulate pixel data, etc.) or more traditional things, like extracting a row with a given customer name, etc.? Why you want a result isn’t so important by itself, but unless you very carefully define the question then giving the “why” helps lead contributors to the kind of answer you want.
Example information needed:
- Where is the cell valued Y. Is it part of a row of similar items (like pixel values) or different items (like names and addresses and phone numbers)?
- Where is the true/false cell? Is it part of the same row as Y, a separate part of a cluster of selection or control cells, or how does it relate?
- Do you want the row on the other sheet/tab to have a blank where Y was on the first sheet, or do you want the cells of the row to “collapse” and now have 1 fewer cell in the copied row compared to the original?
- When you say “paste the entire row…” do you mean you want to have an interactive way to, say, highlight the row in question then start the process, or in the end do you really just want the other sheet/tab to have all the modified data, updating itself automatically whenever something on the first tab changes?
Thanks. Will do.
Okay I think I got it all. Thanks.
No, the use of BASIC for this task is not at all necessary - the Calc formula for this task is not very complicated:
{=IF(ROW(A1)>COUNT(IF(Tab1.$A$2:$A$2000="";"";ROW(Tab1.$A$2:$A$2000)));"";OFFSET($Tab1.$B$1:$D$1;SMALL(IF(Tab1.$A$2:$A$2000="";"";ROW(Tab1.$A$2:$A$2000));ROW(A1))-1; ))}
(This is an array formula, you need to complete the input with Ctrl+Shift+Enter)
On this resource you can find many options for solving your problem, for example here
Any array formula is a priori complex because it contains hidden loops and operations on array elements. This can be intimidating to the inexperienced user. However, I paraphrased John Walkenbach.
But I like @Villeroy, who said here that spreadsheets are so complicated (in the sense that you need to know a lot right away to do something worthwhile) that they are a source of constant errors for beginners. You need to study hard for 2 years, and then enjoy the result of your work. 
thanks for the advice.
Naa, it is not 2 years. Just do not overstretch the tool and your aspirations right from the beginning. In the 90ies you could buy “Excel for Dummies”, “Word for Everybody” or similar publications in every super market because it was an absolute matter of course that such a complex product requires a minimum of training and education. My first bread and butter book on Excel answered all my questions within a weekend or two (selections, data types, input schemes, operators, functions, positional parameters, simple list keeping).
These day are gone. We are in the age of cheap apps and poor AI. People expect that any software is able to do whatever they want right now, anyhow. They open their first sheet and what they see is a database, layout app or tabular text editor with colours and fonts because those funny little boxes take anything you throw at it.
Well, to design intelligently in LO Calc, you need to know a little bit about relational databases.
I was just transforming a spreadsheet (document), and one of the errors was that the user violates the following principle: “The data in the field must be atomic, that is indivisible”. In general, if you want to do something with this data in the field later (filtering, sorting, extracting substrings, etc.), then they should be separated at the design stage and placed in different columns. This is not done. The data subsequently requires procedural processing. This is bad.
Users tend not to use pivot tables because they require “proper” organization of data (tuple, record).
The solution is in the attached file. Pivot Table. A small macro is used.
A filter (=Y) is applied to the first data field in the pivot table.
Sample (2).ods (12.0 KB)
File updated
Are you able to explain what the code is doing, or is that too much?
The code simply refreshes the pivot table when you activate the sheet on which it is located. The name of the sheet is set at the top of the module: SHEETNAME = “Tab2”. You can do without macro, but then you would have to refresh the pivot table yourself each time you change the data (right-click the pivot table, select Refresh).
So did the pivot table solution help you or not?