Brian,
There are several issues affecting your application of the solution I provided. I'll try to address them one at a time.
1. One reason my suggestion is resulting in an error in your template invoice is the formatting differences between your original sample file and your actual template. Take a look at the number
3 in bold in the following formula:
=IFERROR(INDEX(Sheet1!B$4:B$46,(SMALL(IF(Sheet1!$B$4:$B$46>0,ROW(Sheet1!$B$4:$B$46)-3,FALSE),(ROW()/2)-1))),"")
The quantity 3 is a hard-coded value that matches the number of rows in the header of your source tab (based on your original sample file). In order to adapt the formula to the new template, this quantity should be changed to 9 because there are 9 rows in the 'Del & Col Note' tab before the first purchase item.
2. Next (another formatting issue), take a look at the (ROW()/2)-1) portion of the formula -- this was specifically written to produce output on lines 4,6,8,10...in the original sample file. However, in your new template, you want the output on lines 30,33,36,39...so you would need to modify this element of the formula as well.
You can see that if we put Rows 4,6,8,10...in the original (ROW()/2)-1) formula, it results in the series 1,2,3.
In order to produce the same series (1,2,3) from the rows where you'll actually be using the formula (Rows 30,33,36,39...) you can see that you would need to change this as follows:
(ROW()/3)-9
But now there's another constraint to consider -- this is the only item where I have to scold...
-- the blank rows between items on your source sheet aren't blank any more!! You added descriptions to the blank rows between items in 'Del & Col Note' Column A (e.g. multi-point doorsets, fire exit door set). This is problematic, because I original built the formula to find and pass through the (1,2,3...) non-blank rows of the source table, but now, the data you want to pass through is actually in the (2,4,6...) non-blank rows of the source table...so we need to create this new series (2,4,6...) from the row references (30,33,36,39...):
(ROW()-27)/3*2
We got it worked out this time; but next time, no curveballs, okay?
Next, we need to change every occurrence of 'Sheet 1' in my original formula to match the name of the corresponding tab in the new spreadsheet: 'Del & Col Note'. I see that you did this for the first occurrence, but not for the others...
4. Now, to revisit the B4:B46 references from the original formula. You had assumed that they should be A11:A33, but they should actually be A10:A33 if you want to pick up the first line of descriptive data you keyed in row 10 of your source table. Also, watch your absolute and relative references -- you dropped several $ from my original suggestion, which complicated the adapted formula.
So the new formula looks like this:
=IFERROR(INDEX('Del & Col Note'!A$10:A$33,(SMALL(IF('Del & Col Note'!$A$10:$A$33>0,ROW('Del & Col Note'!$A$10:$A$33)-9,FALSE),(ROW()-27)/3*2))),"")
5. Finally, you have to know the magic word in order to make this formula work...And I forgot to mention it in my original reply.
The magic word is "Array Formula" -- In case you haven't used these much before, you must confirm an array formula by pressing Control+Shift+Enter when you create or edit the formula rather than just Enter as you would when you create or edit other formulas.
"Okay..." (I hear you saying.)
"That's a little more 'help' than I bargained for..."
I've fixed the formula in the highlighted cells in the attached file. However, I haven't been able to easily test any data because you've got some data validation restrictions that won't work without the appropriate connections.
I notice that there are some other items you may want help with, but I'll wait for your lead on those.
I apologize that it's taken me so long to respond, but some personal and work items delayed me.
Feel free to ask if you need anything more. But note that you need to get your data layout finalized before you start building formulas...
All best.
Isaac
