Hi Marc,
I'm going to start by giving the two tables distinct names. This avoids the necessity of including the Sheet name in every copy of every formula used to transfer data from one table to the other.
I'll name the tables according to their purposes.
Sheet 1 contains a single table we'll name Data.
Sheet 2 contains a single table which we'll name PO (as in Purchase Order).
So as I see what you've showed so far:
The way it is now, a user is able to change the quantity in "Data" and formulas result in populating the purchase amount, order# and vendor by finding the least expensive. My objective is for "PO" to call these as well as the item description and quantity, from "Data". Where I’m at a total loss is to get the PO to condense and negate the rows of items that are not being ordered. I decided (while obviously not being schooled) that the pop-ups are my solution.
You have the front sheet and its table set up the way you want it, including the generation of data in the rows where the Quantity has been set to a number greater than zero, and you want to transfer that information into a set of contiguous rows on Table 1 on Sheet 2. The Proposed timestamp, currently in column E, in the midst of each row of data to be transferred is NOT to be included in the data copied to "PO". This disruption of the pattern affects several downstream formulas. I propose moving the TimeStamp to column C of Data (the first unhidden column), moving columns C and D to the right to close up the gap.
To copy the data into contiguous rows, ignoring the rows where BUY is set to less than 1, we need to have an index column containing a list if distinct values (eg. integers of increasing value) in the rows rows to be transferred, and 'blanks or zeros in rows which are not to be transferred. Two function sets which can be used to do that are VLOOKUP, or a combination of MATCH and INDEX. The biggest hangup with VLOOKUP is that it requires the search for the index values to take place in the leftmost column of the Lookup table (in this case, "Data"). MATCH and INDEX are more flexible in this regard. The search values must be placed in a single column, but there is no restriction on which column must be used. My choice would be to place it at the right edge of the table—for the example, in column M, which in the example appears to be empty.
MATCH finds the position of the search value in a list. Since the first row to potentially contain data, and the first row on "PO" to contain transferred data is row 3, we will set up the formula calculating the index values to crete 3 as the first index value in column M.
Data:
Data to be transferred is placed in contiguous columns D (Item) to H (Vendor)
The iNdex formula, shown below, is in column M (Index). This column is used by the lookup formula, but not by the human users. It should be hidden. The "Index" label is not required, The value 2 in M2 is required.
Other columns not mentioned were hidden in the example posted, and do not enter into the issue discussed.
Formulas: There is one formula connected with the solution.
Entered in M3, then filled down to the last row of the table.
M3: IF(E3<1,"",MAX(M$1:M2)+1)
IF looks into the cell in 'this row' of column C. If the value in the cell is less than 1, the formula places a null string in its cell and exits. If the value in the cell is 1 or greater, IF calls MAX, which returns the greatest value in the column above this cell, adds 1 to it, and places the result in its cell.
PO:
The PO (Purchase Order is a simple list of data from the rows marked by the index formula above as 'of interest.'
The formula is entered in B3 of PO, then filled down to the last row of that column and filled right to column F, the last column of that table.
B3: IF(ROW()>MAX(Data::$M),"",INDEX(Data::D,MATCH(ROW(),Data::$M,0)))
The IF statement is a test that limits calculation of the INDEX-MATCH part to rows whose row number is less than or equal to the largest number in the index column. For rows with row numbers greater than that index value, ROW()>MAX(Data::M) returns true and the formula places a null string in its cell.
For rows whose row number is less than or equal to the maximum in column F, MATCH looks for the number in column F and returns its position in that list. INDEX takes that number and returns the value from that position in column D of Data.
As the formula is filled right, the column reference "D" increments by 1 for each column while MATCH's column reference remains fixed on "M" due to the absolute reference operator ($) placed in front of the letter.
Regards,
Barry
