Complicated pickle

[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

 

[anon]

Oops. Typos:

Should be:

B2= GrossSales of CompanyName2
B3= CountofManagersOverAge55InCompanyName2

Jim

 

[anon]

It is amazing how, once you write out the issue in detail, a solution
sometimes magically appears!

I think if I change my D worksheet such that I effectively give each
row a numeric (and unique) name, I can use the MATCH() function keep
things aligned when a new row is inserted in the D worksheet.

Kind of ugly, but it appears to work.

Assume that column A in worksheet D is reserved for these numeric
"names". Now, it looks like:

A1:=1
A2:=2
A3:=3

B1:=CompanyName1
....
C1:=CompanyName2
....

Now, cell D11 of the R worksheet contains not:

D11: =Offset(D!$A$1,13,$A10-1,1,1)

but

D11: =Offset(D!$A$1,MATCH(13,D!$A$1:$A$4000,0)-1,$A10-1,1,1)

Well, it works, anyway.

If anybody has a better idea, I'm all ears.

Thanks

Jim

 

[Roger Govier]

Hi Jim

If you used 3 "spare" non printable columns at the start of you report,
you could have the following :-
In column A, in the first 10 rows only, enter the Column number you
wish to pick up on each of your 10 pages
In column B, in the first 66 row only, leave blank so you can enter the
row number of the Item you wish to pick up
In the rest of column B, in B67 enter = B1 and copy down to B132.
Repeat this pattern for the remaining 8 "pages"
In column C in
C1 enter =$A$1
in C2 enter = C1 and copy down to C66
In C67 enter =$B$1 and in C68 enter = C66 and copy down to C132
Repeat pattern for your other "pages"

In D1, your formula now is simply
=IF(OR(B1="",C1=""),"",INDEX(D!$1:$10000,B1,C1))
copy down through D2660
This is a one off exercise which sets up your template.

Now, all you have to do to run your report, is to enter the 10 column
numbers in A1:A10, and they need not be contiguous and then enter the
item numbers in B1:B66.
There is no need to amend any formulae.
The If / Or part of the formula means that you can leave blanks in your
list to space items out, or not bother to populate all 66 rows of a page
if you don't want to without creating a series of zeros.

Compared with Offset, you should find this runs considerably quicker.
Increase or decrease the number of rows 1:10000 to suit the size of your
data in sheet D.

 

[anon]

Hi Jim

If you used 3 "spare" non printable columns at the start of you report,
you could have the following :-
In column A, in the first 10 rows only, enter the Column number you
wish to pick up on each of your 10 pages
In column B, in the first 66 row only, leave blank so you can enter the
row number of the Item you wish to pick up
In the rest of column B, in B67 enter = B1 and copy down to B132.
Repeat this pattern for the remaining 8 "pages"
In column C in
C1 enter =$A$1
in C2 enter = C1 and copy down to C66
In C67 enter =$B$1 and in C68 enter = C66 and copy down to C132
Repeat pattern for your other "pages"

In D1, your formula now is simply
=IF(OR(B1="",C1=""),"",INDEX(D!$1:$10000,B1,C1))
copy down through D2660
This is a one off exercise which sets up your template.

Now, all you have to do to run your report, is to enter the 10 column
numbers in A1:A10, and they need not be contiguous and then enter the
item numbers in B1:B66.
There is no need to amend any formulae.
The If / Or part of the formula means that you can leave blanks in your
list to space items out, or not bother to populate all 66 rows of a page
if you don't want to without creating a series of zeros.

Compared with Offset, you should find this runs considerably quicker.
Increase or decrease the number of rows 1:10000 to suit the size of your
data in sheet D.

I think I see how it would be faster than offset, but I'm at a loss to
understand how it accomplishes my goal of being able to insert a row
into the D worksheet without causing the references in the R worksheet
to stop working.

Instead of using the row number in B, perhaps I would expand it to the
MATCH() value I came up with? Then, my formula in the cell would be:

=INDEX(D!$1:$10000,MATCH(B1,D!$A$1!$A$4000,0),C1)

Since all my output is formatted, I never have need for the IF=""
stuff.

Now, remember that I said this was more like a word mail merge than a
listing. Hence, there are some "rows" in the printing part of the
worksheet that have cells that have formulas in them which format
multiple items from the D worksheet. Not to mention some rows which
have multiple cells on them each picking up information from different
rows of the D worksheet. Think:

F10:="The " & Offset(D!$A$1,MATCH(13,D!$A$1:$A$4000,0)-1,$A10-1,1,1) &
" in " & Offset(D!$A$1,MATCH(14,D!$A$1:$A$4000,0)-1,$A10-1,1,1) & "
falls mainly on the " &
Offset(D!$A$1,MATCH(15,D!$A$1:$A$4000,0)-1,$A10-1,1,1) & "!"

where

the match to 13 has: "rain"; 14 has: "Spain" and 15 has "plain"

would yield:

The rain in Spain falls mainly on the plain!

I think I could extend your concept by expanding column B by, let's
say, 4 columns, so the columns B through F have match references and G
has your C and my print area starts at H. In this way I would be
limited to using no more than 5 "matches" on any one row of the
output.

Nice idea. It would make everything an index, rather than an offset.
For example, assuming I had 13 in my B, 14 in my C, 15 in my D, the
above would be:


M10:="The " & INDEX(D!$1:$10000,MATCH(B10,D!$A$1!$A$4000,0),G10) & "
in " & INDEX(D!$1:$10000,MATCH(C10,D!$A$1!$A$4000,0),G10) & " falls
mainly on the " & INDEX(D!$1:$10000,MATCH(D10,D!$A$1!$A$4000,0),G10)
& "!"

Is Index that much faster than Offset?

Thanks again.

Jim

 

[Roger Govier]

Hi

I had forgotten about your requirement about inserting rows, and yes the
MATCH() function would take care of that.
I would be inclined to set up named ranges
Insert>Name>Define>
Name rng Refers to D!$1:$10000
Name colB Refers to =MATCH('R'!$B1,D!$A$1!$A$4000,0)
Name colC Refers to =MATCH('R'!$C1,D!$A$1!$A$4000,0)
Name colD Refers to =MATCH('R'!$D1,D!$A$1!$A$4000,0)

The formula then becomes shorter and easier to read and maintain
="The "&INDEX(rng,colB,G1)&" in "&INDEX(rng,colC,G1)&
" falls "&INDEX(rng,colD,G1)&" on the "&INDEX(rng,colE,G1)&"!"

Yes INDEX is much faster than OFFSET.
Index is not volatile, whereas Offset is, and has to recalculate every
time there is a change on the sheet.

 

[anon]

Hi

I had forgotten about your requirement about inserting rows, and yes the
MATCH() function would take care of that.
I would be inclined to set up named ranges
Insert>Name>Define>
Name rng Refers to D!$1:$10000
Name colB Refers to =MATCH('R'!$B1,D!$A$1!$A$4000,0)
Name colC Refers to =MATCH('R'!$C1,D!$A$1!$A$4000,0)
Name colD Refers to =MATCH('R'!$D1,D!$A$1!$A$4000,0)

The formula then becomes shorter and easier to read and maintain
="The "&INDEX(rng,colB,G1)&" in "&INDEX(rng,colC,G1)&
" falls "&INDEX(rng,colD,G1)&" on the "&INDEX(rng,colE,G1)&"!"

Yes INDEX is much faster than OFFSET.
Index is not volatile, whereas Offset is, and has to recalculate every
time there is a change on the sheet.

Your attention to my issue is much appreciated.

I very much like your named ranges concept.

I shall soldier forth and let you know if I run into any difficulties.

Thanks again.

Jim