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

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,

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

"vezerid" wrote in message
ups.com...
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

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 (C15)

Then, in your formula you can use the following expression in lieu of,
say, B7:

OFFSET(Sheet1!B7,SUMPRODUCT(--($C$1:C15))-1,0)

If you have filtered by more than one conditions (e.g. C15 and D1="a")
you can use:

OFFSET(Sheet1!B7,SUMPRODUCT(($C$1:C15)*($D$1:D1=" a"))-1,0)

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

"vezerid" wrote in message
ups.com...
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

='[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

"vezerid" wrote in message
ups.com...
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 (C15)

Then, in your formula you can use the following expression in lieu of,
say, B7:

OFFSET(Sheet1!B7,SUMPRODUCT(--($C$1:C15))-1,0)

If you have filtered by more than one conditions (e.g. C15 and D1="a")
you can use:

OFFSET(Sheet1!B7,SUMPRODUCT(($C$1:C15)*($D$1:D1=" a"))-1,0)

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

"vezerid" wrote in message
ups.com...
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

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

"vezerid" wrote in message
ups.com...
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 (C15)

Then, in your formula you can use the following expression in lieu of,
say, B7:

OFFSET(Sheet1!B7,SUMPRODUCT(--($C$1:C15))-1,0)

If you have filtered by more than one conditions (e.g. C15 and D1="a")
you can use:

OFFSET(Sheet1!B7,SUMPRODUCT(($C$1:C15)*($D$1:D1=" a"))-1,0)

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

"vezerid" wrote in message
ups.com...
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

try to grab the whole of column A, click copy, go to Column G, paste
specialformulas

on Column G you will have the linked cells ready made with formulas to be
[cut n paste] shifted on any virgin column you need.
just maybe..
--
*****
birds of the same feather flock together..



"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

"vezerid" wrote in message
ups.com...
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 (C15)

Then, in your formula you can use the following expression in lieu of,
say, B7:

OFFSET(Sheet1!B7,SUMPRODUCT(--($C$1:C15))-1,0)

If you have filtered by more than one conditions (e.g. C15 and D1="a")
you can use:

OFFSET(Sheet1!B7,SUMPRODUCT(($C$1:C15)*($D$1:D1=" a"))-1,0)

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

"vezerid" wrote in message
ups.com...
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,

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.


"vezerid" wrote in message
ups.com...
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

"vezerid" wrote in message
ups.com...
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 (C15)

Then, in your formula you can use the following expression in lieu of,
say, B7:

OFFSET(Sheet1!B7,SUMPRODUCT(--($C$1:C15))-1,0)

If you have filtered by more than one conditions (e.g. C15 and D1="a")
you can use:

OFFSET(Sheet1!B7,SUMPRODUCT(($C$1:C15)*($D$1:D1=" a"))-1,0)

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

"vezerid" wrote in message
ups.com...
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

Driller,

It still picks up the hidden cells in the consecutive numbering of formula
cell references.

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.

Regards
Dylan

"driller" wrote in message
...
try to grab the whole of column A, click copy, go to Column G, paste
specialformulas

on Column G you will have the linked cells ready made with formulas to be
[cut n paste] shifted on any virgin column you need.
just maybe..
--
*****
birds of the same feather flock together..



"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

"vezerid" wrote in message
ups.com...
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 (C15)

Then, in your formula you can use the following expression in lieu of,
say, B7:

OFFSET(Sheet1!B7,SUMPRODUCT(--($C$1:C15))-1,0)

If you have filtered by more than one conditions (e.g. C15 and D1="a")
you can use:

OFFSET(Sheet1!B7,SUMPRODUCT(($C$1:C15)*($D$1:D1=" a"))-1,0)

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

"vezerid" wrote in message
ups.com...
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

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

dd wrote:
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.


"vezerid" wrote in message
ups.com...
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

"vezerid" wrote in message
ups.com...
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 (C15)

Then, in your formula you can use the following expression in lieu of,
say, B7:

OFFSET(Sheet1!B7,SUMPRODUCT(--($C$1:C15))-1,0)

If you have filtered by more than one conditions (e.g. C15 and D1="a")
you can use:

OFFSET(Sheet1!B7,SUMPRODUCT(($C$1:C15)*($D$1:D1=" a"))-1,0)

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

"vezerid" wrote in message
ups.com...
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


"vezerid" wrote in message
ups.com...
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

dd wrote:
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.


"vezerid" wrote in message
ups.com...
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

"vezerid" wrote in message
ups.com...
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 (C15)

Then, in your formula you can use the following expression in lieu of,
say, B7:

OFFSET(Sheet1!B7,SUMPRODUCT(--($C$1:C15))-1,0)

If you have filtered by more than one conditions (e.g. C15 and D1="a")
you can use:

OFFSET(Sheet1!B7,SUMPRODUCT(($C$1:C15)*($D$1:D1=" a"))-1,0)

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

"vezerid" wrote in message
ups.com...
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

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

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


"vezerid" wrote in message
ups.com...
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

dd wrote:
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.


"vezerid" wrote in message
ups.com...
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

"vezerid" wrote in message
ups.com...
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 (C15)

Then, in your formula you can use the following expression in lieu of,
say, B7:

OFFSET(Sheet1!B7,SUMPRODUCT(--($C$1:C15))-1,0)

If you have filtered by more than one conditions (e.g. C15 and D1="a")
you can use:

OFFSET(Sheet1!B7,SUMPRODUCT(($C$1:C15)*($D$1:D1=" a"))-1,0)

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

"vezerid" wrote in message
ups.com...
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

"vezerid" wrote in message
oups.com...
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

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


"vezerid" wrote in message
ups.com...
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

dd wrote:
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.


"vezerid" wrote in message
ups.com...
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

"vezerid" wrote in message
ups.com...
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 (C15)

Then, in your formula you can use the following expression in lieu of,
say, B7:

OFFSET(Sheet1!B7,SUMPRODUCT(--($C$1:C15))-1,0)

If you have filtered by more than one conditions (e.g. C15 and
D1="a")
you can use:

OFFSET(Sheet1!B7,SUMPRODUCT(($C$1:C15)*($D$1:D1=" a"))-1,0)

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

"vezerid" wrote in message
ups.com...
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

Dylan,
glad to be of help. Thanks for the feedback.

Kostis

dd wrote:
Kostis,

It now works fine. Thank you very much for helping me.

Dylan

"vezerid" wrote in message
oups.com...
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

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


"vezerid" wrote in message
ups.com...
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

dd wrote:
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.


"vezerid" wrote in message
ups.com...
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

"vezerid" wrote in message
ups.com...
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 (C15)

Then, in your formula you can use the following expression in lieu of,
say, B7:

OFFSET(Sheet1!B7,SUMPRODUCT(--($C$1:C15))-1,0)

If you have filtered by more than one conditions (e.g. C15 and
D1="a")
you can use:

OFFSET(Sheet1!B7,SUMPRODUCT(($C$1:C15)*($D$1:D1=" a"))-1,0)

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

"vezerid" wrote in message
ups.com...
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