Query: Link data between worksheets?

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

--
Message posted from http://www.ExcelForum.com

0
8/8/2004 8:29:41 AM
excel.misc 78881 articles. 5 followers. Follow

8 Replies
432 Views

Similar Articles

[PageSpeed] 0

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

--
Regards
Frank Kabel
Frankfurt, Germany


> 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
> that need to run off it (all within the same workbook). The slave
> worksheets all retrieve data from the master. The data is setup to
> run across in rows (i.e. Row 1 contains 1 record). Each record has an
> individual 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
> me hours of work.
>
>
> ---
> Message posted from http://www.ExcelForum.com/

0
frank.kabel (11126)
8/8/2004 8:46:25 AM
Thanks.

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

--
Message posted from http://www.ExcelForum.com

0
8/8/2004 9:13:22 AM
Okay, I'm using the formula and it gets me a little closer, but there'
still a problem. :mad: 

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

--
Message posted from http://www.ExcelForum.com

0
8/8/2004 10:18:24 AM
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)
--
Adjust the ranges to suit
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----
"Mutlee >" <<Mutlee.1ao0cm@excelforum-nospam.com> wrote in message
news:Mutlee.1ao0cm@excelforum-nospam.com...
> Okay, I'm using the formula and it gets me a little closer, but there's
> still a problem. :mad:
>
> For my slave sheets it works okay for the first column of data. But
> when I copy and paste it (the slaves are 40 columns x 11 rows) the
> lookup value and table array change whilst the column being returned
> stays the same.
>
> What I want is the opposite.
>
> i.e. Value and range stay the same, but the column returned changes
> sequentially.
>
> Any ideas as to how I can do this?
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
demechanik (4694)
8/8/2004 1:17:05 PM
Max.

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

Cheers. :

--
Message posted from http://www.ExcelForum.com

0
8/8/2004 4:44:45 PM
You're welcome, MutLee !
Thanks for the feedback
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----
"Mutlee >" <<Mutlee.1aoi8j@excelforum-nospam.com> wrote in message
news:Mutlee.1aoi8j@excelforum-nospam.com...
> Max.
>
> Thanks a million for this! I'll report back if I have anymore
> problems.
>
> Cheers. :)
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
demechanik (4694)
8/8/2004 6:45:19 PM
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

--
Message posted from http://www.ExcelForum.com

0
8/11/2004 4:20:55 PM
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

--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
---
"Martin du Saire >" <<Martin.du.Saire.1au14t@excelforum-nospam.com> wrote in
message news:Martin.du.Saire.1au14t@excelforum-nospam.com...
> I have a similar problem but in reverse.  I am trying to link in data
> from a specific page in individual DateWorksheets to a single
> SummaryWorksheet.  The individual worksheets are indexed by date
> (obviously), so I would need to link to a specific worksheet based on
> date input in SummaryWorksheet.  Is your solution applicable?
>
> Thanks.
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
demechanik (4694)
8/11/2004 5:34:22 PM
Reply:

Similar Artilces:

Macro for transferring data from excel to word
Hi, I have an excel file and a word file. I want a macro in excel which would open the existing word file in system, then go to the specified location in the word file, delete the existing picture from word file, copy a range/picture from excel file and then paste that onto the word file as picture enhanced format or bitmap format. Which code should i use for the same? Regards, Ajay Varshney Hi, It's generally advisable to "pull" data rather than to "push" it. So this means you should code this in Word VBA. I suggest you post the question in the Word Newsgrou...

Volatile source data range
I'm trying to construct a template that does some data analysis and plots the results, and I am only one step away from getting what I need. The issue is that the length of my source data table varies for each data set I analyze. I have the charts made up, but as it is now I must manually change the source data range each time I use the template. Ideally, I would like to enter the source data range with a function like INDIRECT (because I can generate the range as text in the spreadsheet). The Source Data window won't accept this, however. Is there some other way to do thi...

How do I hide part of a data series in a chart?
We have a chart with 2 data series - Budget & Actual. We want to show all 12 months of the budget, but only the year-to-date months of the actual. The problem is the data cells for the future actual amounts have formulas in them so the chart use the result and plots the line. We do not want to delete the data (which will require manual input each month, we just want the actual data series line to not show for the future periods. We tried using IF statements with 0, "", " " and an alpha character, but these have not worked. -- Jeff Hi Jeff: You can set up yo...

vba query
What is the vba code for pasting a value in the active cell thanks Madhu Range("A1").Copy ActiveCell.PasteSpecial Mangesh "Madhu" <madhu@paceinfo.com> wrote in message news:#nju$z0ZFHA.2940@tk2msftngp13.phx.gbl... > What is the vba code for pasting a value in the active cell > thanks > Madhu > > if doing the copy then you can just use Activecell.Value = range("A1").Value -- HTH Bob Phillips "Mangesh Yadav" <mangesh.yadav@NOSPAMgmail.com> wrote in message news:eHp6g20ZFHA.3808@TK2MSFTNGP14.phx.gbl... > Ra...

IF formula with multiple data range in cell
I'm trying to create an IF formula that returns data after searching a cell that contains more than one data range. For ex: Cell B3 contains GCVW-00001, GCVW-00002 I need the formula in C3 to return Yes if B3 contains GCVW-00001 and I need the formula in D3 to return Yes if B3 contains GCVW-00002 I'm able to get the formula to return yes or no correctly if cell B3 contains either GCVW-00001 or GCVW-00002, but the formula won't work if it contains both at the same time. right now my basic formula looks like: =IF(B3="GCVW-00001","Yes","No&q...

Use the "mouse over" event to exclude a data point from a series
I'm relatively new to excel macros and would greatly appreciate any help. In an Excel scatter chart, if I move the mouse over a data point, a "tool-tip text" or a "hover text" appears. Is there a way to intercept this event programmatically to exclude the data point from the series and then have the chart re-drawn? Randall - I just wrote an article about chart events in Excel (mouse-overs, etc.), which doesn't tell you exactly how to exclude a point, but it can at least tell you which point it was, and some more detailed code could remove it. The article is in...

LIST out all the data according to the "Criteria"
There are 3 data sets are assumed running in A6, in B6 and in C6 down, viz.: In A6 down is data of "date": 1-Jan-07, 1-Jul-07, 1-Nov-07, 1-Apr-08 and 1- Oct-08 In B6 down is data of "category": Revenue, Revenue, Revenue, Cost and Cost. In C6 down is data of "US$": US$1,200, US$1,250, US$3,000, US$450 and US$550. Another set of data is the "criteria": In cell G7 is the "1-Jan-08" 【it is Starting Date】; in cell G8 is the "1-Dec-08"【it is Ending Date】and in cell G9 is the "Cost" 【it is Category】. I know using the function &...

Excel 2000: Combo box cell link protection question
If the cell to which a combo box "cell link" refers, is protected then Excel generates an error when the combo box value is changed. Is there a way to to have the particular cell protected and yet be able to change the combo box value? Bertus Have the unprotected cell in a hidden row or column -- Message posted from http://www.ExcelForum.com ...

Filtering subform based on query
My form has a subform based on a query where it looks for a date range based on a field from the main form. I need to set a date range to view based on the date field on the main form. Any suggestions? ...

query help 02-23-08
I have a table that I created in Access and I also have created a report to display the information I want from the query, but the report is showing me more than what I want. I have grouping specified on my report to groups records based on the same date and city. However, I only want to report on those records where the date and city match but only on more than 3 occurances and no greater than 12 occurances. Do you recommend I change my query or change something in my report to accomplish this? Any help would be appreciated. create a new query Put the date field, city field, and ...

Input boxes in excel and MS Query
I'm using MS Excel to display the results of a SQL Server database, using the GET EXTERNAL DATA wizard, which then takes you to Microsoft Query so you can set up the SQL coding. I was having difficulty writing the SQL using MS Query, so I set up the same scenario in MS Access to write a query, then just pasted the query into MS Query. Unfortunately the code does not work and I get a "Invalid column name What is the start date" error What I'm actually trying to do is get two input boxes to appear each time the spreadsheet is opened. The user types the FROM and TO dates in...

Long txt data import in cell
Hi there, May I ask for your expertise of how to import a long data into excel Normally I can import a txt data, "text to column" it and it will b laid out into each column nicely. But the data for this file is to long (about 500 column needed) that it would not fit into one row Therefore, I opted for all data separated into each cell fitting int one column instead. Can anyone teach me how to transpose this? When I copy the file and try to paste special or transpose it, I don' get the option to transpose it. Please help. Any advice appreciated. Thank you in advance. Cheers, A...

Outlook Links and Pop Ups
Running Outlook 2002. Windows XP Pro. 1.7 ghz. 50 gig hard drive. 512 memory. Anytime I attempt to open a link in an email while using Outlook the following occurs: 1) The link won't open, or when it does, it takes at least a minute. 2) 10 to 15 pop up ads are generated in the lower right hand corner and then take over the screen. 3) The Outlook program slows to a crawl or stops functioning completely. 4) Sometimes an "Open Links with" dialogue box appears. It may be a virus or some stupid program some third party put into my start up or Outlook program but I can...

Worksheet 260 should NOT update Quantities
A worksheet 260 should not update quantities and there should be another worksheet that does that. I have had clients run a 260 for several reason (add alias, change description, etc.) however because the 401 comes after the 260, everytime a 260 is run, it screws up the quantity amounts in the store. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Mic...

Can't Open links in email messages
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 I am running Windows XP and Office XP with Outlook 2002. I ran Netscape 8.0 beta for awhile and decided I didn't like it. I deleted it and went back to using Internet Explorer 6.x. Now I can't open any live links in my messages like I used to and was wondering what I need to do to get outlook to use IE to open the link. Instead it goes to My Documents. HELP! LV -----BEGIN PGP SIGNATURE----- Version: PGP 8.1 - not licensed for commercial use: www.pgp.com iQA/AwUBQnFhfMr91nvpayIKEQJRiQCdFeUarrnBQIpMk7sQMOlPGEu2NosAoPEX eUsqHd0HHBHYkW0...

Grouped/outlined data only graphs when visible
hi When i group data, it has to be visible to appear on a graph. I also want it to displaye when the grouping is collapsed. I am charting data over a year. On the summary slide, i want to only see the total of all the months, however on the accompanying graph, i want to see how the data compiled over time (even when the grouping is not displayed). Can anyone help? Thanks mickey Hi, Sounds like you have the option to 'plot visible cells only' on. Select the chart and the via the menus Tools > Options > Chart uncheck plot visible only. Cheers Andy mickeykav@gmail.com w...

Help needed for sorting the data and plotting graph
Hi All, I am a newbie to the excel world. I have requirement of analysing some data that I will obtain from symbian profiler. I will be having a .txt file with values seperated with tabs so that it can be directly open in excel. In that I have around 25 colums, first being sample interval and rest are having data. My requirement is to plot a graph against the sample interval with data from 2 colums. I can specify the column name for plotting purpose. Once I plot the graph I should be able to calculate the average value of one series for those interval in which the other plotted series is cross...

Copying from data from another worksheet to exisiting.
Hi- Could someone tell me the way to copy a closed worksheet to the existing one. I have a macro in a template, and it gets kicked off every morning. I want to get the data from a another worksheet when it gets kicked off and then do the manipulation with that data.Could someone help!I would really appreciate it. Thanks jnair ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** If the range doesn't change, maybe you could just use links: Open both workbooks. in the "receiving&quo...

Can I edit a web query?
I want to download some data from the web which is in a simple table format (7 columns x 50 rows). I use MS Web Query to do this. Is there anyway that I can use VBA to just download the first row of the table? Web Query only allows the whole table to be downloaded and I cannot find a way to edit this using Excel to just get specific bits of data. As always, advice is well received... If you give me the url and your present code, I will take a look at it. OR, import to one sheet and use a macro to extract to another sheet. -- Don Guillett SalesAid Software donaldb@281.com "Alex&...

Is there a convenient way to save data
Is there a convenient way to save dialog data so it remembers the users last selection? I have a dialog based application that only has three user variables all of which are ints. What is the best way to make theses variables persistent for a given user on a given machine? Thanks "Jeff B" <jb@nospan.net> wrote in message news:fpDif.10451$Ay6.2336@fe13.lga... > > Is there a convenient way to save dialog data so it remembers the users last > selection? I have a dialog based application that only has three user > variables all of which are ints. What is the best w...

Percent Data Label on 100% Stacked Column Chart
I've created a chart with three 100% stacked columns, each having multiple data points in common. I'd like to show what percentage each of the data points is in relation to its column but the percentage data label is greyed out. Is there another way to do what I'm trying? Thanks! You may want to try Rob Bovey's XY Chart Labeler add in. http://www.appspro.com/Utilities/ChartLabeler.htm "PNichols" wrote: > I've created a chart with three 100% stacked columns, each having multiple > data points in common. I'd like to show what percentage each ...

Complicated text to date query
HIya, I've inherited a database with three text columns with dates in a text format. In each of these columns the text dates have been added in lots of different formats, e.g. 30409 meaning 03/04/09, so missing the leading zero! or 030409 or 03042009 or 03/04/2009 or 03/04/09 So every way which really... Please help me find an update query, or set of them to get this mish mash of different text formats into a unified short date format in Access 2007. This database is quite large, so no room for manual manipualtions. Thanks Heather I would probably take this in...

Worksheet function-Ajit1102730
Dear Max Thanks for the gr8 help. I will be obliged if you can send me sample book. Ajit -- Knowldege is Power As responded in the thread earlier ... Send me an email* so that I can attach the sample book in reply (your email address below doesn't seem valid, going by the mail failure / rejection received) *To either: demechanik <at>yahoo<dot>com, or xdemechanik <at>yahoo<dot>com -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot>com ---- "Ajit Munj" <ajitmunj@microsoft.com> wrote in message news:244...

Data range need help
Hi I have a problem with external data. In the Data Range Properties, under Data Layout. There are 3 options after 'If the number of rows in the data range changes upon refresh:' 1 - Insert cells for new data, delete unused cells 2 - Insert entire rows for new data, clear unused cells 3 - Overwrite existing cells with new data, clear unused cells. I want option 3 to be the default oprion but it always defaults back to option 2. Is there any way I can change this.?? Cheers Baz ...

Implementing a Label with certain data in Chart
Dear All ! I have a data set plotted in Excel. Is there any way to show automatically the minimum, maximum and median value of this data set on the chart? (In a form of label or anything similar) Minimum and maximum values are already part of data set. Median value is a calculated value and is in another cell(not part of data set). Thank You in advance I'd probably do this in a manner similar to what Jon Peltier shows here for the Vertical Category Axis. http://www.peltiertech.com/Excel/Charts/Y_CategoryAxis.html You could then create a custom format for each "Label" s...