|
matching formula
I need to match the returned value in one formula with the text in another
formula. How can I do this? Formula 1: ='M-Z Supplier List'!B11 and returns" Mann" Formula 2 : = Mann'!B11 and returns a dollar value TIA |
='M-Z Supplier List'!B11&" "&TEXT(Mann'!B11,"$#,##0.00")
or if you meant that one formula retruns a string that you want to use as a reference in another formula =INDIRECT("'"&'M-Z Supplier List'!B11&"'!B11") -- Regards, Peo Sjoblom "Todd" wrote in message ... I need to match the returned value in one formula with the text in another formula. How can I do this? Formula 1: ='M-Z Supplier List'!B11 and returns" Mann" Formula 2 : = Mann'!B11 and returns a dollar value TIA |
Thanks, that brings me a lot closer! I don't understand what you meant well
enough, sorry. I will try to explain better. I need to match the text in column 1 that a formula returns with the actual formulas in column 2. column 1 column 2 ='M-Z Supplier List'!$B$11 = Mann!B11 When I look at column 1 the cells read mann and the formula is ='M-Z Supplier List'!B11 When I look at column 2 the cell reads 500.00 and the formula is = Mann'!B11 I am thinking something like MATCH(TEXT(A10,"$#,##0.00"),(LEFT(B10,3))) ??? Thanks again, Todd "Peo Sjoblom" wrote: ='M-Z Supplier List'!B11&" "&TEXT(Mann'!B11,"$#,##0.00") or if you meant that one formula retruns a string that you want to use as a reference in another formula =INDIRECT("'"&'M-Z Supplier List'!B11&"'!B11") -- Regards, Peo Sjoblom "Todd" wrote in message ... I need to match the returned value in one formula with the text in another formula. How can I do this? Formula 1: ='M-Z Supplier List'!B11 and returns" Mann" Formula 2 : = Mann'!B11 and returns a dollar value TIA |
Now I am lost, assuming your example, what is the result you desire?
-- Regards, Peo Sjoblom "Todd" wrote in message ... Thanks, that brings me a lot closer! I don't understand what you meant well enough, sorry. I will try to explain better. I need to match the text in column 1 that a formula returns with the actual formulas in column 2. column 1 column 2 ='M-Z Supplier List'!$B$11 = Mann!B11 When I look at column 1 the cells read mann and the formula is ='M-Z Supplier List'!B11 When I look at column 2 the cell reads 500.00 and the formula is = Mann'!B11 I am thinking something like MATCH(TEXT(A10,"$#,##0.00"),(LEFT(B10,3))) ??? Thanks again, Todd "Peo Sjoblom" wrote: ='M-Z Supplier List'!B11&" "&TEXT(Mann'!B11,"$#,##0.00") or if you meant that one formula retruns a string that you want to use as a reference in another formula =INDIRECT("'"&'M-Z Supplier List'!B11&"'!B11") -- Regards, Peo Sjoblom "Todd" wrote in message ... I need to match the returned value in one formula with the text in another formula. How can I do this? Formula 1: ='M-Z Supplier List'!B11 and returns" Mann" Formula 2 : = Mann'!B11 and returns a dollar value TIA |
Sorry, I am making this complex aren't I. I want to check and see if
everything is in the right place and I can't figure out how to match the text in a10 to the formula in b10. cell a10 returns the sheet name that the formula in B10 should be pulling from. A simple do they match? indicator is what I am trying for. Todd "Peo Sjoblom" wrote: Now I am lost, assuming your example, what is the result you desire? -- Regards, Peo Sjoblom "Todd" wrote in message ... Thanks, that brings me a lot closer! I don't understand what you meant well enough, sorry. I will try to explain better. I need to match the text in column 1 that a formula returns with the actual formulas in column 2. column 1 column 2 ='M-Z Supplier List'!$B$11 = Mann!B11 When I look at column 1 the cells read mann and the formula is ='M-Z Supplier List'!B11 When I look at column 2 the cell reads 500.00 and the formula is = Mann'!B11 I am thinking something like MATCH(TEXT(A10,"$#,##0.00"),(LEFT(B10,3))) ??? Thanks again, Todd "Peo Sjoblom" wrote: ='M-Z Supplier List'!B11&" "&TEXT(Mann'!B11,"$#,##0.00") or if you meant that one formula retruns a string that you want to use as a reference in another formula =INDIRECT("'"&'M-Z Supplier List'!B11&"'!B11") -- Regards, Peo Sjoblom "Todd" wrote in message ... I need to match the returned value in one formula with the text in another formula. How can I do this? Formula 1: ='M-Z Supplier List'!B11 and returns" Mann" Formula 2 : = Mann'!B11 and returns a dollar value TIA |
To get the text string from a formula like
= Mann'!B11 you would need VBA http://www.mvps.org/dmcritchie/excel/formula.htm -- Regards, Peo Sjoblom "Todd" wrote in message ... Sorry, I am making this complex aren't I. I want to check and see if everything is in the right place and I can't figure out how to match the text in a10 to the formula in b10. cell a10 returns the sheet name that the formula in B10 should be pulling from. A simple do they match? indicator is what I am trying for. Todd "Peo Sjoblom" wrote: Now I am lost, assuming your example, what is the result you desire? -- Regards, Peo Sjoblom "Todd" wrote in message ... Thanks, that brings me a lot closer! I don't understand what you meant well enough, sorry. I will try to explain better. I need to match the text in column 1 that a formula returns with the actual formulas in column 2. column 1 column 2 ='M-Z Supplier List'!$B$11 = Mann!B11 When I look at column 1 the cells read mann and the formula is ='M-Z Supplier List'!B11 When I look at column 2 the cell reads 500.00 and the formula is = Mann'!B11 I am thinking something like MATCH(TEXT(A10,"$#,##0.00"),(LEFT(B10,3))) ??? Thanks again, Todd "Peo Sjoblom" wrote: ='M-Z Supplier List'!B11&" "&TEXT(Mann'!B11,"$#,##0.00") or if you meant that one formula retruns a string that you want to use as a reference in another formula =INDIRECT("'"&'M-Z Supplier List'!B11&"'!B11") -- Regards, Peo Sjoblom "Todd" wrote in message ... I need to match the returned value in one formula with the text in another formula. How can I do this? Formula 1: ='M-Z Supplier List'!B11 and returns" Mann" Formula 2 : = Mann'!B11 and returns a dollar value TIA |
oh, thats to bad, thats not going to be practical. THANK YOU for all the
help and hanging in there with me. Thanks again, Todd. "Peo Sjoblom" wrote: To get the text string from a formula like = Mann'!B11 you would need VBA http://www.mvps.org/dmcritchie/excel/formula.htm -- Regards, Peo Sjoblom "Todd" wrote in message ... Sorry, I am making this complex aren't I. I want to check and see if everything is in the right place and I can't figure out how to match the text in a10 to the formula in b10. cell a10 returns the sheet name that the formula in B10 should be pulling from. A simple do they match? indicator is what I am trying for. Todd "Peo Sjoblom" wrote: Now I am lost, assuming your example, what is the result you desire? -- Regards, Peo Sjoblom "Todd" wrote in message ... Thanks, that brings me a lot closer! I don't understand what you meant well enough, sorry. I will try to explain better. I need to match the text in column 1 that a formula returns with the actual formulas in column 2. column 1 column 2 ='M-Z Supplier List'!$B$11 = Mann!B11 When I look at column 1 the cells read mann and the formula is ='M-Z Supplier List'!B11 When I look at column 2 the cell reads 500.00 and the formula is = Mann'!B11 I am thinking something like MATCH(TEXT(A10,"$#,##0.00"),(LEFT(B10,3))) ??? Thanks again, Todd "Peo Sjoblom" wrote: ='M-Z Supplier List'!B11&" "&TEXT(Mann'!B11,"$#,##0.00") or if you meant that one formula retruns a string that you want to use as a reference in another formula =INDIRECT("'"&'M-Z Supplier List'!B11&"'!B11") -- Regards, Peo Sjoblom "Todd" wrote in message ... I need to match the returned value in one formula with the text in another formula. How can I do this? Formula 1: ='M-Z Supplier List'!B11 and returns" Mann" Formula 2 : = Mann'!B11 and returns a dollar value TIA |
Todd
I'm not sure that I fuly comprehend what you are trying to achieve. It seems you are trying to check that a user entered value in row A is matched by a formula in row B. If this is correct, why not use the indirect function and save the checking? '=INDIRECT(A2&"!B11",1)' will give the value shown in a sheet named 'mann' at address 'B11' if the user enters 'Mann'. You might also consider Data Validation so users can only enter valid sheet names. HTH Steve "Todd" wrote in message ... oh, thats to bad, thats not going to be practical. THANK YOU for all the help and hanging in there with me. Thanks again, Todd. "Peo Sjoblom" wrote: To get the text string from a formula like = Mann'!B11 you would need VBA http://www.mvps.org/dmcritchie/excel/formula.htm -- Regards, Peo Sjoblom "Todd" wrote in message ... Sorry, I am making this complex aren't I. I want to check and see if everything is in the right place and I can't figure out how to match the text in a10 to the formula in b10. cell a10 returns the sheet name that the formula in B10 should be pulling from. A simple do they match? indicator is what I am trying for. Todd "Peo Sjoblom" wrote: Now I am lost, assuming your example, what is the result you desire? -- Regards, Peo Sjoblom "Todd" wrote in message ... Thanks, that brings me a lot closer! I don't understand what you meant well enough, sorry. I will try to explain better. I need to match the text in column 1 that a formula returns with the actual formulas in column 2. column 1 column 2 ='M-Z Supplier List'!$B$11 = Mann!B11 When I look at column 1 the cells read mann and the formula is ='M-Z Supplier List'!B11 When I look at column 2 the cell reads 500.00 and the formula is = Mann'!B11 I am thinking something like MATCH(TEXT(A10,"$#,##0.00"),(LEFT(B10,3))) ??? Thanks again, Todd "Peo Sjoblom" wrote: ='M-Z Supplier List'!B11&" "&TEXT(Mann'!B11,"$#,##0.00") or if you meant that one formula retruns a string that you want to use as a reference in another formula =INDIRECT("'"&'M-Z Supplier List'!B11&"'!B11") -- Regards, Peo Sjoblom "Todd" wrote in message ... I need to match the returned value in one formula with the text in another formula. How can I do this? Formula 1: ='M-Z Supplier List'!B11 and returns" Mann" Formula 2 : = Mann'!B11 and returns a dollar value TIA |
| All times are GMT +1. The time now is 09:35 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com