+ Reply to Thread
Results 1 to 3 of 3

Extract certain data from a Column dependent on another Column , to another sheet.

  1. 10-29-2013, 11:50 PM #1
    jason79
    jason79 is offline
    Registered User
    Join Date
    10-29-2013
    Location
    Perth
    MS-Off Ver
    Excel 2011
    Posts
    2

    Lightbulb Extract certain data from a Column dependent on another Column , to another sheet.

    Hello guys,

    I'm a basic excel user trying to figure out what formula/functions to use.

    Master sheet (Sheet1) contains Columns of data. Namely Column A has job numbers. Column S tells me whether these jobs are 'Un-allocated with Titles'.

    I want to create a list on another sheet 'Sketchs With Titles' of the Jobs (MasterSheet!ColumnA). I only want this list to contain the job numbers that have titles (Column S with 1 in the cell). If you look at the example file. There are currently 25 jobs with titles. So the list on the next sheet should output 25 job numbers etc.

    So I need a function that will go through each row, determine if it is in Columns S. If it is then output the Job (data in Column A) in a cell in the 'Sketchs With Titles' sheet. If it isnt, then do nothing.

    Any help is greatly appreciated.

    DRAFTING REPORT BETA.xlsx
    Reply With QuoteRegister To Reply
  2. 10-30-2013, 01:57 AM #2
    benishiryo
    benishiryo is offline
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Extract certain data from a Column dependent on another Column , to another sheet.

    welcome to the forum, jason. try this array formula in Sketches with Titles sheet cell B11:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    it looks like this with cell references:
    =IFERROR(INDEX(MASTER!$A$2:$A$59,SMALL(IF(MASTER!$S$2:$S$59=1,ROW(MASTER!$S$2:$S$59)),ROWS(B$11:B11))-ROW(MASTER!$S$1)),"")

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    Attached Files Attached Files
    Last edited by benishiryo; 10-31-2013 at 08:27 PM. Reason: attached file

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."

    Tips & Tutorials I Compiled | How to Get Quick & Good Answers
    Reply With QuoteRegister To Reply
  3. 10-31-2013, 01:20 AM #3
    jason79
    jason79 is offline
    Registered User
    Join Date
    10-29-2013
    Location
    Perth
    MS-Off Ver
    Excel 2011
    Posts
    2

    Re: Extract certain data from a Column dependent on another Column , to another sheet.

    Thank you Benishiryo. Unfortunately I have not been able to get it to work as yet. I am using a MAC (OS X) and the CTRL+SHIFT+ENTER command doesn't seem to be working. I cannot see the curly braces {} that you state. I have tried Command+Shift+Enter and Command+Return but nothing . The formula is in the cell but it is blank.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Copy Data in column B to other columns dependent on Contents of column A
    By timbo1957 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-07-2013, 05:54 AM
  2. Input data to a sheet into a specific column dependent on other combo box answers
    By bugs78 in forum Excel General
    Replies: 0
    Last Post: 08-23-2012, 04:59 AM
  3. How to search column of data for specific string and extract to new column
    By st3ffl3sx13 in forum Excel General
    Replies: 1
    Last Post: 04-23-2012, 10:27 AM
  4. Concatenate data from a Column dependent on another column
    By Skull_Eagle in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-25-2011, 08:56 PM
  5. Extract data from column B based on data in column A and 'paste' into column D
    By murphy in forum Excel General
    Replies: 3
    Last Post: 02-08-2010, 06:18 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1