# Between Dates Formula Referencing Another Sheet

I have two sheets I am trying to tie together. Here is the scenario:

Sheet 1 (Below): Has the city reference. Current Rent is where I am trying to type the formula. Idea is to return the current rent based on today's date and the city.

Sheet 2 (Below): Has the rent schedule range and the city to reference. There will be multiple cities eventually as shown in the above pic. I will need the formula to determine the city first and then the date range based on today's date.

HELP!!

Tags:

• ✭✭✭✭✭✭

In your 2nd sheet create a text/number helper column [Current] with the formula: =IF(AND(Today()<=[rent end date]@row, today()>=[rent start date]@row), city@row, ""). Place the column to the left of the rent amount you want returned.

On sheet 1 use a VLOOKUP to find the city in the [current] colum and return the rent. Replace my range placeholder with your range to sheet 2: =IFERROR(VLOOKUP(city@row, {sheet 2 current}, 2, false),"-")

Work?

Mark

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• ✭✭✭✭✭✭

In your 2nd sheet create a text/number helper column [Current] with the formula: =IF(AND(Today()<=[rent end date]@row, today()>=[rent start date]@row), city@row, ""). Place the column to the left of the rent amount you want returned.

On sheet 1 use a VLOOKUP to find the city in the [current] colum and return the rent. Replace my range placeholder with your range to sheet 2: =IFERROR(VLOOKUP(city@row, {sheet 2 current}, 2, false),"-")

Work?

Mark

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• Thank you Mark, on the first formula on the 2nd sheet it is coming up #INVALID OPERATION...any thoughts?

• Mark, ignore my last comment, I realized my date columns were not set to "date". This worked great, thank you!!

• ✭✭✭✭✭✭

Excellent. Glad you found a solution. Please accept an answer to close the discussion. Thank you for contributing to the Community.

Mark

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!