Search results

Sub AddFooters() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.PageSetup.LeftFooter = "751" & Format(ws.Index, "0000") Next ws End Sub

Use event code in the worksheet code module Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit Application.EnableEvents = False If Target.Column = 8 Then If Target.Value <> "" Then Me.Cells(Target.Row, "B").Value = Date Me.Cells(Target.Row...

Use =DATEDIF(startdate,enddate,"M")

This consolidates in date order Sub Consolidate() Dim sh As Worksheet Dim t1 As ListObject Dim t2 As ListObject Dim t3 As ListObject Dim t1Rows As Long, t1Next As Long, t1StartRow As Long, t1StartCol As Long Dim t2Rows As Long, t2Next As Long, t2StartRow As Long, t2StartCol As Long Dim t3Next...

Tom, Consolidating is simple enough ... as long as there is some key to tie the data together. Your tables seemed to be cnsolidated by sight, the two tables do not have any common values, so how do we know which goes with which?

So you clearly understand the way to have multiple conditions with separate formats. Do you have Excel 2007 or 2010? IF so, you could format your data as a table, when you add a new row the attributes of the table row are inherited.

Don't respond directly, keep it in the thread. Your questions or further information may be relevant to other viewers/responders. If you do it within the thread, anyone would be hard-pressed too be offended, that is what it is all about. It is not always possible for the OP to fully explain...

You haven't suggested that you need more than two colours, so isn't it irrelevant? If you had more condition such as =0, then you would just add a rule to the CF to accommodate that extra condition and format (colour).

What are temp sheet conditional formats? They are either formats or not. If you delet them all as I said, you can Copy>PasteSpecial Paste:=xlPasteFormats

Write some code to delete all existing conditions first For Each cell In Selection For i = .FormatConditions.Count To 1 .FormatConditions(i).Delete Next i Next cell

<quote>And despite all that, I enjoyed doing such a thing, firstly intended for personal use, then uploaded for those who might be interested on it.</quote> Fair enough, if re-inventing the wheel rocks your boat, enjoy it :) BTW, one serious problem I found when I tried it on...

No it is not, it was around long before Chandoo became prominent. You may have found a post by Chandoo, but it has evolved from the work of many people over many years.

<quote> As you well know I use a Spanish version of Excel and every time I post a formula I feel and seem obliged to traduce it to English so as to not overcharge users</quote> Why not just go into the VBE and type ?Activecell.Formula in the immediate window?

I think the formula he really found was =SUMPRODUCT((G2:G89<>"")/COUNTIF(G2:G89,G2:G89&"")) The basic formula is just =SUMPRODUCT(1/(COUNTIF(G2:G89,G2:G89))) but this returns a #Div/0 error if G2:G89 is all empty, so we force a test against blank like so...

How about this Sub DumpArray() Dim ary As Variant Dim cntRow As Long Dim cntCol As Long ary = [{1,"a","1a";2,"b","2b";3,0,"3c";0,"d",0;5,"e","5e"}] cntRow = UBound(ary, 1) - LBound(ary, 1) + 1 cntCol = UBound(ary, 2) - LBound(ary...

Why not just drop the whole array in and then replace all 0 with blank?

How about a simple =SUM(A:A)

I guess you mean in E8 not E7? If so, you have a problem, as that cell already contains a formula that drives the colour, so if you change that to the name for that period, you will lose the colour.

See http://www.xldynamic.com/source/xld.ColourCounter.html

Are you trying to save a file in Excel 2007/2010 as an Excel 2003 document?

If you change the format to just columns of Ref and Horse name, maybe one for choice #, the pivot would be easy.

Sounds like conditional formatting might do the job. How is the data laid out on the spreadsheet, what are the valid values?

It does? Such as?

Isn't it just (size_hdd1_in_bytes*8)/(speed_to_hdd1-speed_from_hdd1)-1 and assuming that HDD2 is similar in size to HDD1

Suggests data error to me, SP reads from closed workbooks okay. Post the two formulae.