Copy data from one sheet to another based on criteria

[Neil Postlethwaite]

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

 

[bosco_yip]

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

 

[Neil Postlethwaite]

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