Select Excel files by formula

[VinceJ]

Each of my Excel file names is a customer's last name.
I print reports with the customer name down the left side
and information about that customer (for example: has a
certain task been completed by the forecast date?) in
multiple columns to the right of the customer name. Each
column has a formula similar to the one below:

=IF('[Smith.xls]Buyer Progress Record'!
$B$12="failed","F",IF('[Smith.xls]Buyer Progress Record'!
$N$28<>"?","---------",IF('[Smith.xls]Buyer Progress
Record'!$M$28<$AA$2,"! ! ! ! !",'[Smith.xls]Buyer
Progress Record'!$M$28)))

If "Smith" is on Line 1 (say A1) and "Jones" is to be on
Line 2 (say A2) I accomplish it by copying down the Smith
forumlas from Line 1 to Line 2 and then using Replace to
change Smith to Jones. This is very time consuming and
awkward.

How can I simply use one column with the name "Smith" on
Line 1 and "Jones" on Line 2 to automatically modify the
formulas to change Smith to Jones and so forth?

I need to use this capability in many other situations.
The ideal solution would be a formula like:
=IF('[A1.xls]Buyer Progress Record'!$B$12="failed","F",IF
('[A1.xls]Buyer Progress Record'!$N$28<>"?" etc. etc.
But I can't make it work.

 

[Bob Phillips]

=IF(INDIRECT("'["&A1"&".xls]Buyer Progress Record'!
$B$12")="failed","F",IF(INDIRECT("'["&A1"&".xls]Buyer Progress Record'!
$N$28")<>"?","---------",IF(INDIRECT("'["&A1"&".xls]Buyer Progress
Record'!$M$28<$AA$2"),"! ! ! ! !",'INDIRECT("'["&A1"&".xls]Buyer
Progress Record'!$M$28))))

 

[VinceJ]

Thank you for your reply but I can't make it work. Excel
says there is an error in the formula and highlights A1"
Although I haven't played with it, it doesn't do away
with the need to modify the formula on each line because
A1 wouldn't change to A2 when copying down. What am I
missing?

-----Original Message-----
=IF(INDIRECT("'["&A1"&".xls]Buyer Progress Record'!
$B$12")="failed","F",IF(INDIRECT("'["&A1"&".xls]Buyer Progress Record'!
$N$28")<>"?","---------",IF(INDIRECT("'["&A1"&".xls] Buyer Progress
Record'!$M$28<$AA$2"),"! ! ! ! !",'INDIRECT ("'["&A1"&".xls]Buyer
Progress Record'!$M$28))))


--

HTH

RP

Each of my Excel file names is a customer's last name.
I print reports with the customer name down the left side
and information about that customer (for example: has a
certain task been completed by the forecast date?) in
multiple columns to the right of the customer name. Each
column has a formula similar to the one below:

=IF('[Smith.xls]Buyer Progress Record'!
$B$12="failed","F",IF('[Smith.xls]Buyer Progress Record'!
$N$28<>"?","---------",IF('[Smith.xls]Buyer Progress
Record'!$M$28<$AA$2,"! ! ! ! !",'[Smith.xls]Buyer
Progress Record'!$M$28)))

If "Smith" is on Line 1 (say A1) and "Jones" is to be on
Line 2 (say A2) I accomplish it by copying down the Smith
forumlas from Line 1 to Line 2 and then using Replace to
change Smith to Jones. This is very time consuming and
awkward.

How can I simply use one column with the name "Smith" on
Line 1 and "Jones" on Line 2 to automatically modify the
formulas to change Smith to Jones and so forth?

I need to use this capability in many other situations.
The ideal solution would be a formula like:
=IF('[A1.xls]Buyer Progress Record'! $B$12="failed","F",IF
('[A1.xls]Buyer Progress Record'!$N$28<>"?" etc. etc.
But I can't make it work.

.

 

[Bob Phillips]

Vince,

Bit tricky to test that one, but I had a couple of errors in there. Try this
version

=IF(INDIRECT("'["&A1&".xls]Buyer Progress
Record'!$B$12")="failed","F",IF(INDIRECT("'["&A1&".xls]Buyer Progress
Record'!$N$28")<>"?","---------",IF(INDIRECT("'["&A1&".xls]Buyer Progress
Record'!$M$28")<$AA$2,"! ! ! ! !",INDIRECT("'["&A1&".xls]BuyerProgress
Record'!$M$28)"))))

watch wrap-around when you paste it in.

--

HTH

RP

Thank you for your reply but I can't make it work. Excel
says there is an error in the formula and highlights A1"
Although I haven't played with it, it doesn't do away
with the need to modify the formula on each line because
A1 wouldn't change to A2 when copying down. What am I
missing?

-----Original Message-----
=IF(INDIRECT("'["&A1"&".xls]Buyer Progress Record'!
$B$12")="failed","F",IF(INDIRECT("'["&A1"&".xls]Buyer Progress Record'!
$N$28")<>"?","---------",IF(INDIRECT("'["&A1"&".xls] Buyer Progress
Record'!$M$28<$AA$2"),"! ! ! ! !",'INDIRECT ("'["&A1"&".xls]Buyer
Progress Record'!$M$28))))


--

HTH

RP

Each of my Excel file names is a customer's last name.
I print reports with the customer name down the left side
and information about that customer (for example: has a
certain task been completed by the forecast date?) in
multiple columns to the right of the customer name. Each
column has a formula similar to the one below:

=IF('[Smith.xls]Buyer Progress Record'!
$B$12="failed","F",IF('[Smith.xls]Buyer Progress Record'!
$N$28<>"?","---------",IF('[Smith.xls]Buyer Progress
Record'!$M$28<$AA$2,"! ! ! ! !",'[Smith.xls]Buyer
Progress Record'!$M$28)))

If "Smith" is on Line 1 (say A1) and "Jones" is to be on
Line 2 (say A2) I accomplish it by copying down the Smith
forumlas from Line 1 to Line 2 and then using Replace to
change Smith to Jones. This is very time consuming and
awkward.

How can I simply use one column with the name "Smith" on
Line 1 and "Jones" on Line 2 to automatically modify the
formulas to change Smith to Jones and so forth?

I need to use this capability in many other situations.
The ideal solution would be a formula like:
=IF('[A1.xls]Buyer Progress Record'! $B$12="failed","F",IF
('[A1.xls]Buyer Progress Record'!$N$28<>"?" etc. etc.
But I can't make it work.

.

 

[VinceJ]

Hi, Bob:
Thank you very much. I hadn't paid attention to the wrap
around. Also, my example was with an open file and I
tested it on a file that was still in a folder: i.e. IF
('C:\Documents and Settings\All Users\Documents\Daily 2004
\[Smith.xls]BPR'!$B$12="failed" etc. etc. Sorry for my
stupidity.

I can easily live with your solution, however, never
being satisfied, can you think of a way to make A1 change
to (say) A37 when the formula is copied to a cell in
another column or line?

Again, I really appreciate your help. I have worked
around this for a long time and you have made it so much
easier.

Whick brings to mind another way I want to use it:
Each Buyer Progress Record worksheet has room for
multiple customer follow-ups. For example:
Col A Col B Col C
This Date Next Action Date Next Action
Line 1
Line 2
====
Line 35
Anywhere between one line and 35 lines have data in them.

I want to print a report by salesperson which deals only
with lines containing data. Example:
Smith
IF(INDIRECT("'["&A35&".xls]Buyer Progress Record'!$A$35")
="", do not print, do not skip this line, go to look at
A34, etc. repeat up to Line 1 or start at Line 1 and
repeat through Line 35.

Therefore, if there is data only in A1 and A2 on Smith
and only in A1 on Jones the report would look like this:
SMITH
Date Date Action
Date Date Action
JONES
Date Date Action
Next Name, etc.

I have developed a whole system for manufactured home
dealerships which handles everything from the time the
customer walks in the door until the home is ordered from
the factory, construction is scheduled and completed, and
the end-loan is funded. It should have been done in
Access but each time I looked at the Access book I
decided I could get the job done in Excel. The questions
I am asking you are the only truly awkward aspects of my
system. So obviously I am thankful for your help.

Vince

-----Original Message-----
Vince,

Bit tricky to test that one, but I had a couple of errors in there. Try this
version

=IF(INDIRECT("'["&A1&".xls]Buyer Progress
Record'!$B$12")="failed","F",IF(INDIRECT("'["&A1&".xls] Buyer Progress
Record'!$N$28")<>"?","---------",IF(INDIRECT ("'["&A1&".xls]Buyer Progress
Record'!$M$28")<$AA$2,"! ! ! ! !",INDIRECT("'["&A1&".xls] BuyerProgress
Record'!$M$28)"))))

watch wrap-around when you paste it in.

--

HTH

RP

Thank you for your reply but I can't make it work. Excel
says there is an error in the formula and highlights A1"
Although I haven't played with it, it doesn't do away
with the need to modify the formula on each line because
A1 wouldn't change to A2 when copying down. What am I
missing?

-----Original Message-----
=IF(INDIRECT("'["&A1"&".xls]Buyer Progress Record'!
$B$12")="failed","F",IF(INDIRECT("'["&A1"&".xls]Buyer Progress Record'!
$N$28")<>"?","---------",IF(INDIRECT("'["&A1"&".xls] Buyer Progress
Record'!$M$28<$AA$2"),"! ! ! ! !",'INDIRECT ("'["&A1"&".xls]Buyer
Progress Record'!$M$28))))


--

HTH

RP

"VinceJ" <[email protected]> wrote

in
message

Each of my Excel file names is a customer's last name.
I print reports with the customer name down the left side
and information about that customer (for example: has a
certain task been completed by the forecast date?) in
multiple columns to the right of the customer name. Each
column has a formula similar to the one below:

=IF('[Smith.xls]Buyer Progress Record'!
$B$12="failed","F",IF('[Smith.xls]Buyer Progress Record'!
$N$28<>"?","---------",IF('[Smith.xls]Buyer Progress
Record'!$M$28<$AA$2,"! ! ! ! !",'[Smith.xls]Buyer
Progress Record'!$M$28)))

If "Smith" is on Line 1 (say A1) and "Jones" is to

be
on

Line 2 (say A2) I accomplish it by copying down the Smith
forumlas from Line 1 to Line 2 and then using

Replace
to

change Smith to Jones. This is very time consuming and
awkward.

How can I simply use one column with the

name "Smith"
on

Line 1 and "Jones" on Line 2 to automatically modify the
formulas to change Smith to Jones and so forth?

I need to use this capability in many other situations.
The ideal solution would be a formula like:
=IF('[A1.xls]Buyer Progress Record'! $B$12="failed","F",IF
('[A1.xls]Buyer Progress Record'!$N$28<>"?" etc. etc.
But I can't make it work.



.

.

 

[VinceJ]

Please ignore the first part of my previous question. I
now see that A1 will change to A2, to A3, etc. merely by
copying down. When I initally saw the quote marks I
assumed incorrectly that it was just an alpha designation
rather than a live cell reference. I'm afraid I try to
do more than I have hours in the day for and am always
looking for a quick fix.

I would still appreciate an answer to the question at the
end.

-----Original Message-----
Hi, Bob:
Thank you very much. I hadn't paid attention to the wrap
around. Also, my example was with an open file and I
tested it on a file that was still in a folder: i.e. IF
('C:\Documents and Settings\All Users\Documents\Daily 2004
\[Smith.xls]BPR'!$B$12="failed" etc. etc. Sorry for my
stupidity.

I can easily live with your solution, however, never
being satisfied, can you think of a way to make A1 change
to (say) A37 when the formula is copied to a cell in
another column or line?

Again, I really appreciate your help. I have worked
around this for a long time and you have made it so much
easier.

Whick brings to mind another way I want to use it:
Each Buyer Progress Record worksheet has room for
multiple customer follow-ups. For example:
Col A Col B Col C
This Date Next Action Date Next Action
Line 1
Line 2
====
Line 35
Anywhere between one line and 35 lines have data in them.

I want to print a report by salesperson which deals only
with lines containing data. Example:
Smith
IF(INDIRECT("'["&A35&".xls]Buyer Progress Record'!$A$35")
="", do not print, do not skip this line, go to look at
A34, etc. repeat up to Line 1 or start at Line 1 and
repeat through Line 35.

Therefore, if there is data only in A1 and A2 on Smith
and only in A1 on Jones the report would look like this:
SMITH
Date Date Action
Date Date Action
JONES
Date Date Action
Next Name, etc.

I have developed a whole system for manufactured home
dealerships which handles everything from the time the
customer walks in the door until the home is ordered from
the factory, construction is scheduled and completed, and
the end-loan is funded. It should have been done in
Access but each time I looked at the Access book I
decided I could get the job done in Excel. The questions
I am asking you are the only truly awkward aspects of my
system. So obviously I am thankful for your help.

Vince

-----Original Message-----
Vince,

Bit tricky to test that one, but I had a couple of errors in there. Try this
version

=IF(INDIRECT("'["&A1&".xls]Buyer Progress
Record'!$B$12")="failed","F",IF(INDIRECT("'["&A1&".xls] Buyer Progress
Record'!$N$28")<>"?","---------",IF(INDIRECT ("'["&A1&".xls]Buyer Progress
Record'!$M$28")<$AA$2,"! ! ! ! !",INDIRECT

("'["&A1&".xls]

BuyerProgress
Record'!$M$28)"))))

watch wrap-around when you paste it in.

--

HTH

RP

Thank you for your reply but I can't make it work. Excel
says there is an error in the formula and highlights A1"
Although I haven't played with it, it doesn't do away
with the need to modify the formula on each line because
A1 wouldn't change to A2 when copying down. What am I
missing?
-----Original Message-----
=IF(INDIRECT("'["&A1"&".xls]Buyer Progress Record'!
$B$12")="failed","F",IF(INDIRECT("'["&A1"&".xls]Buyer
Progress Record'!
$N$28")<>"?","---------",IF(INDIRECT("'["&A1"&".xls]
Buyer Progress
Record'!$M$28<$AA$2"),"! ! ! ! !",'INDIRECT
("'["&A1"&".xls]Buyer
Progress Record'!$M$28))))


--

HTH

RP

message
Each of my Excel file names is a customer's last name.
I print reports with the customer name down the left
side
and information about that customer (for example: has a
certain task been completed by the forecast date?) in
multiple columns to the right of the customer name.
Each
column has a formula similar to the one below:

=IF('[Smith.xls]Buyer Progress Record'!
$B$12="failed","F",IF('[Smith.xls]Buyer Progress
Record'!
$N$28<>"?","---------",IF('[Smith.xls]Buyer Progress
Record'!$M$28<$AA$2,"! ! ! ! !",'[Smith.xls]Buyer
Progress Record'!$M$28)))

If "Smith" is on Line 1 (say A1) and "Jones" is to be
on
Line 2 (say A2) I accomplish it by copying down the
Smith
forumlas from Line 1 to Line 2 and then using Replace
to
change Smith to Jones. This is very time

consuming

and

awkward.

How can I simply use one column with the name "Smith"
on
Line 1 and "Jones" on Line 2 to automatically modify
the
formulas to change Smith to Jones and so forth?

I need to use this capability in many other situations.
The ideal solution would be a formula like:
=IF('[A1.xls]Buyer Progress Record'!
$B$12="failed","F",IF
('[A1.xls]Buyer Progress Record'!$N$28<>"?" etc. etc.
But I can't make it work.



.

.

.