| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas Reference
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formulas Reference
=IF(INDIRECT(A1)="Milind", "Exist", "Does not Exist")
-- __________________________________ HTH Bob "Milind Keer" wrote in message ... 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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formulas Reference
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 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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formulas Reference
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 "Bob Phillips" wrote: =IF(INDIRECT(A1)="Milind", "Exist", "Does not Exist") -- __________________________________ HTH Bob "Milind Keer" wrote in message ... 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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formulas Reference
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?? "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. Pete On Sep 3, 12:42 pm, Milind Keer 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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formulas Reference
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 "Milind Keer" wrote: 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?? "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. Pete On Sep 3, 12:42 pm, Milind Keer 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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formulas Reference
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&"D1:D65000") = 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 On Sep 4, 10:05*am, Milind Keer 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? Milind "Milind Keer" wrote: 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. Pete On Sep 3, 12:42 pm, Milind Keer 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 - |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formulas Reference
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 "Pete_UK" wrote: 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&"D1:D65000") = 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 On Sep 4, 10:05 am, Milind Keer 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? Milind "Milind Keer" wrote: 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. Pete On Sep 3, 12:42 pm, Milind Keer 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 - |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formulas Reference
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 On Sep 4, 12:16*pm, Milind Keer wrote: 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 "Pete_UK" wrote: 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&"D1:D65000") = 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 On Sep 4, 10:05 am, Milind Keer 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? Milind "Milind Keer" wrote: 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. Pete On Sep 3, 12:42 pm, Milind Keer 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 - |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formulas Reference
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. "Pete_UK" wrote: 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 On Sep 4, 12:16 pm, Milind Keer wrote: 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 "Pete_UK" wrote: 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&"D1:D65000") = 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 On Sep 4, 10:05 am, Milind Keer 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? Milind "Milind Keer" wrote: 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. Pete On Sep 3, 12:42 pm, Milind Keer 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 - |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formulas Reference
You're welcome - thanks for feeding back.
Pete On Sep 4, 1:48*pm, Milind Keer wrote: 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. |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formulas Reference
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" wrote: You're welcome - thanks for feeding back. Pete On Sep 4, 1:48 pm, Milind Keer wrote: 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. |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formulas Reference
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_Adh oc_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 On Sep 4, 5:59*pm, Milind Keer wrote: 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" wrote: You're welcome - thanks for feeding back. Pete On Sep 4, 1:48 pm, Milind Keer wrote: 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.- Hide quoted text - - Show quoted text - |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formulas Reference
Actually, if you amend that formula to this:
=IF(AND(B1=Data!A$3,D1=Data!B$3,AL1=Data!C$3,AP1=D ata!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 On Sep 4, 7:21*pm, Pete_UK wrote: 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_Adh oc_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 On Sep 4, 5:59*pm, Milind Keer wrote: 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" wrote: You're welcome - thanks for feeding back. Pete On Sep 4, 1:48 pm, Milind Keer wrote: 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.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formulas Reference
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 "Pete_UK" wrote: Actually, if you amend that formula to this: =IF(AND(B1=Data!A$3,D1=Data!B$3,AL1=Data!C$3,AP1=D ata!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 On Sep 4, 7:21 pm, Pete_UK wrote: 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_Adh oc_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 On Sep 4, 5:59 pm, Milind Keer wrote: 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" wrote: You're welcome - thanks for feeding back. Pete On Sep 4, 1:48 pm, Milind Keer wrote: 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.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Reference for formulas and arguments | Excel Discussion (Misc queries) | |||
| Row reference in a formulas | Excel Worksheet Functions | |||
| Formulas that reference cells that reference another cell | Excel Discussion (Misc queries) | |||
| Do not want formulas to reference old workbook. | Excel Discussion (Misc queries) | |||
| Reference Formulas | Excel Worksheet Functions | |||
![Milind Keer[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
