Hi all,
I am looking for some help setting up cell references for data pulling from a source with variable data in rows.
Specifically, I am setting up a spreadsheet that will work with our label printing software. We have a column in the spreadsheet where we enter a quantity ("QTY" header in attached file) for the quantity of labels we want to print.
Rather than manually inputting this each day, I am trying to setup a formula for the proper data to be pulled from a csv file that we can easily export from our production software.
The exported report "Bread Production Totals by Customer Type" has 3 columns, "In house", "Wholesale Customer", and "Grand Total". It is categorized in this way because for certain items we print labels for the "Grand Total," and for other items we print labels only for the quantity listed in "Wholesale Customer."
I found a functional formula that is currently pulling quantities from a tab in our label spreadsheet:
=IFERROR(INDEX('Bread Production Totals by Cust'!$D$2:$D$100, SMALL(IF('Bread Production Totals by Cust'!$A$2:$A$100=$A2, ROW('Bread Production Totals by Cust'!$A$2:$A$100)-MIN(ROW('Bread Production Totals by Cust'!$A$2:$A$100))+1), ROW($1:$1))), "")
note the above formula is pulling data from column D "Grand Total," and some items require pulling data from column C "Wholesale Customer."
This works decently, but would be better if we could reference a csv file easily exported from our production software rather than a tab on the same spreadsheet, and ALSO much better if we could reference columns by name rather than letter.
I am attaching our excel file for the label printing and the csv file exported from our production software. Let me know if I can provide any clarifications! Thank you so much.
