Last 50 Posts on Chandoo.org

Naveen.TSS on 2019-01-21 22:09:29
Hi MarkyB, There is a solution, albeit a lengthy one and it's dynamic. Apply once and drag on to the rest of the cells. ={SUM(INDEX(OFFSET($C$5,0,3*(COUNTA(Q$16:Q16)-1),13,3),MATCH(Q$16,OFFSET($B$5,0,0,13),0),0))} goes with ctrl+shift+enter

Moteb on 2019-01-21 06:45:53
=IF(AND(SUM(C4:X4)<=1400,COUNT(C4:X4)<=20),"Can go","Can't go")

here on 2019-01-20 03:33:32
Great website. Plenty of useful information here. I am sending it to several pals ans additionally sharing in delicious. And obviously, thanks in your sweat!

Steve Ross on 2019-01-19 18:53:31
This was very interesting and helpful. Thank you.

Bhimarao Naik on 2019-01-19 10:57:45
Hi, I thick following formula is the simplest one to go for this problem. =IF((IF(COUNT(C4:X4)>=20,0,1)*IF(SUM(C4:X4)>=1400,0,1))=1,"YES","NO") instead of putting "20" and "1400" we can link cell reference to robust the formula. Let me know your opinion with this

Hui... on 2019-01-19 04:42:19
@Chinmaya I would ask the question in the Chandoo.org Forums https://chandoo.org/forum/ Attach a sample file to demonstrate what you want

Chinmaya Ranjan Mohanty on 2019-01-18 17:42:49
if i press 10 in a cell of a work sheet then it shows RAVAN in drop down list in excel.. IS it possible ??? if it is then how.. contact no 9853886050

https://www.artscow.com/user/alexandra88-2837957 on 2019-01-18 15:05:30
Excellent post. I used to be checking constantly this blog and I'm impressed! Very useful information particularly the final part : ) I deal with such information a lot. I was looking for this particular information for a very long time. Thanks and best of luck.

venkat on 2019-01-18 07:22:38
hi Chandoo, I have downloaded the sheet and when i go for the refresh and it says the below error initialization of data source failed. check the database server or contact database admin, make sure external database available. can you help me out.

nggrandong on 2019-01-17 08:29:12
Thank you, awesome website, glad been here

candace on 2019-01-17 06:43:18
Wow, you are truly AWESOME. This is a fantastic resource. Your photograph of you and your family is wonderful, too. You are a great role model for everyone, to maintain this site and share your expertise. And yes, I'll get your book!

Gabriel on 2019-01-16 20:57:15
sushil, ctrl+shift+enter refers to a combination of keys you press just like when you do crtl+alt+delete to reset your computer in this case it will "unlock" the array function of the formula also known as formula validation. If you clic the cell you will then see that the formula is now between {...}. This type of formula needs a validation process which is what the key sequence actually does. If you need to modify the formula, you can, but remember to "validate" it again once you are done. - Gabriel

Akhilesh Shukla on 2019-01-16 20:16:27
Nice tip. Is there a way to create multi select drop down please.

Samuel lege on 2019-01-15 14:30:08
Regestered

Varun Raj Singh on 2019-01-15 07:37:44
Hi Chandoo! When I change travel mode from Driving to Transit, I do not see a change in either Distance or Travel Time. Is there something else I need to do?

Osama on 2019-01-14 21:52:40
Thanks for your help and support

dez on 2019-01-14 09:55:33
Every once in a while when I encounter seemingly impossible issue to be dealt with in Excel, I always remember your words: "Excel can do everything". Well, Hui's World is a vivid proof to that sentiment.

Leonardo on 2019-01-13 14:33:26
Hi Chandoo, I use Excel for Mac 2016, so when I try to paste my version of the suggested formula the range of cells box truncates it to 255 characters max (my formula has 455 characters). Is there a way to get around this situation? I tried splitting it up in two and using CONCATENATE to combine it together but it didn't work. Maybe I'm doing it or there's a more clever way to circumvent this limitation...

Mohammad Ishaq on 2019-01-13 09:13:07
The micro to highlight row and column.But i thing is missing that it is only work on selected range. Is it possible to make it dynamic,i mean that when new data entry the micro automatically highlight the new row or column. Thanks

Axelle on 2019-01-10 19:17:07
Hi, I got the google API as you explained. When I copy this into the sheet, it doesn't work. status = REQUEST_DENIED Am I doing something wrong? Thank you for your feedback.

Kirby on 2019-01-10 17:47:07
Function ElevatorCanGo(MyRange As Range, MaxCount, MaxWeight) As Boolean ' Solves the elevator go/no go problem. ' KJM - 2019-01-10 ' Input: ' MyRange - range of weights for each elevator passanger (any units) ' MaxCount - (int) maximum permissible number of people ' MaxWeight - (real) maximum permissible weight ' Returns: ' True if elevator can go! Dim TotalCount As Long Dim TotalWeight As Single TotalCount = 0 TotalWeight = 0# Debug.Print "" For i = 1 To MyRange.Rows.Count For j = 1 To MyRange.Columns.Count If Not (IsEmpty(MyRange.Cells(i, j))) Then Debug.Print "Cell = "; MyRange.Cells(i, j).Address; " Weight = "; MyRange.Cells(i, j).Value TotalCount = TotalCount + 1 TotalWeight = TotalWeight + MyRange.Cells(i, j).Value End If Next j Next i Debug.Print "Elevator range: "; MyRange.Address; " Count = "; TotalCount; " Total Weight = "; TotalWeight If TotalCount > MaxCount Or TotalWeight > MaxWeight Then ElevatorCanGo = False ' no go Else ElevatorCanGo = True ' go! End If End Function

Saradha on 2019-01-10 11:12:14
Hi , i have created a macro for splitting a range and freezing few ranges to appear in three different worksheets . But sometimes i get error like "Subscript out of range error " Can you please help me

jim on 2019-01-09 08:31:35
thanks for your comment I refer you to above re my caveat

Son Day on 2019-01-09 07:06:49
Still no format to get negative numbers in Lakhs & Crores.........????? Anyone????

seth on 2019-01-08 15:49:27
Thank you for this wonderful blog. I have a little problem and I believe you can help me. I have a list of company fleet with Fleet Numbers and Registration numbers. All that I want to do is make excel be able to reference from the fleet and registration numbers table anytime I type a fleet number in a different worksheet. Please note the fleet and registration numbers are also on another sheet. I cannot go on repeating fleet numbers and registration. I was able to use vlookup to reference one fleet number and registration and it seems i have to change it anytime i change a fleet number It has made me really slow and your help will be more than a relief. You can use my email if you desired. Thank you once again.

Josh on 2019-01-08 06:04:22
I guess the simple formula below should do the trick. =IF((OR(COUNT(C4:X4)>20,SUM(C4:X4)>1400)),"NO","GO") Let me know... Thx.

Akash on 2019-01-07 15:17:41
4 hours 15mnts nearest hour

Camilo on 2019-01-07 05:06:56
Chandoo, thanks for that. I have a challenging one for you or someone that can find a way to solve it. I have 20 rows of different activities, each row/activity has a start location and an end e.g. Excavation from 2.3 km to 3.3km. Each km is in a different cell. Can you think of a way (formula) to conditional format it so I can quickly identify if there are any activities that are taking place within the same area (clashes)? Hopefully you can solve it, mate. Thanks.

Ajay Anand on 2019-01-06 16:54:15
IF(AND((SUM(C4:X4)<=1400),(COUNT(C4:X4))<=20),"GO","STOP")

MikeO3 on 2019-01-06 03:43:39
=AND(SUM(C4:X4)<=$AA$6,COUNT(C4:X4)<=$AA$5)