Rows to reference Columns
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!