Vlookup codes based type of contract

[foxhound82]

Hello,

First timer here

I have been trying to figure how to change the finance subjective codes based upon the type of contract it is.

The three types are AMS, MGS and CDL

On my spread sheet there are 40 different types of shops that are all either AMS,MGS,CDL and had a unque all begining C**** code for each shop. So far I have been able to vlookup on the sheet if I put in a shop code it will recognise the type of contract it is. What I want to do is then pull off from another sheet the subjective codes that are specific to that contract e.g AMS office expenses H2334,Rent H3456,PAYE,h6754. So that if I put a shop that is MGS the subjective codes will change like office expenses X6543,Rent R4567,PAYE 4567

The three seperate contract subjective codes are all on sheet 2 called LC

How would I go about this? is vlookup the correct funtion?

any help is much appreciated

thanks

S

[NBVC]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[brokenbiscuits]

By the sounds of your problem you should definitely be able to use vlookup, but it's dependant upon the structure of sheet 2.

But using the same principles as with your previous vlookup where you used the shop code to bring back the contract type, you need to use the contract type to bring back the subjective codes.

But as NBVC says, if you post a dummy workbook you'll get a much clearer and more accurate response.

[foxhound82]

Hello,

First timer here

I have been trying to figure how to change the finance subjective codes based upon the type of contract it is.

The three types are AMS, MGS and CDL

currently there are 40 shops what I have done so far is when I enter any shop code on the spreadsheet the sheet will vlookup the type of contract it is from the contract tab and display the type i'm fine with that. What I want to do next is I want to populate the main spreadsheet so it draws the contract subjective codes that are specific to it. I have attached a spreadsheet to this the main front spreadsheet is a dummy of what I i am trying to do, the blue shaded one is active so if you put in any shop code from the contract tab it will tell you what type of contract it is. I want the vlookup to look to the contract type cell and draw the subjective codes from finance tab onto the front sheet so that when I put a different code the contract type changes and subjective codes are populated according to the contract type front sheet orange shaded cells a inactive example.

How would I go about this? is vlookup the correct funtion?

i'm new to this site so any help is much appreciated

thanks

S

[NBVC]

Try:

=INDEX(Finance!$C$3:$C$11,MATCH(A12,Finance!$A$3:$A$11,0)+ROWS(A$12:A12)-1)

copied down

[foxhound82]

Hiya,

I tried the formula you gave me and it worked perfectly I also followed the formula through so I learnt how to do it myself and adapt it to different tasks, great help.

Thanks
NBVC