Formulas Reference

[Milind Keer]

Hi

=IF ([Data] UK!B7 = "Milind", "Exist", "Does not Exist")

here can I give reference to '[Data] UK!B7'??

e.g

A1 = [Data] UK!B7
Can I say

=IF (A1 = "Milind", "Exist", "Does not Exist")

basically i want to pull '[Data] UK!B7' from some othr cell... as dis is
dynamic in my case... it keeps changing all the time and evry time i dont
want to modify my formula... i dont even want to do find-replace.

plz advise.

 

[Bob Phillips]

=IF(INDIRECT(A1)="Milind", "Exist", "Does not Exist")

 

[Pete_UK]

Is the file Data an Excel file? Is it open at the same time as the
file with the formula in? If so, you can do this:

A1 = [Data.xls]UK!B7

Then in your formula you can put:

=IF(INDIRECT(A1) = "Milind", "Exist", "Does not Exist")

INDIRECT only works with open workbooks.

Hope this helps.

Pete

 

[Milind Keer]

Hi Bob

Thanks for dis... It works... but now i hv another query...

This is regarding referencing cell from another worksheet.

1. I have to worksheet in 1 XL. One with data and another with Formulas..
Everything is working fine.
2. Now I want to move formula worksheet to another XL and I did that and
formulas are populating appropriate results.
3. But when I close my data sheet formulas gets update with data sheet path
and when i open a data sheet then it sets back to original reference.
4. I would like to understand how it works?
5. Do I always need to put both XL in same folder?
6. What if I change the name of data sheet? Will my fornulas automatically
gets updates?
7. What if the path is very lengthy?
8. What would u suggest, should I hardcode path in my formulas?

please advise.

Thanks in advance!
Milind Keer

=IF(INDIRECT(A1)="Milind", "Exist", "Does not Exist")


--
__________________________________
HTH

Bob

Hi

=IF ([Data] UK!B7 = "Milind", "Exist", "Does not Exist")

here can I give reference to '[Data] UK!B7'??

e.g

A1 = [Data] UK!B7
Can I say

=IF (A1 = "Milind", "Exist", "Does not Exist")

basically i want to pull '[Data] UK!B7' from some othr cell... as dis is
dynamic in my case... it keeps changing all the time and evry time i dont
want to modify my formula... i dont even want to do find-replace.

plz advise.

 

[Milind Keer]

Thanks Pete!!

I haven't yet used INDIRECT function but I will use it...

SUM(IF(([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$B$1:$B$65000 =
Data!$A$3)*([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$D$1:$D$65000 =
Data!$B$3)*([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$AL$1:$AL$65000 =
Data!$C$3)*([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$AP$1:$AP$65000 =
Data!$D$3)*([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$BD$1:$BD$65000 =
Data!$E$3)*([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$BF$1:$BF$65000 =
Data!$F$3)*([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$BH$1:$BH$65000 <>
Data!$G$3),[GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$EM$1:$EM$65000,"false"))

This is my formula.... 'GlobalReport.xls' is data sheet.
Now here i want to use INDIRECT Function for name of the datasheet as
evrytime it is different file.

A1 = [GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$B$1:$B$65000

Here i would like to do something like dis,
SUM(IF((INDIRECT(A1) =
Data!$A$3)*([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$D$1:$D$65000 =
Data!$B$3)*([GlobalReport.xls]....>>

Can I do this? will this work??

And do I need to give path of the datasheet?


Currently I am getting following error,

"Excel Can not complete this task with available resource, chose less data
or close other application"

"Unable to save external link value"

Why am I getting this error??

Is the file Data an Excel file? Is it open at the same time as the
file with the formula in? If so, you can do this:

A1 = [Data.xls]UK!B7

Then in your formula you can put:

=IF(INDIRECT(A1) = "Milind", "Exist", "Does not Exist")

INDIRECT only works with open workbooks.

Hope this helps.

Pete

Hi

=IF ([Data] UK!B7 = "Milind", "Exist", "Does not Exist")

here can I give reference to '[Data] UK!B7'??

e.g

A1 = [Data] UK!B7
Can I say

=IF (A1 = "Milind", "Exist", "Does not Exist")

basically i want to pull '[Data] UK!B7' from some othr cell... as dis is
dynamic in my case... it keeps changing all the time and evry time i dont
want to modify my formula... i dont even want to do find-replace.

plz advise.

 

[Milind Keer]

Hi

ANy updates on this??

If I use INDIRECT function then I have to keep open both the XLs. I don't
want to do that? what is other alternative?

Basically I want to give reference to some other XL which I can store on the
same folder but I dont want to open it. And the formula which I have written
has length approx 512.

How to hardcode path in formulas?

Milind

Thanks Pete!!

I haven't yet used INDIRECT function but I will use it...

SUM(IF(([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$B$1:$B$65000 =
Data!$A$3)*([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$D$1:$D$65000 =
Data!$B$3)*([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$AL$1:$AL$65000 =
Data!$C$3)*([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$AP$1:$AP$65000 =
Data!$D$3)*([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$BD$1:$BD$65000 =
Data!$E$3)*([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$BF$1:$BF$65000 =
Data!$F$3)*([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$BH$1:$BH$65000 <>
Data!$G$3),[GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$EM$1:$EM$65000,"false"))

This is my formula.... 'GlobalReport.xls' is data sheet.
Now here i want to use INDIRECT Function for name of the datasheet as
evrytime it is different file.

A1 = [GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$B$1:$B$65000

Here i would like to do something like dis,
SUM(IF((INDIRECT(A1) =
Data!$A$3)*([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$D$1:$D$65000 =
Data!$B$3)*([GlobalReport.xls]....>>

Can I do this? will this work??

And do I need to give path of the datasheet?


Currently I am getting following error,

"Excel Can not complete this task with available resource, chose less data
or close other application"

"Unable to save external link value"

Why am I getting this error??

Is the file Data an Excel file? Is it open at the same time as the
file with the formula in? If so, you can do this:

A1 = [Data.xls]UK!B7

Then in your formula you can put:

=IF(INDIRECT(A1) = "Milind", "Exist", "Does not Exist")

INDIRECT only works with open workbooks.

Hope this helps.

Pete

Hi

=IF ([Data] UK!B7 = "Milind", "Exist", "Does not Exist")

here can I give reference to '[Data] UK!B7'??

e.g

A1 = [Data] UK!B7
Can I say

=IF (A1 = "Milind", "Exist", "Does not Exist")

basically i want to pull '[Data] UK!B7' from some othr cell... as dis is
dynamic in my case... it keeps changing all the time and evry time i dont
want to modify my formula... i dont even want to do find-replace.

plz advise.

 

[Pete_UK]

So many questions here !! Let's go back to your post from yesterday:

Put this in A1:

[GlobalReport.xls]TBL_Adhoc_Supervised_UIX!

as this is common to each range, then you could try:

SUM(IF((INDIRECT(A1&"B1:B65000") = Data!$A
$3)*(INDIRECT(A1&"D165000") = Data!$B$3)*(INDIRECT(A1&"AL1:AL65000")
= Data!$C$3)*(INDIRECT(A1&"AP1:AP65000") = Data!$D
$3)*(INDIRECT(A1&""BD1:BD65000") = Data!$E
$3)*(INDIRECT(A1&"BF1:BF65000") = Data!$F
$3)*(INDIRECT(A1&"BH1:BH65000") <> Data!$G
$3),INDIRECT(A1&"EM1:EM65000"),"fals­e"))

so your formula is now much shorter. However, for INDIRECT to work you
must have the file GlobalReport.xls open at the same time.

You also need to ask yourself is this the best way to go about things?
Do you really need to check every row from 1 to 65000? Only you know
how your data is laid out, but another approach may be to have a
helper column in GlobalReport.xls (eg column FF) and to put this
formula in FF1:

=B1&D1&AL1&AP1&BD1&BF1

and then you only need to check one column instead of 6.

(More after lunch)

Pete

Hi

ANy updates on this??

If I use INDIRECT function then I have to keep open both the XLs. I don't
want to do that? what is other alternative?

Basically I want to give reference to some other XL which I can store on the
same folder but I dont want to open it. And the formula which I have written
has length approx 512.

How to hardcode path in formulas?

Milind

Thanks Pete!!

I haven't yet used INDIRECT function but I will use it...

SUM(IF(([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$B$1:$B$65000 =
Data!$A$3)*([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$D$1:$D$65000 =
Data!$B$3)*([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$AL$1:$AL$65000 =
Data!$C$3)*([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$AP$1:$AP$65000 =
Data!$D$3)*([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$BD$1:$BD$65000 =
Data!$E$3)*([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$BF$1:$BF$65000 =
Data!$F$3)*([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$BH$1:$BH$65000 <>
Data!$G$3),[GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$EM$1:$EM$65000,"fals­e"))

This is my formula.... 'GlobalReport.xls' is data sheet.
Now here i want to use INDIRECT Function for name of the datasheet as
evrytime it is different file.

A1 = [GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$B$1:$B$65000

Here i would like to do something like dis,
SUM(IF((INDIRECT(A1) =
Data!$A$3)*([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$D$1:$D$65000 =
Data!$B$3)*([GlobalReport.xls]....>>

Can I do this? will this work??

And do I need to give path of the datasheet?  

Currently I am getting following error,

"Excel Can not complete this task with available resource, chose less data
or close other application"

"Unable to save external link value"

Why am I getting this error??

Is the file Data an Excel file? Is it open at the same time as the
file with the formula in? If so, you can do this:
A1 = [Data.xls]UK!B7
Then in your formula you can put:
=IF(INDIRECT(A1) = "Milind", "Exist", "Does not Exist")
INDIRECT only works with open workbooks.
Hope this helps.
Pete
On Sep 3, 12:42 pm, Milind Keer <[email protected]>
wrote:
Hi
=IF ([Data] UK!B7 = "Milind", "Exist", "Does not Exist")
here can I give reference to '[Data] UK!B7'??
e.g
A1 = [Data] UK!B7
Can I say
=IF (A1 = "Milind", "Exist", "Does not Exist")
basically i want to pull '[Data] UK!B7' from some othr cell... as dis is
dynamic in my case... it keeps changing all the time and evry time i dont
want to modify my formula... i dont even want to do find-replace.
plz advise.- Hide quoted text -

- Show quoted text -

 

[Milind Keer]

hey pete.... u r a star... thanks a ton... u made my life easy....

i m just wondering why this didn't click me...

thanks again... i guess this will make my formula much shorter...

milind

So many questions here !! Let's go back to your post from yesterday:

Put this in A1:

[GlobalReport.xls]TBL_Adhoc_Supervised_UIX!

as this is common to each range, then you could try:

SUM(IF((INDIRECT(A1&"B1:B65000") = Data!$A
$3)*(INDIRECT(A1&"D165000") = Data!$B$3)*(INDIRECT(A1&"AL1:AL65000")
= Data!$C$3)*(INDIRECT(A1&"AP1:AP65000") = Data!$D
$3)*(INDIRECT(A1&""BD1:BD65000") = Data!$E
$3)*(INDIRECT(A1&"BF1:BF65000") = Data!$F
$3)*(INDIRECT(A1&"BH1:BH65000") <> Data!$G
$3),INDIRECT(A1&"EM1:EM65000"),"fals­e"))

so your formula is now much shorter. However, for INDIRECT to work you
must have the file GlobalReport.xls open at the same time.

You also need to ask yourself is this the best way to go about things?
Do you really need to check every row from 1 to 65000? Only you know
how your data is laid out, but another approach may be to have a
helper column in GlobalReport.xls (eg column FF) and to put this
formula in FF1:

=B1&D1&AL1&AP1&BD1&BF1

and then you only need to check one column instead of 6.

(More after lunch)

Pete

Hi

ANy updates on this??

If I use INDIRECT function then I have to keep open both the XLs. I don't
want to do that? what is other alternative?

Basically I want to give reference to some other XL which I can store on the
same folder but I dont want to open it. And the formula which I have written
has length approx 512.

How to hardcode path in formulas?

Milind

Thanks Pete!!

I haven't yet used INDIRECT function but I will use it...

SUM(IF(([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$B$1:$B$65000 =
Data!$A$3)*([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$D$1:$D$65000 =
Data!$B$3)*([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$AL$1:$AL$65000 =
Data!$C$3)*([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$AP$1:$AP$65000 =
Data!$D$3)*([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$BD$1:$BD$65000 =
Data!$E$3)*([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$BF$1:$BF$65000 =
Data!$F$3)*([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$BH$1:$BH$65000 <>
Data!$G$3),[GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$EM$1:$EM$65000,"fals­e"))

This is my formula.... 'GlobalReport.xls' is data sheet.
Now here i want to use INDIRECT Function for name of the datasheet as
evrytime it is different file.

A1 = [GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$B$1:$B$65000

Here i would like to do something like dis,
SUM(IF((INDIRECT(A1) =
Data!$A$3)*([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$D$1:$D$65000 =
Data!$B$3)*([GlobalReport.xls]....>>

Can I do this? will this work??

And do I need to give path of the datasheet?

Currently I am getting following error,

"Excel Can not complete this task with available resource, chose less data
or close other application"

"Unable to save external link value"

Why am I getting this error??

"Pete_UK" wrote:

Is the file Data an Excel file? Is it open at the same time as the
file with the formula in? If so, you can do this:

A1 = [Data.xls]UK!B7

Then in your formula you can put:

=IF(INDIRECT(A1) = "Milind", "Exist", "Does not Exist")

INDIRECT only works with open workbooks.

Hope this helps.

On Sep 3, 12:42 pm, Milind Keer <[email protected]>
wrote:
Hi

=IF ([Data] UK!B7 = "Milind", "Exist", "Does not Exist")

here can I give reference to '[Data] UK!B7'??

e.g

A1 = [Data] UK!B7
Can I say

=IF (A1 = "Milind", "Exist", "Does not Exist")

basically i want to pull '[Data] UK!B7' from some othr cell... as dis is
dynamic in my case... it keeps changing all the time and evry time i dont
want to modify my formula... i dont even want to do find-replace.

plz advise.- Hide quoted text -

- Show quoted text -

 

[Pete_UK]

So, with this formula in FF1 of GlobalReport.xls:

=B1&D1&AL1&AP1&BD1&BF1

then copied down for as many rows as you use (I can't believe it is
65000 !!), your formula would become:

=SUM(IF((INDIRECT(A1&"FF1:FF65000") = Data!$A$3 & Data!$B$3 & Data!$C
$3 & Data!$D$3 & Data!$E$3 & Data!$F$3)*(INDIRECT(A1&"BH1:BH65000") <>
Data!$G$3),INDIRECT(A1&"EM1:EM65000")))

Much shorter, and you can probably omit the remaining $ symbols to
save a few more characters (12). You were getting the "unable to
complete task with available resources" error because your formula was
trying to access a massive range. This newer formula might help, but
you really need to keep the ranges as short as possible, so if you
only have 2000 rows, for example, change the 65000 to 2000 - this will
also calculate much more quickly.

There is a free add-in download, Morefunc, which has a function
INDIRECT.EXT which works with closed workbooks, so if you install that
and use INDIRECT.EXT instead of INDIRECT (and put the full path in
with A1 and put apostrophes around the path, filename and sheetname)
then that might work with the GlobalReport.xls file closed. Just do a
Google search for Morefunc to find places where you can download it
from.

I think that answers all your questions.

Hope this helps.

Pete

hey pete.... u r a star... thanks a ton...  u made my life easy....

i m just wondering why this didn't click me...

thanks again... i guess this will make my formula much shorter...

milind

So many questions here !! Let's go back to your post from yesterday:

Put this in A1:

[GlobalReport.xls]TBL_Adhoc_Supervised_UIX!

as this is common to each range, then you could try:

SUM(IF((INDIRECT(A1&"B1:B65000") = Data!$A
$3)*(INDIRECT(A1&"D165000") = Data!$B$3)*(INDIRECT(A1&"AL1:AL65000")
= Data!$C$3)*(INDIRECT(A1&"AP1:AP65000") = Data!$D
$3)*(INDIRECT(A1&""BD1:BD65000") = Data!$E
$3)*(INDIRECT(A1&"BF1:BF65000") = Data!$F
$3)*(INDIRECT(A1&"BH1:BH65000") <> Data!$G
$3),INDIRECT(A1&"EM1:EM65000"),"fals­e"))

so your formula is now much shorter. However, for INDIRECT to work you
must have the file GlobalReport.xls open at the same time.

You also need to ask yourself is this the best way to go about things?
Do you really need to check every row from 1 to 65000? Only you know
how your data is laid out, but another approach may be to have a
helper column in GlobalReport.xls (eg column FF) and to put this
formula in FF1:

and then you only need to check one column instead of 6.

(More after lunch)

Pete

Hi
ANy updates on this??
If I use INDIRECT function then I have to keep open both the XLs. I don't
want to do that? what is other alternative?
Basically I want to give reference to some other XL which I can storeon the
same folder but I dont want to open it. And the formula which I have written
has length approx 512.
How to hardcode path in formulas?
Milind
:
Thanks Pete!!
I haven't yet used INDIRECT function but I will use it...
SUM(IF(([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$B$1:$B$65000 =
Data!$A$3)*([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$D$1:$D$65000 =
Data!$B$3)*([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$AL$1:$AL$65000 =
Data!$C$3)*([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$AP$1:$AP$65000 =
Data!$D$3)*([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$BD$1:$BD$65000 =
Data!$E$3)*([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$BF$1:$BF$65000 =
Data!$F$3)*([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$BH$1:$BH$65000 <>
Data!$G$3),[GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$EM$1:$EM$65000,"fals­­e"))
This is my formula.... 'GlobalReport.xls' is data sheet.
Now here i want to use INDIRECT Function for name of the datasheet as
evrytime it is different file.
A1 = [GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$B$1:$B$65000
Here i would like to do something like dis,
SUM(IF((INDIRECT(A1) =
Data!$A$3)*([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$D$1:$D$65000 =
Data!$B$3)*([GlobalReport.xls]....>>
Can I do this? will this work??
And do I need to give path of the datasheet?  
Currently I am getting following error,
"Excel Can not complete this task with available resource, chose less data
or close other application"
"Unable to save external link value"
Why am I getting this error??
:
Is the file Data an Excel file? Is it open at the same time as the
file with the formula in? If so, you can do this:
A1 = [Data.xls]UK!B7
Then in your formula you can put:
=IF(INDIRECT(A1) = "Milind", "Exist", "Does not Exist")
INDIRECT only works with open workbooks.
Hope this helps.
Pete
On Sep 3, 12:42 pm, Milind Keer <[email protected]>
wrote:
Hi
=IF ([Data] UK!B7 = "Milind", "Exist", "Does not Exist")
here can I give reference to '[Data] UK!B7'??
e.g
A1 = [Data] UK!B7
Can I say
=IF (A1 = "Milind", "Exist", "Does not Exist")
basically i want to pull '[Data] UK!B7' from some othr cell... as dis is
dynamic in my case... it keeps changing all the time and evry time i dont
want to modify my formula... i dont even want to do find-replace.
plz advise.- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Milind Keer]

Thanks again pete!! I will certainly try to implement your suggestion.

Well!! we get this huge data file on monthly basis and currently record
contains 55000 rows which may increase in future. Hence on the safer side i
kept no. of rows 65000 which is the upper limit of XL.

I guess accurate upper limit is 65536 rows but for an array u can use 65535.

So, with this formula in FF1 of GlobalReport.xls:

=B1&D1&AL1&AP1&BD1&BF1

then copied down for as many rows as you use (I can't believe it is
65000 !!), your formula would become:

=SUM(IF((INDIRECT(A1&"FF1:FF65000") = Data!$A$3 & Data!$B$3 & Data!$C
$3 & Data!$D$3 & Data!$E$3 & Data!$F$3)*(INDIRECT(A1&"BH1:BH65000") <>
Data!$G$3),INDIRECT(A1&"EM1:EM65000")))

Much shorter, and you can probably omit the remaining $ symbols to
save a few more characters (12). You were getting the "unable to
complete task with available resources" error because your formula was
trying to access a massive range. This newer formula might help, but
you really need to keep the ranges as short as possible, so if you
only have 2000 rows, for example, change the 65000 to 2000 - this will
also calculate much more quickly.

There is a free add-in download, Morefunc, which has a function
INDIRECT.EXT which works with closed workbooks, so if you install that
and use INDIRECT.EXT instead of INDIRECT (and put the full path in
with A1 and put apostrophes around the path, filename and sheetname)
then that might work with the GlobalReport.xls file closed. Just do a
Google search for Morefunc to find places where you can download it
from.

I think that answers all your questions.

Hope this helps.

Pete

hey pete.... u r a star... thanks a ton... u made my life easy....

i m just wondering why this didn't click me...

thanks again... i guess this will make my formula much shorter...

milind

So many questions here !! Let's go back to your post from yesterday:

Put this in A1:

[GlobalReport.xls]TBL_Adhoc_Supervised_UIX!

as this is common to each range, then you could try:

SUM(IF((INDIRECT(A1&"B1:B65000") = Data!$A
$3)*(INDIRECT(A1&"D165000") = Data!$B$3)*(INDIRECT(A1&"AL1:AL65000")
= Data!$C$3)*(INDIRECT(A1&"AP1:AP65000") = Data!$D
$3)*(INDIRECT(A1&""BD1:BD65000") = Data!$E
$3)*(INDIRECT(A1&"BF1:BF65000") = Data!$F
$3)*(INDIRECT(A1&"BH1:BH65000") <> Data!$G
$3),INDIRECT(A1&"EM1:EM65000"),"fals­e"))

so your formula is now much shorter. However, for INDIRECT to work you
must have the file GlobalReport.xls open at the same time.

You also need to ask yourself is this the best way to go about things?
Do you really need to check every row from 1 to 65000? Only you know
how your data is laid out, but another approach may be to have a
helper column in GlobalReport.xls (eg column FF) and to put this
formula in FF1:

and then you only need to check one column instead of 6.

(More after lunch)

On Sep 4, 10:05 am, Milind Keer <[email protected]>
wrote:
Hi

ANy updates on this??

If I use INDIRECT function then I have to keep open both the XLs. I don't
want to do that? what is other alternative?

Basically I want to give reference to some other XL which I can store on the
same folder but I dont want to open it. And the formula which I have written
has length approx 512.

How to hardcode path in formulas?

:
Thanks Pete!!

I haven't yet used INDIRECT function but I will use it...

SUM(IF(([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$B$1:$B$65000 =
Data!$A$3)*([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$D$1:$D$65000 =
Data!$B$3)*([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$AL$1:$AL$65000 =
Data!$C$3)*([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$AP$1:$AP$65000 =
Data!$D$3)*([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$BD$1:$BD$65000 =
Data!$E$3)*([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$BF$1:$BF$65000 =
Data!$F$3)*([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$BH$1:$BH$65000 <>
Data!$G$3),[GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$EM$1:$EM$65000,"fals­­e"))

This is my formula.... 'GlobalReport.xls' is data sheet.
Now here i want to use INDIRECT Function for name of the datasheet as
evrytime it is different file.

A1 = [GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$B$1:$B$65000

Here i would like to do something like dis,
SUM(IF((INDIRECT(A1) =
Data!$A$3)*([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$D$1:$D$65000 =
Data!$B$3)*([GlobalReport.xls]....>>

Can I do this? will this work??

And do I need to give path of the datasheet?

Currently I am getting following error,

"Excel Can not complete this task with available resource, chose less data
or close other application"

"Unable to save external link value"

Why am I getting this error??

"Pete_UK" wrote:

Is the file Data an Excel file? Is it open at the same time as the
file with the formula in? If so, you can do this:

A1 = [Data.xls]UK!B7

Then in your formula you can put:

=IF(INDIRECT(A1) = "Milind", "Exist", "Does not Exist")

INDIRECT only works with open workbooks.

Hope this helps.

On Sep 3, 12:42 pm, Milind Keer <[email protected]>
wrote:
Hi

=IF ([Data] UK!B7 = "Milind", "Exist", "Does not Exist")

here can I give reference to '[Data] UK!B7'??

e.g

A1 = [Data] UK!B7
Can I say

=IF (A1 = "Milind", "Exist", "Does not Exist")

basically i want to pull '[Data] UK!B7' from some othr cell... as dis is
dynamic in my case... it keeps changing all the time and evry time i dont
want to modify my formula... i dont even want to do find-replace.

plz advise.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

 

[Milind Keer]

Hi!!

Sorry to bother you again... this no. of records really causing a problem,
when I set 65000 as a upper limit for my array in formulas, XL is not
allowing me to save the data and giving following warning

"Excel Can not complete this task with available resource, chose less data
or close other application"

"Unable to save external link value"

Is this the memory issue?? I am using 2 GHz Intel Dual processor with 2 GB
RAM.

Can you please advise on this?

Cheers!
-- Milind

 

[Pete_UK]

Well, of course, the file you are getting data from is itself very
large, with columns going out at least to EM or FF and with 55k or
more rows of data, and then you have the file with the formula in, and
because it is an array formula Excel will use up even more memory ...

That's why I hinted at trying a different way of doing it. Perhaps if
the formula was not in a separate file but in a separate sheet in the
GlobalReport.xls file, then you wouldn't need to specify the path or
the filename and it might work in there. You could then have the file
which currently holds your formula just lookup the values from the
other file.

And, if you did not have that condition for <> then your formula could
become a SUMIF formula, as there would be only one range to check
(FF), so maybe you need to tinker about with the formula in FF1 to see
if you could produce a list of TRUE and FALSE values, something like
this:

=AND(B1=Data!A$3,D1=Data!B$3,AL1=Data!C$3,AP1=Data!D$3,BD1=Data!E
$3,BF1=Data!F$3,BH1<>Data!G$3)

This assumes that you have put the Data sheet in the same file, and
that you copy the formula down as required. Then on the Data sheet,
instead of your array formula you could just have:

=SUMIF(TBL_Adhoc_Supervised_UIX!FF:FF,TRUE,TBL_Adhoc_Supervised_UIX!
EM:EM)

With SUMIF you can use full-column references, and it will be much
quicker than the array-entered SUM(IF(...

Suppose that formula is in H3, then the values in A3 to G3 could be
fed by the original file you had and then just link back the value in
H3 as if the formula was still in that original file. And, if it was a
direct link then you wouldn't have to use INDIRECT...

So, a few more things for you to try out...

Hope this helps.

Pete

 

[Pete_UK]

Actually, if you amend that formula to this:

=IF(AND(B1=Data!A$3,D1=Data!B$3,AL1=Data!C$3,AP1=Data!D$3,BD1=Data!E
$3,BF1=Data!F$3,BH1<>Data!G$3),EM1,0)

and copy down column FF, then all you would need to do in Data!H3 is:

=SUM(TBL_Adhoc_Supervised_UIX!FF:FF)

Hope this helps.

Pete

 

[Milind Keer]

Hey thanks again Pete!! I guess u r Guru of XL...

this sounds ok if i need to to do SUM, but my another requirement is to
filter my data and put in a different sheet.

Currently I am using below formula doing that which is returning appropriate
result

=INDIRECT("[GlobalReport.xls]TBL_Adhoc_Supervised_UIX!AP"&SMALL(IF([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$EQ$1:$EQ$1000=$Z$2,ROW([GlobalReport.xls]TBL_Adhoc_Supervised_UIX!$EQ$1:$EQ$1000)),ROW(1:1)))

But here again I am facing same issue if I put 55000 as a upper limit in a
array.

I am just wondering How to resolve this issue?? Here each condition is
returning aprrox 1000-2000 rows which I want in a different XL.

Is there any way to do that?? Or do I need to write a macro??

Thanks again!!

Milind