How to | Create a Dropdown Price List | Google Sheets / Excel

[@bloomfieldanalysis]

Thank you for watching! Missed the first video? Create an automated invoice here: kzbin.info/www/bejne/jpWco2WCr7GVgw Want to learn more ? FREE templates from our website when you sign up to our newsletter: www.bloomfieldanalysis.com/stingMore videos: kzbin.info/door/yqHAMnFt2yTyYgYsxQMCjQ If you want to add a simple price list (one item per invoice) : kzbin.info/www/bejne/gp7ahJJ-orOZq7s If you want to add multiple items and a double drop down price list: kzbin.info/www/bejne/hXKndmZqd5ppnq8 If you want to print multiple invoices to pdf: kzbin.info/www/bejne/pX_dkpygbMtor68 Or do something different and create a leaderboard: kzbin.info/www/bejne/hqPco6msiMaBoac If you would like us to build you your own bespoke invoicing solution go to: bloomfieldanalysis.com/booking No time to learn? Buy ready made premium templates: etsy.com/shop/bloomfieldanalysis

[@odiepayne6068]

Thank you! this was extremly helpfull!

[@bloomfieldanalysis]

Glad it was helpful!

[@Thissisliss]

Great Video! I am a bit of a novice when it comes to google sheets. I keep getting lost when I get to the vlookup portion. It's not following me over to the Pricelist sheet. Do you have any thoughts on why this is happening? Thanks!

[@bloomfieldanalysis]

Glad you like! By "following you over" - are you getting an error or the wrong value? The main watch out is probably to make sure your price list is unique, otherwise you'll get the first value. Another watch out is make sure you have the ",false" - so that it looks for an exact match rather than first closest. You could also try an xlookup, which is newer than the vlookup, and simpler. =XLOOKUP(lookup_value, lookup_array, return_array) - you dont need the "false" or 0, because exact match is the default. Lookup_value is what your searching for, lookup_array is where you are searching for it, so the whole of column B for instance. Return_array will be what you want instead, e.g. price, so column D for instance. You dont have to count columns or anything. And finally, if you are still stuck, go to bloomfieldanalysis.com/sting download template, I'd recommend the invoice with items and price list one as it gives some more flexibility. It goes with this next video: kzbin.info/www/bejne/hXKndmZqd5ppnq8 Hope something among these ramblings helps :)

[@facebythewind]

Thank you for this tutorial it really helped me understand

[@bloomfieldanalysis]

Great to hear - thank you!

[@ibrahimsholeh5140]

thanks, by the way, how to create dropdown changing currency list.. for example a dropdown that change a cell from value $400 to 400% and otherwise ??

[@bloomfieldanalysis]

Hmmm... thats difficult because its more a format change than the underlying value you want to change. Well, in your example above, both the format and the value would need to be divided by 100. It is possible through apps script but I feel that would be overcomplicating. Whats the reason for it? A discount can be a value or a %? If so, I would have both options available, then hide whichever is unnecessary. Hiding can be by formatting (so white text on white backgound) when not relevant. I think you can achieve the result you want but just not how you wanted to originally.

[@evergarcia8900]

How about If I have a data validation dropdown on colum B and want to insert values to individual catergories without a price sheet as the prices vary (im making a budget/expense tracker and want to be able to change multipe catergories without copying and pasting rows of the same day but different catergories) sorry if that seems confusing

[@bloomfieldanalysis]

I would potentially have a "suggested price" in column B, and then a "override price" in column C, that will give you complete control over your price. So you have it equal to column B, but just enter a different amount if you wish. Or if its based on some kind of logic, which it sounds like it is but I'm not totally getting it, just think a little more about what you are changing and why and try to put that into a formula.

[@evergarcia8900]

Oh thats a great idea, that makes a lot of sense when you think about it thank you so much illl try that out! Cheers! @@bloomfieldanalysis

[@mustasim1]

Is it possible to automatically change the invoice number?

[@bloomfieldanalysis]

Yes, it is! I like to create a new number based on the date (e.g. year, month, date), and also the row number. Row number will ensure its unique. Something like this, if your invoice date is in column B: ="INV-"&text(month(B36),"00")&text(day(B36),"00")&row(B6) The text([ ],"00") ensures number is always 2 digits so its neater, i.e. dec is 12 and January is 01 rather than 1. Row number itself would also be fine, but dates are easier for me to interpret.

[@mustasim1]

@@bloomfieldanalysis Bro!! Please make a video for us highly requesting. How to change the invoice automatically . Thank you ❤️

[@bloomfieldanalysis]

Yes, I can do that - you and hundreds of others better watch though - haha! I've added it to the list. I have a couple of shorts coming up, which I'm hoping will help up the subscribers a little more, as I'm getting very close too 1000 subs! But definitely more long form after that.

[@mustasim1]

@@bloomfieldanalysis Love From Bangladesh

[@bloomfieldanalysis]

@mustasimarif7708 I Hope this helps ! kzbin.info/www/bejne/mGbEhmhqmbFkntE

[@autoloza]

Is there any way to clear it without deleting formulas?

[@bloomfieldanalysis]

What part did you want to clear? If its something thats zero, a couple of options could be if the cell is zero then make it blank, otherwise the value of the cell: if(cell=0,"",cell). Or you can do conditional formatting, so that if the call is zero, then make the colour of the text white (if the background id white)

[@jpf578]

Great tutorial.

[@bloomfieldanalysis]

Glad you liked it!

[@tallyerptutorials]

How to transfer to data sheet from this form ?

[@bloomfieldanalysis]

Ctrl + C, Ctrl + V :) If you want to print multiple invoices to pdf and save them this video could be useful : kzbin.info/www/bejne/pX_dkpygbMtor68

[@camilabelenlagos7985]

Am I the only idiot that is following this very simple tutorial step by step and still gets an error message?

[@bloomfieldanalysis]

If you are still having issues, try downloading the template (bloomfieldanalysis.com/sting), and then compare that to what you have done, or start from that, and adapt it, it could be something really small that’s hard to see

[@camilabelenlagos7985]

@@bloomfieldanalysis Yes! Thank you! That's what I did. I edited the downloaded sheet. Thank you very much!

[@enriquedb666]

GOLD!!!!!!

[@westarbeit1039]

Thank You very much!

[@nicolapipe78]

Thank you!!

[@bloomfieldanalysis]

It was a pleasure !

[@noelvisramirezaccounting]

Thank you very much .

[@bloomfieldanalysis]

You are very welcome!