Making a letter point to a specific cell or equation?

[rpo83]

HI all,

I have just joined as i have spent the last hour unsuccessfully searching the net trying to sort out an excel question i have, what i'm trying to do seems pretty simple but i cant figure out how to do it... I'm a basic excel user, using it mainly to create pricelists.

So here is the situation, we are the distributor for a few manufacturers, we usually simply import their pricelists which contain a retail price, and we have a cell that applies a discount which up to now has been a static percentage. A new manufacturer we represent has just sent us their price list, and in it they have included a column that includes 1 of 6 alphabetical characters, these characters define what discount is applied...

So for example A is 20%, F is 25% and to throw a spanner in the works they also have a 3 letter character lets say DET is 27%

As the pricelist is 11 pages long and very complex, we ideally want to import it layed out the same way as they send it to us, so column A is Article #, column B is product name, column C is price and column D is rebate group which contains one of the 6 possible characters.

What i'd like to do is have the first worksheet contain a list of the 6 characters and their discount amount which can then be applied in the rest of the workgroup..

Thank you for reading this far, i look forward to any help you can provide.

Cheers

Steve

 

[mole999]

you could put that list on a separate page, then use vlookup against the code supplied and with an additional column bring your % into the main data

 

[Rick Rothstein]

Show us all of the single and multiple letter codes and the percentage for each of them.

 

[rpo83]

Hi Rick,

Thanks for replying, the codes and percentages are as follows:

D=15%
K=17.5%
M=20%
S=30%
CIN=22.5%
RYD=25%

The vast majority are M, and only a handful of the other codes....

Cheers

Steve

 

[Rick Rothstein]

Hi Rick,

Thanks for replying, the codes and percentages are as follows:

D=15%
K=17.5%
M=20%
S=30%
CIN=22.5%
RYD=25%

The vast majority are M, and only a handful of the other codes....

Assuming you have your table laid out on Sheet1 with the D, K, M, etc. in cells A1 thru A6 and their equivalent percentages in cells B1 thru B6, then on the other worksheets, put this formula in Row 2 (assumes Row 1 contains header text) and copy down...

=INDEX(Sheet1!B$1:B$6,MATCH(D2&"*",Sheet1!A$1:A$6,0))

 

[rpo83]

Thank you very much Ray,

I wont have a chance to build the workbook until next week, but as soon as i do, i'll report back

I really appreciate your help on this, i would have never figured that formula out!

Cheers

Steve

 

[rpo83]

you could put that list on a separate page, then use vlookup against the code supplied and with an additional column bring your % into the main data

I Mole,

Sorry i didn't reply sooner for some reason i didn't see you post last week... I have tried a few options but i was getting syntax errors!! I am basic excel user...

I will update this thread in my next post as i am still stuck, this will hopefully give yourself and Rick a better understanding of how i have setup a new workbook.

Thanks for the help
H

 

[rpo83]

Assuming you have your table laid out on Sheet1 with the D, K, M, etc. in cells A1 thru A6 and their equivalent percentages in cells B1 thru B6, then on the other worksheets, put this formula in Row 2 (assumes Row 1 contains header text) and copy down...

=INDEX(Sheet1!B$1:B$6,MATCH(D2&"*",Sheet1!A$1:A$6,0))

Hi Rick,

i have just started a new workbook and slightly edited your formula, and everything seems to be working, but i'd love a little more help.

I have named Sheet1 to Variables and wanted to include a description so other users can understand what is going on.

Your formula now is =INDEX(Variables!B$3:B$6,MATCH(D3&"*",Variables!A$3:A$6,0)) and the column i have pasted this into is displaying 0.4 for 40% etc.. In the table below i'd love the Column E (Euro Buy) to be our actual buy price... so column C minus the applicable rebate.

Here is how all the pricelist pages will be laid out...

[TABLE="width: 513"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Article ID[/TD]
[TD]Product Name[/TD]
[TD]Factory RRP[/TD]
[TD]Rebate Grp[/TD]
[TD]Euro Buy[/TD]
[/TR]
[TR]
[TD]00001[/TD]
[TD]a[/TD]
[TD="align: right"]€ 500.00[/TD]
[TD]M[/TD]
[TD="align: right"]0.5[/TD]
[/TR]
[TR]
[TD]00002[/TD]
[TD]b[/TD]
[TD="align: right"]€ 100.00[/TD]
[TD]S[/TD]
[TD="align: right"]0.4[/TD]
[/TR]
[TR]
[TD]00003[/TD]
[TD]c[/TD]
[TD="align: right"]€ 100.00[/TD]
[TD]CIN[/TD]
[TD="align: right"]0.3[/TD]
[/TR]
[TR]
[TD]00004[/TD]
[TD]d[/TD]
[TD="align: right"]€ 100.00[/TD]
[TD]RYC[/TD]
[TD="align: right"]0.2[/TD]
[/TR]
[TR]
[TD]00005[/TD]
[TD]e[/TD]
[TD="align: right"]€ 100.00[/TD]
[TD]m[/TD]
[TD="align: right"]0.5[/TD]
[/TR]
[TR]
[TD]00006[/TD]
[TD]f[/TD]
[TD="align: right"]€ 100.00[/TD]
[TD]s[/TD]
[TD="align: right"]0.4[/TD]
[/TR]
[TR]
[TD]00007[/TD]
[TD]g[/TD]
[TD="align: right"]€ 100.00[/TD]
[TD]ryc[/TD]
[TD="align: right"]0.2[/TD]
[/TR]
[TR]
[TD]00008[/TD]
[TD]h[/TD]
[TD="align: right"]€ 100.00[/TD]
[TD]cin[/TD]
[TD="align: right"]0.3[/TD]
[/TR]
[TR]
[TD]00009[/TD]
[TD]i[/TD]
[TD="align: right"]€ 100.00[/TD]
[TD][/TD]
[TD="align: right"]0.5[/TD]
[/TR]
[TR]
[TD]00010[/TD]
[TD]j[/TD]
[TD="align: right"]€ 100.00[/TD]
[TD][/TD]
[TD="align: right"]0.5[/TD]
[/TR]
</tbody>[/TABLE]

The other thing to note is that when there is nothing in the rebate group column, it defaults to the first row in the rebate %, is there a way to provide an error in the sheet if the rebate group isn't filled with a valid character? See the last 2 rows as an example.

Thank you very much for your help so far... i really appreciate it...

Cheers

Steve

 

[mole999]

0.4 is 40% so its a matter of how you format the data, but for a look up you don't need to have any formatting as it is only a reference for the rest of calculations

 

[rpo83]

Thanks Mole999,

i've sorted out the formula it is now =C3-INDEX(Variables!B$3:B$6,MATCH(D3&"*",Variables!A$3:A$6,0))*C3

So the RRP - the discount and it gives me the correct figure.

The only outstanding issue is where if no valid rebate group code is entered it reverts to the rebate at the top of the column, i'd love to be able to make it display some sort of error so it makes people reading the workbook aware that an incorrect rebate or no rebate is applied.