| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula works only if other sheet is open
The formula below only updates the cell if the worksheet "Requisitions.xls"
is open. Otherwise, I get the message to update the link when I open the current worksheet but all that is displayed is #REF in each cell that the formula exists. Is there a way around this? I would like not to have to open the other workbook in order for the cells to update. I am summing all the values in Requisitions.xls column F where column H = the value in A2 and column G = 80801. Any help or suggestions you have are appreciated. =SUMPRODUCT(--([Requisitions.xls]Requisitions!$H$6:[Requisitions.xls]Requisitions!$H$6553=A2),--(MID([Requisitions.xls]Requisitions!$G$6:[Requisitions.xls]Requisitions!$G$6553,7,5)="80801"),[Requisitions.xls]Requisitions!$F$6:[Requisitions.xls]Requisitions!$F$6553) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula works only if other sheet is open
Hi
Into your workbook add a (hidden) worksheet with duplicate of your source data in external workboook (use links for this, and duplicate only columns you need). Now rewrite your formulas, so they refer to this (hidden) sheet. Example: Create sheet Links Links!A1=IF([Requisitions.xls]Requisitions!$H6="","",[Requisitions.xls]Requisitions!$H6) Links!B1=IF([Requisitions.xls]Requisitions!$F6="","",[Requisitions.xls]Requisitions!$F6) Links!C1=IF([Requisitions.xls]Requisitions!$G6="","",[Requisitions.xls]Requisitions!$G6) Copy Links!A1:C1 down for at least as much rows as in your source table. Your formula will be =SUMPRODUCT(--(Links!$A$1:$A$6547=A2),--(MID(Links!$C$1:$C$6547,7,5)="80801"),Links!$B$1:$ B$6547) Arvi Laanemets "Vince" wrote in message ... The formula below only updates the cell if the worksheet "Requisitions.xls" is open. Otherwise, I get the message to update the link when I open the current worksheet but all that is displayed is #REF in each cell that the formula exists. Is there a way around this? I would like not to have to open the other workbook in order for the cells to update. I am summing all the values in Requisitions.xls column F where column H = the value in A2 and column G = 80801. Any help or suggestions you have are appreciated. =SUMPRODUCT(--([Requisitions.xls]Requisitions!$H$6:[Requisitions.xls]Requisitions!$H$6553=A2),--(MID([Requisitions.xls]Requisitions!$G$6:[Requisitions.xls]Requisitions!$G$6553,7,5)="80801"),[Requisitions.xls]Requisitions!$F$6:[Requisitions.xls]Requisitions!$F$6553) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula works only if other sheet is open
I don't see why your formula requires the sending workbook to be open. Are you
sure that Requisitions.xls is really a normal workbook? Could it be a CSV file just renamed to .xls? (It kind of looks that way based on the name of the workbook and worksheet.) If it's a normal workbook, then your formula is normally written like: =SUMPRODUCT(--([Requisitions.xls]Requisitions!$H$6:$H$6553=A2), --(MID([Requisitions.xls]Requisitions!$G$6:$G$6553,7,5)="80801"), [Requisitions.xls]Requisitions!$F$6:$F$6553) Vince wrote: The formula below only updates the cell if the worksheet "Requisitions.xls" is open. Otherwise, I get the message to update the link when I open the current worksheet but all that is displayed is #REF in each cell that the formula exists. Is there a way around this? I would like not to have to open the other workbook in order for the cells to update. I am summing all the values in Requisitions.xls column F where column H = the value in A2 and column G = 80801. Any help or suggestions you have are appreciated. =SUMPRODUCT(--([Requisitions.xls]Requisitions!$H$6:[Requisitions.xls]Requisitions!$H$6553=A2),--(MID([Requisitions.xls]Requisitions!$G$6:[Requisitions.xls]Requisitions!$G$6553,7,5)="80801"),[Requisitions.xls]Requisitions!$F$6:[Requisitions.xls]Requisitions!$F$6553) -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula works only if other sheet is open
Don,
I defined names and entered the following formula with the same results. I get #Ref in the cell until I open the other workbook. Great tip on making the formula shorter and more readable. Any other tips on what may help? =SUMPRODUCT(--(RcolH=A2),--(MID(RcolG,7,5)="80801"),RcolF) "Don Guillett" wrote: Try using a defined name in the destination file that refers to range in the source file. It will also make your formulas easier RcolH =[Requisitions.xls]Requisitions!$H$6:[Requisitions.xls]Requisitions!$H$6553 -- Don Guillett Microsoft MVP Excel SalesAid Software "Vince" wrote in message ... The formula below only updates the cell if the worksheet "Requisitions.xls" is open. Otherwise, I get the message to update the link when I open the current worksheet but all that is displayed is #REF in each cell that the formula exists. Is there a way around this? I would like not to have to open the other workbook in order for the cells to update. I am summing all the values in Requisitions.xls column F where column H = the value in A2 and column G = 80801. Any help or suggestions you have are appreciated. =SUMPRODUCT(--([Requisitions.xls]Requisitions!$H$6:[Requisitions.xls]Requisitions!$H$6553=A2),--(MID([Requisitions.xls]Requisitions!$G$6:[Requisitions.xls]Requisitions!$G$6553,7,5)="80801"),[Requisitions.xls]Requisitions!$F$6:[Requisitions.xls]Requisitions!$F$6553) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula works only if other sheet is open
I just created the defined name thusly. If you leave out the path, excel
will add it for you . ='C:\yourfoldernamehere\[bookA.xls]Sheet1'!$H$2:$H$22 and used this formula even after closing and re-opening Excel with bookA closed. =SUMPRODUCT(--(rcolh=$I$13),--(MID(rcolg,7,5)="12345"),rcolf) maybe =SUMPRODUCT(--(rcolh=$I$13),--(MID(trim(rcolg),7,5)="12345"),rcolf) -- Don Guillett Microsoft MVP Excel SalesAid Software "Vince" wrote in message ... Don, I defined names and entered the following formula with the same results. I get #Ref in the cell until I open the other workbook. Great tip on making the formula shorter and more readable. Any other tips on what may help? =SUMPRODUCT(--(RcolH=A2),--(MID(RcolG,7,5)="80801"),RcolF) "Don Guillett" wrote: Try using a defined name in the destination file that refers to range in the source file. It will also make your formulas easier RcolH =[Requisitions.xls]Requisitions!$H$6:[Requisitions.xls]Requisitions!$H$6553 -- Don Guillett Microsoft MVP Excel SalesAid Software "Vince" wrote in message ... The formula below only updates the cell if the worksheet "Requisitions.xls" is open. Otherwise, I get the message to update the link when I open the current worksheet but all that is displayed is #REF in each cell that the formula exists. Is there a way around this? I would like not to have to open the other workbook in order for the cells to update. I am summing all the values in Requisitions.xls column F where column H = the value in A2 and column G = 80801. Any help or suggestions you have are appreciated. =SUMPRODUCT(--([Requisitions.xls]Requisitions!$H$6:[Requisitions.xls]Requisitions!$H$6553=A2),--(MID([Requisitions.xls]Requisitions!$G$6:[Requisitions.xls]Requisitions!$G$6553,7,5)="80801"),[Requisitions.xls]Requisitions!$F$6:[Requisitions.xls]Requisitions!$F$6553) |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula works only if other sheet is open
Dave,
Your solution worked great. I see my mistake now. Thanks so much for your help. "Dave Peterson" wrote: I don't see why your formula requires the sending workbook to be open. Are you sure that Requisitions.xls is really a normal workbook? Could it be a CSV file just renamed to .xls? (It kind of looks that way based on the name of the workbook and worksheet.) If it's a normal workbook, then your formula is normally written like: =SUMPRODUCT(--([Requisitions.xls]Requisitions!$H$6:$H$6553=A2), --(MID([Requisitions.xls]Requisitions!$G$6:$G$6553,7,5)="80801"), [Requisitions.xls]Requisitions!$F$6:$F$6553) Vince wrote: The formula below only updates the cell if the worksheet "Requisitions.xls" is open. Otherwise, I get the message to update the link when I open the current worksheet but all that is displayed is #REF in each cell that the formula exists. Is there a way around this? I would like not to have to open the other workbook in order for the cells to update. I am summing all the values in Requisitions.xls column F where column H = the value in A2 and column G = 80801. Any help or suggestions you have are appreciated. =SUMPRODUCT(--([Requisitions.xls]Requisitions!$H$6:[Requisitions.xls]Requisitions!$H$6553=A2),--(MID([Requisitions.xls]Requisitions!$G$6:[Requisitions.xls]Requisitions!$G$6553,7,5)="80801"),[Requisitions.xls]Requisitions!$F$6:[Requisitions.xls]Requisitions!$F$6553) -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Works won't open excel | Excel Discussion (Misc queries) | |||
| Was sent Excel doc but I have only Mic.Works, how to open? | Excel Discussion (Misc queries) | |||
| how do I open a works spredsheet in excel? | Excel Discussion (Misc queries) | |||
| How do i open excel doc in works? | Excel Discussion (Misc queries) | |||
| Open a .xls in micrsoft works sreadsheet | Excel Discussion (Misc queries) | |||
Linear Mode
