|
Excel Formula
Can someone please help me? I can't seem to get the formula right. The
following is what I want to do. IF Column A=SHAW in (Physical Plant.xls) then link it to Column A in (P-Card Log.xls), IF not than don't link. Hope this isn't too confusing but I could really use the help in the formula. Thanks. |
Excel Formula
In order t get the syntax right, it will be a lot easier if you have
both files open to begin with, and both windows visible on screen (use Window | Arrange | Horizontal). Then with your cursor in A1 of the P- Card Log.xls file, begin to type this formula: =IF( at this point click into the window of the Physical Plant.xls file, click the appropriate sheet tab (assume this is Sheet1), and then click in A1 and Excel will have inserted some text for you in the formula bar, so that your formula will look something like: =IF('[Physical Plant.xls]Sheet1'!$A$1 then continue the formula by typing ="SHAW", and then click on the other window again and click on A1, so your formula will now look like: =IF('[Physical Plant.xls]Sheet1'!$A$1="SHAW",'[Physical Plant.xls]Sheet1'!$A$1 and then you need to add ,"") to the formula and then press <enter. You should end up with something like this: =IF('[Physical Plant.xls]Sheet1'!$A$1="SHAW",'[Physical Plant.xls]Sheet1'!$A$1,"") If you want to copy this down the column then you will need to amend the cell references so that they are no longer absolute references, like so: =IF('[Physical Plant.xls]Sheet1'!A1="SHAW",'[Physical Plant.xls]Sheet1'!A1,"") and then you can copy the formula down column A as required. If you close the Physical Plant.xls file, then your formula will expand to show the complete path to the file, so it will look something like: =IF('C:\Documents and Settings\User\My Documents\Developments\ [Physical Plant.xls]Sheet1'!A1="SHAW",'C:\Documents and Settings\User \My Documents\Developments\[Physical Plant.xls]Sheet1'!A1,"") which is why I suggested that you had both files open to begin with - I defy anyone to enter that formula correctly !! <bg Hope this helps. Pete On Jan 14, 4:32*pm, patsy wrote: Can someone please help me? *I can't seem to get the formula right. *The following is what I want to do. IF Column A=SHAW in (Physical Plant.xls) then link it to Column A in (P-Card Log.xls), IF not than don't link. Hope this isn't too confusing but I could really use the help in the formula. *Thanks. |
Excel Formula
Nice post Pete...........well done
Vaya con Dios, Chuck, CABGx3 "Pete_UK" wrote: In order t get the syntax right, it will be a lot easier if you have both files open to begin with, and both windows visible on screen (use Window | Arrange | Horizontal). Then with your cursor in A1 of the P- Card Log.xls file, begin to type this formula: =IF( at this point click into the window of the Physical Plant.xls file, click the appropriate sheet tab (assume this is Sheet1), and then click in A1 and Excel will have inserted some text for you in the formula bar, so that your formula will look something like: =IF('[Physical Plant.xls]Sheet1'!$A$1 then continue the formula by typing ="SHAW", and then click on the other window again and click on A1, so your formula will now look like: =IF('[Physical Plant.xls]Sheet1'!$A$1="SHAW",'[Physical Plant.xls]Sheet1'!$A$1 and then you need to add ,"") to the formula and then press <enter. You should end up with something like this: =IF('[Physical Plant.xls]Sheet1'!$A$1="SHAW",'[Physical Plant.xls]Sheet1'!$A$1,"") If you want to copy this down the column then you will need to amend the cell references so that they are no longer absolute references, like so: =IF('[Physical Plant.xls]Sheet1'!A1="SHAW",'[Physical Plant.xls]Sheet1'!A1,"") and then you can copy the formula down column A as required. If you close the Physical Plant.xls file, then your formula will expand to show the complete path to the file, so it will look something like: =IF('C:\Documents and Settings\User\My Documents\Developments\ [Physical Plant.xls]Sheet1'!A1="SHAW",'C:\Documents and Settings\User \My Documents\Developments\[Physical Plant.xls]Sheet1'!A1,"") which is why I suggested that you had both files open to begin with - I defy anyone to enter that formula correctly !! <bg Hope this helps. Pete On Jan 14, 4:32 pm, patsy wrote: Can someone please help me? I can't seem to get the formula right. The following is what I want to do. IF Column A=SHAW in (Physical Plant.xls) then link it to Column A in (P-Card Log.xls), IF not than don't link. Hope this isn't too confusing but I could really use the help in the formula. Thanks. |
Excel Formula
Thanks Chuck.
Pete On Jan 14, 5:20*pm, CLR wrote: Nice post Pete...........well done Vaya con Dios, Chuck, CABGx3 "Pete_UK" wrote: In order t get the syntax right, it will be a lot easier if you have both files open to begin with, and both windows visible on screen (use Window | Arrange | Horizontal). Then with your cursor in A1 of the P- Card Log.xls file, begin to type this formula: =IF( at this point click into the window of the Physical Plant.xls file, click the appropriate sheet tab (assume this is Sheet1), and then click in A1 and Excel will have inserted some text for you in the formula bar, so that your formula will look something like: =IF('[Physical Plant.xls]Sheet1'!$A$1 then continue the formula by typing ="SHAW", and then click on the other window again and click on A1, so your formula will now look like: =IF('[Physical Plant.xls]Sheet1'!$A$1="SHAW",'[Physical Plant.xls]Sheet1'!$A$1 and then you need to add ,"") to the formula and then press <enter. You should end up with something like this: =IF('[Physical Plant.xls]Sheet1'!$A$1="SHAW",'[Physical Plant.xls]Sheet1'!$A$1,"") If you want to copy this down the column then you will need to amend the cell references so that they are no longer absolute references, like so: =IF('[Physical Plant.xls]Sheet1'!A1="SHAW",'[Physical Plant.xls]Sheet1'!A1,"") and then you can copy the formula down column A as required. If you close the Physical Plant.xls file, then your formula will expand to show the complete path to the file, so it will look something like: =IF('C:\Documents and Settings\User\My Documents\Developments\ [Physical Plant.xls]Sheet1'!A1="SHAW",'C:\Documents and Settings\User \My Documents\Developments\[Physical Plant.xls]Sheet1'!A1,"") which is why I suggested that you had both files open to begin with - I defy anyone to enter that formula correctly !! *<bg Hope this helps. Pete On Jan 14, 4:32 pm, patsy wrote: Can someone please help me? *I can't seem to get the formula right. *The following is what I want to do. IF Column A=SHAW in (Physical Plant.xls) then link it to Column A in (P-Card Log.xls), IF not than don't link. Hope this isn't too confusing but I could really use the help in the formula. *Thanks.- Hide quoted text - - Show quoted text - |
Excel Formula
That didn't work. The problem is that if Physical Plant.xls has anything in
column A = to SHAW then I want that row to LINK to another spreadsheet in another workbook called P-Card Log.xls. I think linking is going to be the problem. Thanks. "Pete_UK" wrote: In order t get the syntax right, it will be a lot easier if you have both files open to begin with, and both windows visible on screen (use Window | Arrange | Horizontal). Then with your cursor in A1 of the P- Card Log.xls file, begin to type this formula: =IF( at this point click into the window of the Physical Plant.xls file, click the appropriate sheet tab (assume this is Sheet1), and then click in A1 and Excel will have inserted some text for you in the formula bar, so that your formula will look something like: =IF('[Physical Plant.xls]Sheet1'!$A$1 then continue the formula by typing ="SHAW", and then click on the other window again and click on A1, so your formula will now look like: =IF('[Physical Plant.xls]Sheet1'!$A$1="SHAW",'[Physical Plant.xls]Sheet1'!$A$1 and then you need to add ,"") to the formula and then press <enter. You should end up with something like this: =IF('[Physical Plant.xls]Sheet1'!$A$1="SHAW",'[Physical Plant.xls]Sheet1'!$A$1,"") If you want to copy this down the column then you will need to amend the cell references so that they are no longer absolute references, like so: =IF('[Physical Plant.xls]Sheet1'!A1="SHAW",'[Physical Plant.xls]Sheet1'!A1,"") and then you can copy the formula down column A as required. If you close the Physical Plant.xls file, then your formula will expand to show the complete path to the file, so it will look something like: =IF('C:\Documents and Settings\User\My Documents\Developments\ [Physical Plant.xls]Sheet1'!A1="SHAW",'C:\Documents and Settings\User \My Documents\Developments\[Physical Plant.xls]Sheet1'!A1,"") which is why I suggested that you had both files open to begin with - I defy anyone to enter that formula correctly !! <bg Hope this helps. Pete On Jan 14, 4:32 pm, patsy wrote: Can someone please help me? I can't seem to get the formula right. The following is what I want to do. IF Column A=SHAW in (Physical Plant.xls) then link it to Column A in (P-Card Log.xls), IF not than don't link. Hope this isn't too confusing but I could really use the help in the formula. Thanks. |
Excel Formula
The formula can't be in Physical Plant.xls and PUSH data to P-Card
Log.xls, because formulae don't work that way - they PULL data from the source. If you have the formula in A1 of P-Card Log.xls, then amend it to the following: =IF('C:\Documents and Settings\User\My Documents\Developments\ [Physical Plant.xls]Sheet1'!$A1="SHAW",'C:\Documents and Settings \User \My Documents\Developments\[Physical Plant.xls]Sheet1'!A1,"") i.e. put a $ symbol in front of the first cell reference. Then you can copy this formula into B1, C1, D1 etc for as many columns as you need, and then copy the formulae in row 1 down for as many rows as you need. In P-Card Log.xls you will now see data on rows where "SHAW" appears in column A of the same row in the Physical Plant.xls file. Hope this helps. Pete On Jan 14, 8:23*pm, patsy wrote: That didn't work. *The problem is that if Physical Plant.xls has anything in column A = to SHAW then I want that row to LINK to another spreadsheet in another workbook called P-Card Log.xls. *I think linking is going to be the problem. Thanks. "Pete_UK" wrote: In order t get the syntax right, it will be a lot easier if you have both files open to begin with, and both windows visible on screen (use Window | Arrange | Horizontal). Then with your cursor in A1 of the P- Card Log.xls file, begin to type this formula: =IF( at this point click into the window of the Physical Plant.xls file, click the appropriate sheet tab (assume this is Sheet1), and then click in A1 and Excel will have inserted some text for you in the formula bar, so that your formula will look something like: =IF('[Physical Plant.xls]Sheet1'!$A$1 then continue the formula by typing ="SHAW", and then click on the other window again and click on A1, so your formula will now look like: =IF('[Physical Plant.xls]Sheet1'!$A$1="SHAW",'[Physical Plant.xls]Sheet1'!$A$1 and then you need to add ,"") to the formula and then press <enter. You should end up with something like this: =IF('[Physical Plant.xls]Sheet1'!$A$1="SHAW",'[Physical Plant.xls]Sheet1'!$A$1,"") If you want to copy this down the column then you will need to amend the cell references so that they are no longer absolute references, like so: =IF('[Physical Plant.xls]Sheet1'!A1="SHAW",'[Physical Plant.xls]Sheet1'!A1,"") and then you can copy the formula down column A as required. If you close the Physical Plant.xls file, then your formula will expand to show the complete path to the file, so it will look something like: =IF('C:\Documents and Settings\User\My Documents\Developments\ [Physical Plant.xls]Sheet1'!A1="SHAW",'C:\Documents and Settings\User \My Documents\Developments\[Physical Plant.xls]Sheet1'!A1,"") which is why I suggested that you had both files open to begin with - I defy anyone to enter that formula correctly !! *<bg Hope this helps. Pete On Jan 14, 4:32 pm, patsy wrote: Can someone please help me? *I can't seem to get the formula right. *The following is what I want to do. IF Column A=SHAW in (Physical Plant.xls) then link it to Column A in (P-Card Log.xls), IF not than don't link. Hope this isn't too confusing but I could really use the help in the formula. *Thanks.- Hide quoted text - - Show quoted text - |
| All times are GMT +1. The time now is 01:43 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com