Re: Saving Workbooks Automatically With Different Names
Find your question a bit fuzzy but maybe this is enough help.
If you want to keep the orginals just make copies
Re: Saving Workbooks Automatically With Different Names
Find your question a bit fuzzy but maybe this is enough help.
If you want to keep the orginals just make copies
Re: Formula Dependent On Variable Range
Your problem is that Range("$A$2:A2") only is the range A2 (no information about the way you entered it) and its address property always returns $A$2 with default arguments.
This should fix your problem
Re: IF Formula Efficiency
I have now maded an experment about this.
In one file I filled the range A1:IS2500 with the if formula and in another file with the multiplication formula. The multiplication file was some tenth of a second faster at calculation. The multiplication file also is 26 236kB less in size.
Re: Formula Dependent On Variable Range
Your code:
ActiveSheet. Names.Add Name:="myRange1", RefersToR1C1:= _
"=Range(R2C1:R[1]C[0])"
ActiveSheet.Names.Add Name:="myRange2", RefersToR1C1:= _
"=Range(R[1]C[0])"
Range("L2").FormulaR1C1 = "= COUNTIF(myRange1,myRange2)=1"
Correct code:
ActiveSheet. Names.Add Name:="myRange1", RefersToR1C1:= _
"=R2C1:R[1]C[0]"
ActiveSheet.Names.Add Name:="myRange2", RefersToR1C1:= _
"=R[1]C[0]"
Range("L2").FormulaR1C1 = "= COUNTIF(myRange1,myRange2)=1"
Range() is only vba code, not a worksheat function. If you would define the name in excel using insert->name->define you would never type range and therefore you shouldn't do it in
either.
Re: Overflow Error
I've tried your code and it doesn't seem to have any problems with cells containing strings longer than 255 characters. Have you posted all of your code?
One way to get around run-time-errors is to use
and somewhere else put
Re: Copy Status Bar Stat To Clipboard
If you use the sum function all time this will work.
sub putSumOnClipBoard()
Dim MyDataObj As New DataObject
MyDataObj.SetText Format(Application.Sum(Selection))
MyDataObj.PutInClipboard
end sub
but it might be formatted as text after pasting. If you have a spare cell you could use
Re: IF Formula Efficiency
I tried thinking in terms of assembler code.
The if way would require one subtraction and on conditional jump equals two operations.
The product sum requieres two multipications and two subtractions/additions and some data has to be stored in a register duringn the process. This adds up to five operations.
So the if solution should be quicker but it is hard to know how it works deassambeled.
The first and second alternative suggested by shg should differ much in assembler.
Hi!
I have a thing I wounder ,not much of a problem thou.
If there is either a 1 or 0 i cell A1 what is the most efficient
=IF(A1=1;B1;C1)
or
=A1*B1 + (1-A1)*C1
?
Or perhaps their are something even more efficient??
Re: List
Lets say your columns are A and B. Furthermore let C1 be where you enter the number to lookup and D1 where the customer pops up.
In D1 type:
=VLOOKUP(C1;A:B;2;FALSE)
Re: Dynamic Pie Chart With No Zeros
First of the labels:
I've added (inserted) a B-column into your table
In B3 I typed (and then filled downwards):
=IF(INDEX(C3:AU3;MATCH($AX$22;$C$1:$AU$1))=0;"";A3)
which returns "" if the value is zero, otherwise the text i column A.
AX22 is the cell where I typed the month I like to view.
Now the correct data:
To get the correct data in the chart i added the name (Insert->Name->Define)
Sheet1!chartData which refers to:
=OFFSET(Sheet1!$C$3;0;MATCH(Sheet1!$AX$22;Sheet1!$C$1:$AU$1)-1;17)
This returns the range with the data you want to use.
Change the charts SourceData so that the series uses Sheet1!chartData and value X-axis uses the B column.
To get the drop down list you can use Data->Validation (Not done in example file)
Note1: It seems important that chartData is local to Sheet1. The name Sheet1!chartData worked fine but just chartData didn't when I tested.
Note2: It would be more clever for row 1 to use the first day of the month instead of the last. That is because the first is always 1 and not sometimes 30 and sometimes 31.
Due to how the match function works using the first day would make 2007-05-15 in AX22 give a correct chart while using the last day would give the preceeding months chart.
However if you use validation this is a small problem.
Re: Looking Up Row Array Data In Column Array
Ah Transpose!!
Now I know many ways to do this =)
Thanks Tom
=SUMPRODUCT((A84:A89="x")*TRANSPOSE('X-ref'!T8:Y8)) as array formula did the trick
Re: Looking Up Row Array Data In Column Array
I'm afraid your formula doesn't work since sumproduct only can handled arrays of the same dimension, same problem as if using sumif
Hi!
I have the following problem.
On one sheet (KPI) I have either the values "x" or "" in the range A84:A89 to mark wheter to use the corresponding project in the range B84:B89
On the sheet X-ref I have the same project names in range T4:Y4 and a corresponding target value in T8:Y8
What I want is the sum (or average) of the marked-projects target-values. The result should end up in KPI!G31.
In other words I want
=sumif(A84:A89;"x";'X-ref'!T4:Y4)
but it doesn't work since the first range is an column-array and the second range is an row-array.
I can't figure out how to work around this. Help appreciated