Help! I have tried replacing values with the INDIRECT function and
referencing it to a cell, but it will not work. Perhaps it's the complexity
of my formula? I have to use the If statement so as to get zeros when there
isn't a valid value. Here is my formula that I need to use the INDIRECT
function with, so that I may change the date in the file name only.
=IF(VLOOKUP(C6,'H:\credit\weekly agings\[Master Cust Aging
Data011606.xls]ARCust#
Data'!$A$5:$O$2000,5,FALSE)="",0,(VLOOKUP(C6,'H:\c redit\weekly agings\[Master
Cust Aging Data011606.xls]ARCust# Data'!$A$5:$O$2000,5,FALSE)))

Any ideas?
I have spent over an hour trying to get one formula to work, and I can't
waste anymore time on it today, so it's back to column by column until I can
figure it out or something else.

"Bruizer8" wrote:

I will check this out. Thank you very much for the idea! (I've never used
the indirect function before.) Brenda

"Kevin Vaughn" wrote:

Perhaps you can use the indirect function so that instead of changing all
formulas everytime, you only need to change one cell (which indirect will
then point at.) Let me see if I can find an example where I have done
similar ...
Couldn't find one but here is one I just created to do a vlookup on a sheet
that was referenced by cell B68.

=VLOOKUP(A69, INDIRECT( B68 & "!a1:f50"),2,FALSE)

Would need a couple changes if there were spaces in the sheet name.

=VLOOKUP(A71, INDIRECT("'" & B68 & "'!a1:b18"),2,FALSE)


--
Kevin Vaughn


"Bruizer8" wrote:

I prepare reports that have VLOOKUP formulas that pull data from another
file. Each time I create a new report, I pull the data from a new file where
the only change is the date in the file name looking to.
First, it seems to work better if I do the "Ctrl ~" to show the formulas
first, then highlight the range of formulas I want to change.....then I did
the Find/Replace to change the date.
Lately it has not been working on "replace all" at all. Even with less than
1,000 formulas to change, when doing "replace all" it stops responding
completely. I have to do "replace" and sit there clicking enter for each
cell for it to work.
As a work around, I have changed the top formula and have been copying it
down on each row, which still takes forever.
What can I do to get the Find/Replace feature to work as it should?