1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy data from one sheet to another based on criteria

Discussion in 'Ask an Excel Question' started by Neil Postlethwaite, Jan 12, 2018.

  1. Neil Postlethwaite

    Neil Postlethwaite New Member

    Messages:
    2
    Hello everyone, apologies if this has been asked before. I have searched and found similar, but to be fair, as soon as VBA is mentioned my brain fogs over and i lose the plot completely! Hopefully what i need to do can be done with formula... if not, please treat me like an idiot! (possibly best to do that anyway!!)

    I am creating a spreadsheet for a PA to be able to take down details of a job (we run Disco's and Photobooths for Weddings, Parties, Corporate events etc) and provide a quote.

    Where i have hit a stumbling block is that I would like the Quote sheet to automatically populate with the services that they have chosen with the relevant confirming rates etc etc.

    How do i do this without having lots of blanks spaces in Sheet 2 where the services not selected haven't been copied over.

    I've attached the sheet I'm creating with the data i want to pull across in red.

    Thanks in advance
    Neil

    Attached Files:

  2. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,948
    Try,

    B23, copied down :

    =IFERROR(INDEX('Main info Sheet'!B$35:B$51,AGGREGATE(15,6,ROW('Main info Sheet'!Z$35:Z$51)-ROW('Main info Sheet'!Z$34)/('Main info Sheet'!Z$35:Z$51>0),ROWS($1:1))),"")

    Q23, copied down :

    =IF(B23="","",IFERROR(1/(1/VLOOKUP(B23,'Main info Sheet'!B$35:Z$51,12,0)),VLOOKUP(B23,'Main info Sheet'!B$35:Z$51,21,0)))

    T23, copied down :

    =IF(B23="","",IFERROR(1/(1/VLOOKUP(B23,'Main info Sheet'!B$35:Z$51,8,0)),VLOOKUP(B23,'Main info Sheet'!B$35:Z$51,17,0)))

    AE23, copied down :

    =IF(B23="","",Q23*T23)

    Regards
    Bosco

    Attached Files:

    deciog and S. Das like this.
  3. Neil Postlethwaite

    Neil Postlethwaite New Member

    Messages:
    2
    @bosco_yip THANK YOU! I don't pretend to even understand how or what that means, but it works. Thank you so much.

Share This Page