Search code examples
google-sheetsconcatenationgoogle-sheets-formulaarray-formulasgoogle-sheets-query

Pulling data from a Named range in Google Sheets

I'm wondering if this is doable in Google Sheets. I have product and ingredient list in a named range called Goods

I want to display a summary of what ingredients are needed in one cell. For example, I want the cell to say for 2 Wheat, 2 carrot.

So in natural language, I would like to look at the row, and if it has a value in it, then return that value and the header value, if it has 0 or null, I want to skip it.

Is this possible?

Solution

  • while there are various ways how to get partial ranges from Named ranges, in a long run it is more clumsy than not using Named ranges. but possible. for example to get headers:

    =INDEX(Goods, 1, )

    using INDEX, FILTER, QUERY, OFFSET, ARRAY_CONSTRAIN you can get various pieces of what you need.

    alternative:

    =ARRAYFORMULA(TRIM(TRANSPOSE(QUERY(TRANSPOSE(
 IF(E4:P10<>"", E3:P3&" "&E4:P10, )),,999^99))))

    0

    =ARRAYFORMULA(REGEXREPLACE(TRIM(TRANSPOSE(QUERY(TRANSPOSE(
 IF(E4:P10<>"", E4:P10&" "&E3:P3&",", )),,999^99))), ",$", ))

    0