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

Christopher Pretty

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?

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