Complicated IF's

[taken]

Don't know if there's a better way of doing this.
Thus nested if-statements has a max value of seven and I'm afraid that this code will be more then that.

Making a sheet of points depending on what a seller has sold.
I've got VLOOKUP-functions fetching data adding points to specific articles.

My problems comes when one article has different scores depending on who sold it.
Therefore I need to make an if-statement looking for the value 1 and 2 and making a VLOOKUP to 1 or 2's specific score sheet.

I really don't know how to solve this one.
Probably it's easier if someone just looks at the file, but there's no possibility to upload it here.

I've got this so far:
=IF(H:H=1&L:L=2;VLOOKUP(C:C;PointsFTG!A:E;4;FALSE);VLOOKUP(C:C;PointsPrivat!A:E;4;FALSE))

Don't know if it's possible to have that beginning in the IF-statement.

Is there any Excel expert out there willing to help me with this?
Thanks!
/ Martin

 

[taken]

I tried this one out ... but, returns FALSE!! =((


=IF($E2<=-2;VLOOKUP($C2;IF($H2*$L2=1;PointsPrivat!A:E;PointsFTG!A:E);4;0)/-1*2;IF($E2<=0;VLOOKUP($C2;IF($H2*$L2=1;PointsPrivat!A:E;PointsFTG!A:E);4;0)/-1;IF($E2>=2;VLOOKUP($C2;IF($H2*$L2=1;PointsPrivat!A:E;PointsFTG!A:E);4;0)*$E2)))

 

[taken]

Redalert19
Need of help here, not spam! =/

 

[markmzz]

Hi Taken,

I'm sorry, but I didn't understand what you want.

Could you post a small example (in English - what you have and what you want)?

Note: with all sheets (Main, PointsPrivat, PointsFTG, etc...).

Markmzz

 

[taken]

I solved it by using your code and adding an if-statement doing following,

=IF($E2<=-1;VLOOKUP($C2;IF($H2*$L2=1;PointsPrivat!A:E;PointsFTG!A:E);4;0)*$E2;IF($E2>=1;VLOOKUP($C2;IF($H2*$L2=1;PointsPrivat!A:E;PointsFTG!A:E);4;0)*$E2;))

Another thing though..

How in the world can I copy something from my file and pasting it somewhere without references?
If I highlight all the cells I need (with all the functions), copying it and pasting it (I've tried to past it by right clicking and choosing to past it with formulas).
Still it pasts it with references from the other file I copied from. I don't want that.. I want the clean formula written, without references.. is that possible?

Last thing! =)

Since I've got tons of information in this file and renewing it a lot. It becomes a lot of rows where my first row always is freezed.
When deleting all the rows beneath it I got like row numbers of really high numbers. I've tried to delete them all but, still there are high numbers.
Is it possible to have the number two row back after deleting everything?

THanks!

 

[taken]

Just out of curiosity..
Can you please explain the code:

=VLOOKUP($C2;IF($H2*$L2=1;PointsPrivat!A:E;PointsFTG!A:E);4;0)

I understand where all the cell information comes from. But, I don't get why you take the $H2*L2 to know if it should look on a specific sheet.

 

[markmzz]

Just out of curiosity..
Can you please explain the code:

=VLOOKUP($C2;IF($H2*$L2=1;PointsPrivat!A:E;PointsFTG!A:E);4;0)

I understand where all the cell information comes from. But, I don't get why you take the $H2*L2 to know if it should look on a specific sheet.

There's two kinds of salesmen and two kinds of stores.
So, sometimes a salesman with pID 1 works in a pID 2 store. Which means that the seller should have a score from the pID 2 sheet (PointsFTG).
And sometimes a salesman with pID 2 works in a pID 1 store. Which means that the seller should have a score from the pID 2 sheet (PointsFTG).
Then also can a salesman with pID 2 work in a pID 2 store. Which means that the seller should have a score from the pID 2 sheet (PointsFTG).
Then a salesman with pID 1 can work in a pID 1 store. Which means that the seller should have a score from the pID 1 sheet. (PointsPrivat)

The only way that we need to use the sheet PointsPrivat is when pID=1 (salesman ) and pID=1 (store). In another words when $H2*$L2=1.

I hope that the information above helps.

Markmzz

 

[taken]

Good explaination.. thanks! =)

 

[taken]

I've got another tricky thing that came up...

In my Pointsprivat I've got columns with article name as column A.
I need to check if a seller has sold >=6 of a specific article name starting with H.

If they've sold 6 or more. Then they should gain the point of those 6 or more.

Is this possible to include in this code with an if-statement?

Thanks for your support!

 

[taken]

To be more specific..

The article number starts with a H and can change after the H but it always has a space after it.

So, if the each seller has sold more then 6 of this article (which is a few). Then this total point should be added to the sellers total point.
I just see a looooong if-statement doing this. But, probably you guys got a solution that is so much shorter! =)

Would be so greatful if you could help out.

My code for the pID looks like this right now.

[COLOR=#333333]=IF($E2<=-1;VLOOKUP($C2;IF($H2*$L2=1;PointsPrivat!A:E;PointsFTG!A:E);4;0)*$E2;IF($E2>=1;VLOOKUP($C2;IF($H2*$L2=1;PointsPrivat!A:E;PointsFTG!A:E);4;0)*$E2;))[/COLOR]

 

[taken]

Oh.. should also say that every seller has their own seller ID which is located in the same sheet as the point column.
All the seller ID's is located in column B... could it be worked out?