Reference a cell on another sheet based of a value in the current sheet
Hi Smartsheet hive mind. Hopefully someone may be able to answer this question. I have a sheet (Sheet A) that will be tracking tickets and will have the dates the ticket was created on it. I have another sheet (Sheet B) that has every date of the year on it but another column that tells what the fiscal month is (in this case the 29th -28th). Is it at all possible to have a column in sheet A (Fiscal Month) reference the fiscal month on sheet B when a new date is added to the Ticket Creation Date column in sheet A?
Answers
-
Yes. You would use an INDEX/MATCH combo similar to:
=INDEX({Sheet B Fiscal Month Column}, MATCH(DATEONLY([Created Date]@row), {Sheet B Date Column}, 0))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
When I try that I get #NO MATCH
Here is my formula
=INDEX({Fiscal Month Calendar Range 1}, MATCH([Notice Date]@row, {Fiscal Month Calendar Range 2}))
-
Try adding in the ", 0" at the end of the MATCH function like I have in my above example. If that doesn't work, double check to make sure your [Notice Date] and the date column on Sheet B are both Date types. If that still isn't it, can you provide some screenshots?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Still receiving Unparseable.
Here are my screen shots
Sheet A is called Warning, Suspension, Termination Tracker 2020
Sheet B is called Fiscal Month Calendar
-
You need to use the correct column names. If you are using the [Notice Date] column then that is the column you would need to reference.
=INDEX({Fiscal Month Calendar Range 1}, MATCH([Notice Date]@row, {Fiscal Month Calendar Range 2}, 0))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 438 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 451 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 282 Events
- 32 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!