I am using the following formula:

=ARRAY_CONSTRAIN(ARRAYFORMULA(IFERROR(INDEX('Pick Your Meals'!$E$1:$BIF$1,SMALL(IF('Pick Your Meals'!$E$130:$BIF$130>0,COLUMN('Pick Your Meals'!$E$1:$BIF$1)-COLUMN($D$1)+1,""),ROWS($1:1))),"")), 1, 1)

What it should be doing is when the total cell on the sheet 'pick your meals' is populated with a number it returns the two header fields and total, if it's a zero in the total column it should return nothing, giving me a shopping list, see link below to doc, but it's returning random zero total columns.


  • Your spreadsheet is set to "View only" for anyone but you, so no one can test anything. And making a copy would disconnect all of the IMPORTRANGE formulas. However, I have two observations. First, it seems odd to me that you would have ROWS($1:1) since that is clearly and always one row, and it's being compared with SMALL to columns. Do you mean COLUMNS($1:1)? Second, with numerous sheets as large as you have (all the way to column BIF) and trying to use individual complex formulas for every cell, you are virtually guaranteed to crash the system. Look into array formulas.
    – Erik Tyler
    May 23 at 20:20


Your Answer

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

Browse other questions tagged or ask your own question.