Rows to reference Columns

Heval A.
Heval A. ✭✭
edited 09/14/21 in Formulas and Functions

Hi,

I have a Form that adds rows to the Consignment Stock Take sheet (seen below). Inventory items in this sheet are arranged by columns. Furthermore, I have an Inventory Management sheet (seen below) which has Inventory Items arranged in rows.

Problem/Desired Outcome

I want the [In Stock] Inventory Management Column to return the cell value of it's respective Inventory Item (from Consignment Stock Sheet). I also want the Stock Check Date of this returned cell value to equal to the Stock Take Date.

I was able to solve the problem by using the below function (referencing individual columns). However, I reached the cross-sheet referencing limit:

=INDEX({Consignment #2 Column}, MATCH([Stock Check Date]@row, {Consignment Stock Take Date}))

I even tried adding-in a helper row with column names, but I still couldn't crack the formula

Any help would be highly appreciated.

Thanks!


Answers

  • I managed to solve the problem using the helper row.

    However, if there are two Stock Take Dates which are the same, the formula references the top most date. From what I understand, the MATCH function searches from top to bottom.

    Is there a way the MATCH function can search from bottom to top?

    Here is the formula:

    =INDEX({All Columns - Consignment}, MATCH([Stock Check Date]@row, {Consignment STD}), MATCH([Inventory Item]@row, {Helper Row - Consignment}, 0))

    Thanks 😀

  • Hey @Heval A.,

    Assuming the Date Column is in chronological order and you will always want to return a value with the corresponding latest date, we can utilize the INDEX, COLLECT, and COUNTIF Functions to perform this action. I've created the example below for your reference:


    Formula (highlighted in yellow): =INDEX(COLLECT(Number1:Number2, Letter1:Letter2, "B"), COUNTIF(Letter1:Letter2, "B"))

    • Using the COLLECT Function, we can create a Range that only includes "B"
    • Using the COUNTIF Function, we can count how many Rows that include "B"
      • In this case it is 2, which is the exact position we would like to return within our Range (it being the latest entry of "B")

    I hope this helps!

    Jaykel

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!