+ Reply to Thread
Results 1 to 9 of 9

autofill formula down based on columns from different worksheet, but skip cell if "FALSE"

  1. #1
    Registered User
    Join Date
    01-16-2013
    Location
    kansas city
    MS-Off Ver
    Excel 2007
    Posts
    37

    autofill formula down based on columns from different worksheet, but skip cell if "FALSE"

    In my data in 'Sheet2'I2 I want to drag the data down from 'Sheet1'A2:E2 depending on if the cell is a number. I want it to skip over the cells labeled "FALSE". For example:

    'Sheet2'I2 copies cell 'Sheet1'B2
    'Sheet2'I3 copies cell 'Sheet1'C3

    and so on.

    Thank you for any help in advance
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    04-03-2012
    Location
    East Sussex, UK
    MS-Off Ver
    Excel 2003:2010
    Posts
    893

    Re: autofill formula down based on columns from different worksheet, but skip cell if "FAL

    In I2 on Sheet2, enter =IF(Sheet1!B2=FALSE,"",Sheet1!B2) and copy down.

    Is that what you mean?
    If I've been of help, please hit the star

  3. #3
    Registered User
    Join Date
    01-16-2013
    Location
    kansas city
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: autofill formula down based on columns from different worksheet, but skip cell if "FAL

    It works for the first cell "I2" but when I drag down "I3" is blank and I want it to pick up cell 'Sheet1'C3

  4. #4
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: autofill formula down based on columns from different worksheet, but skip cell if "FAL

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


    will give the values you have in the sample, but just curious as to why you want to look up diagonally across the sheet ?
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  5. #5
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: autofill formula down based on columns from different worksheet, but skip cell if "FAL

    I think this may be more what you are looking for :
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Drag down

    This will find which Friday close has a number in it, and return that, if no number, then blank (**IMPORTANT***--only 1 cell in the row should have a number !!)

    Hope this helps

  6. #6
    Registered User
    Join Date
    01-16-2013
    Location
    kansas city
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: autofill formula down based on columns from different worksheet, but skip cell if "FAL

    Thank you!! That works. and my data is from a much larger data set where I am analyzing "at the money" stock options, but "at the money" changes as the stock price changes so I have different option strike prices based on what day I am looking at. The way I set up my workbook was one security i.e Wells Fargo in row 1 and then the different strike prices across the columns. i.e. Wells Fargo strike price 35 = (1), Wells Fargo strike price 36 = (2) and so forth, but on my second sheet I only care about the "At the money" strike, hence the one row compared to multiple columns in the previous worksheet. Does that make sense?

  7. #7
    Registered User
    Join Date
    01-16-2013
    Location
    kansas city
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: autofill formula down based on columns from different worksheet, but skip cell if "FAL

    Yes, that one is even better because I created a new worksheet in your previous formula in order for it to work. This way is a bit more streamlined. Thank you very much!!

  8. #8
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: autofill formula down based on columns from different worksheet, but skip cell if "FAL

    Well, I don't deal in stock market, so not really
    But if the solution works, Great!

    btw, I'm assuming its the sumproduct solution that did the trick?

    Have a good day

  9. #9
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: autofill formula down based on columns from different worksheet, but skip cell if "FAL

    You are welcome

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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