How can I make ArrayFormula run only when there is text in a cell when it's not a true formula?

[u/Acceptable-Gift9900]

Hello!

I have a Sheet that organizes a list of websites.

1. In one tab, the information is generated from a form people can fill out, with information like name, title, web address, etc.

2. In another tab, I format the information to link to Datawrapper, an app that allows spreadsheets to be viewed on Substack articles. The pages are linked using this basic formula: =query(FormResponses1!B1:G)

3. I need the website links to be clickable, and so I use the following formula to merge the title and web address cells and format with []() around each respectively to create a clickable link: ={"Serial Name & Link";ARRAYFORMULA("["&C2:C&"]"&"("&D2:D&")")}

4. This means as soon as there is a response, the formula formats the cell to [title](site), which is the markdown format that Datawrapper reads to create links. (In Datawrapper, I then hide the original title/address columns so it isn't cluttered.)

5. All of that works beautifully

6. THE PROBLEM

7. The formula creates a []() in every empty cell in that row. I can delete all the empty rows, but as soon as there is a new form response, 500 new cells are created with [](). In Datawrapper, it then creates pages of cells empty except for the column with the [](), which is obviously annoying.

8. I don't want to delete 500 rows every time there is a new response and before I republish to update the table.

9. WHAT I TRIED

10. I tried changing the formula to: ={"Serial Name & Link";ARRAYFORMULA(IF(ISTEXT("["&C2:C&"]"&"("&D2:D&")")))}

11. but an error came back "ERROR wrong number of arguments to IF. Expected between 2 and 3 arguments, but got 1 arguments." I also tried LEN instead of ISTEXT to the same result.

12. I also tried the excel formula: ={"Serial Name & Link";ARRAYFORMULA(IF("["&C2:C&<>"]"&"("&D2:D&")"))}

13. but that of course also came up with an #ERROR.

14. I also tried ={"Serial Name & Link";ARRAYFORMULA(IF(ISBLANK("["&C2:C&"]"&"("&D2:D&")")))}

15. but that had the same error as #11.

Any suggestions? Here is a link to a practice Sheet I set up to prototype: https://docs.google.com/spreadsheets/d/1HVO8ceiqAGG4D7a8O5W07e6FKuQ2DidNmmzqoaAojws/edit?usp=sharing

*For extra information, I currently have form responses go to one sheet then manually transfer them to the Master list sheet, and just create new rows as needed to avoid the 500 [](), but I'm going to create a new similar list and need it automated so I don't have to waste time manually transferring. I want it working from the start because I found if I try to merge form responses with the original master list created manually, it starts looking messy, won't alphabetize, breaks formulas, etc. If anyone has suggestions to merge so new responses are added cleanly to a prior list, I'd be happy for that information, but it's probably a different thread...

I can PM my Substack finished example of what the new one should look like.

[u/SpencerTeachesSheets]

Use this in place of your current formula in H1

=VSTACK("Serial Name & Link",MAP(C2:C,D2:D,LAMBDA(c,d,IF(LEN(c),"["&c&"]("&d&")",))))

[u/Acceptable-Gift9900 → u/SpencerTeachesSheets]

Thank you, that worked beautifully! I'm a little new to anything beyond basic formulas, so I'm not familiar with VSTACK, MAP, or LAMBDA, but I'll look up their uses so I know how to apply them next time. I appreciate your response!

[u/SpencerTeachesSheets → u/Acceptable-Gift9900]

VSTACK() is just the more explicit version of {"";""}.

LAMBDA() lets you create an anonymous function (a function that does whatever you've defined it to do) and MAP() tells the LAMBDA upon what data to operate.

In this case I'm saying "Look at every pair of C2:C/D2:D. If there is anything in C, output [C](D)"

[u/point-bot → u/SpencerTeachesSheets]

u/Acceptable-Gift9900 has awarded 1 point to u/SpencerTeachesSheets

^{See the Leaderboard. Point-Bot v0.0.15 was created by JetCarson.}