Highlighting or marking an email after processing - part 2 for MC workbook
gowflow, asking for you to perform your magic for the Visa workbook as you did for the MC:
a way to flag with a green flag the emails that get processed when they have been successfully imported into the workbook - and a red when not, I think that will cover all the needs to flag emails. Don't need it for Western Union emails.
a way to flag with a green flag the emails that get processed when they have been successfully imported into the workbook - and a red when not, I think that will cover all the needs to flag emails. Don't need it for Western Union emails.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
no problem
gowflow
gowflow
ASKER
Worked like a charm! Thank you, gowflow.
Now on to a bit more challenging aspects of the Western Union work flow with the Visa workbook. I'll post the link here here when I've thought it through - first with an explanation of what I'm trying to accomplish.
Thanks again, gowflow! I appreciate all your hard work.
Now on to a bit more challenging aspects of the Western Union work flow with the Visa workbook. I'll post the link here here when I've thought it through - first with an explanation of what I'm trying to accomplish.
Thanks again, gowflow! I appreciate all your hard work.
your welcome and tks for the grade and nice comments.
gowflow
gowflow
u seem to be busy ... ?
gowflow
gowflow
ASKER
I'm more trying to figure out how I want to do this next step with Western Union. I think I'll have to explain you the whole thing first and the break the questions up into sections.
ok as you like no problem you can explain here and I will suggest as much as possible.
gowflow
gowflow
ASKER
There are a few things going on with the Visa spreadsheet, particularly the WU-Staging-FBME sheet.
Once we send in the sheet to Joseph he then sends me back a copy of the spreadsheet with column H, Confirmed Amounts, filled in. He does not pick all of them up at once. Sometimes he picks up a batch I sent to him a week or so later. He picks and chooses, but the end result is that I copy and paste the amounts he gives me from his Col H and paste it into my col H of the WU-Staging-FBME sheet. - continued....
Once we send in the sheet to Joseph he then sends me back a copy of the spreadsheet with column H, Confirmed Amounts, filled in. He does not pick all of them up at once. Sometimes he picks up a batch I sent to him a week or so later. He picks and chooses, but the end result is that I copy and paste the amounts he gives me from his Col H and paste it into my col H of the WU-Staging-FBME sheet. - continued....
ASKER
What needs to happen with the amounts I paste in Col H is as follows (and I will submit a question about this) in parts. When Col H in WU-Staging-FBME of a batch is filled:
1. In Col i of the top row of the batch, sum up the batches total amount. (see attached image) A number of months ago someone already created that functionality for me. It also puts in values in Cols T through Z. Here's what it does:
- col U copies the value in Col i.
- puts 1.4 as an arbitrary conversion rate value, converting the USD amount (from Col i) to convert to Euros in Col V
- Col W has this calculation: =$V$1561*5% (for row 1561) which pads the conversion rate by that amount
- then adds the 1.4 from Col V to the value in Col W and puts the new conversion rate (for USD to Euros) in Col X
- then in Col Y it uses this formula: =$U$1561/$X$1561 to finally convert the Value in Col U from USD to Dollars and puts that amount in Col Y
- Col Z uses the formula: =$Y$1561*7.5% to calculate the loading fee
- and finally, in Col T uses this formula: =IF(Y1561*7.5%<75, Y1561-75, Y1561-(Y1561*7.5%)) to put the final Euro amount that gets loaded to the cardholders Visa card.
If you have this sheet if you manually put a value in Col i, you should see this all outplay.
Let me know if you have questions so far and if this is clear.
1. In Col i of the top row of the batch, sum up the batches total amount. (see attached image) A number of months ago someone already created that functionality for me. It also puts in values in Cols T through Z. Here's what it does:
- col U copies the value in Col i.
- puts 1.4 as an arbitrary conversion rate value, converting the USD amount (from Col i) to convert to Euros in Col V
- Col W has this calculation: =$V$1561*5% (for row 1561) which pads the conversion rate by that amount
- then adds the 1.4 from Col V to the value in Col W and puts the new conversion rate (for USD to Euros) in Col X
- then in Col Y it uses this formula: =$U$1561/$X$1561 to finally convert the Value in Col U from USD to Dollars and puts that amount in Col Y
- Col Z uses the formula: =$Y$1561*7.5% to calculate the loading fee
- and finally, in Col T uses this formula: =IF(Y1561*7.5%<75, Y1561-75, Y1561-(Y1561*7.5%)) to put the final Euro amount that gets loaded to the cardholders Visa card.
If you have this sheet if you manually put a value in Col i, you should see this all outplay.
Let me know if you have questions so far and if this is clear.
Couple of questions:
1) but the end result is that I copy and paste the amounts he gives me from his Col H and paste it into my col H of the WU-Staging-FBME sheet.
>> Do you want this to be automated ? if yes I need to see in attachment the file he sends.
2) In Col i of the top row of the batch, sum up the batches total amount. (see attached image) A number of months ago someone already created that functionality for me. It also puts in values in Cols T through Z. Here's what it does:
>> No image attached. What is the name of the Sub created ? is it in the workbook I have copy of ?
3) I input amounts in H and I in WU-Staging-FBME and nothing happened !
gowflow
1) but the end result is that I copy and paste the amounts he gives me from his Col H and paste it into my col H of the WU-Staging-FBME sheet.
>> Do you want this to be automated ? if yes I need to see in attachment the file he sends.
2) In Col i of the top row of the batch, sum up the batches total amount. (see attached image) A number of months ago someone already created that functionality for me. It also puts in values in Cols T through Z. Here's what it does:
>> No image attached. What is the name of the Sub created ? is it in the workbook I have copy of ?
3) I input amounts in H and I in WU-Staging-FBME and nothing happened !
gowflow
ASKER
1. Well, yes, that would be great if you can do that - didn't know it was possible - but you have to match MTCN numbers and another field just to make sure they are the same row. Sometimes MTCN#'s and sender name and address are duplicated. I'll attach an altered copy of what he sends in just a minute.
2. Sorry, yes you have the Visa workbook. Not sure the name of the sub. I could try to figure that out.
3. Maybe you first have to sum a batch from Col H values and put that sum in Col i. Then copy all those values in Col H and paste them right back where you copied them from. Weird that I have to do that to make it work.
2. Sorry, yes you have the Visa workbook. Not sure the name of the sub. I could try to figure that out.
3. Maybe you first have to sum a batch from Col H values and put that sum in Col i. Then copy all those values in Col H and paste them right back where you copied them from. Weird that I have to do that to make it work.
ASKER
an altered spreadsheet of what Joseph sends back to me
Wk-WK42.xls
Wk-WK42.xls
ok the way I see it you need to break this in 2 questions
1) automate worksheet form joseph (provided he only change column H) and he doesn't play with col A,B,C,D,E,F (logically I don't see any reason why he should change these columns .. unless ??? u tell me)
2) Question regarding the rest of what you discribed. Yes there is some code there but when u post I will look at it.
gowflow
1) automate worksheet form joseph (provided he only change column H) and he doesn't play with col A,B,C,D,E,F (logically I don't see any reason why he should change these columns .. unless ??? u tell me)
2) Question regarding the rest of what you discribed. Yes there is some code there but when u post I will look at it.
gowflow
ASKER
1. He doesn't - as much as I can recall - alter Cols A - G other than sometimes pointing out an error by changing the font color to red for a particular row. When that is the case, or if there is a discrepancy, or if he can't pick something up, he'll change the font color and put a note in Col H. Example attached.
2. Ok, I will submit a question asking point 1 to be done first.
Please keep in mind that once the amounts are inserted in Col H of my sheet, then other things happen and more needs to happen - maybe in stages - which will be further parts to this overall question.
2. Ok, I will submit a question asking point 1 to be done first.
Please keep in mind that once the amounts are inserted in Col H of my sheet, then other things happen and more needs to happen - maybe in stages - which will be further parts to this overall question.
ok no problem for me post the question and give detail and then will go one after the othr !!!
I hv no problem even if it is 100 questions !!!
gowflow
I hv no problem even if it is 100 questions !!!
gowflow
ASKER
Plugged it in and will try it when a few of those emails come in later today. Thank you, as always.