|
Reference cell location
Want to determine the address of a given data point. The information is in
one excel sheet "data sheet" and the equation will be in another sheet "calc sheet". I used VLOOKUP to locate the data in the "data sheet" based on the criteria in "calc sheet". Now I need to determine the address of the returned data, which will be used for further calculations. Thanks Dan |
Reference cell location
It's better to get it like this, if
=VLOOKUP(A1,B2:C30,2,0) returns a value from C2:C30 then =INDEX(C2:C30,MATCH(A1,B2:B30,0)) will return the same value now index can be used within the cell function =CELL("address",INDEX(C2:C30,MATCH(A1,B2:B30,0))) will return the cell address, why you want to do this is beyond me though -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "Dan" wrote in message ... Want to determine the address of a given data point. The information is in one excel sheet "data sheet" and the equation will be in another sheet "calc sheet". I used VLOOKUP to locate the data in the "data sheet" based on the criteria in "calc sheet". Now I need to determine the address of the returned data, which will be used for further calculations. Thanks Dan |
Reference cell location
This works for the basic example but my cell is within another excel sheet.
It does not matter which method I use to locate the data, I need a means of determining the position within the spreadsheet. The CELL("address",reference) does not work when I intsert my VLOOKUP or even your CELL("address",INDEX(array range,MATCH(A1,B2:B30,0))). It returns an error. Regards, Dan "Peo Sjoblom" wrote: It's better to get it like this, if =VLOOKUP(A1,B2:C30,2,0) returns a value from C2:C30 then =INDEX(C2:C30,MATCH(A1,B2:B30,0)) will return the same value now index can be used within the cell function =CELL("address",INDEX(C2:C30,MATCH(A1,B2:B30,0))) will return the cell address, why you want to do this is beyond me though -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "Dan" wrote in message ... Want to determine the address of a given data point. The information is in one excel sheet "data sheet" and the equation will be in another sheet "calc sheet". I used VLOOKUP to locate the data in the "data sheet" based on the criteria in "calc sheet". Now I need to determine the address of the returned data, which will be used for further calculations. Thanks Dan |
Reference cell location
It doesn't matter where the the table is, it will return the address with
workbook name and sheet name if it is in another sheet. I suspect that you implement it incorrectly or that your data does not match (if you get #N/A). Create a fake table and test and you'll see that it works =CELL("address",INDEX(Sheet1!B2:B7,MATCH(A1,Sheet1 !A2:A7,0))) will return the cell address in Sheet1 so either you applied it incorrectly or there is no exact matched lookup value Regards, Peo Sjoblom "Dan" wrote: This works for the basic example but my cell is within another excel sheet. It does not matter which method I use to locate the data, I need a means of determining the position within the spreadsheet. The CELL("address",reference) does not work when I intsert my VLOOKUP or even your CELL("address",INDEX(array range,MATCH(A1,B2:B30,0))). It returns an error. Regards, Dan "Peo Sjoblom" wrote: It's better to get it like this, if =VLOOKUP(A1,B2:C30,2,0) returns a value from C2:C30 then =INDEX(C2:C30,MATCH(A1,B2:B30,0)) will return the same value now index can be used within the cell function =CELL("address",INDEX(C2:C30,MATCH(A1,B2:B30,0))) will return the cell address, why you want to do this is beyond me though -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "Dan" wrote in message ... Want to determine the address of a given data point. The information is in one excel sheet "data sheet" and the equation will be in another sheet "calc sheet". I used VLOOKUP to locate the data in the "data sheet" based on the criteria in "calc sheet". Now I need to determine the address of the returned data, which will be used for further calculations. Thanks Dan |
Reference cell location
I retried the equation and it works for your example but it will not work
when the "reference" for CELL is a VLOOKUP. I have VLOOKUP working in the cell directly above the CELL equation, then I copied and inserted the equation into the CELL("address",VLOOKUP(...)) but I get an error when I select enter. Not sure where the error is or why it will not work with VLOOKUP. Regards, Dan "Peo Sjoblom" wrote: It doesn't matter where the the table is, it will return the address with workbook name and sheet name if it is in another sheet. I suspect that you implement it incorrectly or that your data does not match (if you get #N/A). Create a fake table and test and you'll see that it works =CELL("address",INDEX(Sheet1!B2:B7,MATCH(A1,Sheet1 !A2:A7,0))) will return the cell address in Sheet1 so either you applied it incorrectly or there is no exact matched lookup value Regards, Peo Sjoblom "Dan" wrote: This works for the basic example but my cell is within another excel sheet. It does not matter which method I use to locate the data, I need a means of determining the position within the spreadsheet. The CELL("address",reference) does not work when I intsert my VLOOKUP or even your CELL("address",INDEX(array range,MATCH(A1,B2:B30,0))). It returns an error. Regards, Dan "Peo Sjoblom" wrote: It's better to get it like this, if =VLOOKUP(A1,B2:C30,2,0) returns a value from C2:C30 then =INDEX(C2:C30,MATCH(A1,B2:B30,0)) will return the same value now index can be used within the cell function =CELL("address",INDEX(C2:C30,MATCH(A1,B2:B30,0))) will return the cell address, why you want to do this is beyond me though -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "Dan" wrote in message ... Want to determine the address of a given data point. The information is in one excel sheet "data sheet" and the equation will be in another sheet "calc sheet". I used VLOOKUP to locate the data in the "data sheet" based on the criteria in "calc sheet". Now I need to determine the address of the returned data, which will be used for further calculations. Thanks Dan |
Reference cell location
It's because you cannot use VLOOKUP to get this, you have to replace the
vlookup with the index match combination or else it will never work, in my first example, to quote myself: "It's better to get it like this, if =VLOOKUP(A1,B2:C30,2,0) returns a value from C2:C30 then =INDEX(C2:C30,MATCH(A1,B2:B30,0)) will return the same value" VLOOKUP looks up a value in the leftmost column and then offset it by the index number you use, that's the reason, if you have a VLOOKUP formula in the cell above you still would need index, assume that the vlookup value is 10 and it was looked up in Sheet1 column F2:F50 =CELL("address",INDEX(Sheet1!F2:F50,MATCH(VLOOKUP( A1,Sheet1!A2:F50,6,0),Sheet1!F2:F50,0))) but that doesn't make any sense at all, it's much better to change the vlookup to an index match -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "Dan" wrote in message ... I retried the equation and it works for your example but it will not work when the "reference" for CELL is a VLOOKUP. I have VLOOKUP working in the cell directly above the CELL equation, then I copied and inserted the equation into the CELL("address",VLOOKUP(...)) but I get an error when I select enter. Not sure where the error is or why it will not work with VLOOKUP. Regards, Dan "Peo Sjoblom" wrote: It doesn't matter where the the table is, it will return the address with workbook name and sheet name if it is in another sheet. I suspect that you implement it incorrectly or that your data does not match (if you get #N/A). Create a fake table and test and you'll see that it works =CELL("address",INDEX(Sheet1!B2:B7,MATCH(A1,Sheet1 !A2:A7,0))) will return the cell address in Sheet1 so either you applied it incorrectly or there is no exact matched lookup value Regards, Peo Sjoblom "Dan" wrote: This works for the basic example but my cell is within another excel sheet. It does not matter which method I use to locate the data, I need a means of determining the position within the spreadsheet. The CELL("address",reference) does not work when I intsert my VLOOKUP or even your CELL("address",INDEX(array range,MATCH(A1,B2:B30,0))). It returns an error. Regards, Dan "Peo Sjoblom" wrote: It's better to get it like this, if =VLOOKUP(A1,B2:C30,2,0) returns a value from C2:C30 then =INDEX(C2:C30,MATCH(A1,B2:B30,0)) will return the same value now index can be used within the cell function =CELL("address",INDEX(C2:C30,MATCH(A1,B2:B30,0))) will return the cell address, why you want to do this is beyond me though -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "Dan" wrote in message ... Want to determine the address of a given data point. The information is in one excel sheet "data sheet" and the equation will be in another sheet "calc sheet". I used VLOOKUP to locate the data in the "data sheet" based on the criteria in "calc sheet". Now I need to determine the address of the returned data, which will be used for further calculations. Thanks Dan |
Reference cell location
My apologies. I missed the statement about that VLOOKUP would not work within
the CELL call. I rearranged my equations to work with INDEX and all other equations were modified to suit. I appreciate all the assistance. Regards, Dan "Peo Sjoblom" wrote: It's because you cannot use VLOOKUP to get this, you have to replace the vlookup with the index match combination or else it will never work, in my first example, to quote myself: "It's better to get it like this, if =VLOOKUP(A1,B2:C30,2,0) returns a value from C2:C30 then =INDEX(C2:C30,MATCH(A1,B2:B30,0)) will return the same value" VLOOKUP looks up a value in the leftmost column and then offset it by the index number you use, that's the reason, if you have a VLOOKUP formula in the cell above you still would need index, assume that the vlookup value is 10 and it was looked up in Sheet1 column F2:F50 =CELL("address",INDEX(Sheet1!F2:F50,MATCH(VLOOKUP( A1,Sheet1!A2:F50,6,0),Sheet1!F2:F50,0))) but that doesn't make any sense at all, it's much better to change the vlookup to an index match -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "Dan" wrote in message ... I retried the equation and it works for your example but it will not work when the "reference" for CELL is a VLOOKUP. I have VLOOKUP working in the cell directly above the CELL equation, then I copied and inserted the equation into the CELL("address",VLOOKUP(...)) but I get an error when I select enter. Not sure where the error is or why it will not work with VLOOKUP. Regards, Dan "Peo Sjoblom" wrote: It doesn't matter where the the table is, it will return the address with workbook name and sheet name if it is in another sheet. I suspect that you implement it incorrectly or that your data does not match (if you get #N/A). Create a fake table and test and you'll see that it works =CELL("address",INDEX(Sheet1!B2:B7,MATCH(A1,Sheet1 !A2:A7,0))) will return the cell address in Sheet1 so either you applied it incorrectly or there is no exact matched lookup value Regards, Peo Sjoblom "Dan" wrote: This works for the basic example but my cell is within another excel sheet. It does not matter which method I use to locate the data, I need a means of determining the position within the spreadsheet. The CELL("address",reference) does not work when I intsert my VLOOKUP or even your CELL("address",INDEX(array range,MATCH(A1,B2:B30,0))). It returns an error. Regards, Dan "Peo Sjoblom" wrote: It's better to get it like this, if =VLOOKUP(A1,B2:C30,2,0) returns a value from C2:C30 then =INDEX(C2:C30,MATCH(A1,B2:B30,0)) will return the same value now index can be used within the cell function =CELL("address",INDEX(C2:C30,MATCH(A1,B2:B30,0))) will return the cell address, why you want to do this is beyond me though -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "Dan" wrote in message ... Want to determine the address of a given data point. The information is in one excel sheet "data sheet" and the equation will be in another sheet "calc sheet". I used VLOOKUP to locate the data in the "data sheet" based on the criteria in "calc sheet". Now I need to determine the address of the returned data, which will be used for further calculations. Thanks Dan |
Reference cell location
Thanks for the feedback
-- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "Dan" wrote in message ... My apologies. I missed the statement about that VLOOKUP would not work within the CELL call. I rearranged my equations to work with INDEX and all other equations were modified to suit. I appreciate all the assistance. Regards, Dan "Peo Sjoblom" wrote: It's because you cannot use VLOOKUP to get this, you have to replace the vlookup with the index match combination or else it will never work, in my first example, to quote myself: "It's better to get it like this, if =VLOOKUP(A1,B2:C30,2,0) returns a value from C2:C30 then =INDEX(C2:C30,MATCH(A1,B2:B30,0)) will return the same value" VLOOKUP looks up a value in the leftmost column and then offset it by the index number you use, that's the reason, if you have a VLOOKUP formula in the cell above you still would need index, assume that the vlookup value is 10 and it was looked up in Sheet1 column F2:F50 =CELL("address",INDEX(Sheet1!F2:F50,MATCH(VLOOKUP( A1,Sheet1!A2:F50,6,0),Sheet1!F2:F50,0))) but that doesn't make any sense at all, it's much better to change the vlookup to an index match -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "Dan" wrote in message ... I retried the equation and it works for your example but it will not work when the "reference" for CELL is a VLOOKUP. I have VLOOKUP working in the cell directly above the CELL equation, then I copied and inserted the equation into the CELL("address",VLOOKUP(...)) but I get an error when I select enter. Not sure where the error is or why it will not work with VLOOKUP. Regards, Dan "Peo Sjoblom" wrote: It doesn't matter where the the table is, it will return the address with workbook name and sheet name if it is in another sheet. I suspect that you implement it incorrectly or that your data does not match (if you get #N/A). Create a fake table and test and you'll see that it works =CELL("address",INDEX(Sheet1!B2:B7,MATCH(A1,Sheet1 !A2:A7,0))) will return the cell address in Sheet1 so either you applied it incorrectly or there is no exact matched lookup value Regards, Peo Sjoblom "Dan" wrote: This works for the basic example but my cell is within another excel sheet. It does not matter which method I use to locate the data, I need a means of determining the position within the spreadsheet. The CELL("address",reference) does not work when I intsert my VLOOKUP or even your CELL("address",INDEX(array range,MATCH(A1,B2:B30,0))). It returns an error. Regards, Dan "Peo Sjoblom" wrote: It's better to get it like this, if =VLOOKUP(A1,B2:C30,2,0) returns a value from C2:C30 then =INDEX(C2:C30,MATCH(A1,B2:B30,0)) will return the same value now index can be used within the cell function =CELL("address",INDEX(C2:C30,MATCH(A1,B2:B30,0))) will return the cell address, why you want to do this is beyond me though -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "Dan" wrote in message ... Want to determine the address of a given data point. The information is in one excel sheet "data sheet" and the equation will be in another sheet "calc sheet". I used VLOOKUP to locate the data in the "data sheet" based on the criteria in "calc sheet". Now I need to determine the address of the returned data, which will be used for further calculations. Thanks Dan |
| All times are GMT +1. The time now is 12:00 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com