take a look at col.G (utilized as working col.)
Original text is like 1.234,34
so, change to 1,234.34
=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F3,",","/"),".",","),"/",".")
Cheers!
take a look at col.G (utilized as working col.)
Original text is like 1.234,34
so, change to 1,234.34
=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F3,",","/"),".",","),"/",".")
Cheers!
Hi,
maybe CHOOSE function does for the serial date
e.g.
=CHOOSE(WEEKDAY(TODAY()),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")
or
=LOOKUP(WEEKDAY(TODAY()),{1,2,3,4,5,6,7;"Sun","Mon","Tue","Wed","Thu","Fri","Sat"})
??
Hi,
Insert one column anywhere you like, say col.C, and put
in cell C6
=IF(MOD(ROW(A1),4)=1,B6-B5,"")
then drag down as you want
Second problem
In Cell B5
=IF(AND(B5<>"",B6<>""),B6,"")
In cell B6
=IF(AND(B5<>"",B6<>""),B6-B5,"")
then drag down both cells
hope this works
Hi,
Here's how it works.
Formula in col.C on Totals Sheet is to find the rank among "EVAL TOTALS", col.B
and I thought this would be the original ranking.
=IF(B2<>"",RANK(B2,B:B),"")
One problem!
Function RANK will rank the same if the test value is the same.
When it happens, function SMALL will always return the first comer only.
To avoid this problem, I always use another working column which adds or subtracts ROW(A1)/100 to or from the test value.
It really avoids this problem. (The attached has been modified already, ref col.G and formula in co.C is now =IF(B2<>"",RANK(G2,G:G),"") )
Formula in col.L on sheet Shift Input
=IF(ISERROR(MATCH(C10,TOTALS!A:A,0)),"",INDEX(TOTALS!A:C,MATCH(C10,TOTALS!A:A,0),3))
It finds the original ranking of the value in col.C from Totals sheet
Formula in Col.F on sheet Shit Input
=IF(C10="","",INDEX(TOTALS!A:C,MATCH(SMALL(L:L,ROW(A1)),TOTALS!C:C,0),1))
It finds the first smallest value in col.L for the first row and find ‚ƒ‚‚’‚’‚…‚“‚‚‚Ž‚„‚‰‚Žg value in col.A on Totals sheet and second smallest for the second row and so forth.
Try the attached and compare with the other one
In cell M1
=IF(ROW(A1)<=COUNT($A$1:$A$1000),INDIRECT("A"&SMALL(IF($A$1:$A$1000<>0,ROW($A$1:$A$1000)),ROW(A1))),"")
holding down Ctrl+Shift and Enter to get out from the formula bar
then drag down to row 1000th
I'm not sure if this is what you want.
Or,
VBA, try attached.
with Sheet1
When value of last row of col.A changes, the value appear in the last row of col.M.
Cell N1 has bbeen used as working cell.
hope it works
Using Index+Match
Table must in descending order
i.e. B1=30, C1=20, D1=10
=INDEX(B1:D1,1,MATCH(A1,B1:D1,-1))
returns larger value than the test value in the list
There you go.
Hi, Tanis
When A1 changes, same value should be posted to the first available cell in col.M.
Then A2 changes, same value to the next cell in col. M
Is it possible A1 changes again?
What would you like to do when it happens?
Click on
Go Advanced
then scroll dwon a bit and you will find
Attach Files
can you upload the file?
Other solution
Select the range
Choose Edit - Copy
Holding down Shift key and choose Edit - Paste Picture Link
Then you can move the linked picture on the top row and freeze pane.
Jindon
Hi,
Check the attached.
Utilized col.E as working column and you can hide or make it invisible by changing font color to white
Cheers!
Hi,
First of all, at this end, it doesn't sort like you desire even I change the format from number to text. The only way I can get sorted that way is what I mentioned above and it doesn't matter if the values in column A are formatted as number or text.
Anyway, if you want convert numbers in column A to text in column B is as follow
1) Copy columnA and select columnB then pastespecial and check Values then OK
2) Right click and select cell format then change from General to Text
Alternatively, the given formula in cell B1
=TEXT(A1,"@")
changes value to Text from numbers
If you put =ISTEXT(B1) returns TRUE means Text already.
I hope you can get what you wish,
Jindon
Hi,
you probably need
If MsgBox("Are you really sure?", vbOKCancel) <> vbOK Then Exit Sub
"state action you desire here"
Good night
In cell A40 on sheet output
=SUM(IF(ISNUMBER(A33:A38)=TRUE,A33:A38))
then, holding down Ctrl+Shift and press Enter to get out of formula bar
It will give the formula bracket { } arutomatically.
or simply
=SUMIF(A33:A38,">0",A33:A38)
hope it works
There you go!
=IF(D45<=Validation!$K$75;"OPEN";"CLOSED")
should be like
=IF(D45<=Validation!$K$75,"OPEN","CLOSED")
and would you like to tell me what's in A2?
Jindon
Does this work?
=IF(AND(E41<=$K$74,OR(A2="Fr.",A2="Sa",A2="Su")),"","closed")
Jindon
oooops, it's already there.