Video

Great to hear that 👍 John

Replace your zeros with errors as they don't show on graphs. Google for more info 👍 John

Glad you like it. Hope it helps you save time 👍 John

You have to add the sheet name to the reference but you might need to Google the correct format. John

Thanks for your comments John 👍

Glad you like it 👍 John

That sounds like a job for power query to me so maybe look into that. It automatically recognises dates in far more formats than the Excel grid too. If that doesn't work I'd imagine a few if functions might get the job done depending on your circumstances. Buy power query is your best bet I'd say 👍 Good luck 👍 John

@@Up4Excel thank you for the suggestion, John! I definitely need to do some exploring and see if I can come up with a solution

Glad you're learning a lot and happy you find me. There isn't a solution regarding conditional formatting that I know of and I suspect that if there is one it'll be something completely different to this technique. Conditional formatting sits over the top of other formats applied to cells and is temporary. So even if, for example, you format a cell yellow, conditional formatting can change it to red, and the format of the cell will still show up as yellow in almost any kind of integration I know of, including the one in this video. So sorry, no solution I know of. John

Thanks John, hope you find a use for it 👍John

Formatting could certainly be a lot more straightforward that's for sure. I'm not a Crystal user so I'll just take your word they should live long 😊 John

Power Query is your tool for that...it's an option on every date field 👍

You're welcome Sampath 👍 John

Glad you like it 👍

Sir, this date problem please solve in google sheet

It certainly is, this is the tip of the iceberg of course...so more to come 👍John

Very informative video sir thanks

Great to hear, thanks for commenting 👍 John

Dates are very sensitive to formatting and other stuff in excel so we often get issues. I did this video that might give you ideas for solutions. Hope it helps czcams.com/video/cM2PIqcoCkk/video.html

Thank you very much, glad you found the answers you need 👍 John

Thanks for your feedback on the music. I've noticed it can be very different with or without headphones. I'd be interested in what sort of device you watched on and whether you had headphones so I can gauge volume in the future. Glad you found the technique useful btw 👍 John

@@Up4Excel I had headphones on, and was watching it on my MacBook, but the music should definitely softer, maybe a piano playing laying a slow tempo music instrumental. Love your content. That's a 10 on 10. Am following and waiting to watch more videos. Have a great day.

@@paramveerssachdeva ​ Listening back to this you're right about the volume, this one is definitely much louder than I put it these days....and with headphones on sounds even louder. I'll test all new releases with headphones in future, thanks for the feedback 👍John

Thanks and glad you found some useful tips. Not sure about an easier way as such but in this other video I go through some other techniques to create ID values for rows which might give you some ideas czcams.com/video/RhARYxec5vA/video.html Good luck 👍 John

@@Up4Excel Thanks for the answer! I will definitely go through the video!

@@erminsehovic2000 Hope you get some good tips from it

Glad you think so Arbaz 👍 John

Glad you like it. But a shame you're discouraged as there are some almost instant visuals you can apply that improve reports immediately....like the standard databars take seconds to add ( not as good as these though 😉) John 👍

So glad you think so, I shall try to keep it up 👍 John

I'm sure I'm not but I'll take the compliment anyway 😉 Thanks for watching and commenting 👍 John

Thanks for that, hope you put it to use and impress someone yourself 👍 John

Glad it helped you 👍 Your workbook should not protect itself without you or someone else doing something. So I don't know what it is doing, very strange. John

Wow, that's a lot of praise so thank you. Removing frustration and saving time is what I aim for 👍 John

Glad to help your colleague out 👍 John

Glad to help 👍 John

Great news Alex, glad to hear that and hope it continues 👍 John

Thanks John 👍

It's out on 19th June 2024. Keep an eye out 👍John

You could do that but you wouldn't be able to use the same formula across all columns like I did as the centre across selection value only sits in one cell.

No problem! 👍John

Yes, very true thanks, can also put a sort function around it too like =SORT(UNIQUE(FILTER(tblOrders[Product Name],tblOrders[Product Sub-Category]=I6)))...where I6 is the result of the first drop down. I recorded this on Excel 2019 and re-edited it recently to include in my drop down video series. With hindsight, I wouldn't do it like this now, but there are a few useful general tips and obviously good for legacy Excel. Thanks for the comment as anyone with Excel 365 can shortcut this 👍John

@@Up4Excel Yep I realized that after posting my comment. 😕 Your approach is certainly cleaner than many I've seen for handling dependent lists PRE the availability of FILTER!

Yup. Filter made a LOT of my old school expertise obsolete and quaint. Luckily I like to learn!

@@tonypcoyle And a fair few of my older videos the same, but learning something new is the best bit anyway 👍

I think you're referring to the drill down feature which opens the source data for that value on a new sheet....you're correct that it doesn't matter what pivot table settings you have it will open with standard column widths despite the data size in the cells. It shouldn't affect the original pivot table column widths though. Unfortunately I don't know of any way to change this feature other than switch it off in the pivot table options.

No problem 👍John