+ Reply to Thread
Results 1 to 2 of 2

Need help pulling information into new sheet!

  1. #1
    Registered User
    Join Date
    12-14-2011
    Location
    Robert Griffith
    MS-Off Ver
    Excel 2008
    Posts
    1

    Need help pulling information into new sheet!

    I have a table on sheet1

    It consists of columns Description, Quantity, Unit of Measurement, and Total Price

    What I am wanting to do is on sheet2 is if a cell under Quantity is >0 then I want the first Description that meets this criteria to be move to a A2 and Price to B2 then the second Description that meets this description needs to be moved to A3 and Price to B3 and so on to create a proposal.

    I can do the first pull, but where it gets tricky for me is the getting the next value that is >0.
    I am using IF statement but I know it is more than just an IF statement.
    Here is what I am using and as long as every Quantity has a value >0 it works fine even though it is too much work to put in for ever Quantity, but once one of the Quantity is 0 it bugs out:

    A1
    =IF(Tile!B3>0,CONCATENATE(Tile!A3),IF(Tile!B4>0,CONCATENATE(Tile!A4),IF(Tile!B5>0,CONCATENATE(Tile!A5))))
    A2
    =IF(Tile!B4>0,CONCATENATE(Tile!A4),IF(Tile!B5>0,CONCATENATE(Tile!A5),IF(Tile!B6>0,CONCATENATE(Tile!A6))))
    A3
    =IF(Tile!B5>0,CONCATENATE(Tile!A5),IF(Tile!B6>0,CONCATENATE(Tile!A6),IF(Tile!B7>0,CONCATENATE(Tile!A7))))

    So if the first Quantity is 0 it goes to the next and lets say there is a 5 in the second Quantity then it will pull the second Description over to sheet2. But the next formula because "Tile!B4" is the second Quantity and is >0 also gets pulled over to A2.

    So if you look at just the first two rows on sheet2 based on above formulas and Quantity1 being 0 and Quantity2 being 5 you get the following:
    Description2
    Description2

    This is where I am and I need help with a more in-depth formula with maybe If-Then-Else or an icounter or VB or something. I am no expert.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,168

    Re: Need help pulling information into new sheet!

    Hi Robert and welcome to the forum,

    I'm thinking you need to learn Advanced Filters.
    http://www.contextures.com/xladvfilter01.html or http://office.microsoft.com/en-us/ex...005200178.aspx

    No VBA needed, just another tool Excel has built in.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

+ 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