I have a simple formula:

 IF('B-Prename5'!$B$2:$F$7<>""; 'B-Prename5'!$A$1&", "; )&
 IF('C-Prename9'!$B$2:$F$7<>""; 'C-Prename9'!$A$1&", "; )&
 IF('A-Prename10'!$B$2:$F$7<>""; 'A-Prename10'!$A$1; ); ", $"; ))}

And it works as intended. My challenge here is to make the numbers of input dynamical - depending on a list:

=filter(Names!G:G; len(Names!G:G))

I can't figure out if I'm suppose to use INDIRECT what ever to combine these two. Any ideas?


There is no need to use INDIRECT. You could just replace the references to the various tabs with the cells in your filtered list using the following formula.
(Assuming the output of your filtered list is in column P)

 IF('B-Prename5'!$B$2:$F$7<>"", P1&", ", )&
 IF('C-Prename9'!$B$2:$F$7<>"", P2&", ", )&
 IF('A-Prename10'!$B$2:$F$7<>"", P3, ), ", $", ))}
| improve this answer | |
  • Ah! I see what you mean but the challenge here is that the list of variables/names often differs. Some days it will produce 3 or 23 names and some of the names would be edited to. So I need "IF('B-Prename5'!$B$2:$F$7<>"", P1&", ", )&"-part to correspond with the actual and constantly updated list of names. – Allan Bech Feb 23 at 18:32
  • But 'B-Prename5'!$B$2:$F$7 is just one of the multiple variables you use in your formula. Using INDIRECT or whatever method to refer to your tabs would not make any difference. Anyway, I don't think your existing formula could be improved any further. Your data needs to be restructured. Or come up with a different formula altogether. Both options are not related to your original question. – marikamitsos Feb 23 at 18:39
  • Please remember that as per site guidelines when an answer addresses your question, accept it and even upvote it so others can benefit as well. – marikamitsos Feb 23 at 18:39
  • Ofc your answer is acceptable I'm just stuck and in doubt if i asked the right question :-) – Allan Bech Feb 23 at 19:05
  • I'm still wondering how to build a formula where This ={ ARRAYFORMULA(REGEXREPLACE( would turn this -> SORT(filter(Names!G2:G11; len(Names!G2:G11)); 1; TRUE) into this -> IF('C-Prename9'!$B$2:$F$7<>""; 'C-Prename9'!$A$1&", "; )& IF('C-Prename3'!$B$2:$F$7<>""; 'C-Prename3'!$A$1; ); ", $"; ))} – Allan Bech Feb 23 at 19:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.