# Posts by Tom.Jones

• ## Rank function ignoring only zero values

From where you post that data???

• ## Rank function ignoring only zero values

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)))

• ## Summarize data on multiple Excel sheets

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

• ## How To Convert Time String To Time In Excel?

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

For string like this:
13.15.00
22.00.20
17.11.15

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

format cells as Time

• ## Excel generator

Re: Excel generator

• ## Time and data calculations

Re: Time and data calculations

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

• ## VBA Error "Ambiguous name detected" on Worksheet_Change event

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.

• ## Adding a Data Link Range Cell From one Sheet to another Sheet with Complex Formula.

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.

• ## Counting up numbers in VBA

Re: Counting up numbers in VBA

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

[tr]

[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]

[/tr]

[/TABLE]

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

[TABLE="width: 704"]

[tr]

[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]

[/tr]

[/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.

• ## Deleting preceding Zero

Re: Deleting preceding Zero

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))

• ## Deleting preceding Zero

Re: Deleting preceding Zero

That strings are in one cell or in separate cells?

• ## Macro to Automate Transpose Data Set

Re: Macro to Automate Transpose Data Set

• ## INDIRECT Sum across multiple sheets alternative

Re: INDIRECT Sum across multiple sheets alternative

Nice. I was looking for that piece of code.

Thank you skywriter

• ## simply adding cells for a inventory count

Re: simply adding cells for a inventory count

• ## pick up sheet name from cell in formula

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.

• ## File and Folder Index with Hyperlinks

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.

• ## List sheets name

Re: List sheets name

Alan,

Excellent. It works perfectly.
Thank you very much.

• ## List sheets name

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

 abc.xlsx aaa.xlsm month date.xlsx calendar.xls … … … Sheet1 Sheet1 Jan Sheet2 BD Feb Sheet3 value Mar Sheet4 Apr Sheet5 May June

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

• ## List sheets name

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.