Dynamically refer to another sheet and data check

[sxschech]

Given the following:

Cell E4: ='\\Discimageserver\Stats\[STATS-01-26-2009-updated.xls]UG_DOMInt'!E4

How can I change it so that if I put

='\\Discimageserver\Stats\[STATS-01-26-2009-updated.xls]

into Cell A1 and be able to change

[STATS-01-26-2009-updated.xls]

to a different name, Cell E4 will be able to refer to the new file name. This spreadsheet has many cells refering to the filename and I would like to be able to change the filename as needed to do the data checking on all the cells without having to manually do a find and replace each time.

I tried

=A1&UG_DOMInt'!E4

but it came out as #REF.


Also, is there a function that can look at a list of numbers and identify if there is no match.

I can do E4=F4 which evaluates to TRUE or FALSE
How could I do it with E4 through Q4

For Example each cell should contain 350, so if on of the cells has a different number, I would see a false, otherwise true.

At the moment, I have done the two cell comparison,
=E4=F4
=E4=G4
=E4=H4
which is better from the standpoint of identifying specifically which cell may have a problem, but it would also be good to check overall as what if the cell with the problem really is E4.

Thanks

 

[SkipVought]

Hi,

Check out the INDIRECT function.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[sxschech]

Hi Skip,

Looks like the INDIRECT will work, however, not sure how I can easily update the formula globally. Hopefully there is a method to change without having to do each cell one by one. Since I need to wrap the function in parens and quotes.

I changed the formual below from

='\\Discimageserver\Stats\[STATS-01-26-2009-updated.xls]UG_DOMInt'!E3

to

=[red]INDIRECT("'\\Discimageserver\Stats\[" & E1 & "][/red]UG_DOMInt'!E3[red]")[/red]

Any thoughts how I can change the rest of them with find and replace or a macro?

Other formula examples to be changed that are a different pattern than the above:

='\\Discimageserver\Stats\[STATS-01-26-2009-updated.xls]UG_DOMInt'!F3+'\\Discimageserver\Stats\[STATS-01-26-2009-updated.xls]UG_DOMInt'!G3

=SUM('\\Discimageserver\Stats\[STATS-01-26-2009-updated.xls]UG_Eth'!F44)

=SUM('\\Discimageserver\Stats\[STATS-01-26-2009-updated.xls]UG_EthGend'!F5:G5,'\\Discimageserver\Stats\[STATS-01-26-2009-updated.xls]UG_EthGend'!J5)


Thanks