Worksheet referencing and position recognition

Hello All,

I have a bit of a complicated one here so I have attached my book, its probably easier to undertstand the query by looking at the book. Hopefully somebody can help me out here.

I have 2 worksheets:

A results worksheet - this contains all category of results, each category has been given a specific sort id.

A foreign worksheet - this contains all categories found on the results worksheet with sort id = 7.

Now the complicated bit. The whole exercise is too pick up the correct exchange rate for the foreign category (sort id 7). each foreign type has a specific number at the end of its description in brackets e.g (3).

At the bottom of the "results worksheet" is a key which says what type of exchange rate to use for this type e.g. (3) Price of CAD 22.9 converted using exchange rate of CAD 1.9645 = £1.

What I need to do is on the foreign worksheet, it to recognise the number in the item description, keep it in mind then switch over to the "results worksheet" find the keys at the bottom of the page (the key is not necessarily in the same place all the time!!), match the number in the items description with the corresponding key and then find the exchange rate.

As i said very difficult to explain, since I reletively zero experience with this kind of work in excel. Luckily I been muddling my way through a work project gradually thanks to a forum member here. so i hope we can keep this up!

edit:
two restrictions in the way this solution can be done:
1- Results worksheet can not be modified, but it can be referenced to using INDIRECT, and you can make any number of modifications outside of the results worksheet.
2. Any solution must be automatic.
/edit

I have attached my workbook and highlighted the bits I need to fill and the picks I need to pick up.

Would be ever so grateful if some1 could show me the light here.

many thanks

Hi,

Attached is the solution to your query. Please note that I have changed the format of how you should be listing down the Exchange Rate values. Hope this will help

Hi ebbo,

Take a look at the attached file. The first thing you need to do is put the exchange rate in a table that Excel can look at. Here I've put it on a new worksheet called 'Exchange Rate'. For column C, use:

Please Login or Register  to view this content.

For column D, use:

Please Login or Register  to view this content.

Edit: A quick thought: To make things easier and to get rid of the MID function, if you were able to put the currency reference in a seperate column it would clean things up. Not essential - just depends how OCD you want to get!

HTH,

SamuelT

Hi, very much appreciate both you guys have done. Both however will not be suitable. The reason is as follows:

The results sheet is automatically generated (not for this mock up i made - but the real work sheet which i am working on is - i cant attach that as i am unable to disclose work sensitive material)

This means I have no control which row the key on the results worksheet will appear on. I also do not have control of the text that is displayed in the key. So somehow any solution will need to recognise:

1) which row the key is on
2) which key (1), (2) or (3) to lookup - bare in mind there can be (4), (5)... etc as we have many currencies in this world!
3) the position of the currency value & the currency name within the key

So Excel_matic, your solution unfortunately will not work as modifications have been made to the the results worksheet.

SamuelT your solution does not reference to the key on the results worksheet so I would have to create a new worksheet and manually enter currencies each time I need to process this document.

You can make any number of modifications to outside of the results worksheet. I have updated my first post to take this info into account.

I hope someone can still help:D

See attached:

Formula in C2:

Please Login or Register  to view this content.

copied down

Formula in E2:

Please Login or Register  to view this content.

copied down.

Hi NVBC, once again brilliant stuff.

A couple of things here:

If I drag down beyond the last line with data everything defaults to EUR, i would prefer it to remain blank. can this be done?

the second thing, i noticed the code directly references a specific set of cells to get the details of the KEY, in your example it was A22 and A24. Now the problem with this is my lists will grow and shrink depending on the work being carried out. So the location of the KEY on the Results sheet will vary. Is there anyway around this?

another thing (sorry)

the Foreign Currency field should just display the the actually Currency name,e.g

EDIT: solved this, by editing numbers in your code.
US$
instead of
US$22.9

the Exchange Rate field should just display the exchange rate e.g

1.9645
instead of
CAD 1.9645 =£1.

and finally in my screenshot I have done B2*G2 and dragged down beyond the last line of data and i get a #VALUE! error, how can I remove this so it remains blank.

Ok, see if I covered it all....

I added a named range to allow for up to 100 possible notes in A1:

Insert|Name|Define... Add "Notes" (without quotes) and apply formula:

Please Login or Register  to view this content.

then in the C2 of the Foreign Only sheet:

Please Login or Register  to view this content.

copied down as far as you want

in E2 of same sheet:

Please Login or Register  to view this content.

copied down.

Note: to not get the #VALUE errors.. simply add an IF, eg. =IF($A2="","",B2*G2)

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

never quite done when you think its done!

The work i did is in testing with some of my work colleagues so thank you very much for getting me this far.

They have raised a fault in my workings. Please see attached workbook. Basically there are a few different type of notes that they did not tell me about. Which means the equation is thrown off for getting the currency symbol.

right now (from what i understand) your logic says:
1. Look in the range named NOTES (the results worksheet)
2. Match the Bracketed Value in the Foreign Only worksheet to the Note in the results worksheet
3. If a match is found go to the statement "Price of" and match retrieve the next 4 characters after that.

that works but in one of the notes:
(4) Dividend converted using exchange rate of € 1.1314 = £1 (taken at 06/05/2009) and net of tax.

there is no "price of".

So my suggestion is this, but im not quite sure of how to pull it off.
1. Look in the range named NOTES (the results worksheet)
2. Match the Bracketed Value in the Foreign Only worksheet to the Note in the results worksheet
3. If a match is found go to the statement "Exchange rate of" and match the next X amount of characters until an integer is found and retrieve these characters

can the code be adjusted to cover this?

Appreciate your help as always NBVC

So is currently the problem only for cells c6 and c7 of the Foreign Only sheet? Ie. Are the first few rows still correct and only notes 4 and 5 are the problem?

Does this revised formula in C6, copied down work.

Please Login or Register  to view this content.

and in E2, copied down:

Please Login or Register  to view this content.

Just as hard to explain

I have to go now...so can't go into too much detail, but basically as you had described it indexes the Notes range by looking at the note number in your first column of the Foreign sheet and matches it to the same item in the Notes on the other sheet... then it looks for the phrase "exchange rate of" and the "=" sign after it and takes what is between...

If you go to Tools|Formula Auditing|Evaluate Formula and step through by hitting Evaluate repeatedly, you should see how the formula comes up with the final result..

it is basically using the Mid and Search functions to find the positions of those key phrases and manipulating them to extract the parts you want...