Good Day, so find myself in need of assistance here. Also, I am in Canada
I am looking for the best formula to do a variety of Functions.
I have the USD/CAD exchange rate place at YZ 1 &2
I get several sheets that look like the graphic, they represent an Analyst, so the "amount" of files/rows will vary, I need to do the following:
The Highlighted rows are US Banks
The Non Highlighted are Canadian Banks.
I need a formula that will take the "Amount" (Q) of the US funds only, Multiply them by V2 (the current exchange rate), then place the Canadian conversion into the corresponding S cell, and add the total to the CAD Bal (T) like the Balance column does.
I have tried =, SUM, IF, etc with no luck.
The only thing consistent would be Item so tried to formulate using that, as that column will always have a US in front of it for anything billed in US Funds. Still nothing.
CAD Equi Column (S) needs to have the converted US funds, then totaled at bottom. and running balance Like R also totaled at bottom
I don't know what the Range limit is for Q but this report can sometimes be 3 pages long as we have several analyst's working on Canadian and US files.
Trying to get a final report that shows the Canadian Funds billed each month, instead of having to go through each analyst to manually do this for each US Deal.
Alternately if you have a better solution I'm all ears, but when I think I've got it, something screws up....
I am looking for the best formula to do a variety of Functions.
I have the USD/CAD exchange rate place at YZ 1 &2
I get several sheets that look like the graphic, they represent an Analyst, so the "amount" of files/rows will vary, I need to do the following:
The Highlighted rows are US Banks
The Non Highlighted are Canadian Banks.
I need a formula that will take the "Amount" (Q) of the US funds only, Multiply them by V2 (the current exchange rate), then place the Canadian conversion into the corresponding S cell, and add the total to the CAD Bal (T) like the Balance column does.
I have tried =, SUM, IF, etc with no luck.
The only thing consistent would be Item so tried to formulate using that, as that column will always have a US in front of it for anything billed in US Funds. Still nothing.
CAD Equi Column (S) needs to have the converted US funds, then totaled at bottom. and running balance Like R also totaled at bottom
I don't know what the Range limit is for Q but this report can sometimes be 3 pages long as we have several analyst's working on Canadian and US files.
Trying to get a final report that shows the Canadian Funds billed each month, instead of having to go through each analyst to manually do this for each US Deal.
Alternately if you have a better solution I'm all ears, but when I think I've got it, something screws up....