get currency format from souce cell(s) of a formula or referance

[batunar]

I used a index formula as "=INDEX('PRICE LIST'!$A$1:$D$71; MATCH(A7;'PRICE
LIST'!$A$1:$A$71; MATCH($A$1;'PRICE LIST'!$A$1:$D$1)" which returns a
specific value in the intersection of targetted column and row reference. But
I need to get also the number format as currency of the returning cell since
it's varies according to intersection. Custom formating nor conditional
format did not help at all. So could you pls help me on this issue. Thanks in
advance.

 

[Héctor Miguel]

hi, !

I used a index formula as "=INDEX('PRICE LIST'!$A$1:$D$71;
MATCH(A7;'PRICE LIST'!$A$1:$A$71;MATCH($A$1;'PRICE LIST'!$A$1:$D$1)"
which returns a specific value in the intersection of targetted column and row reference.
But I need to get also the number format as currency of the returning cell
since it's varies according to intersection.
Custom formating nor conditional format did not help at all.
So could you pls help me on this issue. Thanks in advance.

formulae (neither functions nor udf) used in worksheet's cells (it's well known that)
WON'T change formats in (same or another) worksheet's cells, so...

1) where (and how or for doing what) do you need the info regarding number format of source cell ?
2) (for sure) you will need some sort of vba-code (maybe use the worksheet events) BUT...
3) AFAIK there is no event to catch any (simple) format-change over worksheet's cells (just in case)

if any doubt (or further information)... would you please comment ?
regards,
hector.

 

[batunar]

Thanks for your intention Hector. First of all let me try to explain it more.
I was trying to make a kind of auto proforma invoice. There is a worksheet
which keeps the client information and there is another one which keeps the
prices per products per customer. Finally there is a last sheet which I was
using as a kind of form of proforma invoice. You can pick either customers
and products from several drop down list. When you make selections then the
index formula takes the related price for spesific customers and spesific
products. Of course then it calculates the value, taxes and grand total etc.

But the problem is some prices are in dolar and some of them in euro. So
when I select the customer and product then formula takes the related price
as number but not the currency of course. I was just curious how I can get
also the currency to the form sheet either it's dolar or euro. Pls let me
know your comment. Thanks.

Regards.

Bertan NARLIOGLU

 

[Héctor Miguel]

hi, Bertan !

... a worksheet... keeps the client information
... another... keeps the prices per products per customer
... a last sheet... as a kind of form of proforma invoice
... the problem is some prices are in dolar and some of them in euro
... formula takes the related price as number but not the currency of course.
... just curious how I can get also the currency to the form sheet either it's dolar or euro...

if "recover" formats per formulae is unavailable (w/out a sort of code), I guess...
use another (tiny) column in source data where you can index/find/lookup/... the currency name or symbol
(and also another -tiny- column in target invoice-proforma to "catch" that currency) -?-

hth,
hector.

__ previous post __

 

[batunar]

Hi again Hector,

Yes, it seems this is the only solution indeed. I will try to like that.
Thanks for your help. Regards.