IFERROR lookup pulling from multiple number sequences in the same column

As always, it would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon, as it doesn't work on this forum.

I don't understand what you mean by this - "multiple numbered indexes that are all on the same column", so perhaps your example file will help to clarify what this is.

Hope this helps.

Pete

Thanks. Example has been uploaded.

Why are you starting counting at row 25, rather than row 19?

It seems that you want to reset the counter whenever there is text in column B - is that right?

Pete

Originally Posted by Pete_UK

Why are you starting counting at row 25, rather than row 19?

It seems that you want to reset the counter whenever there is text in column B - is that right?

Pete

Hey PETE. The cells above B25, generate the values in the other cells. I don't want them to show up on the invoice.
Yes. I want to reset the counter for each section on the NFT System Quote. Growing System - Herb and Lettuce AT FRAME, Propagation - Herb and Lettuce, System Lighting, Tank House, etc...

Thanks,

Eric

Alternatively, keep the formula in the upper range as it is, and put this formula in X58:

=IF(AND(ISNUMBER(--B58),B58>0),COUNTIF(X$24:X57,">0")+1-COUNTIF(X$25:INDEX(X$1:X57,MATCH(LOOKUP("zzz",B$55:B57),B$55:B57,0)+54),">0"),"-")

Copy this down to the bottom of your list (row 210).

Hope this helps.

Pete

Originally Posted by Pete_UK

Alternatively, keep the formula in the upper range as it is, and put this formula in X58:

=IF(AND(ISNUMBER(--B58),B58>0),COUNTIF(X$24:X57,">0")+1-COUNTIF(X$25:INDEX(X$1:X57,MATCH(LOOKUP("zzz",B$55:B57),B$55:B57,0)+54),">0"),"-")

Copy this down to the bottom of your list (row 210).

Hope this helps.

Pete

Thats great but it doesn't fix the formulas on the Herb Crop Invoice. The formula brings in the values for the first set of sequenced numbers from NFT System Quote.

Thanks,

Eric.

I see what you want to do, although it is not a very neat solution. You will need to ensure that the ranges are limited to the rows used for each category. You can leave the first section as it is, because the MATCH function will look for the first occurrence of the number, but for subsequent sections you will not be able to use full-column ranges. For the Propagation section, for example, which covers the rows from 57 to 93 on the Quote sheet, you will have to use these formulae:

=IFERROR(INDEX('NFT System Quote'!$B$57:$B$93,MATCH(ROWS($1:1),'NFT System Quote'!$X$57:$X$93,0)),"")

to get the quantity from column B, and this one:

=IFERROR(INDEX('NFT System Quote'!$D$57:$D$93,MATCH(ROWS($1:1),'NFT System Quote'!$X$57:$X$93,0)),"")

to get the description from column D.

For the other sections, you will need to change the ranges shown in red, for example the Lighting section covers the rows from 96 to 101. Don't forget that the first instance in each section should have ROWS($1:1) (shown in blue), so you can't just copy it to the new section and change the range.

This seems like a very complicated way of doing this compared to the original two formulae that I gave to you. You could still use that approach along with another column which records the highest number reached for each section, and that number is then used in the ROWS( ) term for the next section.

Hope this helps.

Pete

Do you consult outside of excel forum? The whole quote machine has taken use years to develop and we hold it close to our chest. Not sure I want to upload to this forum but also want the best solution to achieve the results we are looking for. You could probably look at our workbook and offer great streamlining advise. 25 worksheets and growing.
Email me at [email protected] if interested. Otherwise I will use the formulas you have provided. Kindest regards, Eric.

Hi Eric,

glad to hear that the formulae worked for you.

Thanks for the rep - if that takes care of your original question, please take a moment to select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Pete