My Contribution for This Week Tip is Below :
Understanding Relative and Absolute References...
Excel accepts cell references in what are called absolute and relative
ranges. Absolute ranges have a $ character before the column portion
of the reference and/or the row portion of the reference. Relative
ranges do not use the $ character. The $ character indicates to Excel
that it should not increment the column and/or row reference as you
fill a range with a formula or as you copy a range. For example A1 is
a relative range, while $A$1 is an absolute range. If you enter =A1 in
a cell and then fill that cell down a column, the '1' in the reference
will increment in each row. Thus, the formula in row 50 would be =A50.
However, if you enter =$A$1 in a cell and fill down, the range
reference will remain $A$1 -- it will not increment as you fill or
copy down a column.
There are three absolute styles:
Reference Style
Meaning
$A$1
Both the column and row reference are fixed. Neither will be
incremented or changed during a copy or fill operation.
$A1
Only the column reference is fixed. It will not change during a fill
or copy, but the row will change.
A$1
Only the row reference is fixed. It will not change during a fill or
copy, but the column will change.
If you select all or part of a formula in the formula, *you can press
F4 to cycle range reference* between the 4 styles (1 relative and 3
absolute).
Even with an absolute referencing style, Excel will still change row
and column references when you insert a row or column. To have a truly
absolute cell reference that will not change under any circumstances,
use the INDIRECT function. For example =INDIRECT("A1") will always
refer to cell A1, regardless of any changes made to the worksheet.
This works because Excel does not interpret the string "A1" as an
address. Instead, it treats it as plain text and therefore does not
change it.
A common use of mixing absolute and relative range specifications is
to create a running total of a column of number. For example, if you
have data in cells A1:A10, the formula =SUM(A$1:A1) in cell B1 and
filled down to cell B10 will return the running total for the numbers
in column A.
Hopes This Clears All Doubts for Absolute and Relative References.....
On Jun 30, 8:54 pm, Ayush Jain <jain_ayus...@yahoo.com> wrote:
> Dear Members,
>
> Thanks for your great response in the Week 26 competition. It was
> again great learning for the members of the group.
> Let us begin week # 27 quiz with the more spirit. This is last chance
> for you to be eligible for June month competition.The winner of week #
> 26 will be declared soon. Keep watching this Space......
>
> Last Date of Submission of tips : 5th July 2009.
>
> PLEASE NOTE THAT THE TIPS SUBMITTED IN THIS POST WILL ONLY BE
> CONSIDERED FOR PRIZES.
> DO NOT START NEW THREADS OR POST FOR SUBMISSION OF TIPS.
>
> You need to submit your name, Subject & the details of tips and
> tricks.
> DO let me know if you need any clarifications.
>
> Let me begin by submitting the first tip for this week competition.
> ---------------------------------------------------------------------------------------------------------------------------------------------------
> Name : Ayush Jain
> Subject : Skip through your worksheets with a shortcut
> Detail : When I'm working on many programs at once (yes, of course I'm
> a multitasker — what did you expect?), I use the keyboard shortcut ALT
> +TAB to move between open programs. (If you haven't tried this, do so
> now — it's a great Microsoft Windows® trick.)
>
> However, this doesn't work when you're moving between worksheets in
> the same workbook. Don't despair; there are a couple of neat little
> shortcuts that handle the job quite nicely.
>
> To move one worksheet to the right
>
> Press CTRL+PAGE DOWN.
>
> To move one worksheet to the left
>
> Press CTRL+PAGE UP.
>
> Now you can skip through those worksheets with lightning speed and
> amaze your friends and coworkers (if they're the type to be amazed by
> that sort of thing, of course).
>
> Who is next ????????????????????
> -----------------------------------------------------------------------------------------------------------------------------------------------------
--~--~---------~--~----~------------~-------~--~----~
-------------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
