Auto Fill

[Bee]

I am trying to copy a cell formula (see formula below, which is a formula
linking the value of a cell in another workbook) using Autofill but not
having much success. The cell formula is:-

='[2nd QTR Price List - Source Data.xls]A227 Grille Products'!$E$170

When I use the drag & fill cross it only copies the formula but does not
automatically change the cell reference i.e. the $E$170 should change to
$E$171.

Any help on this would be much appreciated. Thank you!

 

[JE McGimpsey]

See "The difference between relative and absolute references" in the
"About cell and range references" topic in XL Help.

For instance, you can change

...!$E$170

to

...!$E170

 

[Bee]

I tried editing my formula and now it will change the cell reference
automatically using autofill. However, I need the formula to transpose from
columns in my source data to rows in my destination data (which I can do by
copying and transposing - but it is probably quicker to just change the cell
reference manually!)

Thank you for your help!

See "The difference between relative and absolute references" in the
"About cell and range references" topic in XL Help.

For instance, you can change

...!$E$170

to

...!$E170

I am trying to copy a cell formula (see formula below, which is a formula
linking the value of a cell in another workbook) using Autofill but not
having much success. The cell formula is:-

='[2nd QTR Price List - Source Data.xls]A227 Grille Products'!$E$170

When I use the drag & fill cross it only copies the formula but does not
automatically change the cell reference i.e. the $E$170 should change to
$E$171.

Any help on this would be much appreciated. Thank you!

 

[Gord Dibben]

Bee

Row references won't change as you drag across, only if you drag down.

Either one of these should work.

=INDIRECT("Sheet2!A"&COLUMN(A1))

=OFFSET(Sheet2!$A$1,COLUMN(A1)-1,)

Or this one which is not volatile.

=INDEX($A:$A,(ROWS($1:1)-1)*1+COLUMNS($A:B)-1)

Adjust workbook and sheet path to suit.


Gord Dibben MS Excel MVP

I tried editing my formula and now it will change the cell reference
automatically using autofill. However, I need the formula to transpose from
columns in my source data to rows in my destination data (which I can do by
copying and transposing - but it is probably quicker to just change the cell
reference manually!)

Thank you for your help!

See "The difference between relative and absolute references" in the
"About cell and range references" topic in XL Help.

For instance, you can change

...!$E$170

to

...!$E170

I am trying to copy a cell formula (see formula below, which is a formula
linking the value of a cell in another workbook) using Autofill but not
having much success. The cell formula is:-

='[2nd QTR Price List - Source Data.xls]A227 Grille Products'!$E$170

When I use the drag & fill cross it only copies the formula but does not
automatically change the cell reference i.e. the $E$170 should change to
$E$171.

Any help on this would be much appreciated. Thank you!

 

[Bee]

Thank you very much - it is now working with the new formula!!!

Cheers,
Bee

Bee

Row references won't change as you drag across, only if you drag down.

Either one of these should work.

=INDIRECT("Sheet2!A"&COLUMN(A1))

=OFFSET(Sheet2!$A$1,COLUMN(A1)-1,)

Or this one which is not volatile.

=INDEX($A:$A,(ROWS($1:1)-1)*1+COLUMNS($A:B)-1)

Adjust workbook and sheet path to suit.


Gord Dibben MS Excel MVP

I tried editing my formula and now it will change the cell reference
automatically using autofill. However, I need the formula to transpose from
columns in my source data to rows in my destination data (which I can do by
copying and transposing - but it is probably quicker to just change the cell
reference manually!)

Thank you for your help!

See "The difference between relative and absolute references" in the
"About cell and range references" topic in XL Help.

For instance, you can change

...!$E$170

to

...!$E170

I am trying to copy a cell formula (see formula below, which is a formula
linking the value of a cell in another workbook) using Autofill but not
having much success. The cell formula is:-

='[2nd QTR Price List - Source Data.xls]A227 Grille Products'!$E$170

When I use the drag & fill cross it only copies the formula but does not
automatically change the cell reference i.e. the $E$170 should change to
$E$171.

Any help on this would be much appreciated. Thank you!

 

[Gord Dibben]

Good to hear.


Gord

Thank you very much - it is now working with the new formula!!!

Cheers,
Bee

Bee

Row references won't change as you drag across, only if you drag down.

Either one of these should work.

=INDIRECT("Sheet2!A"&COLUMN(A1))

=OFFSET(Sheet2!$A$1,COLUMN(A1)-1,)

Or this one which is not volatile.

=INDEX($A:$A,(ROWS($1:1)-1)*1+COLUMNS($A:B)-1)

Adjust workbook and sheet path to suit.


Gord Dibben MS Excel MVP

I tried editing my formula and now it will change the cell reference
automatically using autofill. However, I need the formula to transpose from
columns in my source data to rows in my destination data (which I can do by
copying and transposing - but it is probably quicker to just change the cell
reference manually!)

Thank you for your help!

:

See "The difference between relative and absolute references" in the
"About cell and range references" topic in XL Help.

For instance, you can change

...!$E$170

to

...!$E170

I am trying to copy a cell formula (see formula below, which is a formula
linking the value of a cell in another workbook) using Autofill but not
having much success. The cell formula is:-

='[2nd QTR Price List - Source Data.xls]A227 Grille Products'!$E$170

When I use the drag & fill cross it only copies the formula but does not
automatically change the cell reference i.e. the $E$170 should change to
$E$171.

Any help on this would be much appreciated. Thank you!