Hooking cells with certain rows

D

Debi

Let me explain what I'm doing. I'm open to different
suggestions to this.

First I have Excel 2000 running on a 98b machine. The
information I'm working with is statistics for an auto
racing series, current year into a "career total group"

What I'm doing is this. I had it set up like this for the
former series I worked with, but I'm working on an
entirely new batch of drivers now.

I have 3 files. The current year's data, the total of all
the years (the career stats) and a file that adds the two
together. First, I cannot put all three together,
eventually it will become too large of a file to put the
career and current year's data together.

The problem I've run into is, Each file has three or four
sheets of data, linked to the first 'main' sheet. Those
links are fine, work perfectly. My original file from
last year had about 25 drivers in it... this year, I have
to add another 75 to the listing. Here's my question: I
know enough to put the cells for the drivers' name and
such as absolutes... $A1$. Now the problem. How do I
keep the links for say column "B" with the column A (which
is an absolute). The data in B is inputted after each
race.

The only thing I can think of doing at this stage, is
doing a paste vale of the totals on these secondary sheet
and pasting it along with the correct names on the main
sheet. Then starting over for the current year.

Any other ideas. Hope I'm not too confusing to anyone. P

I'm fairly advance in Excel, probably a intermediate-
advance. Although I don't know hardly anything about
Acess, hate that program anyways, the way you have to
enter information. So I want to say in Excel.

I'll try and find this post, but if anyone has any
suggestions, please email me at [email protected]

Thanks a bunch.
 
M

Max

Taking the cue from your subject line and this part of
your post ..
.. Now the problem. How do I keep the links for say
column "B" with the column A which is an absolute
The data in B is inputted after each race ..
Click to expand...

Perhaps one approach is to use OFFSET(..MATCH()...)
in the dependent sheets rather than just simple links to
cells in the "Main" sheet

This arrangement would work if you have a key column (e.g.
Names) that is shared between the dependent sheets and
the "Main" sheet with no duplicates in that column.

For example, let's assume the names are all unique, and
are in col A, row2 down in all the sheets
(row1 contains the col headers)

In the "Main" sheet
-----------------

Put in B2:

=OFFSET(Sheet2!$A$1,MATCH($A2,Sheet2!$A:$A,0)-1,COLUMN()-1)

Copy B2 across as many columns as there is data to be
linked from Sheet2, then copy down as many rows as you
have names in column A

If you need error trapping
(e.g. to take care of possibility of unmatched names),

put instead in B2:

=IF(ISNA(MATCH($A2,Sheet2!$A:$A,0)-1),"",OFFSET(Sheet2!
$A$1,MATCH($A2,Sheet2!$A:$A,0)-1,COLUMN()-1))

The above will return blanks [""] for any unmatched names
instead of #NA

Copy B2 across and down as before

What the above gives you is the ability to pull in
corresponding data from say Sheet2 into the "Main" sheet
according to the names listed in the key col A in "Main",
especially where the names listed in col A in Sheet2 may
*not* be in the same sequence / order as those listed in
col A in "Main"

Hope the above eases you into some ideas you can use
 
M

Max

Apologies, some corrections to the description in this
para:
Perhaps one approach is to use OFFSET(..MATCH()...)
in the dependent sheets rather than just simple links to
cells in the "Main" sheet
Click to expand...

Para should read:
 
B

BrianB

One simple way might be to Save the file with another name, remove th
old data and make additions as required,
then copy formulas down
 
B

BrianB

PS. If you have all files open whilst renaming them and save them al
into the same folder Excel will automatically update the links
 
Top