Hello Kenji. This is a very helpful video. I am wondering if can we use spin button from the developer tab instead of drop down list. For example: the first spin button will spin Region and the second spin button will spin only those Countries of a Region selected by first spin. Thanks
Can we make our lists in Tables and then apply all the formulas and tricks you used? Tables Lists because if it grows, Table account the changes itself.
Word of caution. I updated a file with validations for drop downs and used a transpose formula. The file is used by a team in 3 geographically different places, soon to be 4 and must be shared. The transpose formula “breaks” the validation in one column because it is a shared file. Will update it with one of your suggestions to select horizontal selection for the validation. Thanks!
That error check will only work for one particular cell, is that correct? How can I have conditional formatting based on a formula that checks each row, based on the XLOOKUP? In other words, how can I make that "XLOOKUP(D2, ............, E2)" in the conditional formatting formula apply to each of many rows? In your example, it only works on that one cell.
Doesn't work on 365. As soon as I get to the second Data Validation list it errors out "This entry leads to an error. Try entering different values to continue."
Hi iam using excel 2016 and always having trouble when making dropdowns instead of words in dropdown go down one by one but instead they are all in one place
You’ll probably have to use index match. In older versions, that’s what was generally used when vlookup wasn’t good enough, so it was essentially the precursor to xlookup. Bit more complicated, but not too bad once you get used to it. Just google index match and you’ll find guides etc easily as it was common practice for a long time.
i am trying to do .this thing in excel online it not happening there like adding new formula to existing one trasnpose is not working help @kenji explains
I have a question Kenji. Is there any such formula which can automatically separate the amounts of multiple items in one order id in the total amount column? Order ID Product Qty Product Price Payment Mode Cash Amount Card Amount Total Amount 20240520213102 1 995 Card 0 8500 8500 20240520213102 1 799 Card 0 8500 8500 20240520213102 1 6795 Card 0 8500 8500