Hello
I have a cocktail list with a sheet "Cocktails with ingredients" that shows the various ingredients for each cocktail.
I'm wanting to have a sheet "Select" where if I select a particular cocktail, it pulls up the ingredients from the sheet "Cocktails with ingredients". Struggling to find a way to bring the ingredients and the relevant quantity to the "Select" sheet.
I've attached my file "Cocktail list" here as well.
Any help would be massively appreciated.
with Power Query
The CURSE of the pretty looking blank cell in a data column that make formulae very messy:
=IFERROR(INDEX('Cocktails with ingredients'!B:B,AGGREGATE(15,6,ROW('Cocktails with ingredients'!$B$3:$B$29)/((LOOKUP(ROW('Cocktails with ingredients'!$A$3:$A$29),ROW('Cocktails with ingredients'!$A$3:$A$29)/('Cocktails with ingredients'!$A$3:$A$29>0),'Cocktails with ingredients'!$A$3:$A$29)=Select!$C$4)*('Cocktails with ingredients'!$B$3:$B$29<>"")),ROWS(D$4:D4))),"")
in D4, copied across and down.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Thanks very much sandy666. I unfortunately don't have too much experience with Power Query, so will need to learn how to use it.
Thanks very much Glenn! This is so useful. I had never used these formulae before!
sureOriginally Posted by amar87
https://learn.microsoft.com/en-us/power-query/
Hello Glenn - hope all is well.
I am wondering if there is a way to hide blank rows in the "Select" tab in respect of the cocktails/ingredients that pull through from the "Cocktails with ingredients" tab? e.g. Espresso Martini only has 4 ingredients, so is it possible to hide rows 8:11?
Are you still using Excel 2021?
What do you mean by 'hide'?
AliGW on MS365 Beta Channel (Windows 11) 64 bit
Vodka Kahlua Coffee Sugar Syrup
Sheet: Select
If you mean that the empty rows are not to show, then you'll need VBA and should start a NEW thread in the VBA section with a suitable title. In fact, you should open a new thread regardless, as the original issue here is solved, so please do this.
Thread closed. One issue only per thread here, please.
Last edited by AliGW; 03-03-2024 at 03:21 AM.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks