Link to home
Start Free TrialLog in
Avatar of JaseSt
JaseStFlag for United States of America

asked on

summing values and adding conversion USD to Euros - continued from previous question

A button on the WU-Staging-FBME sheet so that when clicked does this:

1. Sums the individual batch amounts in Col H and inserts that total in Col i of the top row of that batch only for those where Col H has a value for each record in the batch. (A "batch" is the batch of records brought in from a single email. See attached.)  User generated image
If any row in a single batch has an empty value in Col H that means it has yet to be picked up by our receiver and therefore the batch can receive no further processing until all rows in the batch have SOME value.

2. When Col i is filled in it then should kick off the code that is already there, however - and here is a tricky part - I need this formula to instead of putting 1.4 in Col U to look up and input the current USD to Euro conversion rate. Is that possible?

The code that kicks off calculations and input for Cols T - Z is located in Sheet2 (WU-Staging-FBME). It can be removed and replaced with your code if you wish, of course.

Furthermore, the button should only effect those batches where Col i is filled in AND where Cols T-Z are NOT filled in already.

Thank you.
Avatar of ragnarok89
ragnarok89

Looking at your screenshot, it seems that batches are separated by blank lines, is this correct?

Could you attach the excel file in question, and I can build it for you.

Al
Avatar of Jacques Geday
Note in the 2nd block 5795 on the first row although the 3rd row got zero and you mentioned
If any row in a single batch has an empty value in Col H that means it has yet to be picked up by our receiver and therefore the batch can receive no further processing
>>> I gather that if any row in a batch has either blank or zero then don't put the total on first row as we do not want to see the processing from coil T to Z filled
Is my understanding correct ?? if yes why did you put 5795 in that first row ? If not then explain what do you mean by that means it has yet to be picked up by our receiver and therefore the batch can receive no further processing  ???

gowflow
Avatar of JaseSt

ASKER

Anything other than a blank in Col H for a batch indicates it can be processed because if I put a zero or he puts feedback in the form of text, it means that that specific Western Union wire cannot be picked up and will have to be submitted again (with a different MTCN number)

There will be (or should be) no blank cells in Cols A - G. Only in H will we see either a blank (which means he did not pick it up) or text telling me there was something wrong with the wire and it will have to be picked up later (and sent again by the sender).

5795 is the sum of that specific batch.

If he sends back the spreadsheet with a blank value in Col H then the batch it is associated with cannot be processed until he sends me another spreadsheet with the specific cell in Col H filled in.

Clearer? If not, keep asking.
yes for me blank or 0 would mean the same but seems for you blank is not picked up then do not calculate the batch total as still incomplete and zero means he picked it up but unpaid means batch is complete reason why we see the 5795

will revert case any other issues
gowflow
I am still not clear on how to find batches. take this scenario

You have in your workbook 1500 lines in col H you have scattered amounts some follows some have blanks in between, furthermore in the bfirst 1000 lines you have no blank line separating the batches thereafter you have blank lines separating the batches moreover you have in Col I you have values in the beginning of the file that shows the total to that line (say line 50 show a total in Col I and the formula shows SUM(H45:H50) then further down you have batches that shows a formula where in the batch there is blank value in col H

So basically you have a total mixture in the filw of some old data + combined + now you need to set up a new formula

My challenge is
That you want to have a button that each time you clik it wil .... look for batches and sum the batches if complete etc ....

so the problem is where to look for these batches as no specific criteria if I say blank row then I will pick up the first couple of lines and more down ... if I say the last formula in Col I it is at line 1318 in my file but there ater there are couple of batches with no blank lines so what diffrentiate a batch from an other the numbering that start from 1 i figured in Col A but then after I saw some of your formulas ignoring this and stopping just in the middle of numbering like line 58 and still the numbering would continue down

So you need to tell me how to sort all this out !!!!
gowflow
Avatar of JaseSt

ASKER

yes, when I first started using this page I used different formulas and then over time evolved to where I am today. To make things easier I can delete everything from before June 1st (as an arbitrary date) as I have copies of the Visa workbook that I can use as an archive.

The blank yellow highlighted lines serve to breakup the different batches but we can do something else if that will help you. In other words, you tell me what you need to have done to make it work and we can determine the best solution. This sheet does not have to be in a certain format or layout.
ok no problem, anyway, like I always do talking about the problem in details help me find the solution which I am testing now. I should hv something ready soon. As soon as we get the total batch what should we do then you mention Code T to Z kicks off ... I doubt it will do as when I input something in I it does not kick off anything ! will check it. But pls confirm that from me or objective of this question is to get the total batches if anything else please detail it here.
gowflow
Avatar of JaseSt

ASKER

The way the code stands right now is once I sum up the values from Col H into Col i the code populates columns T through Z, but yes, this question is to do just that and if it doesn't kickoff the function to do the calculations and population of T thru Z it needs to:

2. When Col i is filled in it then should kick off the code that is already there, however - and here is a tricky part - I need this formula to instead of putting 1.4 in Col U to look up and input the current USD to Euro conversion rate. Is that possible?

Remember, that way it has kicked off the code to populate A - Z is by summing H and inserting the total in i, then copying and pasting the values from H back into H - that's when it works. This is a part I was hoping you could fix as it is rather cumbersome.

Thank you!
Noted but pls explain what do you mean by:
1.4 in Col U to look up and input the current USD to Euro conversion rate. ????

To lookup the internet for the rate automatically ? to lookup where ?
gowflow
ASKER CERTIFIED SOLUTION
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of JaseSt

ASKER

Tried it and it worked great! Even kicked off the code to populate Cols T - Z - so that is good.

However, it did one strange thing, attached, where it put the sum ($295) of a one-entry batch in a blank row above where it should have been. User generated image

But, in a single-entry batch below it (also attached: $505) the sum was put in the correct row.

Maybe because the $295 sum was the first it had to calculate of the batches? User generated image
Avatar of JaseSt

ASKER

If you can fix the problem (slight as it is) of putting the first calculation in the empty row I will close this question and pose the request to have the USD to Euro conversion rate in Col V part of the question as a different question.
let me get this right. the 295 that came in the yellow line have a 1 in Col A ? and what about the 505 that came correctly what is its line number in Col A ?
gowflow
Avatar of JaseSt

ASKER

no, the 295 had a 17 in Col A as it was a continuation of previous batches not yet sent to Joseph.

The 505 had a 1 in Col A.
What did the yellow line had in Col A ?? click on it and make sure there was no blank space or anything else. I tried every combination but all came ok could not reproduce your problem.
gowflow
Avatar of JaseSt

ASKER

it was blank, but okay. let's just go with it and if there is a problem again, I'll let you know.
Avatar of JaseSt

ASKER

Great work as always, gowflow. Really appreciate it.

Now onto the currency conversion part of the question. Will post a little later and let you know here
Yes coz I could not reproduce no matter what I did. I hope this line you did not delete it or create it manually !!! Anyway hope it is to your satissfaction (beside this not found point !)
gowflow
No need to waist time on the currency conversion in explaining just put a small line and you will get what you want !!! :)
gowflow
Did you post the question ?
gowflow