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;F1
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;F1
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;F1
I hope I didn't confuse
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;F1
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;F1
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;F1
I hope I didn't confuse
if you want anyone other than gowflow to help, need to add a link to the previous question.
@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.8
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-(
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-(
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
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.8
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-(
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-(
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
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-(
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.
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-(
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
going to get to this later today. thanks.
okay
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.
okay
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?
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?
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
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
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.
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.
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
gowflow
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
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
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
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.
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.
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
gowflow
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
ASKER
gowflow, are you able to do anything with this? If so, I'll post a question for it.
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
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
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?
And how would the attachments that get 'dumped' into that sheet be organized?
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
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
ASKER
let's go with #2 and see where it leads. I'll ask a question and put link here
ASKER