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?

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?

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?

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?

Since I haven't been able to figure out how to get Indirect to work, I have
found that the find & replace works better for me if I find & replace the
entire file name path, instead of just the date change within the file name.
I made it thru all my files this monmth without any problems, so maybe that
was the key. Not sure why that would make a difference, but it did. i hope
this helps someone else with the same issue.

"Bruizer8" wrote:

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?

Maybe this will help. Here are two formulas with the only difference being
the use of Indirect:

VLOOKUP(B15,'[Zone 11 Class Dates Months2.xls]Calendar
Notes'!$B$10:$C$16,2,FALSE)
VLOOKUP(B16,INDIRECT("'[Zone " & A16 & " Class Dates Months2.xls]Calendar
Notes'!$B$10:$C$16"), 2, FALSE)

In cell A16 I put the value 11. You could presumably substitute (I believe
it was) your date in the appropriate place.

HTH or your other method continues to work for you.
--
Kevin Vaughn


"Bruizer8" wrote:

Thanks for the speedy response! Actually, I had the file open in the same
excel application, so that couldn't be it. It was probably the syntax I was
using somehow. Oh well, like you said, I got it to work his month, and as
long as that works I'll continue it that way. Thanks for your suggestions.
Brenda

"Kevin Vaughn" wrote:

I believe, looking at your previous post, that you are probably trying to use
Indirect on a closed workbook. If so, then no. As I understand it Indirect
will not work on closed files (that is what I have read. I just have never
tried it.)
It looks like you got something working for at least this month so that is
good. Good luck with future months!
--
Kevin Vaughn


"Bruizer8" wrote:

Since I haven't been able to figure out how to get Indirect to work, I have
found that the find & replace works better for me if I find & replace the
entire file name path, instead of just the date change within the file name.
I made it thru all my files this monmth without any problems, so maybe that
was the key. Not sure why that would make a difference, but it did. i hope
this helps someone else with the same issue.

"Bruizer8" wrote:

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?

I believe, looking at your previous post, that you are probably trying to use
Indirect on a closed workbook. If so, then no. As I understand it Indirect
will not work on closed files (that is what I have read. I just have never
tried it.)
It looks like you got something working for at least this month so that is
good. Good luck with future months!
--
Kevin Vaughn


"Bruizer8" wrote:

Since I haven't been able to figure out how to get Indirect to work, I have
found that the find & replace works better for me if I find & replace the
entire file name path, instead of just the date change within the file name.
I made it thru all my files this monmth without any problems, so maybe that
was the key. Not sure why that would make a difference, but it did. i hope
this helps someone else with the same issue.

"Bruizer8" wrote:

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?

If you want to post your formula and the cell containing the date, I could
take a shot at it. The reason I thought maybe the file was closed was
because the formula I saw had the path included (IIRC.) I don't remember all
of the particulars, but I just glanced at it. Sorry, I didn't see the other
post until today. Or I could just try an indirect formula on one of my
spreadsheets. (If I get time, I see some email that I have to deal with
right now.)
--
Kevin Vaughn


"Bruizer8" wrote:

Thanks for the speedy response! Actually, I had the file open in the same
excel application, so that couldn't be it. It was probably the syntax I was
using somehow. Oh well, like you said, I got it to work his month, and as
long as that works I'll continue it that way. Thanks for your suggestions.
Brenda

"Kevin Vaughn" wrote:

I believe, looking at your previous post, that you are probably trying to use
Indirect on a closed workbook. If so, then no. As I understand it Indirect
will not work on closed files (that is what I have read. I just have never
tried it.)
It looks like you got something working for at least this month so that is
good. Good luck with future months!
--
Kevin Vaughn


"Bruizer8" wrote:

Since I haven't been able to figure out how to get Indirect to work, I have
found that the find & replace works better for me if I find & replace the
entire file name path, instead of just the date change within the file name.
I made it thru all my files this monmth without any problems, so maybe that
was the key. Not sure why that would make a difference, but it did. i hope
this helps someone else with the same issue.

"Bruizer8" wrote:

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?

Thanks for the speedy response! Actually, I had the file open in the same
excel application, so that couldn't be it. It was probably the syntax I was
using somehow. Oh well, like you said, I got it to work his month, and as
long as that works I'll continue it that way. Thanks for your suggestions.
Brenda

"Kevin Vaughn" wrote:

I believe, looking at your previous post, that you are probably trying to use
Indirect on a closed workbook. If so, then no. As I understand it Indirect
will not work on closed files (that is what I have read. I just have never
tried it.)
It looks like you got something working for at least this month so that is
good. Good luck with future months!
--
Kevin Vaughn


"Bruizer8" wrote:

Since I haven't been able to figure out how to get Indirect to work, I have
found that the find & replace works better for me if I find & replace the
entire file name path, instead of just the date change within the file name.
I made it thru all my files this monmth without any problems, so maybe that
was the key. Not sure why that would make a difference, but it did. i hope
this helps someone else with the same issue.

"Bruizer8" wrote:

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?

Hi - Thanks for the reply. Unfortunately I haven't had time to address it.
I will try to get to it in the next couple of weeks. I have a ton of
projects right now. Thaks again! Brenda

"Kevin Vaughn" wrote:

Maybe this will help. Here are two formulas with the only difference being
the use of Indirect:

VLOOKUP(B15,'[Zone 11 Class Dates Months2.xls]Calendar
Notes'!$B$10:$C$16,2,FALSE)
VLOOKUP(B16,INDIRECT("'[Zone " & A16 & " Class Dates Months2.xls]Calendar
Notes'!$B$10:$C$16"), 2, FALSE)

In cell A16 I put the value 11. You could presumably substitute (I believe
it was) your date in the appropriate place.

HTH or your other method continues to work for you.
--
Kevin Vaughn


"Bruizer8" wrote:

Thanks for the speedy response! Actually, I had the file open in the same
excel application, so that couldn't be it. It was probably the syntax I was
using somehow. Oh well, like you said, I got it to work his month, and as
long as that works I'll continue it that way. Thanks for your suggestions.
Brenda

"Kevin Vaughn" wrote:

I believe, looking at your previous post, that you are probably trying to use
Indirect on a closed workbook. If so, then no. As I understand it Indirect
will not work on closed files (that is what I have read. I just have never
tried it.)
It looks like you got something working for at least this month so that is
good. Good luck with future months!
--
Kevin Vaughn


"Bruizer8" wrote:

Since I haven't been able to figure out how to get Indirect to work, I have
found that the find & replace works better for me if I find & replace the
entire file name path, instead of just the date change within the file name.
I made it thru all my files this monmth without any problems, so maybe that
was the key. Not sure why that would make a difference, but it did. i hope
this helps someone else with the same issue.

"Bruizer8" wrote:

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?

You're welcome.
--
Kevin Vaughn


"Bruizer8" wrote:

Hi - Thanks for the reply. Unfortunately I haven't had time to address it.
I will try to get to it in the next couple of weeks. I have a ton of
projects right now. Thaks again! Brenda

"Kevin Vaughn" wrote:

Maybe this will help. Here are two formulas with the only difference being
the use of Indirect:

VLOOKUP(B15,'[Zone 11 Class Dates Months2.xls]Calendar
Notes'!$B$10:$C$16,2,FALSE)
VLOOKUP(B16,INDIRECT("'[Zone " & A16 & " Class Dates Months2.xls]Calendar
Notes'!$B$10:$C$16"), 2, FALSE)

In cell A16 I put the value 11. You could presumably substitute (I believe
it was) your date in the appropriate place.

HTH or your other method continues to work for you.
--
Kevin Vaughn


"Bruizer8" wrote:

Thanks for the speedy response! Actually, I had the file open in the same
excel application, so that couldn't be it. It was probably the syntax I was
using somehow. Oh well, like you said, I got it to work his month, and as
long as that works I'll continue it that way. Thanks for your suggestions.
Brenda

"Kevin Vaughn" wrote:

I believe, looking at your previous post, that you are probably trying to use
Indirect on a closed workbook. If so, then no. As I understand it Indirect
will not work on closed files (that is what I have read. I just have never
tried it.)
It looks like you got something working for at least this month so that is
good. Good luck with future months!
--
Kevin Vaughn


"Bruizer8" wrote:

Since I haven't been able to figure out how to get Indirect to work, I have
found that the find & replace works better for me if I find & replace the
entire file name path, instead of just the date change within the file name.
I made it thru all my files this monmth without any problems, so maybe that
was the key. Not sure why that would make a difference, but it did. i hope
this helps someone else with the same issue.

"Bruizer8" wrote:

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?