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
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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> > > news:[email protected]...
>> > > > 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" <[email protected]> wrote in message
>> > > > > news:[email protected]...
>> > > > > > 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
>> > > > >
>> > > > >
>> > > > >
>> > >
>> > >
>> > >
>>
>>
>>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks