Hi
How to create a formula that is circular to go through an array and return to the beginning when it reaches the end of it?
Thanks in advance
Hi
How to create a formula that is circular to go through an array and return to the beginning when it reaches the end of it?
Thanks in advance
Are you still on Version 2010? If not, please update your profile so we know what formulas are available to you.
Also, it's best to attach a sample workbook, with mocked up answers so we can test our results.
I don't understand your question without more detail (sample workbook).
Yes I am still on Version 2010
Names
Paulo
Luis
Armstrong
Celine
Julia
Richard
Nathaniel
Jackson
Merly
Column A has 170 names. Sometimes I need to select 20 names starting from row 160. This search only ends when the selection reaches the first names in the array.
What formula should I use to return to the beginning of the array?
I think you misunderstand what "recursive" means. The term you are looking for is "wrap around."
I think you also misunderstand "sample workbook." It's pretty clear that you have names in column A but we have no idea how you will indicate how many names you want, what row to start in, or where to put the results. What do you mean by "select"? Do you mean literally select the cells? Or do you mean generate a list?
I have attached a sample file to illustrate what I am guessing you want.
(I understand people have various constraints but 2010 is getting pretty old and problems like this could be solved in in the latest version very simply.)
Last edited by 6StringJazzer; 09-30-2024 at 11:45 PM.
Thanks Mr 6StringJazzer
Sorry for my poor english
Alternatively, using the volatile OFFSET formula:
=IF(COUNTA(G$1:G1)>$E$2,"",OFFSET($B$1,IF($E$3+ROWS(B$2:B2)<=COUNTA(B:B),$E$3+ROWS(B$2:B2)-1,ROW(B2)-ROW(B$1:B1)-(COUNTA(B:B)-$E$3)),0))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks