# Posts by jindon

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!

## Files

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

## Files

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

## Files

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

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

then scroll dwon a bit and you will find

Attach Files

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!

## Files

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

Using working column:
If the data is in column A
B1:
=--LEFT(A1,1)
fildown to the last row of col. A

then sort by column b as first criteria and a for second.

Hope this does good for you.

=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.