Pulling data from a Named range in Google Sheets

2
votes

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?

1
google-sheetsconcatenationgoogle-sheets-formulaarray-formulasgoogle-sheets-query
docs.google.com/spreadsheets/d/… – KiloJKilo

1 Answers

3
votes

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