+ Reply to Thread
Results 1 to 2 of 2

Need to add iftext in relative cell refrence to a offset(match+match) formula

  1. #1
    Forum Contributor
    Join Date
    04-02-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    452

    Need to add iftext in relative cell refrence to a offset(match+match) formula

    Training Stats, upload.xlsm

    In this sheet I have a drop down menu for the week and another one for the day. Based off of that, I have these two formulas,
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The second formula is abbreviated since it is so long. These formulas have the istext qualifier so that if the cell above it is blank it too will be blank.

    With Week 1 and Day 1 selected in the dropdown menus of the Calculator sheet, you can see the data in O28:R33. Then looking at the Training Plan sheet that data is Week 1, day 1 and day 2. What I would like is a formula that before it pulls the data out of a row, it checks the A column of the same row for text, if there is no text, pull the data out of the row. If there is text, leave the cell blank.

    The point of this is because right now each day contains 3 rows. However in the future I may want to vary that. Once the formula returns a blank, the istext qualifier already in the formulas would make all the below formulas return a blank value. The formulas in O28 and R28 do not need these qualifiers because there would always be at least one row of data to return.

    Edit:My title says it has to be an iftext, if ifblank works better then that would be fine.
    Last edited by Butcher1; 10-06-2014 at 12:06 PM.

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,910

    Re: Need to add iftext in relative cell refrence to a offset(match+match) formula

    I would change the training plan sheet to make it easier to extract the data of interest:


    Training Stats, changed for upload.xlsm
    Bernie Deitrick
    Excel MVP 2000-2010

+ 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] Dynamic Name Range using Offset&Match with Match based off a different column
    By mdlpjr in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-16-2013, 06:33 PM
  2. [SOLVED] index match with row information offset from the match cell
    By smls in forum Excel General
    Replies: 7
    Last Post: 08-30-2012, 09:48 AM
  3. Replies: 2
    Last Post: 03-16-2012, 12:03 PM
  4. Replies: 5
    Last Post: 02-29-2012, 08:51 PM
  5. Excel 2007 : OFFSET Match formula need help
    By feno1 in forum Excel General
    Replies: 11
    Last Post: 12-24-2010, 01:20 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