Posts by Tom.Jones

Quote from peteacher

Thanks Tom,

Please see below:

[TABLE="border: 1, cellpadding: 0, cellspacing: 0"]

[tr]

[td]

A

[/td]

[td]

B

[/td]

[/tr]

[tr]

[td]

0:00:00.001

[/td]

[td]

6

[/td]

[/tr]

[tr]

[td]

0:00:00.100

[/td]

[td]

7

[/td]

[/tr]

[tr]

[td]

0:00:00.000

[/td]

[td]

0

[/td]

[/tr]

[tr]

[td]

0:00:00.000

[/td]

[td]

0

[/td]

[/tr]

[tr]

[td]

0:00:00.000

[/td]

[td]

0

[/td]

[/tr]

[tr]

[td]

0:00:00.000

[/td]

[td]

0

[/td]

[/tr]

[tr]

[td]

0:00:00.000

[/td]

[td]

0

[/td]

[/tr]

[/TABLE]

I was expecting B1 to be 1 and B2 equal to 2 but the five 0s have been factored into the ranking order.

Is there something that needs to be added to the forula to avoid this?

Display More

From where you post that data???

Maybe I don't understand, but:

=IF(D4=0,0,IF(D4<>0,RANK(D4,$D$4:$AY$4,0)))

it is all you need.

and if you don't want zero, then:

=IF(D4=0,"",IF(D4<>0,RANK(D4,$D$4:$AY$4,0)))

Re: Summarize data on multiple Excel sheets

Upload a workbook, with insensitive data and tell us, this is what I have and I expect like so, and maybe we came up with some formulas...

Re: Trying to insert a formula into a cell - Throwing error

Cross post

You got the answer here: https://www.mrexcel.com/forum/…-cell-throwing-error.html
Even if you registered with another name ...

Re: How To Convert Time String To Time In Excel?

For string like this:
13.15.00
22.00.20
17.11.15
your formula don't work.

you can use:
=--SUBSTITUTE(F20;".";":")

format cells as Time

Re: Excel generator

Cross-post at this link:
http://www.thecodecage.com/forumz/showthread.php?t=215105
http://www.vbaexpress.com/foru…with-this-excel-generator
http://www.eileenslounge.com/viewtopic.php?f=27&t=26299

Re: Time and data calculations

Try
in G1 =(C1-B1)+(F1-E1)

Re: VBA Codes, getting an error message &quot; Ambiguous name detected&quot;:Worksheep Change

Because you have two codes Worksheet_Change and Excel is confused. Remove one of them or combine them.

Re: Adding a Data Link Range Cell From one Sheet to another Sheet with Complex Formul

See if this is what you want.
In attach.

Re: Counting up numbers in VBA

If this is how numbers are positioned
[TABLE="width: 576"]

[TD="class: xl63, width: 64, align: right"]3
[/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64, align: right"]2
[/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64, align: right"]1
[/TD]
[TD="class: xl63, width: 64, align: right"]2
[/TD]

[/TABLE]

So, in this case, the code should turn the row above into:

[TABLE="width: 704"]

[TD="class: xl65, width: 64, align: right"]3
[/TD]
[TD="class: xl65, width: 64, align: right"]1
[/TD]
[TD="class: xl65, width: 64, align: right"]2
[/TD]
[TD="class: xl65, width: 64, align: right"]3
[/TD]
[TD="class: xl65, width: 64, align: right"]2
[/TD]
[TD="class: xl65, width: 64, align: right"]1
[/TD]
[TD="class: xl65, width: 64, align: right"]2
[/TD]
[TD="class: xl65, width: 64, align: right"]1
[/TD]
[TD="class: xl65, width: 64, align: right"]2
[/TD]
[TD="class: xl65, width: 64, align: right"]1
[/TD]
[TD="class: xl65, width: 64, align: right"]2
[/TD]

[/TABLE]

Then none of VBA code give correct answer.

BaraaKhalil

3 1 2 3 2 1 2 1 2 1 2 3 This 3 in last position should not be here

StephenR and S O have this answer

3 1 2 3 2 1 2 1 2 and here is missing 1 and 2 in last position.

Re: Deleting preceding Zero

Quote from raajnabriz;779064

Thanks All,

i used this Excel formula: =RIGHT(a1, LEN(a1)-1) and got the required answer.

Thanks anyway for all your efforts.

Raaj.

Display More

that formula will give you wrong answer (00000001 ...)
Use this small modification of your formula:
=--RIGHT(a1, LEN(a1)-1)

And if is a comma after string only this formula work
=--RIGHT(a1, LEN(a1)-1) and
S O formula
=INT(RIGHT(A1,LEN(A1)-1))

Re: Deleting preceding Zero

That strings are in one cell or in separate cells?

Re: Macro to Automate Transpose Data Set

cross posting w/o links:
http://www.excelguru.ca/forums/showt...ed=1#post27162
http://answers.microsoft.com/en-us/o...c1a6ba8?auth=1
http://www.mrexcel.com/forum/excel-q...pose-list.html
http://www.ozgrid.com/forum/showthread.php?t=200951

Re: INDIRECT Sum across multiple sheets alternative

Nice. I was looking for that piece of code.

Thank you skywriter

Re: simply adding cells for a inventory count

crosspost: http://www.mrexcel.com/forum/excel-q...ml#post4506672
http://www.excelforum.com/showthread.php?t=1137277

Re: pick up sheet name from cell in formula

Look to modiffication formula in attach.
In D2 (also E2, F2 and G2) I change your formula to =DATE(YEAR($B$1),1,1) and modiffy to the right =DATE(YEAR($B$1),2,1)
then modiffy D3 (also E3, F3 and G3) formula with =INDEX(INDIRECT("'"&$A3&"'!$C$9:$Q$9"),MATCH(D$2,INDIRECT("'"&$A3&"'!$C$7:$Q$7"),0))

Hope it helps.

Re: File and Folder Index with Hyperlinks

Hi,

Get error to GetShortFileName

where is GetShortFileName?

Ok I see where is Function GetShortPathName but the list of file is not good.

Re: List sheets name

Alan,

Excellent. It works perfectly.
Thank you very much.

Re: List sheets name

Alan,

Thank you very much for VBA code. I do not realize how I can modify the code so I like:

Sheet1

If you have time and want to change the code would be excellent. Thank you very much anyway.

Re: List sheets name

Believe me I searched 5 days, but probably did not put the right questions or perhaps not so smart (I am 78 years old).
I do not understand why all malice. I saw thousands of responses to the requests of OP without being asked anything. As I said just I can change some easy codes but can not create VBA code. The code in this link (https://www.reddit.com/r/excel/comme..._and_list_all/) lists the files and leaves in 2 columns. I would have wanted the name of file to be in row 1 and sheet names to be under filenames.
I do not know how to change that code. If you help me is OK, if not, I will not bother you with my questions, which were not many anyway.
I wish you health and more understanding.