11 ай бұрын
2 жыл бұрын
2 жыл бұрын
2 жыл бұрын
2 жыл бұрын
2 жыл бұрын
3 жыл бұрын
4 жыл бұрын
4 жыл бұрын
4 жыл бұрын
4 жыл бұрын
5 жыл бұрын
5 жыл бұрын
5 жыл бұрын
5 жыл бұрын
5 жыл бұрын
5 жыл бұрын
Пікірлер
@AhmadIsmail-x3o 23 күн бұрын
Hi Dear, thanks for your video but i am stuck on the condition and rules because in my sheet color cannot change
@ELKAMELBI 22 күн бұрын
Send me an email and I will try my best to help you
@AlvinDelaPaz-ni3jd Ай бұрын
Thank you so much!
@AidablastusNgimba 2 ай бұрын
Please sir help me🤲🤲🙏🙏
@ELKAMELBI 2 ай бұрын
🚑 email the channel with your problem 🙂
@milododds1 2 ай бұрын
Is it possible to make the dynamic pulldown list into where they can select more than one like a checklist?
@ELKAMELBI 2 ай бұрын
Not in Excel but you can in MS Access.
@milododds1 2 ай бұрын
@@ELKAMELBI I have found some code in other videos, but I need to figure out how to exclude their code from applying to all of the pulldowns.
@ELKAMELBI 2 ай бұрын
Was the vba code? Or do you mean formulas ? If vba code then yes because you can link it to a form. I don't vba code and I am not a fan of it. However, if it is a formulas that enables you to make multiple selections in a cell, I would like to see that, I am yet to see something like this without being overly complicated and involves redundant data.
@Bagheera19 3 ай бұрын
hello, the url you set at start is not working currently. Can you give any alternative
@ELKAMELBI 3 ай бұрын
Hi, yes they are using https now, add s after http
@adhammayassi9847 4 ай бұрын
Great Vid! How can I increase the currencies? There seems to be just three (USD, EUR, GBP) in the setup sheet.
@ELKAMELBI 4 ай бұрын
You can define more currencies by going to 'Lists' tab and add the symbol under Currencies table and update the Exchange Rate table as well and e erything should work fine.
@smartnewenergy9678 4 ай бұрын
Thanks for the good video. Regarding your example, I have placed the days of the month vertically and I use not the column function but the line and it works perfectly. If we have 10 employees, the working days are placed vertically at the beginning of the work type and concern all 10 employees. But in the calculation for working hours and overtime pay, For example we have payments in the first 8 hours of work ($1.00) X 1, in the 9th hour ($1.00) X 1.20, in the 10th hour ($1.00) X 1.40(only 150 hours per year allowed), 11th hour ($1.00) X 1.60, more than 11th hour worked ($1.00) X 2.20, also night hours get an extra ( $1.00) X 0.25, also for offsite work (work in another city) they get an additional 10% or 15%, so I have them placed in different columns. The first question is what formula should I use? and the next is the following: on Saturday the first 8 hours of work ($1.00) X 1.30 and overtime on Saturday ($1.00) X 2.30 and on Sunday or a public holiday the first 8 hours ($1.00) X 1.75 and overtime ($1.00) X 2.75 but in Timesheet Automation we have the days of the week in some cells and Saturday and Sunday in some others, next month they change position, so a formula e.g. (if(Sunday=(calculation of hours or overtime ); if(not Sunday); "blank cell" same for Saturday. Also for days of the week e.g. (if(Monday, Tuesday, Wednesday, Thursday , Friday =(calculate hours or overtime ); if(not Monday, Tuesday, Wednesday, Thursday, Friday); "empty cell") Also, how will the formulas contain if(official holiday =(calculation of hours or overtime); if(not official holiday);"empty cell" Thanks for the help and I apologize if I bore you with the long comment and I look forward to your help ps : all the above are for example for the production of the formulas and do not really exist!
@ELKAMELBI 4 ай бұрын
Interesting, the way to go about this is to calculate the combined factor (multiplier) that the hourly rate should be multiplied by. since we have few of those, the best approach is to test each condition (you get a TRUE or FALSE result) and then multiply it by the factor you are testing for and multiply it by the hourly rate. do this for the conditions and add them up. the formula is something like this: 8 x StdRate + [is total hrs more than 8] x (StdRate x 1.2) + [is total hrs more than 9] x (StdRate x 1.4) + [is total hrs more than 10] x (StdRate x 1.6) + [is total hrs more than 11] x (#Hrs - 11) x (StdRate x 2.2) + ........etc. you are not going to be using IF statements for checking the hours you just use a logical statement that result in TRUE or FALSE like (#Hrs > 9). the formula can get bigger with all the conditions you have but a good practice is to use Alt+Enter to add new line in the formula bar and it the formula would still work. this is good to having an easy-to-read formula and limit errors.
@jules1599 4 ай бұрын
This is so awesome! How would it look like if i had each customer on a separate sheet?
@ELKAMELBI 4 ай бұрын
It will act the same because we are referring to the table name not the sheet name.
@jules1599 4 ай бұрын
@@ELKAMELBI i see, i guess what i am trying to say is what if i wanted to keep each customer on a different sheet tab, what would the formula be then? I guess this could be done in a different video. Also, how can we download the sample excel sheet? Even if I sign in with my Microsoft account it gives me error.
@ELKAMELBI 4 ай бұрын
@jules1599 I will fix the file link issue and look into what you described about separating customers soon as I can, thanks.
@ELKAMELBI 4 ай бұрын
@@jules1599 The link is working fine on my end. I opened it in a private tab without login and it opened the file. did the file open for you? if yes, just go to file menu and then Save As and then click on Excel, if you are still facing issues send me an email and I will send you the file. as for separating the customers, are you talking about the price lists for company1, company2, and company3 in the example? you want to put each one in a separate sheet? if that is the case then as I said just cut them and paste them and everything will continue working as is. if you mean something else, please elaborate 😊.
@jallahkennedy8298 4 ай бұрын
Very good. Thank you.
@ELKAMELBI 4 ай бұрын
You're welcome!
@sonaimuthu2060 4 ай бұрын
Very use full ur video but if i am enter one monh time sheet completely then i will go next month my previous month record will not come as weel ass i will record when previous month details also .any idea?
@ELKAMELBI 4 ай бұрын
Yes, because the template is designed to handle one month at a time, so each month will have its own file. if you want to keep a history in the same sheet for all the months, design changes need to be made, first data entry will be in a separate table and the timesheet report will read from the data entry table and display it accordingly. Actually, when done this way you can also include employee names as well. However, it is not a simple project, but I agree it is very useful, thanks for sharing your thoughts. I have done something simiar on the channel, check the VertiCAL strategic calendar video, there should be a link to download the file.
@humaynarimanova5294 5 ай бұрын
Great, fantastic... Thank you very much.
@ELKAMELBI 5 ай бұрын
You're most welcome 🙏
@BillyJoe-gk4ud 6 ай бұрын
do you have a tutorial how to put computation based on that format? ty
@ELKAMELBI 6 ай бұрын
Can you elaborate? I am sure I am following.
@BertoniFlores 8 ай бұрын
hello! when I click on the link the template it's not empty, how do I fix this?
@ELKAMELBI 8 ай бұрын
I see, open the template in Excel use Ctrl+G to open the go to window, go to special and select formulas, this should select all cells with formulas, once selected hit the Delete key. also open conditional formatting and click manage rules and delete all conditional formatting.
@BertoniFlores 8 ай бұрын
hello! when I tried to use the link to try to create a copy and follow along, the template was not empty. how do I fix this?
@chrisr3592 9 ай бұрын
Wow. Thanks
@ELKAMELBI 9 ай бұрын
Glad you liked it 😊
@ImperfectPerfection72 9 ай бұрын
This is cool, thanks! Is this still available for download?
@ELKAMELBI 9 ай бұрын
Thanks, yes I just found out the link was an old one. I just fixed it.
@ImperfectPerfection72 9 ай бұрын
@@ELKAMELBI Thanks so much 🥰🥰
@ELKAMELBI 9 ай бұрын
@@ImperfectPerfection72 you're welcome 🤗
@BrandonDorame-je5tu 10 ай бұрын
Thanks! When I add another column for "quantity" in column c and manually input a 5 for Bluetooth Headphones and create a subtotal column using =(C5*D5) I get a #VALUE error. How can I fix this? I want to be able to build out how many of each quantity the customer is buying and keep a running total of pricing. Ideally when I do this equation, I get (66*5) = 330.
@BrandonDorame-je5tu 10 ай бұрын
Please help!
@ELKAMELBI 10 ай бұрын
Please send me your file by email?
@BrandonDorame-je5tu 10 ай бұрын
@@ELKAMELBI I duplicated the same document as shown in the video. The formula shown works but now I want to take that result and multiply it by a quantity. Thank you!
@ELKAMELBI 10 ай бұрын
@@BrandonDorame-je5tu I don't see why this is an issue, if you put quantity in column D because column C already contains the unit price, however, if you switch companies the quantity will not change automatically. if you want to have quantities appear automatically, you need to add a quantity column for each company table and then create a function in the main sheet similar to the one that brings in the unit price. hope I understood you question correctly.
@Fuyu4537 10 ай бұрын
hi, this is what i looking for. thank you. would like to check with you, this function is used in column, how about for row? does the function can be change to row type? thank you
@ELKAMELBI 10 ай бұрын
Thanks, glad you liked it, yes it can be modified to act on rows but it is easier to change your data to columns I believe.
@RosemaryCainLawrence 10 ай бұрын
I cannot get a line even though I watched your video =OFFSET($B5,1,0)<>$B5 (my row is B5) It just keeps on saying error with this formula
@ELKAMELBI 10 ай бұрын
The formula looks correct, the steps are as follows, SELECT the whole range and B5 should be in the top row, next write the formula in the new rule box and then don’t forget to select a border from the format button. If you have done all that then it should work. You can me a sample file of your data and I will create it for you and you can where thing got mixed up but keep your attempt.
@guichunpan56 6 ай бұрын
@@ELKAMELBI Mine doesnt work either, and the error keeps changing
@ELKAMELBI 6 ай бұрын
@@guichunpan56 send me an email and I will reply with what I gave Rosemary.
@Lyriks_ 11 ай бұрын
Genius
@ELKAMELBI 11 ай бұрын
Thanks 🙏
@mikeschieffer6323 Жыл бұрын
Elkamelbi, A great video. Could you provide a formula IF my table (price list) is in another open excel file? I would like to have my different price lists in a separate Excel file from the Invoice file.
@ELKAMELBI Жыл бұрын
Thanks, I am currently busy, but I will try and work on it when I have time, thanks for the suggestion.
@salesppw Жыл бұрын
you are a genius. It works well.
@ELKAMELBI Жыл бұрын
Thanks 🙏 you made my day 😊
@haifaalinoora7182 Жыл бұрын
Good teaching video, you are a very helpful
@ELKAMELBI Жыл бұрын
Thank you 🙏
@DianneDixonLuxeNest Жыл бұрын
The instructions here helped me build an Estimate calculator with different price lists - thank you so much!
@ELKAMELBI Жыл бұрын
Glad I could help, thanks for the feedback 😊
@JathanLane Жыл бұрын
BUDDY. THIS IS INCREDIBLE. Thank you for this information. You just saved my job. I needed to compare two HR lists and highlight the removed employees, the added employees, and the moved from location or dept.
@ELKAMELBI Жыл бұрын
@JathanLane Wow! I am flattered, glad to hear that, you're most welcome! thank you for the feedback :)
@rayangoatking Жыл бұрын
Nice
@khalidmajeed2886 Жыл бұрын
Mashallah sir,,,,,,,,,,,,,,need videos on power query
@ELKAMELBI Жыл бұрын
Thanks, I should do that one day 😅, thanks for the suggestion.
@LuciePerrin-w4o Жыл бұрын
Great tutorial! 1 question: How would I need to change the formula of the Lowest Price Offered column in order to get the lowest price excluding $0 for suppliers not selling a specific item? Thank you!
@ELKAMELBI Жыл бұрын
Thanks, will check the formula and get back to you. it should be possible to do.
@mahmoudaljamal2911 Жыл бұрын
i believe that we can use Ending date - starting date of the contract to calculate the remaining days for ending of the contract could this be useful ? thanks
@ELKAMELBI Жыл бұрын
It would give you the total days of the contract, the remaining days has to be end date - today
@Omega90-ws2vg Жыл бұрын
Thank you very much! Your personal budget templates are the best!
@ELKAMELBI Жыл бұрын
That is great to hear! Thanks 🙏
@mkkhatri Жыл бұрын
I found this video very effective but I have faced an issue as I have data distributors in columns not in row. Please suggest what should I do?
@ELKAMELBI Жыл бұрын
Usually, the table can be transformed using Power Query in Excel, or the formula get changed but it would be complex non the less. without looking at the data myself, it is hard to tell. feel free to send it by email and I will see what I can do.
@DavidAkinyanbola Жыл бұрын
I tried it but after i format the table and do my first drop-down calculation i have to format the table again. is that normal?
@DavidAkinyanbola Жыл бұрын
I just realized I confused saving the document for saving the table. It worked perfectly after I saved the table, thank you!
@ELKAMELBI Жыл бұрын
@user-ou9sw4vw3n glad you managed yourself 😊
@colinwalder5033 Жыл бұрын
What an excellent tutorial, taught with a calm voice and each step explained with an unhurried and very clear style. You are a great teacher. Thank you and subscribed.
@ELKAMELBI Жыл бұрын
Thank you so much for the feedback, it means a lot 😊 glad to hear this
@gajanangalgale205 Жыл бұрын
It is apply only on same sheet .if we have list on other sheet it cannot apply.
@ELKAMELBI Жыл бұрын
In old Excel versions yes, but I believe from 2013 you can put the table in another sheet.
@gajanangalgale205 Жыл бұрын
I am using Excel 2016 . But it cannot apply
@ELKAMELBI Жыл бұрын
@@gajanangalgale205 that is weird, it shouldn't be, 2016 should support having the list in another sheet. maybe the reason is related to something else.
@kaltimus Жыл бұрын
That's great - unless you need to use the value in another formula elsewhere. :(
@ELKAMELBI Жыл бұрын
True! though if you need to, you can use a combination of the VALUE function and maybe LEFT, RIGHT, or MID function to make it a number you can use in another function but of course it is not ideal. normally I would limit the use of this feature to Totals only.
@kaltimus Жыл бұрын
@@ELKAMELBI What I finally ended up doing is using a hidden column for my formula calculation to get my value (and then use in other formulas) and then your suggestion in the visible column. I wanted to display variable text based on the row, like "date1 - date2 $#0.00", where date1/2 are from another cell. I was using the console to input a custom date format for each cell, but with a spreadsheet of cells that wasn't feasible, as all new cells would need edited via the console. So your solution made it more elegant and expandable. Thanks!
@ELKAMELBI Жыл бұрын
@@kaltimus you're welcome Glad I could help :), it always bothers me why until now Microsoft couldn't introduce these features without us digging deeper in what could be possible!
@bart-jandekker9105 Жыл бұрын
The amount of grains adds up to 18,446,744,073,709,551,615 - so your total is off. Things go wrong in Row 53 (square 51). It seems to be a limitation of Excel.
@ELKAMELBI Жыл бұрын
Thanks for highlighting this, yes recently I figured that Excel is limited to 15 whole numbers anything above that is switched to zero!
@kumulsoft1061 Жыл бұрын
This video is great, I am so thrilled to learn something I am struggling to address. Thank you.
@ELKAMELBI Жыл бұрын
I am so glad that you learned something here and hope to find more in the channel, best of luck and thank you for the comment.
@kiritlemaila2350 Жыл бұрын
How do I use the data to determine temperature for the next coming two hours
@ELKAMELBI Жыл бұрын
Hi Kiritle, I recommend visiting open-meteo.com, you can get a free API for historical and forecasted weather, you can use it through power bi or excel by utilizing Power Query, if you got stuck, send me an email and will give a file that contains the API and you just need to refresh the data 🙂.
@kiritlemaila2350 Жыл бұрын
How do you determine the time the next temperature could be at ?
@abdulhamidalhaddadi2255 Жыл бұрын
Thank you
@ELKAMELBI Жыл бұрын
You are welcome 🙏
@DellaHalvorson-j1f Жыл бұрын
My head hurts but it works! Mahalo nui loa!!!
@ELKAMELBI Жыл бұрын
'A' ole pilikia 😉 glad you made it work
@Untold_Incredible Жыл бұрын
your video is good but less audible
@ELKAMELBI Жыл бұрын
True, I didn't have a good mic back then, now I do! do you think I should redo it?
@usmaniqbal1836 Жыл бұрын
Please share data file for practice
@ELKAMELBI Жыл бұрын
thanks for reminding me, will do shortly
@cristofuscolombus1175 Жыл бұрын
If I want to create one more drop-down list under the Vegetables>Broccoli>"Types of Broccoli" how could I do it
@ELKAMELBI Жыл бұрын
Doable, but a bit harder to explain and requires a bit of management as you add more categories, basic idea is that the items in the second list will become a header in tree of third list. If this is something you need, send me your file and I will see what I can do.
@cristofuscolombus1175 Жыл бұрын
@@ELKAMELBI Thanks, I sent Excel file, you can examine my problem.😃
@cristofuscolombus1175 Жыл бұрын
you can call me from as I sent my mail from my mail address
@ELKAMELBI Жыл бұрын
Ok will check once I get some free time 🙂
@ryanmoore4432 Жыл бұрын
cheers guv
@ELKAMELBI Жыл бұрын
I actually had to look guv up :)
@ryanmoore4432 Жыл бұрын
@@ELKAMELBI no problem geezer, great video, saved me a bunch of time!
@ELKAMELBI Жыл бұрын
Glad it helped 👍🏼
@yigityilmaz3317 Жыл бұрын
BROOO I've been looking for this for like 1 hour. Great video! Thank you
@ELKAMELBI Жыл бұрын
You’re welcome! 😁. Glad to hear that! Also please read the pinned comment to understand the limitations
@boorayin2773 Жыл бұрын
Doh! Excel Online doesn't have Name Manager.
@ELKAMELBI Жыл бұрын
True! just checked, however, there is a workaround. select the cells that you want to name and then go to the Name Box just left of the formula bar and write the name there and hit enter and the range select will be named, make sure that it contains data before you name otherwise it will give you an error. it is really strange that this is the case, how Microsoft did pick this issue. thanks for mentioning it, will dig more into it and see maybe it is hidden somewhere.
@billhawkins9297 Жыл бұрын
I am trying to add the formula on the Dashboard for This Month and I have the formula written exactly as yours and I continuously keep getting the error that excel doesn't understand the formula
@ELKAMELBI Жыл бұрын
What version of office are you using? If before 2013, the formula needs to be written differently. Also, you can send me your file by email and I will look at it.
@billhawkins9297 Жыл бұрын
@@ELKAMELBI excel 365
@ELKAMELBI Жыл бұрын
365 should work perfectly, it would best to send me your file by email (you can find it in the about section of my channel) and I will tell why it is not working.
@ohthts 2 жыл бұрын
I can not find data validation anywhere on newest 365. Please help
@ELKAMELBI 2 жыл бұрын
Oh! Really? It should be under DATA tab. Or just search for it in the search bar at the top.