+ Reply to Thread
Results 1 to 4 of 4

Using text from one cell in a formula in another cell

  1. #1
    Registered User
    Join Date
    07-16-2013
    Location
    Columbia, Maryland, United States
    MS-Off Ver
    Excel 2010
    Posts
    9

    Using text from one cell in a formula in another cell

    I have data in one sheet in my workbook with that sheet name being "20130509".

    in another sheet I have formulas:

    In Cell A2 is the text "20130509" without the quotes.
    In Cell B1 is the text "NCAA" without the quotes
    In cell C1 is the text "NFL" without the quotes
    In Cell B2 is the formula =countifs('20130509'!$H$3:$H$10000,$B1)
    In Cell C2 is the formula =countifs('20130509'!$H$3:$H$10000,$C1)

    Is there a way to change the formula in cells B2 and C2 so that it references the text in A2 such that if I change the text in A2 to a different sheet, say "20130517" the formulas all work for the new sheet?

    Thanks,

    Tim

  2. #2
    Valued Forum Contributor Harribone's Avatar
    Join Date
    02-24-2013
    Location
    Midlands, UK
    MS-Off Ver
    Excel 2019/365
    Posts
    570

    Re: Using text from one cell in a formula in another cell

    This can be done using the INDIRECT function:

    Example

    INDIRECT(A1&"!A1")

    the first A1 in the above is where the sheet name is. The second A1 refers to cell A1 of that sheet. This just needs changing to work inside you formula.

    =countifs(INDIRECT(A2&"!$H$3:$H$10000"),$B1)
    Say thanks, click *

  3. #3
    Registered User
    Join Date
    07-16-2013
    Location
    Columbia, Maryland, United States
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Using text from one cell in a formula in another cell

    That worked. Thanks:-)

  4. #4
    Valued Forum Contributor Harribone's Avatar
    Join Date
    02-24-2013
    Location
    Midlands, UK
    MS-Off Ver
    Excel 2019/365
    Posts
    570

    Re: Using text from one cell in a formula in another cell

    Not a problem, that's what we are here for
    Last edited by Harribone; 07-16-2013 at 03:49 PM.

+ 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] VBA macro to check if one cell has text and paste a formula in a different cell
    By vitt4300 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-10-2013, 01:38 PM
  2. [SOLVED] text formula to parse a cell, clean it, and dump into a fixed CELL RANGE - NO MACROS
    By James C in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 03-04-2013, 08:42 PM
  3. [SOLVED] Formula Needed to Copy Cell with Date and Text of another Cell
    By thejayjetson in forum Excel General
    Replies: 5
    Last Post: 07-02-2012, 11:21 AM
  4. Replies: 1
    Last Post: 01-24-2012, 11:15 AM
  5. Replies: 4
    Last Post: 04-11-2011, 08:41 PM

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