Search results

Hi Bob and Narayan, Thank you for the kind welcome. :)

Hi Ian, I don't quite follow what you've said there. My understanding from your earlier post is that the dropdowns already have today's date in their lists and that by setting the ListIndex to 24, today's date will be shown in the dropdown.

If you've ticked a reference and saved the workbook, it will still be ticked when another user opens the workbook. So, suppose you have a reference which is: C:MyReference.xla When the other user opens the workbook, Excel will try to find the file C:MyReference.xla on the other computer. If...

Ian, As a first effort, you can loop through the sheet's dropdowns collection and set each dropdown's listindex to 24, like so: Sub foo() Dim drp As DropDown For Each drp In Sheets("Dashboard").DropDowns drp.ListIndex = 24 Next drp End Sub Then, it turns out that you can set all of...

This might help: http://www.cpearson.com/excel/rounding.htm Basically, computers hold numbers in binary, not decimal, so two numbers which appear to be the same in decimal (how you see it), might not be the same in binary (how the computer sees it). Applying rounding is a workaround, or you...

Welcome to the forum. :) Give these a try: In F73 =MIN(F72,3000)*10% In F74 =MAX(0,F72-3000)*7%

Did adding it to the trusted locations fix the problem then? If it did, then yes, I guess they'll have to. It's not something which should be done programmatically (it could be exploited). To help, you could add some error handling to ensure that the master workbook isn't left open. Perhaps...

Which version of Excel are you using? 2007 or 2010? Try adding the network subfolder to your trusted locations in the trust center.

I guess the error is because of the network server in the filepath. Try this instead: Private Sub Workbook_open() Const strFILENAME As String = "networkpathsubfoldermaster forecast.xlsx" Dim wkbMaster As Workbook Set wkbMaster = Workbooks.Open(Filename:=strFILENAME...

I clicked on the insert function button (to the left of the formula bar) and filtered by category 'Most Recently Used'. It shows: INDEX CUBEVALUE CEILING.PRECISE WORKDAY.INTL TEXT AGGREGATE SUMIF CLEAN ISLOGICAL SUM I don't think they're the ones I use the most though. IF and MATCH...

You can do it okay if you're prepared to use a helper column. In B1 put this formula and fill down: =IF(OR(A2="All Segments",B2=""),A1&"",IF(OR(A1={"All Segments",""}),B2,A1&", "&B2)) In C1 put this formula and fill down: =IF(A1="All...

Disabling the automatic update is something you should do manually and then save the workbook. Once you've changed it to not automatically update and saved the workbook, it will keep that setting, so no need to do it the VBA. To do this go to the Data tab on the ribbon > Edit Links &#62...

There's an AutoOpen() event handler, but that gets called after Workbook_Open(). So you're already using the correct event handler from that perspective. The option I posted before should do the trick - did you implement it?

Hi, The link is probably trying to update before the master workbook is opened by your code. Without trying to redesign your current workbook structure, you could disable the automatic update of the link and then add more code to your Workbook_Open() event handler to update the link after it...

Hi, Yes, they need to be used with caution because a lot of users don't understand them and they can slow your spreadsheets down if overused or used incorrectly. Here's a tutorial I wrote 5 years ago on array formulas: http://www.xtremevbtalk.com/showthread.php?t=296012 At the end of the...

Another way: With Worksheets("Sheet1") If .Cells(.Rows.Count, "A").End(xlUp).Value2 < 5 Then Worksheets("Sheet2").Select Else Worksheets("Sheet3").Select End If End With

Hi, I expect the problem is that you can't unprotect a sheet if the workbook is shared. Hope that helps... Colin

Your alternative formula didn't work because the letter pairings need to be in an array constant. For example: =IF(OR(RIGHT(V2,1)="1",OR(RIGHT(V2,2)={"AP","SH","MG"})),1,2) But as Narayank991 points out, it'd be much better to put these two letter combinations in...

Hi jsto, If you're using Excel 2007 or later then I'd recommend using SUMIFS() instead of SUMPRODUCT() for this, because the SUMIFS() formula will be more efficient. =SUMIFS($H$2:$H$4233,$C$2:$C$4233,C3,$G$2:$G$4233,$G$3)

Hi, Yes, just to explain why.... The syntax of the RIGHT() worksheet function is: RIGHT(text,num_chars) RIGHT(L13,) is the equivalent of RIGHT(L13,0) which isn't what you want. RIGHT(L13) is the equivalent of RIGHT(L13,1) because if num_chars is omitted then the default value is 1.

Hi bobhc Spam is almost certainly going to come from a new member with a very low post count. Can't the spam catcher be loosened for members who have a certain number of posts? Thanks for unlocking it, much appreciated. :)

Two more of my posts have hit the moderation queue. (One of them is a duplicate where I re-tried, so it can be deleted). It's a little bit frustrating that posts where I try to include a link get queued, even if I use the 'a' tag. Am I doing it wrong?

Also see this thread from here.

Hi Rob, If you right click on the ActiveX control > format control > properties tab, you can set the property to not move + size with cells and not to print. This should help. However, my advice would also be not to generally use ActiveX controls embedded on worksheets: there are lots...

If the two ranges are on different sheets then slightly different code is required. Sub FooBar2() Dim rngToCheck As Range Dim rngTopLeftCompareTo As Range Dim r As Long, c As Long 'these are the cells which will be highlighted Set rngToCheck =...