Usefull Excel Trick!!! Easy to Advance!! Keep this going!!

[coocoo]

I'm wondering if anyone can share a neat and Usefull excel tricks!!

It can be something easy or advance. Anything would be great!

Here's mine:
To copy just the subtotals into another spreadsheet (instead of selecting one ea. row/cell one by one)

Select the are you'd like to copy - F5, special - visible cell only - copy. Then paste in the diff area/spreadsheet. You will only copy the visible area only.

Any other trick/shortcut would be greatly appreciated.

 

[VoG]

=A1+B1+N("AddsA1toB1")

Displays what the formula does in the formula bar but not the result.

 

[coocoo]

Hi VoG,

When i try that it gives me this result #name?. Any idea why

 

[roscoe]

disregard

 

[ZVI]

...Any idea why

Seems that you've used non English version of Excel.
This macro sets the required formula irregardless of Excel localization:

Sub Test()
  [A1].Value = 1
  [B1].Value = 2
  [C1].Formula = "=A1+B1+N(""MyComment:AddsA1toB1"")"
End Sub

 

[Legacy 139926]

Quick and easy way of converting a 7 digit Julian date (i.e. YYYYDDD. 31 Jan 2010 would read 2010031) back to a normal date is using the DATE function. So:

 =DATE(LEFT(A1,4),1,RIGHT(A1,3))

Assuming Cell A1 contains the julian date.

This happily replaced the following:

Format(CDate(Format(((DateValue("01/01/"+LTrim(Str(1900+Int([dob]/1000))))+[dob]-Int([dob]/1000)*1000))-1,"mm-dd-yy")),"dd/mm/yyyy")

-------------------------
damir

 

[al_b_cnu]

So combining the two suggestions we get:

=DATE(LEFT(A1,4),1,RIGHT(A1,3))+N("convert Julian date")

 

[yytsunamiyy]

Seems that you've used non English version of Excel.
This macro sets the required formula irregardless of Excel localization:

Sub Test()
[A1].Value = 1
[B1].Value = 2
[C1].Formula = "=A1+B1+N(""MyComment:AddsA1toB1"")"
End Sub

This doesn't work for my German XL 07 version on Win XP. I see the result in the cell and the formula in the formula bar.

[A23].Formula = "=C10+C11+N(""MyComment:AddsA1toB1"")"

Excel Workbook
	A
23	16
Programmübersicht

Cell Formulas
Range	Formula
A23	=C10+C11+N("MyComment:AddsA1toB1")

 

[Expiry]

I'm wondering if anyone can share a neat and Usefull excel tricks!!

It can be something easy or advance. Anything would be great!

Here's mine:
To copy just the subtotals into another spreadsheet (instead of selecting one ea. row/cell one by one)

Select the are you'd like to copy - F5, special - visible cell only - copy. Then paste in the diff area/spreadsheet. You will only copy the visible area only.

Any other trick/shortcut would be greatly appreciated.

Pressing Alt + ; will select visible cells only. Saves you having to press F5, click special etc.

 

[ZVI]

This doesn't work for my German XL 07 version on Win XP. I see the result in the cell and the formula in the formula bar.

It’s correct if C10 + C11 = 16.
N(“AnyText”) is used just as a formula comment and does not change the sum as equally to zero