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

Find more posts tagged with

Sheets

Formulas

functionality

Accepted answers

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

All comments

Mark Cronk

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.