Avatar of JaseSt
JaseSt
Flag for United States of America asked on

adjusting loading fees from Mastercard workbook based on value inserted

gowflow has worked on this before and is familiar with the function.

For the Matercard workbook:

I need to adjust the amount of loading fees taken out (Col i and Col L) based on what is input into Col F (for the regular sheets only, not EMP, or MCR sheets). This is only for Earthport wires coming in, not Liberty Reserve,.

So here's what I need. When an Earthport wire email comes in and the function LocateEmailsToTabsNew is activated (I think that is the one) and inserts data into a row, I need the function that determines the loading fee (in Col i) to no longer be a flat fee as this calculation gives: =IF(F10*1.85%<25;F10-30;F10-(F10*1.85%)-5)

but rather I need the loading fee to adjust so that if the amount entered in Col F is less than $10,000 it is minus 2. So it would be: =IF(F10*1.85%<25;F10-30;F10-(F10*1.85%)-2)

if the amount in Col F is 10,000 or more then it is -5

So, in your calculation: =IF(F10*1.85%<25;F10-30;F10-(F10*1.85%)-5) it is only -5 when the  amount is 10,000.00 or over. Otherwise if less than 10,000 it is -2

I hope I didn't confuse
Microsoft Excel

Avatar of undefined
Last Comment
JaseSt

8/22/2022 - Mon
JaseSt

ASKER
and also, I guess the above adjustment effects the function in Col H where it asks if to take away $5 fee so that it would now have to ask if to take away $5 or $2.
Robberbaron (robr)

if you want anyone other than gowflow to help, need to add a link to the previous question.
gowflow

@Jasest
Let me get this correctly:
Not Liberty Reserve
that leaves us with Payment Received Emails or Transfer Emails which of the 2 or both ?

then you say Col I and Col L affected but what I see in the complex code you have is that
somewhere in Sub ImportData5New we have (for sheets not HMF Account and not EMP and not MCR
New Formula Col G =IF(F3*1.85%<25,30,(F3*1.85%)) Changed on 23/11/2011

and somewhere else in and other routine FillRowFormulas
 we have this if Not Liberty and not EMP
'---> Col I  =IF(F5*1.85%<25,F5-30,F5-(F5*1.85%)-5) Changed on 23/11/2011

So definitively it is not in the Sub you mentioned LocateEmailsToTabsNew.

My honest feeling is in the last comment I pointed out that we need to look but will need your confirmation as you best know your business could it be in the sub FillRowFormulas
'---> Col I  =IF(F5*1.85%<25,F5-30,F5-(F5*1.85%)-5) Changed on 23/11/2011
as I see at the end (-5) ????

@robberbaron
The workbook is so complex and has so many code modules and subs that owner as you can see from my comments not sure where modification should be performed. Do not misunderstand my comment as to have exclusivity on this work but it happens so that I have been answering most of his questions and developing this for over 1 year and half now that seems quite a big complex project and understand the frustration of owner not to know what to post and what part especially that he has so much sensitive data that need to be scrubbed each and every time that makes all this quite intricate to handle.

I will leave asker to best reply to your comment anyhow.
gowflow
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
JaseSt

ASKER
gowflow:

Yes, not Liberty Reserve. For now, that is always a flat fee.
Yes, it is Payment Received emails - (not sure what you mean by Transfer Emails)

"So definitively it is not in the Sub you mentioned LocateEmailsToTabsNew."
Yes, you are correct. Sorry.

I believe FillRowFormulas is correct.

FillRowFormulas
'---> Col I  =IF(F5*1.85%<25,F5-30,F5-(F5*1.85%)-5) Changed on 23/11/2011
as I see at the end (-5) ????

@robberbaron
you are correct that I should have added a link to the previous question this one relates to. Problem is I have so many previous questions that it takes a bit of time finding which one and there are a number that branch off. I'm going to have to title them better. And gowflow is correct as well. And I greatly appreciate his help.
ASKER CERTIFIED SOLUTION
gowflow

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
JaseSt

ASKER
going to get to this later today. thanks.
gowflow

okay
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
JaseSt

ASKER
gowflow, installed this and now just have to wait for wires to come to test. might not be until next week. we'll see. thank you.
gowflow

okay
JaseSt

ASKER
works like a champ, gowflow! Thank you!

Next question for you is this: Do you know if you can drag and drop a file into an Excel cell? Like pasting it right into the cell so that clicking on that cell will open up the file be it pdf, excel, jpg or word or even an html page?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
gowflow

Let me get this correctly before I answer yes or no.

You have an xcel sheet opened I suppose you visa or MC for that matter. You have say the HMF Account sheet displayed and then your excel somehow is not maximized so you can see your desktop or any folder containing files so what you are asking if I got this correctly you want to know if it is possible to click on a file and drag it to HMF Account and drop it onto cell say A20 and then the result of this is the creation of a HYPERLINK in cell A20 that when you click on it would open that file if it is a pdf it would launch Acrobat and open the file etc ..

Is that what you want ? If not pls clarify.
gowflow
JaseSt

ASKER
This is not the Visa or MC workbook. Different one: Applicant Status.xls

I have two monitors so it is easy to see the spreadsheet while dragging a file from one monitor to the spreadsheet in the other monitor.

Here's what I'd like to do:
An email comes in and has attachments. They are pdf, excel, jpg, gif, html or word. I would like to either copy and paste each individual attachment into a specific cell in Applicant Status.xls or drag it to that cell.

And yes, I'd like to be able to click on the file in the Applicant Status.xls sheet and the application would open up. Ideally, I'd like the files to show as icons not as text links as it shows here that can be done: http://www.mydigitallife.info/how-to-add-attach-or-insert-a-file-in-microsoft-office-excel-worksheet/

The solution the above link provides works if the files were already saved, but I would like to copy and paste or drag and drop directly from Outlook.  Can it be done?
Since they are attachments they are not yet saved to my hard drive.
gowflow

Well dragging in excel from other places is usally not that abvious I have been reading since you ask for that and lots of difficulties around it. Could you post this Applicant Status.xls so I can do more research then will revert ?
gowflow
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
JaseSt

ASKER
If drag and drop can't be done, then if I could just copy from Outlook and paste in a cell that would be fine. Just don't want to save the attachment then search for it and insert it. A copy is attached. Thank you. Applicant-Status-scrubbed.xls
gowflow

ok I see your file under what column you want to paste the link ? Then what do you mean by I don't want to save the attachment and then search and insert it ?

You need to give me an attachment file and what is the info you want to paste it is not clear what you want to do.

Let me recap my understanding then correct me. I had said you will get the link to that file in a cell as hyperlink then when you click on that link it will open the file and the you can do your copy paste into Excel is that what you want ? If the answer is yes then will need somehow to save that file so we can have the link ready for you.

gowflow
JaseSt

ASKER
There are four columns that files go into: O, Q and R

With Outlook you can just copy the attachment and paste it into another outlook email. I was hoping that the same could be done with copying and pasting into an Excel spreadsheet.

If I have to save first, then search for the file to embed in the excel spreadsheet, I'm not sure that is going to save me much time or make the process very efficient.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
gowflow

ok attach the bloody .... file or files so I can at least understand how it is formated and understand more of what you are trying to acheive. If you hv diffrent types of these files better attch them also like pdf, word, excel image whatever
gowflow
JaseSt

ASKER
Attached is a copy of a scrubbed email with the attachments, attached. If they are not attached in the email posted I will attach them separately.SGC-Mastercard-Application--app-.msg
JaseSt

ASKER
gowflow, are you able to do anything with this? If so, I'll post a question for it.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
gowflow

Sorry was a bit busy didn't hv a chance to look at it. Will look at it and revert in the course of the day. Tks
gowflow
gowflow

Just looked at the email you posted and see it has 3 attachments. If we create a sheet in Analysis.xls called attachments and we monitor a certain folder and dump in that attachment sheet all the emails info for a certain subject that we can filter in a way to show you 3 columns
Subject                                                                              Date            Attchement
SGC Mastercard Application; app ID: 264 from ...   4/17/2013  bank-statement.jpg
SGC Mastercard Application; app ID: 264 from ...   4/17/2013  untitled attachment 00032.xls
SGC Mastercard Application; app ID: 264 from ...   4/17/2013  id-national-high.jpg

and when you click on the attachment cell it opens the file would that be fine ??
gowflow
JaseSt

ASKER
We're getting there but the emails don't always come in with the same subject.

And how would the attachments that get 'dumped' into that sheet be organized?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
gowflow

we could do it in 2 ways

1) dump all emails in a certain folder to that worksheet each attachment in a row you will have to manually look for a certain item in that sheet ...

2) make it the manual way ie your worksheet is opened and have a button import attachment and also your outlook is opened you click on the email you need to import and then press on that button and it will import it in the said worksheet (for sure when we import we do a background work for you) we save the attachment on your drive in a certain folder and put the lik to that file in the cell so that when you click on it it will go and open it !!! :) no magic just doing the work for you.

gowflow
JaseSt

ASKER
let's go with #2 and see where it leads. I'll ask a question and put link here
JaseSt

ASKER
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.