Thanks. Example has been uploaded.
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
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
I would make each "sub-group" a named range (highlighted in example)and use the following formula:
=IFERROR(INDEX(Growing_System,MATCH(ROWS($1:1),INDEX(Growing_System,,24),0),4),"")
Sample for "Growing System" and "System Lighting" in attached.
Last edited by JohnTopley; 04-27-2018 at 01:47 PM.
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
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks