Search results

See http://blogs.msdn.com/b/excel/archive/2005/10/20/483205.aspx

Normally, I create 3D data as separate tables. For example a customer table CusNum.......CustomerName 1............Company A 2............Company B etc. a Product table ProductNum...ProductName...Price 1............Widget A......12.29 2............Gizmo B.......2.39 etc. and a...

It is not clear to me from this post what the question is. You say that you have created the data and its structure, so what do you actually need?

Surely, they are only two-tone when selected, and single solid when not selected. This is to highlight the selected tab.

90 report tabs? did I read that right? How can you ever assimilate that amount of information?

Try this =IF(A2=A3,"",SUMIF(A:A,A2,C:C))

Another shot Public Function MakeVCF() Dim vecCal As Variant Dim Keysh As Worksheet Dim Source As Worksheet Dim Target As Worksheet Dim Lastrow As Long Dim i As Long i = 2 'Row No Set Keysh = Worksheets("Paste Data Here") 'Real data sheet change to suit Set Source =...

Oops, my bad. Try this Public Function MakeVCF() Dim vecCal As Variant Dim Source As Worksheet Dim Target As Worksheet Dim Lastrow As Long Dim i As Long i = 2 'Row No j = 0 'Record No Set Source = Worksheets("Data") 'Source sheet change to suit Set Target = Worksheets("Cal") 'Destination...

It is determining 450,000 minus B9 divided by 100,000 but with a maximum of 1, and zeroising if negative. It can better be written as =MAX(0,MIN(1,1-(450000-B9)/100000))

Public Function MakeVCF() Dim mtxCal As Variant Dim Source As Worksheet Dim Target As Worksheet Dim Lastrow As Long Dim i As Long i = 2 'Row No j = 0 'Record No Set Source = Worksheets("Data") 'Source sheet change to suit Set Target = Worksheets("Cal") 'Destination sheet change to suit With...

You will have to do =Client1.xlsx!B38+Client13.xlsx!B38+...

Private FSO As Object Private NextRow As Long Sub LoopFolders() Set FSO = CreateObject("Scripting.FileSystemObject") selectFiles "c:MyTest", ThisWorkbook '<<<< change as required Set FSO = Nothing End Sub...

Actually, we have been misleading you a bit with OFFSET($A$1,,,COUNTA($A:$A)-1,1),5) because if we subtract 1 fro the header row, the header row is likely row 1, so we should either start explicitly at A2 OFFSET($A$2,,,COUNTA($A:$A)-1,1),5) or implicitly by offsetting the cell reference...

<quote>In regards to using * or , between arrays, that wouldn't be my concern.</quote> It would be, will be, if you are trying to evaluate all conditions using,.

Actually, Excel treats any non-zero number as true, not just 1 (it is very lax). That is why you often see code like =IF(COUNTIF(rng,condition),"one thing","something else") because whatever COUNTIF returns as a result other than 0 it will take the first action. If you have two conditions...

<quote>I understood when I thought about it more. Hui (Excel Ninja) had explained about getting +1 or -- get a positive digit. I know (LEFT(OFFSET($A$1,,,COUNTA($A:$A)-1,1),5)="WHO54" returns: either 0 or 1.</quote> No it doesn't! It returns True or False, you need some way...

Private Sub Workbook_NewSheet(ByVal Sh As Object) Sh.Name = "Week" & Me.Worksheets.Count - 1 With Me.Worksheets("Summary") With .Range("A1").End(xlDown) .EntireRow.Copy .Offset(1, 0) .Offset(1, 0).Value = Sh.Name End With End With End Sub 'This is workbook event code. 'To input this code...

It is a string, it is a Boolean result, because you are comparing cell values to a specific text value, which will always be True or False. See http://xldynamic.com/source/xld.SUMPRODUCT.html for a detailed explanation

In a word, no. You can use VBA event code to trap the value as input and change it, but not a formula.

Get used to scripts Thomas, it extends the power of Excel geometrically.

Private Sub Worksheet_SelectionChange(ByVal Target As Range) Const WS_RANGE As String = "D3" '<<<< change to suit If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target .Validation.Delete .Validation.Add Type:=xlValidateList...

Or http://xldynamic.com/source/xld.SUMPRODUCT.html

That can be single column specific, or multiple non-contiguous columns (use Ctrl-click).

Using this technique, they would select the source and destination column using the mouse. This allows, indeed requires, navigating to the appropriate sheet. Sub SelectEntireColumn() Dim x As Range Dim y As Range 'Dim sheet As Range On Error Resume Next source_file =...

Use data validation with a type of custom and a formula such as =OR(A2="",B2="",C2="")