[bmxfreakrider]

I am a data management tech for a small internet advertising company, and need some major assistance. basically this is how the system works.

a user posts an ad, I have to go to twitter and find their post. that is simple. I take their post and put it in the database. That is simple too. I add all their posts up, and multiply it by how much they earn per post (post rev.). simple too. Now for caluclating their balances it gets tricky. There are over 300 users, and lets say user 12 referred users 72, 80, and 96. User 12 would earn 5% of whatever users 72, 80, and 96 makes.

Currently I have to go thru the database and find users 72, 80, and 96, take their current values, add them all up, then multiply by .05. it works, but takes roughly 30s to a minute for one user, and up to 5 mintues for our power users (over 20 referrals). I know there is a function called "=vlookup" but I need some help setting it up. here is an example of our database. (names hidden for security purpouses)



so let me explain whats going on in this picture.

User 68 signed up users 231, 232, and 140. 231 didnt make anything this week, 232 made 6.50, and 140 made 4.50. I have to manually find their values (in this case 6.50 and 4.50, add them up, and multiply by 0.05, then take that value and put it in to the affilate earnings column in user 68's row. Very time consuming) Since I have the affiliates in column F, is there a forumla (such as vlookup) that can find 232 in column A, grab the value from column B, same for 140, add the numbers together and just give me a raw output (no multiplication, I can do that in another step) of $11.00 or even just 11.



heres the formulas

[Crysalis]

The vlookup will work, but the F column SHOULD only have one value.

It would be

=vlookup(F2,$A$2:$F$30,2,0)

Let me know if you need help. I do this all day everyday (microsoft office "expert" for software consulting co)

The vlookup will work if the column F has one value. Without an array formula or VBA, we can't do it that way. Check out this file that I worked on. Instead of putting all affiliates in one column, listing them at the end will work better and using a SUMIF function.

That formula needs to be modified dependent upon the max number of affiliates a person will have.

http://files.me.com/crysalis/8021gb

[bmxfreakrider]

Just saw the post. Ill def check it out after I finish with these emails. Dealing with e-cash is very sensitive stuff, I can NOT mess up or i loose my job esentially. Thats why I perfer excel to do all the work because its very powerfull and acurrate at math (unlike my soft-bodied counterpart upstairs, eg: brain)

[Crysalis]

you can either do it that way, or i can try to work up some VBA for you.

EDIT: Looks like a Mod edited my posts. Those were meant to be separate...

[bmxfreakrider]

also one more question. I have a balance draft template that I use for the emails but im a little confused about the way excel handles formulas. I have a forumla calling to cell d2:h2 where I have a whole bunch of info from the DB that i have to go thru one by one. Its really hard to explain...

I have a formula, say

"=G2"

and I cany type data and change the data in G2 as I please. If I paste data into the cell, it works, but if i drag data into the cell i get a #REF! error. Is there any way to lock the formula to a cell regardless of what happens to the cell? I might load up in mac and use screencap to show what im talking about.

also, dont know if it helps any, but I did take a year course on office 07 so im not a total noob, just didnt use a pc for a couple months and got reallllllllly rusty.

[Hefemeister]

adding a $ sign in front of the cell will lock them if you always want it g2 no matter where you copy and paste it then $g$2 is what you want. If you want one of the two to float then do not place the $ sign in front of it.

Crysalis is correct that vlookup is the way to go.

[bmxfreakrider]

i know about absolute cell references, but my problem is when I drag and drop a value in to a cell that is referred to i get the ref error.

[Hefemeister]

Have you tried copy and paste special with either value or formula depending upon your needs?

[bmxfreakrider]

copy and paste works fine, id like to move the entire column and replace the origional cells contents. when I do that, it gives me the error. Im doing copy and paste right now, but its fairly time consuming