Absolute reference in excel tables

[ANIL ERDINC TUFEKCI]

Hi,

I have a problem in copy formulas including refence to excel table columns.
In a normal formula I can use $ sign for ablosute reference, but in tables ı
can not use 4 sign. So when i want to copy formulas with absolute reference
of table column it chages to other columns.

How can i solve this problem?

thanks...

 

[Jacob Skaria]

Copy the formula from the formula bar(Right click).
OR
Copy from cell in Edit mode (F2)

If this post helps click Yes

 

[ANIL ERDINC TUFEKCI]

thank you for your help,
but i wonder if i can use $ sign? I want to copy the formula to more cells
by just draging with my mouse. I can do my job by copy and paste but in this
way i have to click on each cell that i want to copy the formula. how cannot
it be possible to use $ sign in reference to table columns like
"table1[column1]". I cannot understand this. did Microsoft missed that
special sign $ for absolute references...

 

[Jacob Skaria]

Below are the different reference styles. In the initial post I thought you
are asking for copying formulas without absolute references...

A1 Relative referencing. Both column and row will change if you copy or drag
the formula.
$A1 The column reference is fixed and will not change
A$1 The row reference is fixed and will not change.
$A$1 Column and row reference are fixed.


If this post helps click Yes
---------------
Jacob Skaria

thank you for your help,
but i wonder if i can use $ sign? I want to copy the formula to more cells
by just draging with my mouse. I can do my job by copy and paste but in this
way i have to click on each cell that i want to copy the formula. how cannot
it be possible to use $ sign in reference to table columns like
"table1[column1]". I cannot understand this. did Microsoft missed that
special sign $ for absolute references...

Copy the formula from the formula bar(Right click).
OR
Copy from cell in Edit mode (F2)

If this post helps click Yes

 

[Shane Devenshire]

Hi,

I presume you are in Excel 2007 so your formulas are something like

=Table4[[#This Row],[Regions]]*3

If you want to use absolute cell references in this case change your
formulas by typing the cell addresses

=$C$4*3

If would help us if you showed us the formulas you are currently using.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

thank you for your help,
but i wonder if i can use $ sign? I want to copy the formula to more cells
by just draging with my mouse. I can do my job by copy and paste but in this
way i have to click on each cell that i want to copy the formula. how cannot
it be possible to use $ sign in reference to table columns like
"table1[column1]". I cannot understand this. did Microsoft missed that
special sign $ for absolute references...

Copy the formula from the formula bar(Right click).
OR
Copy from cell in Edit mode (F2)

If this post helps click Yes

 

[M Thompson]

Hello...I'm not familiar with tables in Excel, but can you use the F4 key to
get your absolute references, or is that also not available like the Shift+4
method?

 

[ANIL ERDINC TUFEKCI]

My formula is;

=If(Table1[column1];"<01.03.2009") like that. when i want to copy it other
cells by draging with my mouse to the right cell,
it turns =If(Table1[column2];"<01.03.2009"). So the column reference chages
from "column1" to "column2".

I know that =If($A$1;"<01.03.2009") there will be no problem because with $
sign, i make it an absolute reference.

I cannot put a dollar sign in references like "Table1[column1]"

The problem is in excel 2007 table references. I also want to use excel
tables in the database that i used for formulas.

If i change the reference to like "A1", no problem, but i don't want to
change reference to a Cell. Because when i'm entering the data to database,
excel tables are heplful.

thank you for everbody...

Hi,

I presume you are in Excel 2007 so your formulas are something like

=Table4[[#This Row],[Regions]]*3

If you want to use absolute cell references in this case change your
formulas by typing the cell addresses

=$C$4*3

If would help us if you showed us the formulas you are currently using.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

thank you for your help,
but i wonder if i can use $ sign? I want to copy the formula to more cells
by just draging with my mouse. I can do my job by copy and paste but in this
way i have to click on each cell that i want to copy the formula. how cannot
it be possible to use $ sign in reference to table columns like
"table1[column1]". I cannot understand this. did Microsoft missed that
special sign $ for absolute references...

Copy the formula from the formula bar(Right click).
OR
Copy from cell in Edit mode (F2)

If this post helps click Yes
---------------
Jacob Skaria


:

Hi,

I have a problem in copy formulas including refence to excel table columns.
In a normal formula I can use $ sign for ablosute reference, but in tables ı
can not use 4 sign. So when i want to copy formulas with absolute reference
of table column it chages to other columns.

How can i solve this problem?

thanks...

 

[Jacob Skaria]

Try

=INDEX(Table1,1,1)

If this post helps click Yes
---------------
Jacob Skaria

My formula is;

=If(Table1[column1];"<01.03.2009") like that. when i want to copy it other
cells by draging with my mouse to the right cell,
it turns =If(Table1[column2];"<01.03.2009"). So the column reference chages
from "column1" to "column2".

I know that =If($A$1;"<01.03.2009") there will be no problem because with $
sign, i make it an absolute reference.

I cannot put a dollar sign in references like "Table1[column1]"

The problem is in excel 2007 table references. I also want to use excel
tables in the database that i used for formulas.

If i change the reference to like "A1", no problem, but i don't want to
change reference to a Cell. Because when i'm entering the data to database,
excel tables are heplful.

thank you for everbody...

Hi,

I presume you are in Excel 2007 so your formulas are something like

=Table4[[#This Row],[Regions]]*3

If you want to use absolute cell references in this case change your
formulas by typing the cell addresses

=$C$4*3

If would help us if you showed us the formulas you are currently using.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

thank you for your help,
but i wonder if i can use $ sign? I want to copy the formula to more cells
by just draging with my mouse. I can do my job by copy and paste but in this
way i have to click on each cell that i want to copy the formula. how cannot
it be possible to use $ sign in reference to table columns like
"table1[column1]". I cannot understand this. did Microsoft missed that
special sign $ for absolute references...

:

Copy the formula from the formula bar(Right click).
OR
Copy from cell in Edit mode (F2)

If this post helps click Yes
---------------
Jacob Skaria


:

Hi,

I have a problem in copy formulas including refence to excel table columns.
In a normal formula I can use $ sign for ablosute reference, but in tables ı
can not use 4 sign. So when i want to copy formulas with absolute reference
of table column it chages to other columns.

How can i solve this problem?

thanks...

 

[ANIL ERDINC TUFEKCI]

For a reference to a single cell in table, "Index" is a good solution.
But i have different sutiation.

My exact formula is;

=COUNTIFS(Table1[Column1];"<01.02.2009";Table1[Column1];">31.12.2008"Table1[Column2];"cars")

Column1 Column2
01.01.2009 cars
02.02.2009 cars
05.01.2009 cars
03.01.2009 apple

My formula turns 2, which means i sold 2 cars in January 2009.

By this formula i try to count how many cars i sold in January 2009. Later i
want to do this formula to other months. Also for many products. What i want
to do is to write the formula for January and by draging it i want to write
formula for other months. I will just change the dates. However, when i drag
the formula to the right cells, column numbers change. So i have to change
column name also. As i cannot solve this problem by using $ sign or other
things i have to copy the formula to each cell by click them one by one.

Try

=INDEX(Table1,1,1)

If this post helps click Yes
---------------
Jacob Skaria

My formula is;

=If(Table1[column1];"<01.03.2009") like that. when i want to copy it other
cells by draging with my mouse to the right cell,
it turns =If(Table1[column2];"<01.03.2009"). So the column reference chages
from "column1" to "column2".

I know that =If($A$1;"<01.03.2009") there will be no problem because with $
sign, i make it an absolute reference.

I cannot put a dollar sign in references like "Table1[column1]"

The problem is in excel 2007 table references. I also want to use excel
tables in the database that i used for formulas.

If i change the reference to like "A1", no problem, but i don't want to
change reference to a Cell. Because when i'm entering the data to database,
excel tables are heplful.

thank you for everbody...

Hi,

I presume you are in Excel 2007 so your formulas are something like

=Table4[[#This Row],[Regions]]*3

If you want to use absolute cell references in this case change your
formulas by typing the cell addresses

=$C$4*3

If would help us if you showed us the formulas you are currently using.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


:

thank you for your help,
but i wonder if i can use $ sign? I want to copy the formula to more cells
by just draging with my mouse. I can do my job by copy and paste but in this
way i have to click on each cell that i want to copy the formula. how cannot
it be possible to use $ sign in reference to table columns like
"table1[column1]". I cannot understand this. did Microsoft missed that
special sign $ for absolute references...

:

Copy the formula from the formula bar(Right click).
OR
Copy from cell in Edit mode (F2)

If this post helps click Yes
---------------
Jacob Skaria


:

Hi,

I have a problem in copy formulas including refence to excel table columns.
In a normal formula I can use $ sign for ablosute reference, but in tables ı
can not use 4 sign. So when i want to copy formulas with absolute reference
of table column it chages to other columns.

How can i solve this problem?

thanks...

 

[Jacob Skaria]

Anil

When you drag the formula; yes the column changes but if you copy the
formula; select all the cells towards right and right click>Paste
special>formulas then the formulas will not change.
OR
Even you copy cell, select cells, Enter also works

Another way is to use the INDIRECT function; which will be an absolute
reference
Table1[Column1]
becomes
INDIRECT("Table1[Column1]")

If this post helps click Yes
---------------
Jacob Skaria

For a reference to a single cell in table, "Index" is a good solution.
But i have different sutiation.

My exact formula is;

=COUNTIFS(Table1[Column1];"<01.02.2009";Table1[Column1];">31.12.2008"Table1[Column2];"cars")

Column1 Column2
01.01.2009 cars
02.02.2009 cars
05.01.2009 cars
03.01.2009 apple

My formula turns 2, which means i sold 2 cars in January 2009.

By this formula i try to count how many cars i sold in January 2009. Later i
want to do this formula to other months. Also for many products. What i want
to do is to write the formula for January and by draging it i want to write
formula for other months. I will just change the dates. However, when i drag
the formula to the right cells, column numbers change. So i have to change
column name also. As i cannot solve this problem by using $ sign or other
things i have to copy the formula to each cell by click them one by one.

Try

=INDEX(Table1,1,1)

If this post helps click Yes
---------------
Jacob Skaria

My formula is;

=If(Table1[column1];"<01.03.2009") like that. when i want to copy it other
cells by draging with my mouse to the right cell,
it turns =If(Table1[column2];"<01.03.2009"). So the column reference chages
from "column1" to "column2".

I know that =If($A$1;"<01.03.2009") there will be no problem because with $
sign, i make it an absolute reference.

I cannot put a dollar sign in references like "Table1[column1]"

The problem is in excel 2007 table references. I also want to use excel
tables in the database that i used for formulas.

If i change the reference to like "A1", no problem, but i don't want to
change reference to a Cell. Because when i'm entering the data to database,
excel tables are heplful.

thank you for everbody...




:

Hi,

I presume you are in Excel 2007 so your formulas are something like

=Table4[[#This Row],[Regions]]*3

If you want to use absolute cell references in this case change your
formulas by typing the cell addresses

=$C$4*3

If would help us if you showed us the formulas you are currently using.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


:

thank you for your help,
but i wonder if i can use $ sign? I want to copy the formula to more cells
by just draging with my mouse. I can do my job by copy and paste but in this
way i have to click on each cell that i want to copy the formula. how cannot
it be possible to use $ sign in reference to table columns like
"table1[column1]". I cannot understand this. did Microsoft missed that
special sign $ for absolute references...

:

Copy the formula from the formula bar(Right click).
OR
Copy from cell in Edit mode (F2)

If this post helps click Yes
---------------
Jacob Skaria


:

Hi,

I have a problem in copy formulas including refence to excel table columns.
In a normal formula I can use $ sign for ablosute reference, but in tables ı
can not use 4 sign. So when i want to copy formulas with absolute reference
of table column it chages to other columns.

How can i solve this problem?

thanks...

 

[ANIL ERDINC TUFEKCI]

Thank you Jacob,

you're right if i select the cells that i want to copy the formula, it is
almost the same effort as to drag the cell with the mouse.

thank you very much...

Anil

When you drag the formula; yes the column changes but if you copy the
formula; select all the cells towards right and right click>Paste
special>formulas then the formulas will not change.
OR
Even you copy cell, select cells, Enter also works

Another way is to use the INDIRECT function; which will be an absolute
reference
Table1[Column1]
becomes
INDIRECT("Table1[Column1]")

If this post helps click Yes
---------------
Jacob Skaria

For a reference to a single cell in table, "Index" is a good solution.
But i have different sutiation.

My exact formula is;

=COUNTIFS(Table1[Column1];"<01.02.2009";Table1[Column1];">31.12.2008"Table1[Column2];"cars")

Column1 Column2
01.01.2009 cars
02.02.2009 cars
05.01.2009 cars
03.01.2009 apple

My formula turns 2, which means i sold 2 cars in January 2009.

By this formula i try to count how many cars i sold in January 2009. Later i
want to do this formula to other months. Also for many products. What i want
to do is to write the formula for January and by draging it i want to write
formula for other months. I will just change the dates. However, when i drag
the formula to the right cells, column numbers change. So i have to change
column name also. As i cannot solve this problem by using $ sign or other
things i have to copy the formula to each cell by click them one by one.

Try

=INDEX(Table1,1,1)

If this post helps click Yes
---------------
Jacob Skaria


:

My formula is;

=If(Table1[column1];"<01.03.2009") like that. when i want to copy it other
cells by draging with my mouse to the right cell,
it turns =If(Table1[column2];"<01.03.2009"). So the column reference chages
from "column1" to "column2".

I know that =If($A$1;"<01.03.2009") there will be no problem because with $
sign, i make it an absolute reference.

I cannot put a dollar sign in references like "Table1[column1]"

The problem is in excel 2007 table references. I also want to use excel
tables in the database that i used for formulas.

If i change the reference to like "A1", no problem, but i don't want to
change reference to a Cell. Because when i'm entering the data to database,
excel tables are heplful.

thank you for everbody...




:

Hi,

I presume you are in Excel 2007 so your formulas are something like

=Table4[[#This Row],[Regions]]*3

If you want to use absolute cell references in this case change your
formulas by typing the cell addresses

=$C$4*3

If would help us if you showed us the formulas you are currently using.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


:

thank you for your help,
but i wonder if i can use $ sign? I want to copy the formula to more cells
by just draging with my mouse. I can do my job by copy and paste but in this
way i have to click on each cell that i want to copy the formula. how cannot
it be possible to use $ sign in reference to table columns like
"table1[column1]". I cannot understand this. did Microsoft missed that
special sign $ for absolute references...

:

Copy the formula from the formula bar(Right click).
OR
Copy from cell in Edit mode (F2)

If this post helps click Yes
---------------
Jacob Skaria


:

Hi,

I have a problem in copy formulas including refence to excel table columns.
In a normal formula I can use $ sign for ablosute reference, but in tables ı
can not use 4 sign. So when i want to copy formulas with absolute reference
of table column it chages to other columns.

How can i solve this problem?

thanks...