Indirect concatenated link doesn't work with UNC

Excel 2003 SP3. ARRRGGGHHHH! I need to sum multiple cells in workbook A into 
one cell in workbook B, where part of path to workbook A is a variable input 
by the user. If I use 
=INDIRECT("'x:\folder\folder\"&input&"\[filename.xls]worksheet_name'!cell")+INDIRECT(same 
thing, different cell), all is well. If I change x: to the UNC path 
(\\server\sharename, and I know I have the right server and share names) I 
get #REF! error. Workbook A is already open in the same instance of Excel. 

To make it even more frustrating, if I copy the cell that doesn't work, then 
edit the copy changing x: to \\server\share, and hard code in the variable 
instead of concatenating, it works again.  

From reading other posts, this should work. What am I missing? 
0
lynn1 (103)
5/20/2009 6:10:01 PM
excel 39879 articles. 2 followers. Follow

9 Replies
995 Views

Similar Articles

[PageSpeed] 12

If workbook A (the sending workbook, right?) is open then you don't need the
path (UNC or mapped drive).

And if the sending workbook is closed, then the =indirect() won't work anyway.



Lynn wrote:
> 
> Excel 2003 SP3. ARRRGGGHHHH! I need to sum multiple cells in workbook A into
> one cell in workbook B, where part of path to workbook A is a variable input
> by the user. If I use
> =INDIRECT("'x:\folder\folder\"&input&"\[filename.xls]worksheet_name'!cell")+INDIRECT(same
> thing, different cell), all is well. If I change x: to the UNC path
> (\\server\sharename, and I know I have the right server and share names) I
> get #REF! error. Workbook A is already open in the same instance of Excel.
> 
> To make it even more frustrating, if I copy the cell that doesn't work, then
> edit the copy changing x: to \\server\share, and hard code in the variable
> instead of concatenating, it works again.
> 
> From reading other posts, this should work. What am I missing?

-- 

Dave Peterson
0
petersod (12005)
5/20/2009 7:23:50 PM
Dave, That doesn't accomplish my goal.    I can't enter a reference to an 
external cell without either the path, or another workbook open (A, in this 
case). But if I open workbook A first, then set up the formulae, even without 
specifying the path it eventually hard codes to that particular A, no matter 
what order I close or save things in. I need it to go to whatever version of 
that workbook A is open.

Here's what I'm trying to do: A and B both summarize the same information, 
just differently. A is generated by another program, B is our standard 
in-house summary. Information in B is organized so as to match input required 
by another department; A is not. Output of the other program is organized so 
that every project has its own folder, and each folder has workbook A in it 
(all named identically), but with data for that project. Path to every A is 
identical except for the project number, including file name. I need to set 
up B so that it will pull numbers from the A file in the path specified by 
the user's input of project number. 

Did I explain that well enough? And if so, how do I accomplish it, other 
than use the one method I've found so far that works and make sure all users 
have the same drive letter mapped to the source share? 

Thanks again....

"Dave Peterson" wrote:

> If workbook A (the sending workbook, right?) is open then you don't need the
> path (UNC or mapped drive).
> 
> And if the sending workbook is closed, then the =indirect() won't work anyway.
> 
> 
> 
> Lynn wrote:
> > 
> > Excel 2003 SP3. ARRRGGGHHHH! I need to sum multiple cells in workbook A into
> > one cell in workbook B, where part of path to workbook A is a variable input
> > by the user. If I use
> > =INDIRECT("'x:\folder\folder\"&input&"\[filename.xls]worksheet_name'!cell")+INDIRECT(same
> > thing, different cell), all is well. If I change x: to the UNC path
> > (\\server\sharename, and I know I have the right server and share names) I
> > get #REF! error. Workbook A is already open in the same instance of Excel.
> > 
> > To make it even more frustrating, if I copy the cell that doesn't work, then
> > edit the copy changing x: to \\server\share, and hard code in the variable
> > instead of concatenating, it works again.
> > 
> > From reading other posts, this should work. What am I missing?
> 
> -- 
> 
> Dave Peterson
> 
0
lynn1 (103)
5/20/2009 9:35:01 PM
First, you can't refer to a cell in a different file if that sending file is
closed and use =indirect().

Second, the stuff ("'x:\folder\folder\") inside the =indirect() function is a
string.  This would always point at the X:\folder\folder location.

I tried this in a test workbook (book1.xls).

I created a new workbook and put "Root" in A1 of sheet1.
Then I saved this workbook as C:\book2.xls

Then I changed A1 to Excel and saved a copy in C:\excel

Then in A1 of Book1.xls, I added this formula.
=INDIRECT("[book2.xls]Sheet1!$A$1")

It returned Excel (since that version of book2.xls was open).

I closed c:\excel\book2.xls and recalculated.  I got a #REF! error
(recalculating is important).

Then I opened c:\book2.xls and saw that Root was returned.

So you could drop all the drive/path info from your formulas as long as you
explain that the user MUST open the correct file first.

If they open a file with the same name, but from a different folder, excel will
go happily along.  Your users won't get the results they want, but excel won't
care.

==================
On the other hand, if you want to keep the sending workbook closed, you can't
use =indirect() in your function call.

That means you'll have to either tell them to create the formula themselves (or
provide a macro that does the work for them).

Or you could use a function called =indirect.ext() written by Laurent Longre. 
(Everyone will have to have a copy of this function/addin, too.)

You can find it in the morefunc.xll addin:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

(I didn't test this, so you'll have to watch out for typos.)

=INDIRECT.ext("'" & $a$1 & $b$1 & $C$1 & "\[filename.xls]Sheet1'!A1")

Where 
A1 holds the drive (either X: or the UNC share--\\something\otherthing)
B1 holds the folder (\folder\folder)
C1 holds the input (whatever that is)




Lynn wrote:
> 
> Dave, That doesn't accomplish my goal.    I can't enter a reference to an
> external cell without either the path, or another workbook open (A, in this
> case). But if I open workbook A first, then set up the formulae, even without
> specifying the path it eventually hard codes to that particular A, no matter
> what order I close or save things in. I need it to go to whatever version of
> that workbook A is open.
> 
> Here's what I'm trying to do: A and B both summarize the same information,
> just differently. A is generated by another program, B is our standard
> in-house summary. Information in B is organized so as to match input required
> by another department; A is not. Output of the other program is organized so
> that every project has its own folder, and each folder has workbook A in it
> (all named identically), but with data for that project. Path to every A is
> identical except for the project number, including file name. I need to set
> up B so that it will pull numbers from the A file in the path specified by
> the user's input of project number.
> 
> Did I explain that well enough? And if so, how do I accomplish it, other
> than use the one method I've found so far that works and make sure all users
> have the same drive letter mapped to the source share?
> 
> Thanks again....
> 
> "Dave Peterson" wrote:
> 
> > If workbook A (the sending workbook, right?) is open then you don't need the
> > path (UNC or mapped drive).
> >
> > And if the sending workbook is closed, then the =indirect() won't work anyway.
> >
> >
> >
> > Lynn wrote:
> > >
> > > Excel 2003 SP3. ARRRGGGHHHH! I need to sum multiple cells in workbook A into
> > > one cell in workbook B, where part of path to workbook A is a variable input
> > > by the user. If I use
> > > =INDIRECT("'x:\folder\folder\"&input&"\[filename.xls]worksheet_name'!cell")+INDIRECT(same
> > > thing, different cell), all is well. If I change x: to the UNC path
> > > (\\server\sharename, and I know I have the right server and share names) I
> > > get #REF! error. Workbook A is already open in the same instance of Excel.
> > >
> > > To make it even more frustrating, if I copy the cell that doesn't work, then
> > > edit the copy changing x: to \\server\share, and hard code in the variable
> > > instead of concatenating, it works again.
> > >
> > > From reading other posts, this should work. What am I missing?
> >
> > --
> >
> > Dave Peterson
> >

-- 

Dave Peterson
0
petersod (12005)
5/20/2009 10:08:21 PM
Lynn <L...@discussions.microsoft.com> wrote..
....
> . . .=A0I can't enter a reference to an
>external cell without either the path, or another workbook open (A, in thi=
s
>case). But if I open workbook A first, then set up the formulae, even with=
out
>specifying the path it eventually hard codes to that particular A, no matt=
er
>what order I close or save things in. I need it to go to whatever version =
of
>that workbook A is open.
....

If you create STATIC external references into OPEN workbooks, they'll
omit the workbook's path. When you close that workbook, Excel will
include the path to the workbook in the external references. That
*ONLY* works for STATIC external references.

Dynamic external references using INDIRECT can *ONLY* refer to ranges
in OPEN workbooks.

This is a syntactic limitation of Excel. Excel resolved static and
dynamic external references into OPEN workbooks as range objects, but
it resolves static external references into CLOSED workbooks as single
values or arrays BUT NOT RANGE OBJECTS. Excel's INDIRECT functions can
ONLY return range objects, and that's the reason why it can't resolve
external references into closed workbooks.

You mention that you have a working solution that just requires that
all users employ the same drive mapping. I'm not sure I see how that
would work when users change product IDs. If the workbooks you need to
access are all named A.xls but in different directories, if they're
OPEN, then formulas like

=3DINDIRECT("'[A.xls]XYZ'!C5")

will refer to the XYZ!C5 cell in whichever A.xls is open, no matter
what that A.xls file's path may be. OTOH, if no A.xls file is open,
it'll return #REF!, and there's no way to have it return anything else.
0
hrlngrv1 (375)
5/20/2009 10:17:08 PM
ps.

Another way to do this is to provide a macro that would build the formula based
on values that the user supplied--maybe in a userform or maybe in cells.

Then plop those "prebuilt" formulas into the range.  They would be no need for
the =indirect() or =indirect.ext() functions.  The macro would build the correct
formula just like there was only once choice.

You could give them the cells (with instructions/data|Validation) and when
they've finished, they could click a button to populate the cells with the
formulas that retrieve the values from the specified location.

======
Another way if there are only a few workbooks and those workbooks don't change
name/location.

Populate another worksheet (hide it when you're done).  Then the cells you
really want populated can retrieve the data from this hidden worksheet.

This may work if there isn't too much data to retrieve.  But once you start
retrieving more than a little data across a network, you may find that it seems
to take forever.



Dave Peterson wrote:
<<snipped>>
0
petersod (12005)
5/20/2009 10:17:51 PM
OK, so what I'm missing is apparently a few brain cells. It's been one of 
those weeks. I'm still missing Monday, think yesterday was Monday and today 
Tuesday. Guess that's better than thinking today's Thursday! 

In your prior post, I missed the thought that I should still use Indirect 
but drop out the path. My bad - big time. How complicated can I try to make 
something this simple?

Some of your other ideas are no-go's. There are over 500 lines in this 
summary sheet (B), many imports from the 'workbook A' all over the place, and 
most of them sum numbers from various cells in A into one cell in B. So to do 
it behind the scenes requires building a table listing, for each cell in B, 
what cells in A to sum into it, then running however many iterations of the 
value lookup are required, then summing those results - in addition to 
getting project number and other information. And having users do their own 
formulas? I'm just going to have them open the source by going through the 
other program. Trying to explain how to navigate to it through Excel or 
Explorer would be challenging.

An option I may explore down the road, once I get this working by the brute 
force open-it-first method, is an 'on open' macro in B which asks for project 
number then opens the correct version of A based on the input. Of course, 
they'd need to know project number - and we know our projects by name, only 
that other software package refers to it by the number. So they'd still have 
to open the other software to get the project number!

I still don't know why the indirect function wouldn't work with UNC but did 
work with mapped drive. That would make life easier. But I can live with this.

THANKS - both for the help and for beating me over the head until the 
lightbulb went on.

"Dave Peterson" wrote:

> First, you can't refer to a cell in a different file if that sending file is
> closed and use =indirect().
> 
> Second, the stuff ("'x:\folder\folder\") inside the =indirect() function is a
> string.  This would always point at the X:\folder\folder location.
> 
> I tried this in a test workbook (book1.xls).
> 
> I created a new workbook and put "Root" in A1 of sheet1.
> Then I saved this workbook as C:\book2.xls
> 
> Then I changed A1 to Excel and saved a copy in C:\excel
> 
> Then in A1 of Book1.xls, I added this formula.
> =INDIRECT("[book2.xls]Sheet1!$A$1")
> 
> It returned Excel (since that version of book2.xls was open).
> 
> I closed c:\excel\book2.xls and recalculated.  I got a #REF! error
> (recalculating is important).
> 
> Then I opened c:\book2.xls and saw that Root was returned.
> 
> So you could drop all the drive/path info from your formulas as long as you
> explain that the user MUST open the correct file first.
> 
> If they open a file with the same name, but from a different folder, excel will
> go happily along.  Your users won't get the results they want, but excel won't
> care.
> 
> ==================
> On the other hand, if you want to keep the sending workbook closed, you can't
> use =indirect() in your function call.
> 
> That means you'll have to either tell them to create the formula themselves (or
> provide a macro that does the work for them).
> 
> Or you could use a function called =indirect.ext() written by Laurent Longre. 
> (Everyone will have to have a copy of this function/addin, too.)
> 
> You can find it in the morefunc.xll addin:
> http://xcell05.free.fr/
> or
> http://xcell05.free.fr/morefunc/english/index.htm
> 
> (I didn't test this, so you'll have to watch out for typos.)
> 
> =INDIRECT.ext("'" & $a$1 & $b$1 & $C$1 & "\[filename.xls]Sheet1'!A1")
> 
> Where 
> A1 holds the drive (either X: or the UNC share--\\something\otherthing)
> B1 holds the folder (\folder\folder)
> C1 holds the input (whatever that is)
> 
> 
> 
> 
> Lynn wrote:
> > 
> > Dave, That doesn't accomplish my goal.    I can't enter a reference to an
> > external cell without either the path, or another workbook open (A, in this
> > case). But if I open workbook A first, then set up the formulae, even without
> > specifying the path it eventually hard codes to that particular A, no matter
> > what order I close or save things in. I need it to go to whatever version of
> > that workbook A is open.
> > 
> > Here's what I'm trying to do: A and B both summarize the same information,
> > just differently. A is generated by another program, B is our standard
> > in-house summary. Information in B is organized so as to match input required
> > by another department; A is not. Output of the other program is organized so
> > that every project has its own folder, and each folder has workbook A in it
> > (all named identically), but with data for that project. Path to every A is
> > identical except for the project number, including file name. I need to set
> > up B so that it will pull numbers from the A file in the path specified by
> > the user's input of project number.
> > 
> > Did I explain that well enough? And if so, how do I accomplish it, other
> > than use the one method I've found so far that works and make sure all users
> > have the same drive letter mapped to the source share?
> > 
> > Thanks again....
> > 
> > "Dave Peterson" wrote:
> > 
> > > If workbook A (the sending workbook, right?) is open then you don't need the
> > > path (UNC or mapped drive).
> > >
> > > And if the sending workbook is closed, then the =indirect() won't work anyway.
> > >
> > >
> > >
> > > Lynn wrote:
> > > >
> > > > Excel 2003 SP3. ARRRGGGHHHH! I need to sum multiple cells in workbook A into
> > > > one cell in workbook B, where part of path to workbook A is a variable input
> > > > by the user. If I use
> > > > =INDIRECT("'x:\folder\folder\"&input&"\[filename.xls]worksheet_name'!cell")+INDIRECT(same
> > > > thing, different cell), all is well. If I change x: to the UNC path
> > > > (\\server\sharename, and I know I have the right server and share names) I
> > > > get #REF! error. Workbook A is already open in the same instance of Excel.
> > > >
> > > > To make it even more frustrating, if I copy the cell that doesn't work, then
> > > > edit the copy changing x: to \\server\share, and hard code in the variable
> > > > instead of concatenating, it works again.
> > > >
> > > > From reading other posts, this should work. What am I missing?
> > >
> > > --
> > >
> > > Dave Peterson
> > >
> 
> -- 
> 
> Dave Peterson
> 
0
lynn1 (103)
5/20/2009 11:25:01 PM
Glad things are sorted out.

You could have a macro that runs when the user opens the file and prompt the
user for the name/location of the "sending" file--or just sits there waiting for
them to click a button when they know what to use.

Good luck.





Lynn wrote:
> 
> OK, so what I'm missing is apparently a few brain cells. It's been one of
> those weeks. I'm still missing Monday, think yesterday was Monday and today
> Tuesday. Guess that's better than thinking today's Thursday!
> 
> In your prior post, I missed the thought that I should still use Indirect
> but drop out the path. My bad - big time. How complicated can I try to make
> something this simple?
> 
> Some of your other ideas are no-go's. There are over 500 lines in this
> summary sheet (B), many imports from the 'workbook A' all over the place, and
> most of them sum numbers from various cells in A into one cell in B. So to do
> it behind the scenes requires building a table listing, for each cell in B,
> what cells in A to sum into it, then running however many iterations of the
> value lookup are required, then summing those results - in addition to
> getting project number and other information. And having users do their own
> formulas? I'm just going to have them open the source by going through the
> other program. Trying to explain how to navigate to it through Excel or
> Explorer would be challenging.
> 
> An option I may explore down the road, once I get this working by the brute
> force open-it-first method, is an 'on open' macro in B which asks for project
> number then opens the correct version of A based on the input. Of course,
> they'd need to know project number - and we know our projects by name, only
> that other software package refers to it by the number. So they'd still have
> to open the other software to get the project number!
> 
> I still don't know why the indirect function wouldn't work with UNC but did
> work with mapped drive. That would make life easier. But I can live with this.
> 
> THANKS - both for the help and for beating me over the head until the
> lightbulb went on.
> 
> "Dave Peterson" wrote:
> 
> > First, you can't refer to a cell in a different file if that sending file is
> > closed and use =indirect().
> >
> > Second, the stuff ("'x:\folder\folder\") inside the =indirect() function is a
> > string.  This would always point at the X:\folder\folder location.
> >
> > I tried this in a test workbook (book1.xls).
> >
> > I created a new workbook and put "Root" in A1 of sheet1.
> > Then I saved this workbook as C:\book2.xls
> >
> > Then I changed A1 to Excel and saved a copy in C:\excel
> >
> > Then in A1 of Book1.xls, I added this formula.
> > =INDIRECT("[book2.xls]Sheet1!$A$1")
> >
> > It returned Excel (since that version of book2.xls was open).
> >
> > I closed c:\excel\book2.xls and recalculated.  I got a #REF! error
> > (recalculating is important).
> >
> > Then I opened c:\book2.xls and saw that Root was returned.
> >
> > So you could drop all the drive/path info from your formulas as long as you
> > explain that the user MUST open the correct file first.
> >
> > If they open a file with the same name, but from a different folder, excel will
> > go happily along.  Your users won't get the results they want, but excel won't
> > care.
> >
> > ==================
> > On the other hand, if you want to keep the sending workbook closed, you can't
> > use =indirect() in your function call.
> >
> > That means you'll have to either tell them to create the formula themselves (or
> > provide a macro that does the work for them).
> >
> > Or you could use a function called =indirect.ext() written by Laurent Longre.
> > (Everyone will have to have a copy of this function/addin, too.)
> >
> > You can find it in the morefunc.xll addin:
> > http://xcell05.free.fr/
> > or
> > http://xcell05.free.fr/morefunc/english/index.htm
> >
> > (I didn't test this, so you'll have to watch out for typos.)
> >
> > =INDIRECT.ext("'" & $a$1 & $b$1 & $C$1 & "\[filename.xls]Sheet1'!A1")
> >
> > Where
> > A1 holds the drive (either X: or the UNC share--\\something\otherthing)
> > B1 holds the folder (\folder\folder)
> > C1 holds the input (whatever that is)
> >
> >
> >
> >
> > Lynn wrote:
> > >
> > > Dave, That doesn't accomplish my goal.    I can't enter a reference to an
> > > external cell without either the path, or another workbook open (A, in this
> > > case). But if I open workbook A first, then set up the formulae, even without
> > > specifying the path it eventually hard codes to that particular A, no matter
> > > what order I close or save things in. I need it to go to whatever version of
> > > that workbook A is open.
> > >
> > > Here's what I'm trying to do: A and B both summarize the same information,
> > > just differently. A is generated by another program, B is our standard
> > > in-house summary. Information in B is organized so as to match input required
> > > by another department; A is not. Output of the other program is organized so
> > > that every project has its own folder, and each folder has workbook A in it
> > > (all named identically), but with data for that project. Path to every A is
> > > identical except for the project number, including file name. I need to set
> > > up B so that it will pull numbers from the A file in the path specified by
> > > the user's input of project number.
> > >
> > > Did I explain that well enough? And if so, how do I accomplish it, other
> > > than use the one method I've found so far that works and make sure all users
> > > have the same drive letter mapped to the source share?
> > >
> > > Thanks again....
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > If workbook A (the sending workbook, right?) is open then you don't need the
> > > > path (UNC or mapped drive).
> > > >
> > > > And if the sending workbook is closed, then the =indirect() won't work anyway.
> > > >
> > > >
> > > >
> > > > Lynn wrote:
> > > > >
> > > > > Excel 2003 SP3. ARRRGGGHHHH! I need to sum multiple cells in workbook A into
> > > > > one cell in workbook B, where part of path to workbook A is a variable input
> > > > > by the user. If I use
> > > > > =INDIRECT("'x:\folder\folder\"&input&"\[filename.xls]worksheet_name'!cell")+INDIRECT(same
> > > > > thing, different cell), all is well. If I change x: to the UNC path
> > > > > (\\server\sharename, and I know I have the right server and share names) I
> > > > > get #REF! error. Workbook A is already open in the same instance of Excel.
> > > > >
> > > > > To make it even more frustrating, if I copy the cell that doesn't work, then
> > > > > edit the copy changing x: to \\server\share, and hard code in the variable
> > > > > instead of concatenating, it works again.
> > > > >
> > > > > From reading other posts, this should work. What am I missing?
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

-- 

Dave Peterson
0
petersod (12005)
5/21/2009 12:05:29 AM
I take it back, it doesn't entirely solve the problem. Saving B doesn't save 
the values that were calculated, just the formula/function. And if the source 
workbook A goes away (for example, is archived off active server), we won't 
be able to retrieve the summary for the job as quoted if we need to refer 
back to it. 

Another potential issue is that some projects will involve pulling numbers 
from 2 different summary spreadsheets, which are in 2 different folders. I 
don't yet know what the name of the 2nd summary spreadsheet is. However, if 
it's named the same as the first (but in a different path, such as 
\screws\A.xls and \nails\A.xls), the current method has no way to specify 
which of the Workbook A's to pull from, will just pull from whichever is open 
at the time. I can work around this by coding the path into each cell, but 
then I need to make sure everyone is mapped the same (since I never solved 
the issue of not being able to use the UNC in the indirect function). 

That stil leaves the issue of saving the values into the cells instead of 
saving the formula when the workbook is saved. I had a thought that I could 
create a hidden version of the summary worksheet, where I actually have all 
the formulas, then have a macro which would use copy/paste special/value from 
the hidden worksheet to the visible one. If I did this, would it just be a 
huge macro with a line in the macro for each cell needing a value? Or is 
there some other way to do this? Can I attach the macro to only certain cells 
in a worksheet? And what would be the most user-proof way to make sure this 
macro ran after all the values were updated in the hidden worksheet? 




> 
0
lynn1 (103)
5/21/2009 1:09:08 AM
If I were going to have a macro that converted formulas to values, I'd have the
same macro do all the work.  I'd have it build the formulas, populate the cells
and convert the formulas to values.

I don't have guess how intricate the macro would be.  But if your data is laid
out nicely, it could be as straightforward as filling the range with a single
formula.  If you write the formula nicely, then excel will adjust it just like
it does when you do it manually--select a range, write the formula for the
activecell and hit control-enter to fill all the cells in the range.



Lynn wrote:
> 
> I take it back, it doesn't entirely solve the problem. Saving B doesn't save
> the values that were calculated, just the formula/function. And if the source
> workbook A goes away (for example, is archived off active server), we won't
> be able to retrieve the summary for the job as quoted if we need to refer
> back to it.
> 
> Another potential issue is that some projects will involve pulling numbers
> from 2 different summary spreadsheets, which are in 2 different folders. I
> don't yet know what the name of the 2nd summary spreadsheet is. However, if
> it's named the same as the first (but in a different path, such as
> \screws\A.xls and \nails\A.xls), the current method has no way to specify
> which of the Workbook A's to pull from, will just pull from whichever is open
> at the time. I can work around this by coding the path into each cell, but
> then I need to make sure everyone is mapped the same (since I never solved
> the issue of not being able to use the UNC in the indirect function).
> 
> That stil leaves the issue of saving the values into the cells instead of
> saving the formula when the workbook is saved. I had a thought that I could
> create a hidden version of the summary worksheet, where I actually have all
> the formulas, then have a macro which would use copy/paste special/value from
> the hidden worksheet to the visible one. If I did this, would it just be a
> huge macro with a line in the macro for each cell needing a value? Or is
> there some other way to do this? Can I attach the macro to only certain cells
> in a worksheet? And what would be the most user-proof way to make sure this
> macro ran after all the values were updated in the hidden worksheet?
> 
> >

-- 

Dave Peterson
0
petersod (12005)
5/21/2009 2:13:16 AM
Reply:

Similar Artilces:

How do I remove empty chart plots from linked worksheet charts
Using Excel 2003, I have a workbook with a master worksheet and an associated line chart, also several derivative worksheets with associated individual line charts. The other worksheets are all linked to their respective columns on the master sheet so that as the master is periodically updated, the others update automatically. The problem is that currently empty cells on the master sheet are displayed as zero's on the linked worksheets and subsequently plotted on their individual respective charts. The zero's can be removed from both the linked worksheets and their respective ...

IIf works in query but not in report
Does the IIf function work differently in queries from the way it works in reports? For example, this works in a query: lastName: IIf([Last]="Smith",Null,([Last])) (I can hid the last name "Smith" in the lastName field.) However, I cannot use the same IIf as a control source in a report. It generates a circular reference error message. = IIf([Last]="Smith",Null,([Last])) (If the name is not "Smith", then print it.) Is this just how IIf works? ...

Remove
I have a problem with a server install in which the IIS configuration is corrupt. Outlook clients can still connect but the Internet explorer client don’t work anymore. Is it possible to uninstall the CRM server and install it again. I will leave the database intact. Are the Outlook clients affected? Server is windows 2000 - IIS 5 yes you can uninstall the crm web server and reinstall then point to an existing database ie the sql database. Make sure your outlook clients go into online mode in case they have any data that needs synching however you may want to try and troubleshoot thi...

OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Unspecified error".
I want to update with same tables an online sql server with local data of a software. The online and local tables structures are the same, the name of the table also. I receive this error: Errore -2147217900 OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Unspecified error". the code is: set connACC=Server.createobject("adodb.connection") connNostrosql1 = "Provider=SQLOLEDB;Data Source=**.**.**.**;database=030918ferwoodsrl;uid=**;pwd=**;" connACC.Open connNostrosql1 INSERT INTO MAGAnagrafica...

work in 2003 mode if I have 2007?
Is it possible to work in 2003 mode if I have PPT 2007? I don't mean working in a 2003 document and saving it down to 2003. I need to create an organization chart in PPT 2007, but my custmer only has 2003, and they need to edit it. when I save it down to 2003, it is not editable. -- Marc Saving it as a 2003 file shouldn't make it un-editable, but why not just have your customer load the file converter, so they can open the 2007 doc. They would not have all the new effects or features of 2007, so you would need to design it accordingly. Compatibility Pack: http...

Compact & Repair, It Works and then It Doesn't... Help?!?
I have run into a problem with the "compact and repair" feature on my Access 2003. Recently I had my PC upgraded and had my IT PC Technician reload Office Professional and all upgrades, so it is current in that department. My PC is part of a distributed network and I access and administer databases across that same distributed network. The problem is that when I try to compact and repair (C&R) my databases I have some mixed results. 1) On my local drives, it works fine. 2) On the network drives/shares I have a problem that the compact and repair works on some dri...

Windows Media Player (WMP) ripping errors/problems/doesn't work
To put it simply, my WMP's ripping is malfunctioning. The details are as the following. At some point, when I tried to rip a CD into my hard drive using WMP, it didn't work properly. The ripping process all looked fine, and WMP didn't pop up any error signs or such; the ripping process looked completely normal and was completed. The problem started when I tried to play the ripped music. The audio files were present in the folder I was looking for, and I tried to play them. But WMP won't play them, saying this: "Windows Media Player cannot play the file. Th...

customise outlook today not working
when I press on the customise outlook today button nothing happens, the customisation screen does not load. I have run a detect and repair, reinstalled outlook 2000 over the top and also recreated my outlook profile. Someone else logged into the pc and it doesnt work for them either (but they can do it on their pc) so I'm thinking its a registry hack somewhere, any ideas? The following MSKB article provides the fix for this issue. OL2000: You Cannot Customize Outlook Today After You Install Critical Update 813489 for Internet Explorer: http://support.microsoft.com/default.aspx?s...

linking to documents
I am creating a webapge in publisher. I have buttons on a navigation bar. I can link those buttons to existing webpages or "places within this document". However, if I try to link one to a file, say a word.doc file it appears the link was successful. When I do a webpage preview, however, and click the button, it doesn't seem to activate???? On Wed, 3 Aug 2005 16:11:21 +0100, scrabtree23 wrote (in article <110F6DC0-F3BD-4414-A4C2-077D9E4081C6@microsoft.com>): > I am creating a webapge in publisher. I have buttons on a navigation bar. I > can link those ...

Open link in Excel, not Windows explorer
We have an Intrnet search which locates specific excel files on our network. When you click on the link to the workbook, it open the workbok in Windows Explorer, not actually launching Excel. This is a problem because among other things, print preview is not available. How can we alter this so that when you click on the hyper link, it will launch Excel? :rolleyes: -- amthyst826 ------------------------------------------------------------------------ amthyst826's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28308 View this thread: http://www.excelforum.com/s...

How do I change how links in E-mails are opened? Outlook 2007
I'm using Outlook 2007 and whenever I click a link in an e-mail that goes to a website it tries to open it in the Outlook window instead of using my internet browser. This REALLY bugs me. Is there any way the change this so that the link will open in a new IE or Firefox window? Microsoft Help has been useless and I can't seem to find a solution online anywhere. What is the program associated with web pages under Folder Options->File Types? -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. ALWAYS post your Outlook versi...

pdf links #2
how do i create a link that when you click on it, it will open the pdf document in a new window? ...

Outlook 2002 Won't Snd/Rcv but Test Settings Works
Just recently, when I press the send/receive button, it fails to do so. It's a hit or miss occurance, happening for a while every day for the last week. When I check my account settings, they're all exactly as they should be (other computers on the same network use the same settings and have no issues). When I hit the 'Test Account Settings' button, it works perfectly. Yet when I go to check for the test mail, the send/receive no longer works (I checked my mail using webmail and the test emails were there). Basically I've been able to find no correlation with...

2005: Update Retirement Account working OK for others?
Every month I use the somewhat tedious Update Retirement Account wizard to enter the month's 401(k) transactions. Since upgrading to Money 2005, I get an error at the end of the process when I click the Finish button: "Your information is being saved. If the problem persists..." When I click OK, none of my data is saved. Anyone else having the same problem? I've tried everything...work offline, delete the online data, recreate the online data, etc. Thanks! -Doug Yes, I'm experiencing the same problem with Money 2005 Premium on Windows XP with Service Pack 2. I...

formula adding cells in worksheets when # of sheets in work book changes
I have a workbook template which contains a worksheet for each day o the month and a summary sheet which totals cells from the dail worksheets. The problem is that each month does not have the sam number of days, so the cells of the summary sheet needs to have th range changed when there is a different number of days. Is there a way to write a formula to include only worksheets that ar present - ie when a new file is created from the template i automatically has worksheets for 31 days - and a worksheet will b deleted if there are only 30 days. Is there a way to create a formul so that the f...

work flow wait condition is not working for datetime field added to entity in MS CRM
Microsoft CRM : Does the work flow wait condition work for a customised(added) datetime field? This is not working for datetime field added to case entity? any help will be appreciated ...

97 to 2003 conversion
I have converted a 97 mdb to 2003 that uses access security. Everthing I've read states that the mdw file can still be used in 2003. When I join the MDW and open the database I get a Runtime Error '3112' no permission to MsysAccounts. I can open the security/user and group accounts and no users are listed. If I try to add one of the uses it says the user already exists. Any ideas? > no permission MsysAccounts Bad news I'm afraid. It's an unfixed bug resulting from a system.mdw ORIGINALLY being converted from an Access2.0 system.mda. The bug manifests from Ac...

Concatenate spaces before each alt-enter seperated value in a cell?
I hope I am making sense. Within one cell, I have several values seperated by alt-enter. So it looks this: abcdef <alt-enter> abcdef <alt-enter> abcedf <alt-enter> I want spaces before each "abcdef" so it will look like this: abcdef <alt-enter> abcdef <alt-enter> abcedf <alt-enter> This gets close: =REPT(" ",4)&SUBSTITUTE(A1,CHAR(10),CHAR(10)&REPT(" ",4)) But it actually adds extra characters at the right end of the string. Change the 4 (in both spots) to how many space characters you want. wa...

Clipboard / Drag&Drop not working
Over the last few weeks both the Office Clipboard and the drag and drop feature have stopped working. The clipboard shows an error message along the lines of "Not collected - format not supported" but I'm only trying to copy a few words from one part of a Word document to another part. A very clumsy workaround is to have two copies of the document open, one in Word and one in Wordpad, and then copy from Wordpad into Word - but it's very clumsy!! Have run Norton virus scan, Spysweeper, used Regedit to delete the Data key. Nothing seems to work. Any fresh i...

refresh linked query
i have a pivot table in an excel sheet that is linked to an access database. i would like to program a macro to refresh the pivot table. i thought the following would work, but it does not: FinalRowFRONT = Cells(Cells.Rows.Count, "A").End(xlUp).Row Range("a" & FinalRowFRONT).Select Selection.QueryTable.Refresh BackgroundQuery:=False how do i get the pivot table to refresh? The macro recorder is you friend :) -- Regards Dave Hawley www.ozgrid.com "joemeshuggah" <joemeshuggah@discussions.microsoft.com> wrote in messa...

Recipient Update policy not working correctly
I am having an issue with creating new users/mailboxes. I can create users normally on a Windows 2003 server w/ Exchange 2003 SP1. However, the user mailboxes and email address are not being generated properly. If we wait they are usually created within a 24 hr period, or if the server is restarted they are created. I have check the update policies and everything appears to be configured correctly. The 2003 server was added to an existing Windows 2000 AD, if that helps at all. Is there anyway to correct this, or a way to manually add the mail settings? Thanks Hi maybe this KB may he...

Synchronized Combo Box Not Working
Hello, I have been working on this issue for hours and have spent many days trying to read the board to solve my issue, but I cannot, which is why I am posting this question. I cannot get my drop down boxes to work. I am simply trying to use two drop down boxes and limit the second drop down box based on a selection I made in the first drop down box. Here is what I have so far: Form Name: fBenchmarkJobDetails RecordSource: tBenchmarkJobDetails Fields in tBenchmarkJobDetails JobCode SvyName_ID SvyEffectiveDate SvyJobCode And so on… First Drop Down Box Control So...

Last Working Day In Month
Hi All What is the formula for the last working day in month? Anyone HELP? Winston. :confused: -- Winston ------------------------------------------------------------------------ Winston's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28344 View this thread: http://www.excelforum.com/showthread.php?threadid=480968 Please define working day... is that a Friday or a Saturday? -- swatsp0p ------------------------------------------------------------------------ swatsp0p's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15101 ...

Enabling Radio button at runtime not working
I have a class derived from CPropertyPage. At design time I am setting RADIO button to DISABLED state. At run time I want to enable this radio button. Both of the fallowing failed. ::SendDlgItemMessage(GetDlgItem(IDC_RADIO2)->m_hWnd,IDC_RADIO2,WM_ENABLE,TRUE,0); and GetDlgItem(IDC_RADIO2)->EnableWindow(1); Pls let me know how to do. Thanks Ramesh <asnowfall@gmail.com> wrote in message news:1103870967.024619.273160@z14g2000cwz.googlegroups.com... > I have a class derived from CPropertyPage. At design time I am setting > RADIO button to DISABLED state. At run time I want...

Also the Edit in Excel button has stopped working
When a user opens a spreadsheet from Companyweb it opens ReadOnly and there is a button which says Edit at the top. Clicking this button has no effect. This worked fine until a few days ago will this help ? http://blogs.technet.com/sbs/archive/2009/05/07/files-in-companyweb-are-opening-read-only-after-sbs-2008-ur2.aspx "gocat2005" <gocat2005@discussions.microsoft.com> wrote in message news:6FF74225-9785-4BE3-97FD-B9007752B4C5@microsoft.com... > When a user opens a spreadsheet from Companyweb it opens ReadOnly and > there > is a button which says ...