[Solved] List Only Rows With "1" in one Cell

[sylvestersergio]

Can I do the following without using filters but dynamically through a formula?

Code: Select all

1 |01 | 02 | trio       -->     |01 | 02 | trio 
  |05 | 66 | du         -->     |12 | 65 | ert 
1 |12 | 65 | ert        -->
  |45 | 12 | mao        -->
  |55 | 02 | ty         -->

I was starting from this formula (=IF(Y3="";"";MAX(Z$3:Z3)+1)) but it gives me iteration errors

Thanks

[RusselB]

I don't think this can be done using just formulas. A macro might be a possibility, but using one of the filter options (my preference is the Standard filter), is easily the simplest option.
As to the iteration errors, they usually come up from a formula that uses the iteration setting to avoid the circular error reference, which is what one normally gets if the cell that the formula is in is required for the formula.
Using iterations is not usually a good idea, as it can lead to results that can be progressively incorrect.

Out of curiosity, why don't you want to use filters?

[MrProgrammer]

Hi, and welcome to the forum.

Can I do the following without using filters but dynamically through a formula?

[Tutorial] Sorting and Filtering data with formulas

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[einstein]

How are your data designed?
Will they be like this?
If yes then
It is easier to make the formula in LO

[sylvestersergio]

Thanks everyone for the welcome.

@MrProgrammer
With your example I almost got what I wanted, but for some reason I get an unexcpected error.
I attached an example that will also give you a better idea of what I have to do.

[MrProgrammer]

With your example I almost got what I wanted, but for some reason I get an unexcpected error.

You're close. You'll want =COUNT(D2:D5) in F2. You've managed to get #REF! errors in G3:G5; fill G2 down into those cells to fix the formulas. In I2 you can put =IF(ISNUMBER($G2);OFFSET(A$1;$G2;0);""). Fill right and down. Then you don't need columns E or H. This may not quite match the tutorial that I suggested; there are dozens of different ways to arrange the formulas. The important part is to understand how they work.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[sylvestersergio]

Thanks! solved

I don't eveen need the count() as I can just put in the mattch(offset()) formula a high number that I know I will never reach in terms of how many rows I have to copy from