Entering Data into Ext. Data Range

T

Tim

I am trying to create an external data range for each
employee an then perform more calculations on the filtered
data. The users of the source data range must be able to
enter in new rows of data. If the users insert rows
within the source data range, will the range automatically
extend and, subsequently, the external data ranges? This
seems like a simple question, but I figured I should make
sure before I waste multiple hours on it.

Thanks,
Tim
 
F

Frank Stone

I am not quite sure i understand you. external data range?
what do you mean by that?
 
T

Tim

I have a list of projects, their schedule dates for three
consecutive periods, man hours assigned, and the engineer
assigned to each project. I want to take the hours in
each period for each engineer and, based on the nunmber of
weeks between "start" and "end", evenly disperse the hours
across the weeks in that period. Then, take the total for
each employee for all his/her jobs each week. (It's a
manpower distribution visual aid.)

I have the formulas all set up. An advanced filter won't
work, because it doesn't refresh with any new inputs.

Basically, I am creating an external data range, for each
engineer, that filters for each engineer and puts the
extracted data on a separate, hidden tab.

My question is: If I enter new projects for one of the
engineers, which will insert rows into the source data,
will it extend the source data RANGE, or bump out rows,
keeping the RANGE the same?

Hopefully you can follow all that.

Thanks in advance,
Tim
 
F

Frank Stone

you didn't answer my question. what do you mean by
external data range. you me that means you are getting
data from an exteranl data sorce through MS Query(connect
to exteranl).
if that is the case, the range that is the exteranl data
range belongs to the database query. you may put formula
ant the bottom or at the side but no hard data at the
bottom or in the middle. the database query will crash and
give error message "unable to expand range". to get
formuals at the bottom or at the side, you will have to go
into the query's property sheet and check "fill formulas
down" at the bottom of the sheet.
 
T

Tim

Thanks, Frank. That answered my question.
-----Original Message-----
you didn't answer my question. what do you mean by
external data range. you me that means you are getting
data from an exteranl data sorce through MS Query(connect
to exteranl).
if that is the case, the range that is the exteranl data
range belongs to the database query. you may put formula
ant the bottom or at the side but no hard data at the
bottom or in the middle. the database query will crash and
give error message "unable to expand range". to get
formuals at the bottom or at the side, you will have to go
into the query's property sheet and check "fill formulas
down" at the bottom of the sheet.

.
Click to expand...
 
D

David

The short answer is yes and no. I am doing something
similar but with one source and many destinations. Defined
names are inserted into a validation list. As long as the
source and destination files are open the changes you are
talking about happen in real time. If one of the files is
closed, in your case a destination file, and you open it,
if you have the appropriate boxes checked under Edit/Links
and Tools/Option/Calculation and Edit, then the system
will ask you if you want to update links(see Help menu -
Control When Links are updated). "Click on Update Links".
The trouble is the links are not always updated and there
doesn't seem to be a way to force the update. Which means
you have to do it manually.
I need this to happen because I create the Sources and
many users use the Destinations. They shouldn't have to
and don't know how to mannually update. In your situation
you can manually update but it would be a hastle.
If you determine how to make the update work automatically
or by forcing it let me know.
 
D

David

The short answer is yes and no. I am doing something
similar but with one source and many destinations. Defined
names are inserted into a validation list. As long as the
source and destination files are open the changes you are
talking about happen in real time. If one of the files is
closed, in your case a destination file, and you open it,
if you have the appropriate boxes checked under Edit/Links
and Tools/Option/Calculation and Edit, then the system
will ask you if you want to update links(see Help menu -
Control When Links are updated). "Click on Update Links".
The trouble is the links are not always updated and there
doesn't seem to be a way to force the update. Which means
you have to do it manually.
I need this to happen because I create the Sources and
many users use the Destinations. They shouldn't have to
and don't know how to mannually update. In your situation
you can manually update but it would be a hastle.
If you determine how to make the update work automatically
or by forcing it let me know.
 
Top