Query: Link data between worksheets?

[Mutlee]

I'm trying to find a shortcut to save me some time.

This is the problem:

I'm setting up a master worksheet, and 30 to 40 "slave" worksheets tha
need to run off it (all within the same workbook). The slave worksheet
all retrieve data from the master. The data is setup to run across i
rows (i.e. Row 1 contains 1 record). Each record has an individua
identification number entered in column A.

Is there a way that I can tell the slave sheets to copy an entire row
based purely on the identification number entered in column A?

Thanks in advance if anyone can help, as this could potentially save m
hours of work

 

[Frank Kabel]

Hi
for a formula solution have a look at VLOOKUP. for a VBA solution see:
http://www.rondebruin.nl/copy5.htm

 

[Mutlee]

Thanks.

I'll give it a whirl and see how I get on

 

[Mutlee]

Okay, I'm using the formula and it gets me a little closer, but there'
still a problem.

For my slave sheets it works okay for the first column of data. Bu
when I copy and paste it (the slaves are 40 columns x 11 rows) th
lookup value and table array change whilst the column being returne
stays the same.

What I want is the opposite.

i.e. Value and range stay the same, but the column returned change
sequentially.

Any ideas as to how I can do this

 

[Max]

Assume your sheet: Master
holds the base table in the range A1:AN100,
with col headers in row1, data from row2 down
and the lookup col is col A

In your "slave" sheets,
the lookup values are in col A, row2 down

Try something along these lines:

Put in B2:

=IF(ISNA(MATCH($A2,Master!$A$2:$A$100,0)),"",VLOOKUP($A2,Master!$A$2:$AN$100
,COLUMN(A1),0))

Copy B2 across to AN2,
then copy down as many rows as there is data in col A

Any unmatched lookup values in col A will return blanks ("")
[via use of the error trap: IF(ISNA( ...),"",VLOOKUP(...))]

Since we're using "COLUMN(A1)" as the col_index_num
in the VLOOKUP above, this will increment sequentially
as you copy across
( COLUMN(A1) = 1, COLUMN(B1)=2, and so on)

The use of "$" signs for the cell references in the lookup value: $A2
and in the table array: $A$2:$AN$100
will ensure that these references do not change as we copy across
and as we copy down (for the table array)

 

[Mutlee]

Max.

Thanks a million for this! I'll report back if I have anymor
problems.

Cheers. :

 

[Max]

You're welcome, MutLee !
Thanks for the feedback

 

[Martin du Saire]

I have a similar problem but in reverse. I am trying to link in dat
from a specific page in individual DateWorksheets to a singl
SummaryWorksheet. The individual worksheets are indexed by dat
(obviously), so I would need to link to a specific worksheet based o
date input in SummaryWorksheet. Is your solution applicable?

Thanks

 

[Max]

Perhaps something along these lines ..

Assume you have this kind of set-up / arrangement:

In Sheet: 01Jan
in cols A and B, data from row2 down
------------------------
Name Field
ABC Text1
DEF Text2
etc

Note: Names in col A: ABC, DEF are assumed unique

In Sheet: 02Jan
in cols A and B, data from row2 down
------------------------
Name Field
DEF Text22
ABC Text11
etc

And the other individual "daily" sheets: 03Jan, 04Jan ... 31Dec
all bear the same structure as above

In Sheet: Summary
----------------------
The names (ABC, DEF, etc) are listed in B1 across
and the sheetnames (01Jan, 02Jan ..etc)
are listed in A2 down in TEXT* format, not as actual dates

*via pre-formatting col A in Text format (Format > Cells > Text > OK)
or via entering the sheetnames: "01Jan", "02Jan" with leading apostrophes
( ' )
viz.:

...........ABC DEF
01Jan
02Jan
etc

Put in B2:

=IF(ISNA(MATCH(TRIM(B$1),INDIRECT("'"&TRIM($A2)&"'!A:A"),0)),"",OFFSET(INDIR
ECT("'"&TRIM($A2)&"'!A1"),MATCH(TRIM(B$1),INDIRECT("'"&TRIM($A2)&"'!A:A"),0)
-1,1))

Copy B2 across as many cols as there are names listed in row1,
then fill down for as many rows as you have individual daily sheets (365 ?)

This'll extract the data from col B in each of the "date" sheets
which are listed down in col A, matched with the names listed in row1.

For the samples given above, it'll show as:

...........ABC DEF
01Jan Text1 Text2
02Jan Text11 Text22

For a neater look, we could suppress extraneous zeros in "Summary" via:
Tools > Options > View tab > Uncheck "Zero values" > OK