+ Reply to Thread
Results 1 to 4 of 4

Formula - able to update to point to a tab based on a cell?

  1. #1
    Forum Contributor
    Join Date
    05-02-2017
    Location
    NoVa, United States
    MS-Off Ver
    Microsoft 365
    Posts
    154

    Formula - able to update to point to a tab based on a cell?

    Please see attached.

    Okay, so I'm tired of updating my formulas whenever a new month roles in. I was wondering if there was an easier way. On the attached spreadsheet there are three tabs:
    Data - this calculates the tenure (and what I want to most update).
    5-31-19 - this is the material that the 'Data' tab is currently calculating.
    6-30-19 - this is the data that I want the formula to calculate BASED on what is in 'Data'!K3

    So one of the formulas is this:
    =COUNTIFS('5-31-19'!$E:$E,Data!B$1,'5-31-19'!$D:$D,"<1")

    Instead of having to alter the formula to capture the new tab (ex. =COUNTIFS('6-30-19'!$E:$E,Data!B$1,'6-30-19'!$D:$D,"<1") )
    I would like to be able to do this:
    =COUNTIFS('Data'!K3$E:$E,Data!B$1,'Data'!K3$D:$D,"<1")

    Or something similar?

    Is that possible?
    Attached Files Attached Files

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Formula - able to update to point to a tab based on a cell?

    Sure...

    Try in B2 copied across =COUNTIFS(INDIRECT("'" & $K$3 & "'!$E:$E"),Data!B$1,INDIRECT("'" & $K$3 & "'!$D:$D"),"<1")

    Now for the rest of the formulas you will need to add the Indirect part in "Red"
    HTH
    Regards, Jeff

  3. #3
    Forum Contributor
    Join Date
    05-02-2017
    Location
    NoVa, United States
    MS-Off Ver
    Microsoft 365
    Posts
    154

    Re: Formula - able to update to point to a tab based on a cell?

    I gave you a reputation point and indicated that what you wrote didn't work - but it does. My mistake.

    I had to change K3 to TEXT and then input 6-30-19

    Works like a charm!

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Formula - able to update to point to a tab based on a cell?

    Yes, very important to make sure K3 is text. If you enter 6-30-19 it will immediately turn into a number.

    Thanks for the rep and the feedback. You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Formula to update report based on cell
    By jimstrongy in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-09-2015, 01:05 AM
  2. [SOLVED] Formula needed to update list based on a cell ref
    By WimpieOosthuizen in forum Excel General
    Replies: 3
    Last Post: 02-06-2014, 01:36 PM
  3. Formula needed to update list based on a cell ref
    By WimpieOosthuizen in forum Excel General
    Replies: 2
    Last Post: 02-06-2014, 10:24 AM
  4. [SOLVED] I need a formula to update based on a cell
    By brharrii in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-21-2012, 03:13 PM
  5. Update datestamp based on formula in adjacent cell
    By nuttyengineer in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 10-07-2010, 04:35 PM
  6. Update formula based on cell selection
    By Maver1ck666 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-01-2010, 09:49 AM
  7. Replies: 1
    Last Post: 04-21-2006, 02:15 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1