1="a"))-1,0)1="a"))-1,0)='[RDD Register.xls]Oban'!G59
Is the formula I'm using. I'm trying to link to data in another book. But,
when I copy my formula down the column it goes out of sync, because I have
added additional rows.
When I created the document, In column A:A I linked to cells in another book
using ='[RDD Register.xls]Oban'!A01 etc.
Then I added rows below each referenced row and added more data, as per the
diagram.
Now I want to import other columns from the source document using ='[RDD
Register.xls]Oban'!G1 etc. and make them fit beside the previously
referenced cells. It know it sounds complicated but it isn't. I have
A B C D
001 00 Elevations 6-8-06
001 01 Windows in wrong location 6-9-06
001 02 Lintols missing 6-10-06
002 00 Plans 7-8-06
etc.
The items in column A are irregular, in our example 001 has three items, 002
may have 2 items, 003 may have 5 items, 004 may have ten items.
I want my referenced cells to be input where the rows B = "00", otherwise
the numbering goes out of sync and it repeats the copied cells.
Regards
Dylan
Ah see...
OK here is a try. Let us say that you filtered on column C:C being
greater than 5, i.e. your condition is (C1>5)
Then, in your formula you can use the following expression in lieu of,
say, B7:
OFFSET(Sheet1!B7,SUMPRODUCT(--($C$1:C1>5))-1,0)
If you have filtered by more than one conditions (e.g. C1>5 and D1="a")
you can use:
OFFSET(Sheet1!B7,SUMPRODUCT(($C$1:C1>5)*($D$1
Without knowing the formula you are using I cannot be more specific.
Post if you need more help.
HTH
Kostis
='[RDD Register.xls]Oban'!G59
Is the formula I'm using. I'm trying to link to data in another book. But,
when I copy my formula down the column it goes out of sync, because I have
added additional rows.
When I created the document, In column A:A I linked to cells in another
book
using ='[RDD Register.xls]Oban'!A01 etc.
Then I added rows below each referenced row and added more data, as per
the
diagram.
Now I want to import other columns from the source document using ='[RDD
Register.xls]Oban'!G1 etc. and make them fit beside the previously
referenced cells. It know it sounds complicated but it isn't. I have
A B C D
001 00 Elevations 6-8-06
001 01 Windows in wrong location 6-9-06
001 02 Lintols missing 6-10-06
002 00 Plans
7-8-06
etc.
The items in column A are irregular, in our example 001 has three items,
002
may have 2 items, 003 may have 5 items, 004 may have ten items.
I want my referenced cells to be input where the rows B = "00", otherwise
the numbering goes out of sync and it repeats the copied cells.
Regards
Dylan
Ah see...
OK here is a try. Let us say that you filtered on column C:C being
greater than 5, i.e. your condition is (C1>5)
Then, in your formula you can use the following expression in lieu of,
say, B7:
OFFSET(Sheet1!B7,SUMPRODUCT(--($C$1:C1>5))-1,0)
If you have filtered by more than one conditions (e.g. C1>5 and D1="a")
you can use:
OFFSET(Sheet1!B7,SUMPRODUCT(($C$1:C1>5)*($D$1
Without knowing the formula you are using I cannot be more specific.
Post if you need more help.
HTH
Kostis
dd said:='[RDD Register.xls]Oban'!G59
Is the formula I'm using. I'm trying to link to data in another book. But,
when I copy my formula down the column it goes out of sync, because I have
added additional rows.
When I created the document, In column A:A I linked to cells in another
book
using ='[RDD Register.xls]Oban'!A01 etc.
Then I added rows below each referenced row and added more data, as per
the
diagram.
Now I want to import other columns from the source document using ='[RDD
Register.xls]Oban'!G1 etc. and make them fit beside the previously
referenced cells. It know it sounds complicated but it isn't. I have
A B C D
001 00 Elevations 6-8-06
001 01 Windows in wrong location 6-9-06
001 02 Lintols missing 6-10-06
002 00 Plans
7-8-06
etc.
The items in column A are irregular, in our example 001 has three items,
002
may have 2 items, 003 may have 5 items, 004 may have ten items.
I want my referenced cells to be input where the rows B = "00", otherwise
the numbering goes out of sync and it repeats the copied cells.
Regards
Dylan
Ah see...
OK here is a try. Let us say that you filtered on column C:C being
greater than 5, i.e. your condition is (C1>5)
Then, in your formula you can use the following expression in lieu of,
say, B7:
OFFSET(Sheet1!B7,SUMPRODUCT(--($C$1:C1>5))-1,0)
If you have filtered by more than one conditions (e.g. C1>5 and D1="a")
you can use:
OFFSET(Sheet1!B7,SUMPRODUCT(($C$1:C1>5)*($D$1
Without knowing the formula you are using I cannot be more specific.
Post if you need more help.
HTH
Kostis
Kostis,
Thanks for your patience. I tried the Offset function and it does import
what I want.
I amended the reference from column B to column G to import from the
required column, so that the first part is =OFFSET('[RDD
Register.xls]Oban'!G8,
I'm not sure what SUMPRODUCT(--($B$1:B1="00"))-1,0) does?
when I select it now and stretch it to copy over the filtered cells the
reference B7 still jumps ahead according to location i.e. 5 Cells below =
B12 when I want it should be B8.
With your formula:
In my working document, the rows in between 3 to 10 are hidden.
In Row 2 (visible)
=OFFSET('[RDD Register.xls]Oban'!G8,SUMPRODUCT(--(B2:B2 ="00"))-1,0)
In Row 11 (visible) ----
=OFFSET('[RDD Register.xls]Oban'!G17,SUMPRODUCT(--(B11:B11 ="00"))-1,0)
----
How do I make the formula in Row 11 equal?
=OFFSET'[RDD Register.xls]Oban'!G9,SUMPRODUCT(--($B$11:B11="00"))-1,0)
The next row from my source. ----
I previously made an error in the diagram and have updated it thus:
Please, let me explain further:
Column G in the reference document is being imported into column D of the
working document.
Where data is consecutively added to column D where B:B="00"
I'm sure your offset would work, but I may be able to do it with and If
statement:
If the Cell in B#="00", D# should = '[RDD Register.xls]Oban'!G#
If the cell in B#>"00" D# should = B#-1
I thought excel would allow me to update formulae over a filtered range, so
that the references run consecutilely over the visible cells in the filter.
Then use almost the formula I suggested:
=OFFSET'[RDD Register.xls]Oban'!B7,SUMPRODUCT(--($B$1:B1="00"))-1,0)
Does this help?
Kostis
='[RDD Register.xls]Oban'!G59
Is the formula I'm using. I'm trying to link to data in another book. But,
when I copy my formula down the column it goes out of sync, because I have
added additional rows.
When I created the document, In column A:A I linked to cells in another
book
using ='[RDD Register.xls]Oban'!A01 etc.
Then I added rows below each referenced row and added more data, as per
the
diagram.
Now I want to import other columns from the source document using ='[RDD
Register.xls]Oban'!G1 etc. and make them fit beside the previously
referenced cells. It know it sounds complicated but it isn't. I have
A B C D
001 00 Elevations 6-8-06
001 01 Windows in wrong location 6-9-06
001 02 Lintols missing 6-10-06
002 00 Plans
7-8-06
etc.
The items in column A are irregular, in our example 001 has three items,
002
may have 2 items, 003 may have 5 items, 004 may have ten items.
I want my referenced cells to be input where the rows B = "00", otherwise
the numbering goes out of sync and it repeats the copied cells.
Regards
Dylan
Ah see...
OK here is a try. Let us say that you filtered on column C:C being
greater than 5, i.e. your condition is (C1>5)
Then, in your formula you can use the following expression in lieu of,
say, B7:
OFFSET(Sheet1!B7,SUMPRODUCT(--($C$1:C1>5))-1,0)
If you have filtered by more than one conditions (e.g. C1>5 and D1="a")
you can use:
OFFSET(Sheet1!B7,SUMPRODUCT(($C$1:C1>5)*($D$1
Without knowing the formula you are using I cannot be more specific.
Post if you need more help.
HTH
Kostis
Kostis,
Thanks for your patience. I tried the Offset function and it does import
what I want.
I amended the reference from column B to column G to import from the
required column, so that the first part is =OFFSET('[RDD
Register.xls]Oban'!G8,
I'm not sure what SUMPRODUCT(--($B$1:B1="00"))-1,0) does?
when I select it now and stretch it to copy over the filtered cells the
reference B7 still jumps ahead according to location i.e. 5 Cells below =
B12 when I want it should be B8.
With your formula:
In my working document, the rows in between 3 to 10 are hidden.
In Row 2 (visible)
=OFFSET('[RDD Register.xls]Oban'!G8,SUMPRODUCT(--(B2:B2 ="00"))-1,0)
In Row 11 (visible) ----
=OFFSET('[RDD Register.xls]Oban'!G17,SUMPRODUCT(--(B11:B11 ="00"))-1,0)
----
How do I make the formula in Row 11 equal?
=OFFSET'[RDD Register.xls]Oban'!G9,SUMPRODUCT(--($B$11:B11="00"))-1,0)
The next row from my source. ----
I previously made an error in the diagram and have updated it thus:
Please, let me explain further:
Column G in the reference document is being imported into column D of the
working document.
Where data is consecutively added to column D where B:B="00"
I'm sure your offset would work, but I may be able to do it with and If
statement:
If the Cell in B#="00", D# should = '[RDD Register.xls]Oban'!G#
If the cell in B#>"00" D# should = B#-1
I thought excel would allow me to update formulae over a filtered range,
so
that the references run consecutilely over the visible cells in the
filter.
Then use almost the formula I suggested:
=OFFSET'[RDD Register.xls]Oban'!B7,SUMPRODUCT(--($B$1:B1="00"))-1,0)
Does this help?
Kostis
='[RDD Register.xls]Oban'!G59
Is the formula I'm using. I'm trying to link to data in another book.
But,
when I copy my formula down the column it goes out of sync, because I
have
added additional rows.
When I created the document, In column A:A I linked to cells in another
book
using ='[RDD Register.xls]Oban'!A01 etc.
Then I added rows below each referenced row and added more data, as per
the
diagram.
Now I want to import other columns from the source document using
='[RDD
Register.xls]Oban'!G1 etc. and make them fit beside the previously
referenced cells. It know it sounds complicated but it isn't. I have
A B C D
001 00 Elevations
6-8-06
001 01 Windows in wrong location 6-9-06
001 02 Lintols missing
6-10-06
002 00 Plans
7-8-06
etc.
The items in column A are irregular, in our example 001 has three items,
002
may have 2 items, 003 may have 5 items, 004 may have ten items.
I want my referenced cells to be input where the rows B = "00",
otherwise
the numbering goes out of sync and it repeats the copied cells.
Regards
Dylan
Ah see...
OK here is a try. Let us say that you filtered on column C:C being
greater than 5, i.e. your condition is (C1>5)
Then, in your formula you can use the following expression in lieu of,
say, B7:
OFFSET(Sheet1!B7,SUMPRODUCT(--($C$1:C1>5))-1,0)
If you have filtered by more than one conditions (e.g. C1>5 and D1="a")
you can use:
OFFSET(Sheet1!B7,SUMPRODUCT(($C$1:C1>5)*($D$1
Without knowing the formula you are using I cannot be more specific.
Post if you need more help.
HTH
Kostis
Kostis,
It works now, but when I try to apply it to Column A it doesn't work - it
shows the formula in the cell.
=OFFSET('[RDD Register.xls]Oban'!$A$8,SUMPRODUCT(--($B$2:B2="00"))-1,0)
Dylan
OK, let me explain the structure of the original formula I suggested,
before you tried to adapt it.
=OFFSET('[RDD Register.xls]Oban'!$B$8,SUMPRODUCT(--($B$2:B2
="00"))-1,0)
First of all, I forgot to put $$ in Oban'!$B$8, which are necessary.
Sorry for this mistake.
OFFSET will compute a range relative to another range. Thus the idea is
that, for every cell in the output where we want the formula, we jump
one position from the first cell that you want to copy. In the output,
the cells that should have a formula are dispersed, but for each next
cell with a formula (dispersed) we want the next cell from the input
(consecutive).
Thus we are using a 2nd argument in OFFSET which should grow by 1 every
time that we enter a formula in the output. To do this we put
programmatically, in the formula, the same condition that you used for
filtering. If you filter by column B:B="00" then this is what we will
put in the formula. If you filter by another column, this is what we
will put in the formula. The expression:
SUMPRODUCT(--($B$2:B2 ="00"))-1
Notice that we have $$ around the range start but not around the range
end. When this formula is copied down, to say cell in row 10, it
becomes $B$2:B10. Thus, in that row, it counts how many cells, until
now, have had the condition satisfied. If it finds 3 cells then it
knows that from the first cell with success of the input, it should go
down 2 places, to get the 3rd cell (we started with 1st cell).
It is important here to use the column that you use for filtering. If
you are filtering by column G:G being equal to "00" then the expression
should become
SUMPRODUCT(--($G$2:G2 ="00"))-1
The row (2) in the range start ($G$2) should be the cell from where you
start your output.
Does this help?
Kostis
Kostis,
Thanks for your patience. I tried the Offset function and it does import
what I want.
I amended the reference from column B to column G to import from the
required column, so that the first part is =OFFSET('[RDD
Register.xls]Oban'!G8,
I'm not sure what SUMPRODUCT(--($B$1:B1="00"))-1,0) does?
when I select it now and stretch it to copy over the filtered cells the
reference B7 still jumps ahead according to location i.e. 5 Cells below =
B12 when I want it should be B8.
With your formula:
In my working document, the rows in between 3 to 10 are hidden.
In Row 2 (visible)
=OFFSET('[RDD Register.xls]Oban'!G8,SUMPRODUCT(--(B2:B2 ="00"))-1,0)
In Row 11 (visible) ----
=OFFSET('[RDD Register.xls]Oban'!G17,SUMPRODUCT(--(B11:B11 ="00"))-1,0)
----
How do I make the formula in Row 11 equal?
=OFFSET'[RDD Register.xls]Oban'!G9,SUMPRODUCT(--($B$11:B11="00"))-1,0)
The next row from my source. ----
I previously made an error in the diagram and have updated it thus:
Please, let me explain further:
Column G in the reference document is being imported into column D of the
working document.
Where data is consecutively added to column D where B:B="00"
I'm sure your offset would work, but I may be able to do it with and If
statement:
If the Cell in B#="00", D# should = '[RDD Register.xls]Oban'!G#
If the cell in B#>"00" D# should = B#-1
I thought excel would allow me to update formulae over a filtered range,
so
that the references run consecutilely over the visible cells in the
filter.
Then use almost the formula I suggested:
=OFFSET'[RDD Register.xls]Oban'!B7,SUMPRODUCT(--($B$1:B1="00"))-1,0)
Does this help?
Kostis
='[RDD Register.xls]Oban'!G59
Is the formula I'm using. I'm trying to link to data in another book.
But,
when I copy my formula down the column it goes out of sync, because I
have
added additional rows.
When I created the document, In column A:A I linked to cells in another
book
using ='[RDD Register.xls]Oban'!A01 etc.
Then I added rows below each referenced row and added more data, as per
the
diagram.
Now I want to import other columns from the source document using
='[RDD
Register.xls]Oban'!G1 etc. and make them fit beside the previously
referenced cells. It know it sounds complicated but it isn't. I have
A B C D
001 00 Elevations
6-8-06
001 01 Windows in wrong location 6-9-06
001 02 Lintols missing
6-10-06
002 00 Plans
7-8-06
etc.
The items in column A are irregular, in our example 001 has three items,
002
may have 2 items, 003 may have 5 items, 004 may have ten items.
I want my referenced cells to be input where the rows B = "00",
otherwise
the numbering goes out of sync and it repeats the copied cells.
Regards
Dylan
Ah see...
OK here is a try. Let us say that you filtered on column C:C being
greater than 5, i.e. your condition is (C1>5)
Then, in your formula you can use the following expression in lieu of,
say, B7:
OFFSET(Sheet1!B7,SUMPRODUCT(--($C$1:C1>5))-1,0)
If you have filtered by more than one conditions (e.g. C1>5 and D1="a")
you can use:
OFFSET(Sheet1!B7,SUMPRODUCT(($C$1:C1>5)*($D$1
Without knowing the formula you are using I cannot be more specific.
Post if you need more help.
HTH
Kostis
dd wrote:
Kostis,
I tried it and it pastes to the selected cells okay, but the link
updates
according to the row number rather than sequentially.
For example, if my first row is 1 and the reference I want to copy is
"B7"
and the next row is row 5,
I want the reference to update to "B8", but it updates to "B11"
Regards
Dylan
Select the visible cells. Edit|Go To...| Click the Special... button.
Select Visible Cells Only.
HTH
Kostis Vezerides
dd wrote:
I want to copy the following function across only the cells
displayed
in
my
autofilter
='[RDD Register.xls]Oban'!G59
But it's picking up the cells hidden and the numbers jump out of
sequence
on
the filtered sheet.
Is there any way to overcome this?
D Dawson
Kostis,
It works now, but when I try to apply it to Column A it doesn't work - it
shows the formula in the cell.
=OFFSET('[RDD Register.xls]Oban'!$A$8,SUMPRODUCT(--($B$2:B2="00"))-1,0)
Dylan
OK, let me explain the structure of the original formula I suggested,
before you tried to adapt it.
=OFFSET('[RDD Register.xls]Oban'!$B$8,SUMPRODUCT(--($B$2:B2
="00"))-1,0)
First of all, I forgot to put $$ in Oban'!$B$8, which are necessary.
Sorry for this mistake.
OFFSET will compute a range relative to another range. Thus the idea is
that, for every cell in the output where we want the formula, we jump
one position from the first cell that you want to copy. In the output,
the cells that should have a formula are dispersed, but for each next
cell with a formula (dispersed) we want the next cell from the input
(consecutive).
Thus we are using a 2nd argument in OFFSET which should grow by 1 every
time that we enter a formula in the output. To do this we put
programmatically, in the formula, the same condition that you used for
filtering. If you filter by column B:B="00" then this is what we will
put in the formula. If you filter by another column, this is what we
will put in the formula. The expression:
SUMPRODUCT(--($B$2:B2 ="00"))-1
Notice that we have $$ around the range start but not around the range
end. When this formula is copied down, to say cell in row 10, it
becomes $B$2:B10. Thus, in that row, it counts how many cells, until
now, have had the condition satisfied. If it finds 3 cells then it
knows that from the first cell with success of the input, it should go
down 2 places, to get the 3rd cell (we started with 1st cell).
It is important here to use the column that you use for filtering. If
you are filtering by column G:G being equal to "00" then the expression
should become
SUMPRODUCT(--($G$2:G2 ="00"))-1
The row (2) in the range start ($G$2) should be the cell from where you
start your output.
Does this help?
Kostis
Kostis,
Thanks for your patience. I tried the Offset function and it does import
what I want.
I amended the reference from column B to column G to import from the
required column, so that the first part is =OFFSET('[RDD
Register.xls]Oban'!G8,
I'm not sure what SUMPRODUCT(--($B$1:B1="00"))-1,0) does?
when I select it now and stretch it to copy over the filtered cells the
reference B7 still jumps ahead according to location i.e. 5 Cells below
=
B12 when I want it should be B8.
With your formula:
In my working document, the rows in between 3 to 10 are hidden.
In Row 2 (visible)
=OFFSET('[RDD Register.xls]Oban'!G8,SUMPRODUCT(--(B2:B2 ="00"))-1,0)
In Row 11 (visible) ----
=OFFSET('[RDD Register.xls]Oban'!G17,SUMPRODUCT(--(B11:B11 ="00"))-1,0)
----
How do I make the formula in Row 11 equal?
=OFFSET'[RDD Register.xls]Oban'!G9,SUMPRODUCT(--($B$11:B11="00"))-1,0)
The next row from my source. ----
I previously made an error in the diagram and have updated it thus:
Please, let me explain further:
Column G in the reference document is being imported into column D of
the
working document.
Where data is consecutively added to column D where B:B="00"
I'm sure your offset would work, but I may be able to do it with and If
statement:
If the Cell in B#="00", D# should = '[RDD Register.xls]Oban'!G#
If the cell in B#>"00" D# should = B#-1
I thought excel would allow me to update formulae over a filtered range,
so
that the references run consecutilely over the visible cells in the
filter.
Then use almost the formula I suggested:
=OFFSET'[RDD Register.xls]Oban'!B7,SUMPRODUCT(--($B$1:B1="00"))-1,0)
Does this help?
Kostis
='[RDD Register.xls]Oban'!G59
Is the formula I'm using. I'm trying to link to data in another book.
But,
when I copy my formula down the column it goes out of sync, because I
have
added additional rows.
When I created the document, In column A:A I linked to cells in
another
book
using ='[RDD Register.xls]Oban'!A01 etc.
Then I added rows below each referenced row and added more data, as
per
the
diagram.
Now I want to import other columns from the source document using
='[RDD
Register.xls]Oban'!G1 etc. and make them fit beside the previously
referenced cells. It know it sounds complicated but it isn't. I have
A B C
D
001 00 Elevations
6-8-06
001 01 Windows in wrong location 6-9-06
001 02 Lintols missing
6-10-06
002 00 Plans
7-8-06
etc.
The items in column A are irregular, in our example 001 has three
items,
002
may have 2 items, 003 may have 5 items, 004 may have ten items.
I want my referenced cells to be input where the rows B = "00",
otherwise
the numbering goes out of sync and it repeats the copied cells.
Regards
Dylan
Ah see...
OK here is a try. Let us say that you filtered on column C:C being
greater than 5, i.e. your condition is (C1>5)
Then, in your formula you can use the following expression in lieu of,
say, B7:
OFFSET(Sheet1!B7,SUMPRODUCT(--($C$1:C1>5))-1,0)
If you have filtered by more than one conditions (e.g. C1>5 and
D1="a")
you can use:
OFFSET(Sheet1!B7,SUMPRODUCT(($C$1:C1>5)*($D$1
Without knowing the formula you are using I cannot be more specific.
Post if you need more help.
HTH
Kostis
dd wrote:
Kostis,
I tried it and it pastes to the selected cells okay, but the link
updates
according to the row number rather than sequentially.
For example, if my first row is 1 and the reference I want to copy
is
"B7"
and the next row is row 5,
I want the reference to update to "B8", but it updates to "B11"
Regards
Dylan
Select the visible cells. Edit|Go To...| Click the Special...
button.
Select Visible Cells Only.
HTH
Kostis Vezerides
dd wrote:
I want to copy the following function across only the cells
displayed
in
my
autofilter
='[RDD Register.xls]Oban'!G59
But it's picking up the cells hidden and the numbers jump out of
sequence
on
the filtered sheet.
Is there any way to overcome this?
D Dawson
Kostis,
It now works fine. Thank you very much for helping me.
Dylan
If it shows the formula it most certainly means that the cells are
formatted as Text. Format|Cells...|Number tab, choose General.
Does this work?
Kostis
Kostis,
It works now, but when I try to apply it to Column A it doesn't work - it
shows the formula in the cell.
=OFFSET('[RDD Register.xls]Oban'!$A$8,SUMPRODUCT(--($B$2:B2="00"))-1,0)
Dylan
OK, let me explain the structure of the original formula I suggested,
before you tried to adapt it.
=OFFSET('[RDD Register.xls]Oban'!$B$8,SUMPRODUCT(--($B$2:B2
="00"))-1,0)
First of all, I forgot to put $$ in Oban'!$B$8, which are necessary.
Sorry for this mistake.
OFFSET will compute a range relative to another range. Thus the idea is
that, for every cell in the output where we want the formula, we jump
one position from the first cell that you want to copy. In the output,
the cells that should have a formula are dispersed, but for each next
cell with a formula (dispersed) we want the next cell from the input
(consecutive).
Thus we are using a 2nd argument in OFFSET which should grow by 1 every
time that we enter a formula in the output. To do this we put
programmatically, in the formula, the same condition that you used for
filtering. If you filter by column B:B="00" then this is what we will
put in the formula. If you filter by another column, this is what we
will put in the formula. The expression:
SUMPRODUCT(--($B$2:B2 ="00"))-1
Notice that we have $$ around the range start but not around the range
end. When this formula is copied down, to say cell in row 10, it
becomes $B$2:B10. Thus, in that row, it counts how many cells, until
now, have had the condition satisfied. If it finds 3 cells then it
knows that from the first cell with success of the input, it should go
down 2 places, to get the 3rd cell (we started with 1st cell).
It is important here to use the column that you use for filtering. If
you are filtering by column G:G being equal to "00" then the expression
should become
SUMPRODUCT(--($G$2:G2 ="00"))-1
The row (2) in the range start ($G$2) should be the cell from where you
start your output.
Does this help?
Kostis
Kostis,
Thanks for your patience. I tried the Offset function and it does import
what I want.
I amended the reference from column B to column G to import from the
required column, so that the first part is =OFFSET('[RDD
Register.xls]Oban'!G8,
I'm not sure what SUMPRODUCT(--($B$1:B1="00"))-1,0) does?
when I select it now and stretch it to copy over the filtered cells the
reference B7 still jumps ahead according to location i.e. 5 Cells below
=
B12 when I want it should be B8.
With your formula:
In my working document, the rows in between 3 to 10 are hidden.
In Row 2 (visible)
=OFFSET('[RDD Register.xls]Oban'!G8,SUMPRODUCT(--(B2:B2 ="00"))-1,0)
In Row 11 (visible) ----
=OFFSET('[RDD Register.xls]Oban'!G17,SUMPRODUCT(--(B11:B11 ="00"))-1,0)
----
How do I make the formula in Row 11 equal?
=OFFSET'[RDD Register.xls]Oban'!G9,SUMPRODUCT(--($B$11:B11="00"))-1,0)
The next row from my source. ----
I previously made an error in the diagram and have updated it thus:
A B C
D
001 00 Elevations
06-08-06
001 01 Windows in wrong location <blank, or
same
as
D1>
001 02 Lintols missing
<blank,
or same as D1>
002 00 Plans
22-10-07
Please, let me explain further:
Column G in the reference document is being imported into column D of
the
working document.
Where data is consecutively added to column D where B:B="00"
I'm sure your offset would work, but I may be able to do it with and If
statement:
If the Cell in B#="00", D# should = '[RDD Register.xls]Oban'!G#
If the cell in B#>"00" D# should = B#-1
I thought excel would allow me to update formulae over a filtered range,
so
that the references run consecutilely over the visible cells in the
filter.
Then use almost the formula I suggested:
=OFFSET'[RDD Register.xls]Oban'!B7,SUMPRODUCT(--($B$1:B1="00"))-1,0)
Does this help?
Kostis
dd wrote:
='[RDD Register.xls]Oban'!G59
Is the formula I'm using. I'm trying to link to data in another book.
But,
when I copy my formula down the column it goes out of sync, because I
have
added additional rows.
When I created the document, In column A:A I linked to cells in
another
book
using ='[RDD Register.xls]Oban'!A01 etc.
Then I added rows below each referenced row and added more data, as
per
the
diagram.
Now I want to import other columns from the source document using
='[RDD
Register.xls]Oban'!G1 etc. and make them fit beside the previously
referenced cells. It know it sounds complicated but it isn't. I have
A B C
D
001 00 Elevations
6-8-06
001 01 Windows in wrong location 6-9-06
001 02 Lintols missing
6-10-06
002 00 Plans
7-8-06
etc.
The items in column A are irregular, in our example 001 has three
items,
002
may have 2 items, 003 may have 5 items, 004 may have ten items.
I want my referenced cells to be input where the rows B = "00",
otherwise
the numbering goes out of sync and it repeats the copied cells.
Regards
Dylan
Ah see...
OK here is a try. Let us say that you filtered on column C:C being
greater than 5, i.e. your condition is (C1>5)
Then, in your formula you can use the following expression in lieu of,
say, B7:
OFFSET(Sheet1!B7,SUMPRODUCT(--($C$1:C1>5))-1,0)
If you have filtered by more than one conditions (e.g. C1>5 and
D1="a")
you can use:
OFFSET(Sheet1!B7,SUMPRODUCT(($C$1:C1>5)*($D$1
Without knowing the formula you are using I cannot be more specific.
Post if you need more help.
HTH
Kostis
dd wrote:
Kostis,
I tried it and it pastes to the selected cells okay, but the link
updates
according to the row number rather than sequentially.
For example, if my first row is 1 and the reference I want to copy
is
"B7"
and the next row is row 5,
I want the reference to update to "B8", but it updates to "B11"
Regards
Dylan
Select the visible cells. Edit|Go To...| Click the Special...
button.
Select Visible Cells Only.
HTH
Kostis Vezerides
dd wrote:
I want to copy the following function across only the cells
displayed
in
my
autofilter
='[RDD Register.xls]Oban'!G59
But it's picking up the cells hidden and the numbers jump out of
sequence
on
the filtered sheet.
Is there any way to overcome this?
D Dawson