Spreadsheet Example

Sorry if this is a bit confusing or if I’m not explaining this well enough. Using my provided picture as an example how would one auto copy data that gets inputted into that table based on estimated rates vs actual rate tables?

For example, in rows 13-15 you can see the estimated rates are extremely off in comparison to the actual rates. But in rows 6-10 the estimates are almost spot on. How would one auto copy that data & paste it into a new sheet depending on close matches / inaccurate matches?

Would it be possible to just auto copy the data based on accurate estimates / inaccurate estimates? Essentially I would be looking to just have it be so that when someone inputs the necessary data into the spreadsheet it will just automatically copy itself into a new page depending on if it falls into one of those parameters?


=ARRAYFORMULA({SORT({0; FILTER(Sheet1!M2:M, Sheet1!M2:M<>"")}, 1, 1),
 IFERROR(VLOOKUP(SORT({MIN(Sheet1!F2:F); FILTER(Sheet1!M2:M, Sheet1!M2:M<>"")}, 1, 1),
 SORT(FILTER(Sheet1!F2:F, Sheet1!F2:F<>""), 1, 1), 1, 1))})


demo spreadsheet

  • Would I just use this as a script?
    – 4tWinks
    Mar 29 '19 at 15:49
  • copy this formula and paste it in Sheet2 where you need it. and also change a name from Sheet1 to YOUR-SHEET-NAME-OF-THE-SHEET-WHICH-IS-ON-THE-PICTURE - if it's not Sheet1
    – user0
    Mar 29 '19 at 16:00
  • @4tWinks docs.google.com/spreadsheets/d/…
    – user0
    Mar 29 '19 at 16:21
  • Odd. When I paste the formula in Sheet2 I just get two columns of ascending decimal numbers. Not sure what I’m doing wrong, everything’s name correctly as my first sheet is named Sheet1 & everything.
    – 4tWinks
    Mar 29 '19 at 16:29
  • can you share a copy of your sheet?
    – user0
    Mar 29 '19 at 16:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.