move latest row to other sheet based on column

idigitsys

I created quotation sheet for internet service pricing where each internet service provider (ISP) can quote me 2 different options based on bandwidth and contract term : 500MB (24 months), 500MB (36 months).

I created form to be sent to vendor and quote me will create transaction row by using "request for update". It will generate more as bidding going back and forth as shown example below.

For store 1, TransID 1 and 2 are created by request for quote and vendor updated with price. Then Trans 5 and 6, both AT&T and verizon came back with lower pricing.

Up to here, i was able to do it. The problem I have is when I would need to created another sheet that only can see the latest of individual pricing by bandwidth, term for each store.

The there are several things involve.

First, it has to collect latest transaction from each quotation (by storeID, options (Bandwidth, term - 4 options).

Second, 4 options has to be in different row. The reason I was trying this way is because when I create the report and do grouping, having different options in same row makes difficult to create comprehensive report.

I would be very appreciate it if someone can help me with this. Thank you

idigitsys

=IF(CONTAINS(DISTINCT(StoreID$1:StoreID@row), DISTINCT(ISP$1:ISP@row)) = "[BididingID]", INDEX(BiddingID:BiddingID, MATCH(MAX(Created:Created), Created:Created)))

idigitsys

=IF(CONTAINS(DISTINCT(StoreID$1:StoreID@row), DISTINCT(ISP$1:ISP@row)) = 1, INDEX(BiddingID:BiddingID, MATCH(MAX(Created:Created), Created:Created)))

Julio S.

​Hi @idigitsys ,

It appears that you'd like to return in another sheet the latest transaction data for each store and provider. To do this, I would first create a formula on the destination Trans ID column that returns the latest transaction based on the store and internet provider (you can add more parameters as needed if you'd like to refine the results further). An example of this formula would be:

=MAX(COLLECT({Transaction ID}, {Store ID}, StoreID@row, {ISP}, ISP@row)) - each {} reference represents the whole column with the same name in the source sheet

Once the latest TransID is return based on the requested parameters, you can create different INDEX(MATCH()) formulas for each of the values to return in the additional fields (Bandwidth 1, Term 1, Monthly charge 1 etc..) Here's an example on how that formula would look like to return the Monthly charge of the latest given transaction on a certain row:

=INDEX({Monthly Charge 1}, MATCH(TransID@row, {Transaction ID}, 0))

When new quotations come for the same store and ISP as they would have their unique Transaction ID, the Transaction ID in the destination should update automatically and with it, the different values for each field to calculate.

You may want to review the following articles for further information on how these formulas were built:

• Formulas: Reference Data from Other Sheets (https://help.smartsheet.com/articles/2476606-formulas-reference-data-from-other-sheets)
• MATCH Function (https://help.smartsheet.com/function/match)
• INDEX Function (https://help.smartsheet.com/function/index)
• MAX Function (https://help.smartsheet.com/function/max)
• COLLECT Function (https://help.smartsheet.com/function/collect)
• Set Formulas for All Rows with Column Formulas (https://help.smartsheet.com/articles/2481944-set-formulas-for-all-rows-with-column-formulas)

I hope this can be of help.

Cheers!

Julio