A
anon
I have a complicated (for me, anyway) problem that I have solved in a
way that I'm not happy with. If anybody can offer suggestions for
improvement to my methods, I'd very much appreciate it.
The problem is that I've ended up with a set of formulas which fall
apart if I insert a row. This is because I'm using the offset()
function, where I suspect I should be using something else.
The goal is to have 2 worksheets, named D (for data) and R (for
report). The data is organized such that each column is a distinct
entity. That is, the data points are rows and there can be hundreds,
if not thousands, of them. There are never more than 256 entities
(columns).
The R worksheet is set up like a poor man's mail merge. Imagine that
there are 66 lines to a "report" and that these 66 lines repeat 10
times in the R worksheet. Each set of 66 lines is effectively a page
and one page is "filled in" for each of up to 10 pre-selected (and
contiguous at this point) columns from the D worksheet. That is, I
want to be able to make this report display on these 10 pages
information from Columns H through Column Q or, Columns AZ through
Column BI, etc. At some point, I may want to use non-contiguous
columns, but that is down the road a bit.
To simplify, assume that worksheet D has the following information:
A1: CompanyName1
A2= GrossSales of CompanyName1
A3= CountofManagersOverAge55InCompanyName1
....
B1= CompanyName2
B2= GrossSales of CompanyName1
B3= CountofManagersOverAge55InCompanyName1
....
I have set up Column A in worksheet R so that it has an indicator as
to which page (of 10) is being produced (rows 1 through 66 will have a
1 in Column A, rows 67 through 132 will have a 2, etc.). I know I
could use zero as my base to make the spreadsheet easier, but for now
let's just assume I start counting from 1. Column A is not included
in the print area, so it doesn't show up when the report is printed.
Let's assume that line 10 of each report page will show the name of
the entity in column D. Hence, if I wanted to use direct addressing,
and assuming I'm just going after the first 10 entities in worksheet
D, D10 in worksheet R would have the following formula:
=D!A1
D76 would have:
=D!B1
D142 would have:
=D!C1
etc.
What I actually have in the R worksheet is:
D10: =Offset(D!$A$1,0,A10-1,1,1)
D76: =Offset(D!$A$1,0,A76-1,1,1)
D142: =Offset(D!$A$1,0,A142-1,1,1)
etc.
OK, so far so good. Now, in cell D11 (and D77 and D143, etc), I want
to display, let's say, the 14th row associated with each entity. My
formulas are:
D11: =Offset(D!$A$1,13,A10-1,1,1)
D77: =Offset(D!$A$1,13,A76-1,1,1)
D143: =Offset(D!$A$1,13,A142-1,1,1)
etc.
This works fine as long as I never change my D worksheet. As you can
imagine, though, with potentially thousands of data points for each
company, I'm constantly tinkering with what shows up on what row of
the D worksheet. If I insert a row between the 3rd and 4th rows, for
example, for a new data point, my R worksheet continues to pick up the
14th row in cells D11, D77, D143, etc.
Maybe the solution is obvious (other than: use Word for mailmerge
<g>), but can anybody suggest a construct which would allow me to
insert a row into the D worksheet and have my addressing in the R
sheet updated?
Thanks
Jim
way that I'm not happy with. If anybody can offer suggestions for
improvement to my methods, I'd very much appreciate it.
The problem is that I've ended up with a set of formulas which fall
apart if I insert a row. This is because I'm using the offset()
function, where I suspect I should be using something else.
The goal is to have 2 worksheets, named D (for data) and R (for
report). The data is organized such that each column is a distinct
entity. That is, the data points are rows and there can be hundreds,
if not thousands, of them. There are never more than 256 entities
(columns).
The R worksheet is set up like a poor man's mail merge. Imagine that
there are 66 lines to a "report" and that these 66 lines repeat 10
times in the R worksheet. Each set of 66 lines is effectively a page
and one page is "filled in" for each of up to 10 pre-selected (and
contiguous at this point) columns from the D worksheet. That is, I
want to be able to make this report display on these 10 pages
information from Columns H through Column Q or, Columns AZ through
Column BI, etc. At some point, I may want to use non-contiguous
columns, but that is down the road a bit.
To simplify, assume that worksheet D has the following information:
A1: CompanyName1
A2= GrossSales of CompanyName1
A3= CountofManagersOverAge55InCompanyName1
....
B1= CompanyName2
B2= GrossSales of CompanyName1
B3= CountofManagersOverAge55InCompanyName1
....
I have set up Column A in worksheet R so that it has an indicator as
to which page (of 10) is being produced (rows 1 through 66 will have a
1 in Column A, rows 67 through 132 will have a 2, etc.). I know I
could use zero as my base to make the spreadsheet easier, but for now
let's just assume I start counting from 1. Column A is not included
in the print area, so it doesn't show up when the report is printed.
Let's assume that line 10 of each report page will show the name of
the entity in column D. Hence, if I wanted to use direct addressing,
and assuming I'm just going after the first 10 entities in worksheet
D, D10 in worksheet R would have the following formula:
=D!A1
D76 would have:
=D!B1
D142 would have:
=D!C1
etc.
What I actually have in the R worksheet is:
D10: =Offset(D!$A$1,0,A10-1,1,1)
D76: =Offset(D!$A$1,0,A76-1,1,1)
D142: =Offset(D!$A$1,0,A142-1,1,1)
etc.
OK, so far so good. Now, in cell D11 (and D77 and D143, etc), I want
to display, let's say, the 14th row associated with each entity. My
formulas are:
D11: =Offset(D!$A$1,13,A10-1,1,1)
D77: =Offset(D!$A$1,13,A76-1,1,1)
D143: =Offset(D!$A$1,13,A142-1,1,1)
etc.
This works fine as long as I never change my D worksheet. As you can
imagine, though, with potentially thousands of data points for each
company, I'm constantly tinkering with what shows up on what row of
the D worksheet. If I insert a row between the 3rd and 4th rows, for
example, for a new data point, my R worksheet continues to pick up the
14th row in cells D11, D77, D143, etc.
Maybe the solution is obvious (other than: use Word for mailmerge
<g>), but can anybody suggest a construct which would allow me to
insert a row into the D worksheet and have my addressing in the R
sheet updated?
Thanks
Jim