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

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


"ANIL ERDINC TUFEKCI" wrote:

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

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" wrote:

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


"ANIL ERDINC TUFEKCI" wrote:

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

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


"ANIL ERDINC TUFEKCI" wrote:

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" wrote:

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


"ANIL ERDINC TUFEKCI" wrote:

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

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


"ANIL ERDINC TUFEKCI" wrote:

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" wrote:

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


"ANIL ERDINC TUFEKCI" wrote:

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

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?
--
OneFineDay


"ANIL ERDINC TUFEKCI" wrote:

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

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




"Shane Devenshire" wrote:

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


"ANIL ERDINC TUFEKCI" wrote:

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" wrote:

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


"ANIL ERDINC TUFEKCI" wrote:

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

Try

=INDEX(Table1,1,1)

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


"ANIL ERDINC TUFEKCI" wrote:

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




"Shane Devenshire" wrote:

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


"ANIL ERDINC TUFEKCI" wrote:

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" wrote:

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


"ANIL ERDINC TUFEKCI" wrote:

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

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.




"Jacob Skaria" wrote:

Try

=INDEX(Table1,1,1)

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


"ANIL ERDINC TUFEKCI" wrote:

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




"Shane Devenshire" wrote:

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


"ANIL ERDINC TUFEKCI" wrote:

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" wrote:

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


"ANIL ERDINC TUFEKCI" wrote:

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

When you drag the formula; yes the column changes but if you copy the
formula; select all the cells towards right and right clickPaste
specialformulas 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


"ANIL ERDINC TUFEKCI" wrote:

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.




"Jacob Skaria" wrote:

Try

=INDEX(Table1,1,1)

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


"ANIL ERDINC TUFEKCI" wrote:

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




"Shane Devenshire" wrote:

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


"ANIL ERDINC TUFEKCI" wrote:

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" wrote:

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


"ANIL ERDINC TUFEKCI" wrote:

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

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

"Jacob Skaria" wrote:

Anil

When you drag the formula; yes the column changes but if you copy the
formula; select all the cells towards right and right clickPaste
specialformulas 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


"ANIL ERDINC TUFEKCI" wrote:

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.




"Jacob Skaria" wrote:

Try

=INDEX(Table1,1,1)

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


"ANIL ERDINC TUFEKCI" wrote:

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




"Shane Devenshire" wrote:

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


"ANIL ERDINC TUFEKCI" wrote:

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" wrote:

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


"ANIL ERDINC TUFEKCI" wrote:

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