multiple select cells with formula only

[excel_fans]

Hi,
Is it possible to select cells with conditions below using formula only?
Yes, VBA is easier, but I want to know if this is possible using formula.
1. selected cells (requested): A1, A3, A6, A10, A15 .... total 20 cells.
2. the increasement between them are: 2, 3, 4, 5, 6, 7, 8 ,,,,

Thank you.

Sep 29, 2008

 

[Richard Schollar]

Hi & Welcome to the Board!

I am not certain what it is you are asking for - you can't physically select cells with a formula, only thru the user interface or via VBA. Can you explain what it is you want to do with these cells once 'selected'?

 

[Kaps_mr2]

I think this can be done - you want to select rows 3,6,10,15 etc..

In cell 01 put the value 1, and in cell P1 put the value 2.

In O2 put the formulae =O1+P1

In P2 put the formulae =P1+1

and in cell N3 put :-

=IF(ISNUMBER(MATCH(ROW(),O:O,0)),1,0)

and copy all three formulae down

You can then filter on the 1 in column N and get your selected rows. Thanks

Kaps

 

[excel_fans]

Hi & Welcome to the Board!

I am not certain what it is you are asking for - you can't physically select cells with a formula, only thru the user interface or via VBA. Can you explain what it is you want to do with these cells once 'selected'?

Thank you, Richard.
If physically selecting cells with a formula is not possible, is it possible to select them in the "memory" and then take action for these cells?

No special action in plan after the selection yet, but just want to know if this is possible.

Thanks again.

Sep29, 2008

 

[Kaps_mr2]

Did you try my idea ? Thanks

Kaps

 

[Richard Schollar]

You could certainly create a named range to hold a reference to these cells: go Insert>Name>Define and call your name "data" (without quotes - you can call it anything you like but the name must start with an alpha character or an underscore and mustn't contain spaces) and then in the refers to box type:

=A1,A3,A6,A10,A15,A21,...

until all your cells are included. There may be a maximum number of non-contiguous cells allowed within a named range, but it should work with 20 without a problem.

Then you can either use the name dropdown box in top left of your screen to select the cells or you can write formulas eg like:

=SUM(data)

which will sum all those cells.

 

[excel_fans]

I think this can be done - you want to select rows 3,6,10,15 etc..

In cell 01 put the value 1, and in cell P1 put the value 2.

In O2 put the formulae =O1+P1

In P2 put the formulae =P1+1

and in cell N3 put :-

=IF(ISNUMBER(MATCH(ROW(),O:O,0)),1,0)

and copy all three formulae down

You can then filter on the 1 in column N and get your selected rows. Thanks

Kaps

Thank you, Kaps. That is a solution.
Is there addtional way to do that instead of using column O, column P and then put the result in column N?
I am challenged with this conditions.

Have a nice day.

Sep 29, 2008

 

[Kaps_mr2]

Thank you, Kaps. That is a solution.
Is there addtional way to do that instead of using column O, column P and then put the result in column N?
I am challenged with this conditions.

Have a nice day.

Sep 29, 2008

I'm afraid I don't understand ....

 

[excel_fans]

You could certainly create a named range to hold a reference to these cells: go Insert>Name>Define and call your name "data" (without quotes - you can call it anything you like but the name must start with an alpha character or an underscore and mustn't contain spaces) and then in the refers to box type:

=A1,A3,A6,A10,A15,A21,...

until all your cells are included. There may be a maximum number of non-contiguous cells allowed within a named range, but it should work with 20 without a problem.

Then you can either use the name dropdown box in top left of your screen to select the cells or you can write formulas eg like:

=SUM(data)

which will sum all those cells.

Richard, I appreciate your petience.
Yes, that is also a solution.
If this is not an improper request, kindly help to guide me how to complete this task using "array".

Sep 29, 2008

 

[excel_fans]

I'm afraid I don't understand ....

Thank you, Kaps. You helped me much too.
Let me visit your website later to see if I can find some clues.

Have a nice day.
Sep 29, 2008.