Reference a cell on another sheet based of a value in the current sheet

12/31/19
Answered - Pending Review

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

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    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))

  • Christopher PrettyChristopher Pretty ✭✭✭✭✭

    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}))

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    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?

  • Christopher PrettyChristopher Pretty ✭✭✭✭✭

    Still receiving Unparseable.

    Here are my screen shots

    Sheet A is called Warning, Suspension, Termination Tracker 2020

    Sheet B is called Fiscal Month Calendar


  • Paul NewcomePaul Newcome ✭✭✭✭✭

    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))

Sign In or Register to comment.