| 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
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. |
|
#4
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. |
|
#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 - |
| 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)
Hybrid Mode
