linked text ranges or lists

[RHall]

I have a list with a range name in one worksheet that I would like linked to
another workbook. The list from the destination workbook needs to print on a
report. Excel 2002

 

[Debra Dalgleish]

In the destination workbook, choose Data>Import External Data>New
Database Query
Click on Excel Files as the data source, and click OK
Select the workbook with the list, and click OK
In the Query Wizard, select your named range, and click the > button, to
add the columns to the query
Click Next, three times, then click Finish.
Select a starting cell for the imported list, and click OK

Right-click a cell in the imported list, and click Data Range Properties
Add a check mark to Refresh Data on File Open, and click OK.

 

[RHall]

You really know your stuff, I would never have figured that solution out.
Although it was very useful I still have one problem with it. This solution
does not keep a link to update the destination when the source has been
changed or new records added.
Is there a way to link a text range from one EXCEL worksheet to another?
When I try to refer to the source range I get a VALUE error since the data is
text and not numbers. Thanks for your attention to my problem. Rhall

 

[Debra Dalgleish]

If you change the definition of the named range, so it includes the new
rows of data, the linked data should show the new rows when updated.
This method works for text or numbers. How are you linking to the cells
when you get the Value error?

 

[RHall]

I tried using the =with the workbook, worksheet and range name. I also tried
doing the instructions you gave me in your previous post but that did not
change the destination when I inserted an additional row in the souce range
list.

 

[Debra Dalgleish]

If you select the named range in the source data, is the new row
included? Perhaps you added the data at the end, and it's not in the
named range.

 

[RHall]

Thanks for all the help. Everything worked this time.

 

[RHall]

Now that I have the information, I need to transpose one of the lists. The
list with names will stay in rows and the list with jobs will be the column
headers. I've played with the Transpose function and I wind up getting only
the first cell. Any ideas? I'm try to set up a time sheet to enter the
employees time for each job.
(then I have to figure out how to double the columns for each job to allow
for regular time and overtime. I wish I could write in COBOL on my PC but it
has been years since I retired from the big computers.) Thanks for any
suggestions.

 

[Gord Dibben]

When using the TRANSPOSE function you must select a range of cells to receive
the data.

Then type your TRANSPOSE formula with source range in active cell and hit CRTL +
SHIFT + ENTER

i.e. select B1:F1

Type =TRANSPOSE(A1:A5) in B1 and then CSE


Gord Dibben MS Excel MVP