Between Dates Formula Referencing Another Sheet

Stephanie Bujwit

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

Mark Cronk

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

Mark Cronk

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

Stephanie Bujwit

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

Stephanie Bujwit

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

Mark Cronk

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

Mark