|
One more try!!
I have 2 posting here, but everybody gets bogged down on the wrong part of
the question. My original ques ion is very similar to what I have seen posted here, yet it seems to be a problem. Right now the problem is no the formula I use except for I need to specific a range that includes using more than one worksheet tab. Here is the formula it works for one worksheet because I only specifics one worksheet. I just need to know how to encode the range of the other worksheet tabs. first worksheet is sheet1 and goes through sheet16 in the same work book. =INDEX('D:\GLOBAL X RAY\[customer price level4.xls]Sheet1'!$A2:$C2,3) How do I edit this formula to include the other worksheet. |
One more try!!
With customer price level4.xls open* simultaneously
*this is a requirement for INDIRECT to work And in a sheet in another book, you want this kind of link propagation in C2 down, viz: In C2: ='[customer price level4.xls]Sheet1'!$C$2 In C3: ='[customer price level4.xls]Sheet2'!$C$2 etc where only the sheetname referenced changes/increments as you copy down, then you could use INDIRECT .. Place this in C2: =INDIRECT("'[customer price level4.xls]Sheet"&ROWS($1:1)&"'!C2") Copy C2 down by 16 rows (to C17) to return the contents of C2 in Sheet1, Sheet2, ... Sheet16 in customer price level4.xls. Note: Wrt your posted formula =INDEX('D:\GLOBAL X RAY\[customer price level4.xls]Sheet1'!$A2:$C2,3) If the book: customer price level4.xls is open simultaneously, the above is simply the same as: ='[customer price level4.xls]Sheet1'!$C$2 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "klafert" wrote: I have 2 posting here, but everybody gets bogged down on the wrong part of the question. My original ques ion is very similar to what I have seen posted here, yet it seems to be a problem. Right now the problem is no the formula I use except for I need to specific a range that includes using more than one worksheet tab. Here is the formula it works for one worksheet because I only specifics one worksheet. I just need to know how to encode the range of the other worksheet tabs. first worksheet is sheet1 and goes through sheet16 in the same work book. =INDEX('D:\GLOBAL X RAY\[customer price level4.xls]Sheet1'!$A2:$C2,3) How do I edit this formula to include the other worksheet. |
One more try!!
Look, people are trying to help you here, but it is not exactly clear
what you are trying to do, so we have pointed out that your formula is not searching for anything - basically, you are returning the 3rd cell in the range A2:C2 in Sheet1 of your other workbook. So, this is basically a straightforward link to cell C2, and you could make your formula: ='D:\GLOBAL X RAY\[customer price level4.xls]Sheet1'!$C2 If you wanted to return data from another sheet, all you need to do is change the sheet reference, i.e.: ='D:\GLOBAL X RAY\[customer price level4.xls]Sheet2'!$C2 assuming you still want to get data from C2 of that sheet. If you don't want to manually change the sheet name, then this could be "calculated" or derived from a cell on your sheet by means of the INDIRECT function, but this only works if both workbooks are open at the same time. You originally asked how you could get the data from one of up to 16 sheets in the other workbook - the way you would normally do this with a VLOOKUP on 3 sheets, for example, is along the lines of: =IF(ISNA(VLOOKUP(on_sheet1), IF(ISNA(VLOOKUP(on_sheet2), IF(ISNA(VLOOKUP(on_sheet3),"not present", VLOOKUP(on_sheet3)), VLOOKUP(on_sheet2)), VLOOKUP(on_sheet1)) but with a limit of 7 nested IFs in Excel you would have to use a different approach. Hence my questions to you - can you combine the 16 sheets into one composite sheet, and what ranges of data do you have on each sheet? Please keep all responses in the same thread - I, for one, can't keep up with all your multi-postings. Pete On Jul 2, 9:42 am, klafert wrote: I have 2 posting here, but everybody gets bogged down on the wrong part of the question. My original ques ion is very similar to what I have seen posted here, yet it seems to be a problem. Right now the problem is no the formula I use except for I need to specific a range that includes using more than one worksheet tab. Here is the formula it works for one worksheet because I only specifics one worksheet. I just need to know how to encode the range of the other worksheet tabs. first worksheet is sheet1 and goes through sheet16 in the same work book. =INDEX('D:\GLOBAL X RAY\[customer price level4.xls]Sheet1'!$A2:$C2,3) How do I edit this formula to include the other worksheet. |
One more try!!
Check your other posts in the other newsgroups, too.
klafert wrote: I have 2 posting here, but everybody gets bogged down on the wrong part of the question. My original ques ion is very similar to what I have seen posted here, yet it seems to be a problem. Right now the problem is no the formula I use except for I need to specific a range that includes using more than one worksheet tab. Here is the formula it works for one worksheet because I only specifics one worksheet. I just need to know how to encode the range of the other worksheet tabs. first worksheet is sheet1 and goes through sheet16 in the same work book. =INDEX('D:\GLOBAL X RAY\[customer price level4.xls]Sheet1'!$A2:$C2,3) How do I edit this formula to include the other worksheet. -- Dave Peterson |
One more try!!
sorry Pete,
I guess I am getting frustrated and need this today. I am trying to make myself clear. I am going to start from scratch. Source spreadsheet - has all the data customer Id Item ID Price joe blow 2mxr 50.00 carol windy 2mxr 60.00 2nd spreadsheet - this spreadsheet will pull the data from the source spreadsheet Customer ID Item ID Billing Rate Joe blow 2mxr ???? carol windy 2mxr ??? The Billing rate is pulled from the source sheet from the price column. So my second sheet will have the 1st two column filled in with the customer id Item id, but not the Bill rate (Price from source sheet). The source sheet is created in Crystal reports and exported to Excel. So, there are no formulas in this sheet. But due to the amount of data, when Crystal export the data it creates 16 worksheets in one workbook. The second sheets needs a formula that will check Column A (customer id) + B (Item Id) when they match then pull the price into column c of the second spreadsheet for the billing rate. In other words, each customer mite have a different price for the same item. The spreadsheet that came from Crystal has the correct price for each customers. So, the formula needs to search all 16 tabs and they are labeled sheet1, sheet2, etc up to sheet 16 and return the price from the source sheet into the second spreadsheet. I done this before and usually use the VLOOKup but since I have to meet two criteria and due to the many tabs, I need a new formula. I know that I have to adjust my formula. I more than appreciate any help I can get and would even pay a fee to get this done. I have a lot going on not to mention Brother in hospital. Thanks, Mary "Pete_UK" wrote: Look, people are trying to help you here, but it is not exactly clear what you are trying to do, so we have pointed out that your formula is not searching for anything - basically, you are returning the 3rd cell in the range A2:C2 in Sheet1 of your other workbook. So, this is basically a straightforward link to cell C2, and you could make your formula: ='D:\GLOBAL X RAY\[customer price level4.xls]Sheet1'!$C2 If you wanted to return data from another sheet, all you need to do is change the sheet reference, i.e.: ='D:\GLOBAL X RAY\[customer price level4.xls]Sheet2'!$C2 assuming you still want to get data from C2 of that sheet. If you don't want to manually change the sheet name, then this could be "calculated" or derived from a cell on your sheet by means of the INDIRECT function, but this only works if both workbooks are open at the same time. You originally asked how you could get the data from one of up to 16 sheets in the other workbook - the way you would normally do this with a VLOOKUP on 3 sheets, for example, is along the lines of: =IF(ISNA(VLOOKUP(on_sheet1), IF(ISNA(VLOOKUP(on_sheet2), IF(ISNA(VLOOKUP(on_sheet3),"not present", VLOOKUP(on_sheet3)), VLOOKUP(on_sheet2)), VLOOKUP(on_sheet1)) but with a limit of 7 nested IFs in Excel you would have to use a different approach. Hence my questions to you - can you combine the 16 sheets into one composite sheet, and what ranges of data do you have on each sheet? Please keep all responses in the same thread - I, for one, can't keep up with all your multi-postings. Pete On Jul 2, 9:42 am, klafert wrote: I have 2 posting here, but everybody gets bogged down on the wrong part of the question. My original ques ion is very similar to what I have seen posted here, yet it seems to be a problem. Right now the problem is no the formula I use except for I need to specific a range that includes using more than one worksheet tab. Here is the formula it works for one worksheet because I only specifics one worksheet. I just need to know how to encode the range of the other worksheet tabs. first worksheet is sheet1 and goes through sheet16 in the same work book. =INDEX('D:\GLOBAL X RAY\[customer price level4.xls]Sheet1'!$A2:$C2,3) How do I edit this formula to include the other worksheet. |
One more try!!
Ok, it's a bit clearer now. Presumably you have so much data that you
cannot join any of the sheets together? Is it possible for you to add an extra column C in each of the sheets 1 -16 ? If so, then you can add this formula: =A2&B2 and copy it down each sheet (double-click the fill handle - the small black square in the bottom right corner of the cursor). Then in your other workbook you can have a formula in D2 along the lines of: =IF(ISNA(VLOOKUP($A2&$B2,'D:\GLOBAL X RAY\[customer price level4.xls]Sheet1'!$C:$D,2,0)),"",VLOOKUP($A2&$B2,'D:\GLOBAL X RAY\ [customer price level4.xls]Sheet1'!$C:$D,2,0)) & IF(ISNA(VLOOKUP($A2& $B2,'D:\GLOBAL X RAY\[customer price level4.xls]Sheet2'!$C:$D, 2,0)),"",VLOOKUP($A2&$B2,'D:\GLOBAL X RAY\[customer price level4.xls]Sheet2'!$C:$D,2,0)) & IF(ISNA(VLOOKUP($A2&$B2,'D:\GLOBAL X RAY\[customer price level4.xls]Sheet3'!$C:$D,2,0)),"",VLOOKUP($A2& $B2,'D:\GLOBAL X RAY\[customer price level4.xls]Sheet3'!$C:$D,2,0)) & IF(ISNA(VLOOKUP($A2&$B2,'D:\GLOBAL X RAY\[customer price level4.xls]Sheet4'!$C:$D,2,0)),"",VLOOKUP($A2&$B2,'D:\GLOBAL X RAY\ [customer price level4.xls]Sheet4'!$C:$D,2,0)) Note this is all one formula - you will need to be wary of spurious line breaks. This will only look at the first 4 sheets, as the formula will be too long for all 16, so copy the formula into E2:G2 and for E2 change the sheet references from Sheet1 to Sheet5, Sheet2 to Sheet6, Sheet3 to Sheet7 and Sheet4 to Sheet8 (you can use Edit|Replace to do this). Do the same with the formula in F2, but it should refer to Sheets 9 to 12, and G2 should refer to Sheets 13 to 16. In C2 you can then add a formula to join these together: =SUM(D2:G2) which is the value that you want - if it is zero then there is no match in any of the 16 sheets. Copy the formulae in C2:G2 down the sheet as required - you can hide columns D to G if you don't want to see them. Hope this helps. Pete On Jul 2, 5:28 pm, klafert wrote: sorry Pete, I guess I am getting frustrated and need this today. I am trying to make myself clear. I am going to start from scratch. Source spreadsheet - has all the data customer Id Item ID Price joe blow 2mxr 50.00 carol windy 2mxr 60.00 2nd spreadsheet - this spreadsheet will pull the data from the source spreadsheet Customer ID Item ID Billing Rate Joe blow 2mxr ???? carol windy 2mxr ??? The Billing rate is pulled from the source sheet from the price column. So my second sheet will have the 1st two column filled in with the customer id Item id, but not the Bill rate (Price from source sheet). The source sheet is created in Crystal reports and exported to Excel. So, there are no formulas in this sheet. But due to the amount of data, when Crystal export the data it creates 16 worksheets in one workbook. The second sheets needs a formula that will check Column A (customer id) + B (Item Id) when they match then pull the price into column c of the second spreadsheet for the billing rate. In other words, each customer mite have a different price for the same item. The spreadsheet that came from Crystal has the correct price for each customers. So, the formula needs to search all 16 tabs and they are labeled sheet1, sheet2, etc up to sheet 16 and return the price from the source sheet into the second spreadsheet. I done this before and usually use the VLOOKup but since I have to meet two criteria and due to the many tabs, I need a new formula. I know that I have to adjust my formula. I more than appreciate any help I can get and would even pay a fee to get this done. I have a lot going on not to mention Brother in hospital. Thanks, Mary "Pete_UK" wrote: Look, people are trying to help you here, but it is not exactly clear what you are trying to do, so we have pointed out that your formula is not searching for anything - basically, you are returning the 3rd cell in the range A2:C2 in Sheet1 of your other workbook. So, this is basically a straightforward link to cell C2, and you could make your formula: ='D:\GLOBAL X RAY\[customer price level4.xls]Sheet1'!$C2 If you wanted to return data from another sheet, all you need to do is change the sheet reference, i.e.: ='D:\GLOBAL X RAY\[customer price level4.xls]Sheet2'!$C2 assuming you still want to get data from C2 of that sheet. If you don't want to manually change the sheet name, then this could be "calculated" or derived from a cell on your sheet by means of the INDIRECT function, but this only works if both workbooks are open at the same time. You originally asked how you could get the data from one of up to 16 sheets in the other workbook - the way you would normally do this with a VLOOKUP on 3 sheets, for example, is along the lines of: =IF(ISNA(VLOOKUP(on_sheet1), IF(ISNA(VLOOKUP(on_sheet2), IF(ISNA(VLOOKUP(on_sheet3),"not present", VLOOKUP(on_sheet3)), VLOOKUP(on_sheet2)), VLOOKUP(on_sheet1)) but with a limit of 7 nested IFs in Excel you would have to use a different approach. Hence my questions to you - can you combine the 16 sheets into one composite sheet, and what ranges of data do you have on each sheet? Please keep all responses in the same thread - I, for one, can't keep up with all your multi-postings. Pete On Jul 2, 9:42 am, klafert wrote: I have 2 posting here, but everybody gets bogged down on the wrong part of the question. My original ques ion is very similar to what I have seen posted here, yet it seems to be a problem. Right now the problem is no the formula I use except for I need to specific a range that includes using more than one worksheet tab. Here is the formula it works for one worksheet because I only specifics one worksheet. I just need to know how to encode the range of the other worksheet tabs. first worksheet is sheet1 and goes through sheet16 in the same work book. =INDEX('D:\GLOBAL X RAY\[customer price level4.xls]Sheet1'!$A2:$C2,3) How do I edit this formula to include the other worksheet.- Hide quoted text - - Show quoted text - |
One more try!!
Sorry, the SUM won't pick up a numeric if it is in text format, so put
this in C2 instead: =(D2&E2&F2&G2)*1 and then copy C2:G2 down. I presume you would want to do something with the billing rate once you have brought it into the sheet. Note that the long formulae avoid the use of INDIRECT, for which you would need to have the other workbook open (and which in another thread you have said you do not want to do). Hope this helps. Pete On Jul 2, 7:48 pm, Pete_UK wrote: Ok, it's a bit clearer now. Presumably you have so much data that you cannot join any of the sheets together? Is it possible for you to add an extra column C in each of the sheets 1 -16 ? If so, then you can add this formula: =A2&B2 and copy it down each sheet (double-click the fill handle - the small black square in the bottom right corner of the cursor). Then in your other workbook you can have a formula in D2 along the lines of: =IF(ISNA(VLOOKUP($A2&$B2,'D:\GLOBAL X RAY\[customer price level4.xls]Sheet1'!$C:$D,2,0)),"",VLOOKUP($A2&$B2,'D:\GLOBAL X RAY\ [customer price level4.xls]Sheet1'!$C:$D,2,0)) & IF(ISNA(VLOOKUP($A2& $B2,'D:\GLOBAL X RAY\[customer price level4.xls]Sheet2'!$C:$D, 2,0)),"",VLOOKUP($A2&$B2,'D:\GLOBAL X RAY\[customer price level4.xls]Sheet2'!$C:$D,2,0)) & IF(ISNA(VLOOKUP($A2&$B2,'D:\GLOBAL X RAY\[customer price level4.xls]Sheet3'!$C:$D,2,0)),"",VLOOKUP($A2& $B2,'D:\GLOBAL X RAY\[customer price level4.xls]Sheet3'!$C:$D,2,0)) & IF(ISNA(VLOOKUP($A2&$B2,'D:\GLOBAL X RAY\[customer price level4.xls]Sheet4'!$C:$D,2,0)),"",VLOOKUP($A2&$B2,'D:\GLOBAL X RAY\ [customer price level4.xls]Sheet4'!$C:$D,2,0)) Note this is all one formula - you will need to be wary of spurious line breaks. This will only look at the first 4 sheets, as the formula will be too long for all 16, so copy the formula into E2:G2 and for E2 change the sheet references from Sheet1 to Sheet5, Sheet2 to Sheet6, Sheet3 to Sheet7 and Sheet4 to Sheet8 (you can use Edit|Replace to do this). Do the same with the formula in F2, but it should refer to Sheets 9 to 12, and G2 should refer to Sheets 13 to 16. In C2 you can then add a formula to join these together: =SUM(D2:G2) which is the value that you want - if it is zero then there is no match in any of the 16 sheets. Copy the formulae in C2:G2 down the sheet as required - you can hide columns D to G if you don't want to see them. Hope this helps. Pete On Jul 2, 5:28 pm, klafert wrote: sorry Pete, I guess I am getting frustrated and need this today. I am trying to make myself clear. I am going to start from scratch. Source spreadsheet - has all the data customer Id Item ID Price joe blow 2mxr 50.00 carol windy 2mxr 60.00 2nd spreadsheet - this spreadsheet will pull the data from the source spreadsheet Customer ID Item ID Billing Rate Joe blow 2mxr ???? carol windy 2mxr ??? The Billing rate is pulled from the source sheet from the price column. So my second sheet will have the 1st two column filled in with the customer id Item id, but not the Bill rate (Price from source sheet). The source sheet is created in Crystal reports and exported to Excel. So, there are no formulas in this sheet. But due to the amount of data, when Crystal export the data it creates 16 worksheets in one workbook. The second sheets needs a formula that will check Column A (customer id) + B (Item Id) when they match then pull the price into column c of the second spreadsheet for the billing rate. In other words, each customer mite have a different price for the same item. The spreadsheet that came from Crystal has the correct price for each customers. So, the formula needs to search all 16 tabs and they are labeled sheet1, sheet2, etc up to sheet 16 and return the price from the source sheet into the second spreadsheet. I done this before and usually use the VLOOKup but since I have to meet two criteria and due to the many tabs, I need a new formula. I know that I have to adjust my formula. I more than appreciate any help I can get and would even pay a fee to get this done. I have a lot going on not to mention Brother in hospital. Thanks, Mary "Pete_UK" wrote: Look, people are trying to help you here, but it is not exactly clear what you are trying to do, so we have pointed out that your formula is not searching for anything - basically, you are returning the 3rd cell in the range A2:C2 in Sheet1 of your other workbook. So, this is basically a straightforward link to cell C2, and you could make your formula: ='D:\GLOBAL X RAY\[customer price level4.xls]Sheet1'!$C2 If you wanted to return data from another sheet, all you need to do is change the sheet reference, i.e.: ='D:\GLOBAL X RAY\[customer price level4.xls]Sheet2'!$C2 assuming you still want to get data from C2 of that sheet. If you don't want to manually change the sheet name, then this could be "calculated" or derived from a cell on your sheet by means of the INDIRECT function, but this only works if both workbooks are open at the same time. You originally asked how you could get the data from one of up to 16 sheets in the other workbook - the way you would normally do this with a VLOOKUP on 3 sheets, for example, is along the lines of: =IF(ISNA(VLOOKUP(on_sheet1), IF(ISNA(VLOOKUP(on_sheet2), IF(ISNA(VLOOKUP(on_sheet3),"not present", VLOOKUP(on_sheet3)), VLOOKUP(on_sheet2)), VLOOKUP(on_sheet1)) but with a limit of 7 nested IFs in Excel you would have to use a different approach. Hence my questions to you - can you combine the 16 sheets into one composite sheet, and what ranges of data do you have on each sheet? Please keep all responses in the same thread - I, for one, can't keep up with all your multi-postings. Pete On Jul 2, 9:42 am, klafert wrote: I have 2 posting here, but everybody gets bogged down on the wrong part of the question. My original ques ion is very similar to what I have seen posted here, yet it seems to be a problem. Right now the problem is no the formula I use except for I need to specific a range that includes using more than one worksheet tab. Here is the formula it works for one worksheet because I only specifics one worksheet. I just need to know how to encode the range of the other worksheet tabs. first worksheet is sheet1 and goes through sheet16 in the same work book. =INDEX('D:\GLOBAL X RAY\[customer price level4.xls]Sheet1'!$A2:$C2,3) How do I edit this formula to include the other worksheet.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
One more try!!
I will give this a try, but once I have the billing rate that is all I need.
At that point this I will do a paste special - values only. Then save the spreadsheet in .csv format and import it into another program. So, once I have the billing rate I am done. "Pete_UK" wrote: Sorry, the SUM won't pick up a numeric if it is in text format, so put this in C2 instead: =(D2&E2&F2&G2)*1 and then copy C2:G2 down. I presume you would want to do something with the billing rate once you have brought it into the sheet. Note that the long formulae avoid the use of INDIRECT, for which you would need to have the other workbook open (and which in another thread you have said you do not want to do). Hope this helps. Pete On Jul 2, 7:48 pm, Pete_UK wrote: Ok, it's a bit clearer now. Presumably you have so much data that you cannot join any of the sheets together? Is it possible for you to add an extra column C in each of the sheets 1 -16 ? If so, then you can add this formula: =A2&B2 and copy it down each sheet (double-click the fill handle - the small black square in the bottom right corner of the cursor). Then in your other workbook you can have a formula in D2 along the lines of: =IF(ISNA(VLOOKUP($A2&$B2,'D:\GLOBAL X RAY\[customer price level4.xls]Sheet1'!$C:$D,2,0)),"",VLOOKUP($A2&$B2,'D:\GLOBAL X RAY\ [customer price level4.xls]Sheet1'!$C:$D,2,0)) & IF(ISNA(VLOOKUP($A2& $B2,'D:\GLOBAL X RAY\[customer price level4.xls]Sheet2'!$C:$D, 2,0)),"",VLOOKUP($A2&$B2,'D:\GLOBAL X RAY\[customer price level4.xls]Sheet2'!$C:$D,2,0)) & IF(ISNA(VLOOKUP($A2&$B2,'D:\GLOBAL X RAY\[customer price level4.xls]Sheet3'!$C:$D,2,0)),"",VLOOKUP($A2& $B2,'D:\GLOBAL X RAY\[customer price level4.xls]Sheet3'!$C:$D,2,0)) & IF(ISNA(VLOOKUP($A2&$B2,'D:\GLOBAL X RAY\[customer price level4.xls]Sheet4'!$C:$D,2,0)),"",VLOOKUP($A2&$B2,'D:\GLOBAL X RAY\ [customer price level4.xls]Sheet4'!$C:$D,2,0)) Note this is all one formula - you will need to be wary of spurious line breaks. This will only look at the first 4 sheets, as the formula will be too long for all 16, so copy the formula into E2:G2 and for E2 change the sheet references from Sheet1 to Sheet5, Sheet2 to Sheet6, Sheet3 to Sheet7 and Sheet4 to Sheet8 (you can use Edit|Replace to do this). Do the same with the formula in F2, but it should refer to Sheets 9 to 12, and G2 should refer to Sheets 13 to 16. In C2 you can then add a formula to join these together: =SUM(D2:G2) which is the value that you want - if it is zero then there is no match in any of the 16 sheets. Copy the formulae in C2:G2 down the sheet as required - you can hide columns D to G if you don't want to see them. Hope this helps. Pete On Jul 2, 5:28 pm, klafert wrote: sorry Pete, I guess I am getting frustrated and need this today. I am trying to make myself clear. I am going to start from scratch. Source spreadsheet - has all the data customer Id Item ID Price joe blow 2mxr 50.00 carol windy 2mxr 60.00 2nd spreadsheet - this spreadsheet will pull the data from the source spreadsheet Customer ID Item ID Billing Rate Joe blow 2mxr ???? carol windy 2mxr ??? The Billing rate is pulled from the source sheet from the price column. So my second sheet will have the 1st two column filled in with the customer id Item id, but not the Bill rate (Price from source sheet). The source sheet is created in Crystal reports and exported to Excel. So, there are no formulas in this sheet. But due to the amount of data, when Crystal export the data it creates 16 worksheets in one workbook. The second sheets needs a formula that will check Column A (customer id) + B (Item Id) when they match then pull the price into column c of the second spreadsheet for the billing rate. In other words, each customer mite have a different price for the same item. The spreadsheet that came from Crystal has the correct price for each customers. So, the formula needs to search all 16 tabs and they are labeled sheet1, sheet2, etc up to sheet 16 and return the price from the source sheet into the second spreadsheet. I done this before and usually use the VLOOKup but since I have to meet two criteria and due to the many tabs, I need a new formula. I know that I have to adjust my formula. I more than appreciate any help I can get and would even pay a fee to get this done. I have a lot going on not to mention Brother in hospital. Thanks, Mary "Pete_UK" wrote: Look, people are trying to help you here, but it is not exactly clear what you are trying to do, so we have pointed out that your formula is not searching for anything - basically, you are returning the 3rd cell in the range A2:C2 in Sheet1 of your other workbook. So, this is basically a straightforward link to cell C2, and you could make your formula: ='D:\GLOBAL X RAY\[customer price level4.xls]Sheet1'!$C2 If you wanted to return data from another sheet, all you need to do is change the sheet reference, i.e.: ='D:\GLOBAL X RAY\[customer price level4.xls]Sheet2'!$C2 assuming you still want to get data from C2 of that sheet. If you don't want to manually change the sheet name, then this could be "calculated" or derived from a cell on your sheet by means of the INDIRECT function, but this only works if both workbooks are open at the same time. You originally asked how you could get the data from one of up to 16 sheets in the other workbook - the way you would normally do this with a VLOOKUP on 3 sheets, for example, is along the lines of: =IF(ISNA(VLOOKUP(on_sheet1), IF(ISNA(VLOOKUP(on_sheet2), IF(ISNA(VLOOKUP(on_sheet3),"not present", VLOOKUP(on_sheet3)), VLOOKUP(on_sheet2)), VLOOKUP(on_sheet1)) but with a limit of 7 nested IFs in Excel you would have to use a different approach. Hence my questions to you - can you combine the 16 sheets into one composite sheet, and what ranges of data do you have on each sheet? Please keep all responses in the same thread - I, for one, can't keep up with all your multi-postings. Pete On Jul 2, 9:42 am, klafert wrote: I have 2 posting here, but everybody gets bogged down on the wrong part of the question. My original ques ion is very similar to what I have seen posted here, yet it seems to be a problem. Right now the problem is no the formula I use except for I need to specific a range that includes using more than one worksheet tab. Here is the formula it works for one worksheet because I only specifics one worksheet. I just need to know how to encode the range of the other worksheet tabs. first worksheet is sheet1 and goes through sheet16 in the same work book. =INDEX('D:\GLOBAL X RAY\[customer price level4.xls]Sheet1'!$A2:$C2,3) How do I edit this formula to include the other worksheet.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| All times are GMT +1. The time now is 05:45 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com