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



## coocoo (Feb 9, 2010)

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 (Feb 9, 2010)

=A1+B1+N("AddsA1toB1")

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


----------



## coocoo (Feb 9, 2010)

Hi VoG,

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


----------



## roscoe (Feb 9, 2010)

disregard


----------



## ZVI (Feb 9, 2010)

coocoo said:


> ...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 (Feb 9, 2010)

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 (Feb 10, 2010)

So combining the two suggestions we get:

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


----------



## yytsunamiyy (Feb 11, 2010)

ZVI said:


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


 
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 WorkbookA2316ProgrammübersichtCell FormulasRangeFormulaA23=C10+C11+N("MyComment:AddsA1toB1")


----------



## Expiry (Feb 11, 2010)

coocoo said:


> I'm wondering if anyone can share a neat and Usefull excel tricks!!
> 
> It can be something easy or advance. Anything would be great!
> 
> ...




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


----------



## ZVI (Feb 11, 2010)

yytsunamiyy said:


> 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


----------



## yytsunamiyy (Feb 11, 2010)

ZVI said:


> N(“AnyText”) is used just as a formula comment and does not change the sum as equally to zero


 
OK. I got the point now. Thanks for clarifying that. I was led astray by VoG's 





> ...but not the result.


----------



## arkusM (Feb 11, 2010)

Expiry said:


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


 

Ahhha, I have been try to remember that one (yes too lazy to look it up, not a high priority LOL)

I like:
CTRL+D ~ duplicates the cell(s) above formula and all
ALT + Dwn Arrow ~ drop down of previously entered values for the column (in continuous data, mostly seems quirk sometimes). Behaves like a ComboBox

EDIT: This seems almost like the favorite Short-Cuts thread....


----------

