Vlookup table solution

[RachelMads02]

Hi, my name is Rachel, and I'm referencing a thread i just closed yesterday. I came across another problem before i should have titles the previous thread solved. Here is the link to the thread if anyone is interested in reading.

http://www.excelforum.com/excel-form...ith-dates.html

Here's my situation.

In the sheet titled "BABY LINE" I have a column AU called "Transaction Fees". With this company there are two forms of payment that a customer can choose when checking out and each option has a different transaction fee associated with them. For example:

Etsy payment = 3.0% of the total transaction + $.25
Paypal payment = 2.9% of the total transaction + $.30

Total Transaction = Total Customer Cost (Column AP in "Baby Line")

The payment option is listed in the sheet titled "SHIPPING INFORMATION" Q23:Y28 (highlighted in yellow).

Now my problem is creating a Vlookup similar to the older thread that allows me the option of changing the transaction fees of each payment option at a future date without losing prior data.

Let's say those fees listed above are the current fees as of 8/26/2015 and all the items i sell going forward will have fees calculated based on those numbers above. Now let's say in 3 months the fees for paypal change (increase of decrease), but the fees for Etsy stay the same, or vice versa. I want the items sold after the date of the fee change to adjust going forward without compromising the fees associated prior to the change. Sometimes the fees for both payment options will change at the same time and sometimes they won't, sometimes one will change and the other will remain the same, sometimes the flat fee of $.25 or $.30 will change, but the percents remain the same, etc.

I'd like to link it to the "Sale Date" column (AR) in "Baby Line" with an effective date column in "Shipping Information", but I don't know how to utilize more than one form of payment when compared to the solutions offered in the previous thread.

Can someone provide me a solution or at least point me in the right direction because I'm sort of confused right now?

Thank you in advance!!!

Rachel

[BadlySpelledBuoy]

I can see two ways round this. One quicker and simpler than the other, but the more complex and time consuming one.

The quick way would be a worksheet change event so when you alter a value in a specific column the transaction cost is calculated and stamped to the sheet as a value rather than the result of a formula. That would allow you to change the lookup table at will and wouldn't affect any previous data.

The more complex approach would be a UserForm for data entry. The transaction fee is then generated on the fly and stamped to the relevant column so works similarly to my first option but with the added bonus that it's easier to use for data entry compared with scrolling along a worksheet that takes up many columns.

Either of those options interest you?

BSB

[RachelMads02]

I can see two ways round this. One quicker and simpler than the other, but the more complex and time consuming one.

The quick way would be a worksheet change event so when you alter a value in a specific column the transaction cost is calculated and stamped to the sheet as a value rather than the result of a formula. That would allow you to change the lookup table at will and wouldn't affect any previous data.

The more complex approach would be a UserForm for data entry. The transaction fee is then generated on the fly and stamped to the relevant column so works similarly to my first option but with the added bonus that it's easier to use for data entry compared with scrolling along a worksheet that takes up many columns.

Either of those options interest you?

BSB

I wasn't aware of said changes, and am a little confused as what you mean without actually seeing it, so yes I'm very interested in both options.

[BadlySpelledBuoy]

OK, we'll go for the first option (to start with). When filling out the spreadsheet I presume when you input data into this workbook you start at column A and work your way across. If that's the case, at which point would the transaction fee calculate? i.e. which column would you complete that would be the trigger for transaction fee to calculate?

As mentioned, a UserForm solution would take a little while to put together and I'd need to know more about the way you work to provide something that fits your needs.

BSB

[JohnTopley]

See attached:

I have created two lookup tables: Etsy and Paypal. Both have "Effective from" dates , % uplift and "constant".(Sheet1)

These work in the same way as previous solutions I have supplied.

Formula in AU7

=IF(AT7="etsy",VLOOKUP(AR7,Sheet1!$A$2:$C$5,2)*AP7+VLOOKUP(AR7,Sheet1!$A$2:$C$5,3),VLOOKUP(AR7,Sheet1!$D$2:$F$5,2)*AP7+VLOOKUP(AR7,Sheet1!$D$2:$F$5,3))

See attached.

[RachelMads02]

See attached:

I have created two lookup tables: Etsy and Paypal. Both have "Effective from" dates , % uplift and "constant".(Sheet1)

These work in the same way as previous solutions I have supplied.

Formula in AU7

=IF(AT7="etsy",VLOOKUP(AR7,Sheet1!$A$2:$C$5,2)*AP7+VLOOKUP(AR7,Sheet1!$A$2:$C$5,3),VLOOKUP(AR7,Sheet1!$D$2:$F$5,2)*AP7+VLOOKUP(AR7,Sheet1!$D$2:$F$5,3))

See attached.

Thank You John, I'm downloading now and will check it out.

[RachelMads02]

OK, we'll go for the first option (to start with). When filling out the spreadsheet I presume when you input data into this workbook you start at column A and work your way across. If that's the case, at which point would the transaction fee calculate? i.e. which column would you complete that would be the trigger for transaction fee to calculate?

As mentioned, a UserForm solution would take a little while to put together and I'd need to know more about the way you work to provide something that fits your needs.

BSB

It would calculate at column AS.

[JohnTopley]

Change AR7 to AS7. You said AR is your original posting!

[RachelMads02]

OK, we'll go for the first option (to start with). When filling out the spreadsheet I presume when you input data into this workbook you start at column A and work your way across. If that's the case, at which point would the transaction fee calculate? i.e. which column would you complete that would be the trigger for transaction fee to calculate?

As mentioned, a UserForm solution would take a little while to put together and I'd need to know more about the way you work to provide something that fits your needs.

BSB

What other information do you need?

[RachelMads02]

Change AR7 to AS7. You said AR is your original posting!

Worked like a charm again, thank you so much!!