Відео

No doubt it is possible, but not convinced it is desirable. The less complicated the formulas the better I always feel. There is a benefit to coming back to a spreadsheet after a long time and instantly seeing how it works without spending time getting your head around a formula. Not to mention other people being able to pick it up and understand it. I appreciate the skill of lengthy complex formulas but in businesses they can be a bit of a liability in my experience. Happy to hear alternative views on this, as given their popularity on videos I feel I might be missing something 🙂👍 John

Fast in there watching this John. Thanks again 👍John

You're welcome 👍 John

Thanks for the shortcut tip on the accounting format as it is often good to see dashes instead of blank. As for tracing errors, they have to come from the source data, unless you have added calculated fields or using data models. Assuming it is in the source it has to be from the column being used as the value field generating the error. Most often it is non-numbers in a numerical field...things like spaces and other text. use the ISNUMBER function on the source data and filter it to FALSE to detect them if they can't be seen. Other ideas welcome 👍John

Thank you! Cheers! 👍John

Happy to help Karl. Thanks for your comment 👍John

Thanks for watching 👍John

Well let me know if you find out Eddie 😂 Glad you're getting something out of my videos 👍John

Glad you got something useful from the video 👍I haven't tested the insert copied cells / rows thing myself. My guess is that it won't split the formats unless what you are cutting in already has conditional formats on it...but that is only a guess. Maybe try it out and let us all know the result 👍John

@@Up4Excel Challenge accepted. I tried the insert copied rows thing and it also buggered up the conditional formatting. In fact, I also had 4 rules but they looked nothing like your 4 rules and I took a screenshot of my End-Formatted sheet and yours too and put then side by side. There was a considerable difference between the 2 in appearance. UA-cam doesn't allow attachments in comments anymore so I can't show you. So, the moral of the story is, DON'T DO IT!

@@ianl1052 Well thanks for trying that out and reporting back. Just goes to show how sensitive conditional formats are to being duplicated and split. I'll stick to making a gap and pasting values only I think 👍John

Thanks for your support John 👍 If you get a chance, let me know any video topics you think would be good for me to cover.

Good to hear you got useful info from the video even if you don't use it for the same exact purpose. All learning is good 👍 John

Thank you Juliane and glad you learned a lot and could apply it too 👍 I've never tried this method on a data model pivot so good to hear you got it working 👍John

Glad to hear it 👍John

I suggest this video of mine which sounds exactly what you want...2 drop downs linked to a chart 👍 ua-cam.com/video/u9vs7_BCRX4/v-deo.html If you have really long lists then this video has a different technique too: ua-cam.com/video/s_ZElT6Kzj4/v-deo.html

Glad you learnt some useful stuff here 👍 The days left is the duration of the task. I was going to call it duration but then I realised it needed to reduce as the task was being done. With hindsight I think 2 columns would be better, one for the total duration and one that calculates the remaining duration from the percentage complete. It should be fairly straightforward to change if you prefer that. Hope that explains it 👍 John

Glad you like my videos Philip👍 I still give away free downloads with all my videos but stopped maintaining the library as it became a bit too much to work. I now sell Up4Excel Pro, which is all the video workbooks, advert free versions of all videos, and a searchable index etc. If you're interested I'm actually doing a bundle offer on it at present here: gw.up4excel.com/up4excelpro-discount

This is one of my older videos and I've taken on board a similar comment for my more recent stuff. Apparently the music is much louder with headphones so if you can use speakers you might be able to get through it 😬 wish I could edit it out but I can't without releasing the entire video

Well glad you finally found what you're looking for 👍 John

Thanks John, and Happy New Year 🎊 👍 John

Thank you very much for saying so. How you find the information useful 👍 John

Great to hear, that's what it's all about 👍 John

Well thank you for saying thanks. Glad to hear it was clear and easy to understand. 👍John

Glad you think so. Thanks for saying. John 👍

Thanks again John👍

Any time, hope it helps. John 👍

Glad it was helpful, John 👍

I think you're correct they'll need to be treated as text. You can turn the true dates into text with the =TEXT function to keep everything consistent. John 👍

Thanks for watching 👍John

Strange that doesn't work in charts if it works as a custom format in normal cells 🤔 I'd be interested to hear if you find a solution to it. John

@@Up4Excel I did not. Based on AI answer, for chart labels different style formats can be used so it happen that something does not work as usually. :(

@@marossik That's a shame, maybe something Microsoft will change one day ??

Thanks for your comments. You're right to question the possible non-uniqueness and whether Excel is the right tool. Excel is certainly not the best tool, or even a good tool, for storing datasets unless they're quite small really (max low 1,000's of rows I'd say). However, the unique check I put in, and the addition of going one character further than strictly needed, should give some confidence...although if handing over the spreadsheet to less competent users might still be of concern. To address the next ID issue I personally store a next ID number on the sheet using =MAX([ID Column])+1 and then type that in for new entries. This is only really fine if the number of new entries is low and typically added one at a time. Code is always messy in Excel, especially with all the current macro blocking and warnings so I try to avoid it these days.

@@Up4Excel I also try to avoid Macro/VBA code if possible. Once was the way to go (store a counter in the workbook, at running index numbers on data entry (automatically), and just keep counting). The other thing this automatically took care of: newer re-use an ID. It should count strictly upwards. As a plus, instead of deleting rows with their respective indices, one should mark rows as deleted and filter by that tick. More ovious: if you only use the ID to split and re-join tables during the update of a model, it is irrelevant if IDs get mixed up by sorting. They only need to stay fixed during runtime of the model - like Power Query, add Index-Column, and quickly forget about that index once the updates are made.

@@renatorosco325 It's a good point about there being no need for the IDs to stay fixed beyond the running of a model, as long as you're certain you won't be using them for anything else it certainly solves the issue. As for VBA, I suspect that an office script solution might be possible, but I've yet to dig into that so wouldn't know either way at present.

Thanks back at you John 👍

TRIM removes all spaces from the beginning and end of the text in a cell. Is that what you meant?

​I mean the last part from the video, it wasn't clear for me how you cleaned the dates there using the different formelas ‏‪8:43‬‏ ​@@Up4Excel

@@latifahfahad6121 I think you just need to watch it again. All the formulas show up at the top of the screen and you can look them up online if you don't fully understand them. John

@@Up4Excel i am just not sure what is the concept of combining the formulas

Glad you liked the video and sorry it's taken a long time to get back to you. Yes, add a line of code to come back to the original sheet. something like Sheet1.Select where Sheet1 is the VBA code name of the sheet as referred to in the VBA explorer on the left hand side of the VBA editor screen. Note it's not usually the same as the name visible in Excel such as Example in your case. Best of luck 👍John

Thanks for the compliments Jess, I'll take them with thanks 👍 John

I think that's either your connection or video playback speed setting 🤔

Cheers 👍 John

Your data needs to be inside an Excel table for that formula notation. Have a look at this video if you want some more info ua-cam.com/video/b5vruA_Dw9Y/v-deo.html. Glad you liked the video 👍 John

@@Up4Excel Right on! I appreciate all the time you are saving me.

I don't see why not. You'd have to work out the formula because that would be different, but other that it would be fine 👍 John

Glad you liked it Mohit 👍 John

No worries John 👍

Glad to hear that you're saved 👍 John

Sounds like you might be using a different version of Excel or something. The unprotect button is on the review ribbon and if it is already unprotected then there will be a protect sheet button instead.

Me to, I'll be in touch soon 👍

No worries 👍 John

Anything you add to a spreadsheet will slow things down, but how much will depend on the power of the computer being used. I'd suggest trying it on 100 rows at a time. If you're not noticing any impact after 500 then do another 500 etc. I'd be interested to hear how you get on 👍 John

Never use merged cells 🤣 Seriously though, merged cells in anything other than non-connected headers are a big issue for many many things in Excel and should be avoided at all costs. I'm not aware of anything other than VBA that can detect merged cells either, so doubt there is an index solution with formulas. My genuine advice is work out how to get the data into a table without needing merged cells. Good luck 👍 John

Good to hear 👍 John

Very true indeed John. Thanks for your comment 👍 John