Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 68
  • Last Modified:

Calculate the new value and update only the first value per criteria - Clarifying my original post looking for solution for over a week now.

https://www.experts-exchange.com/questions/28957295/Syntax-help-translating-actual-column-field-names-to-test-sample-SumProduct-returning-wrong-value.html

I have a workbook that containing 2 sheets Sheet1 = "BalanceSheet", Sheet2 ="qryDifference". I need to determine if a value from BalanceSheet = Y and if so then compare the AgentNO to QryDifference, determine the first value from qryDifference per agent no and then add that to the difference column on BalanceSheet. NOTE: Sheet name Country_results is same as QryDifference.

QryDifference Data Sample:

 agent_name  agentno    agent_state agt_amt tot_leads
 Aaron d    25044        WA         218.95  23
 Aaron d    25044        WA         330     46
 Aaron d    25044        WA         613.5   63
BalanceSheet Data Sample

 agent_name agentno  Balance    SumOfAgtAmt SumLeads Difference  Validate Adjustment    New Adj Amt
Aaron H     16813    $281.00     $281.00       24       0                                 $281.00
Aaron d     25044    $1,067.10  $1,162.45   132  -95.35            y                 $1,067.10
Adam P      22312    $11.00      $11.00         1       0                                $11.00
I have tried numerous methods including IndexMatch, Sumif, SumProduct, nested Ifs.

Problem: There is a discrepancy in the amount for Aaron d - the Balance = #1067.10, SumofAgtAmount = 1162.45 - Difference -95.35, So I need to update the qryDifference.Agt_Amount for the FIRST RECORD ONLY for Aaron D.

Here are examples of what I Have tried, NOTE: I am using named ranges.

=SUMPRODUCT((qryDifference[[#All],[agentno]]=agtno)*(BalanceSheet!G10:G258="Y")* Country_results[agt_amt]+[Difference])

=SUMPRODUCTif((qryDifference[agentno]=Country_results[@agentno])*(qryDifference[Validate Adjustment]="Y")*qryDifference[New Adj Amt])+Country_results[agt_amt])

=IF(qryDifference[agentno]=Country_results[agentno],SUMPRODUCT((qryDifference[agentno]=Country_results[@agentno])*(qryDifference[Validate Adjustment]="Y")*qryDifference[New Adj Amt])+Country_results[agt_amt])

Open in new window

I AM STUMPED

Thanks,
0
Karen Schaefer
Asked:
Karen Schaefer
  • 9
  • 8
1 Solution
 
Glenn RayExcel VBA DeveloperCommented:
I'm restating your request to make sure I understand:

You have two spreadsheets with Excel Tables (qryDifference, BalanceSheet).  You're looking for a formulaic solution that uses structured referencing.  

You want to adjust the first table - Country_Results (on qryDifference sheet) when the following exists:
* a record on the BalanceSheet has the value "Y" in the field [Validate Adjustment]
* the first record on the Country_Results table with the same [agentno] as the record with "Y" [Validate Adjustment] will be affected
* the [agt_amt] value will be adjusted by the [Difference] value from the BalanceSheet table.

In your example, you would want to see the first table results changed like so:
before-after
Of course, if there are multiple adjustments, you want all applicable records changed on the Country_Results sheet.

Is this correct?  If so, then this is possible without VBA, because you have values in the qryDifference table, not formulas.  You could produce a separate, adjusted table that mimics the qryDifference table with all the adjusted records.

Regards,
Glenn
0
 
Karen SchaeferAuthor Commented:
Thanks Glen you got it exactly,  Please note I will need to readjust the Tot_leads in the same way also.
Thanks,

Karen
0
 
Glenn RayExcel VBA DeveloperCommented:
I'm sorry, my previous comment should have been "... then this is NOT possible without VBA..."

Regarding the Tot_leads value; there is no difference in your example between the SumLeads (BalanceSheet) and tot_leads (qryDiffference).  There is also no column that reflect the leads difference (if any).

-Glenn
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Karen SchaeferAuthor Commented:
no column that reflect the leads difference (if any).
- Not Yet  A

then this is NOT possible without VBA..."
 Not looking for a VBA solution if a function will do the job that's great.
0
 
Glenn RayExcel VBA DeveloperCommented:
Sorry, unless you add a new column(s) to your qry Difference table to reflect the adjusted agt_amt and tot_leads amounts, this is not possible without VBA.
0
 
Karen SchaeferAuthor Commented:
Yes, I understand never-mind about total leads I will just adapt what we come up with for the adj_amt.  New columns to the worksheet is what I intended to do.

I was not planning on creating a vba function to accomplish the task but a excel function such as (ie. SumofProduct, Index Match, etc)
0
 
Glenn RayExcel VBA DeveloperCommented:
Okay.  I think we can solve it with the addition of "Adjustment" columns.  

Question:  How do you want the adjustments to be handled if the difference adjustment is greater than the first occurring agt_amt?

For example, suppose that in your example, "Aaron d" had a balance of only $900.00 instead of 1,067.10.  This would make the Difference value -262.45.  Since the first transaction on the qryDifference table is only 218.95, that might create an adjusted value of -43.50.  Is that acceptible or do you need the amount to "roll" to subsequent transaction(s)?
0
 
Glenn RayExcel VBA DeveloperCommented:
Assuming the simplest solution for now - no rolling of extended differences past the first transaction - see the attached workbook for a possible solution to your question.

The BalanceSheet table is "Table1" and the qryDifference table has a new column titled "adj_agt_amt" that contains this formula:

=IF(AND(COUNTIF($B$2:B2,B2)=1,IFERROR(VLOOKUP([@agentno],Table1[[agentno]:[Validate Adjustment]],6,FALSE),"")="Y"),[@[agt_amt]]+VLOOKUP([@agentno],Table1[[agentno]:[Difference]],5,FALSE),[@[agt_amt]])

It only adjusts the first occurrence of an agentno and only if "Validate Adjustment" has a "Y" value on the BalanceSheet table.

Regards,
Glenn
EE_Q_28958166.xlsx
0
 
Karen SchaeferAuthor Commented:
sorry I am a bit confused and maybe can help me with using the correct sheet names.

Please see below the proper worksheet names and their data sources.  Could you please correct the formula accordingly.  also Note the number of columns on the Data Sheet is actually different - A thru L (C-G) is hidden due to proprietary data.

Also I would like the formula on the Balance sheet since the Data sheet datasource can change.Captur3.PNG
Note the Balance Sheet New Adj Amt column is also a calculation formula.

Captur3a.PNG
When I copy your formula it wants to keep the workbook name.

I appreciate your help.
0
 
Karen SchaeferAuthor Commented:
When I place the formula on the Balance sheet I get the following error:

Capture.PNG
I copy the formula from above and not your sample.
0
 
Karen SchaeferAuthor Commented:
here is my latest attempt still getting the formula error seen above:

=IF(AND(COUNTIF($B$2:b2,b2)=1,IFERROR(VLOOKUP([agentno],agtno:[Validate Adjustment]],6,FALSE),"")="Y"),[@[agt_amt]]+VLOOKUP([@agentno],agtno:[@Difference],5,FALSE),agtno)

Open in new window

0
 
Karen SchaeferAuthor Commented:
still looking for assistance?
0
 
Glenn RayExcel VBA DeveloperCommented:
Sorry for the delay.  

1) It should be easy enough to replace any sheet or table names from my example to match those in your workbook.
2) Similarly, the lookup ranges and column arguments can also be easily changed to match your actual table.
3) What value do you now want in the BalanceSheet table in column I ("adj_agt_amt")?  And why do you actually need to see it there since you already are showing the Balance, SumOfAgtAmt and Difference?

I would not ever try to copy and paste directly from one workbook to another to do this - mainly because you'll get the kind of external link references that you got (i.e., "EE_Q_28958166.xlsx!" and so on).  Instead, highlight and copy the entire formula text within the formula bar and then paste that into the destination cell in your workbook.  Then, before entering (i.e., pressing [Enter]), edit the particulars to match your own workbook environment (i.e, sheet names, table names, table field names, etc.).

I am away from my main computer, but will try to provide an example formula that matched what you've shown when I return.  Hopefully, you might be able to determine some of the solution in the meantime.

Regards,
-Glenn
0
 
Karen SchaeferAuthor Commented:
thanks for your response, still getting the error message about the formula being text- see previous post.
0
 
Glenn RayExcel VBA DeveloperCommented:
1)  You are likely getting the error because the column may be defined as having Text values, however...
2)  the formula actually belongs in the qryDifference table (on Data sheet), not Country_Results (on BalanceSheet sheet)
3)  Given the table layouts you provided, the new column in qryDifference - adj_agt_amt - will have the following formula:
=IF(AND(COUNTIF($B$2:B2,B2)=1,IFERROR(VLOOKUP([@agentno],qryDifference[[agentno]:[Validate Adjustment]],6,FALSE),"")="Y"),[@[agt_amt]]+VLOOKUP([@agentno],qryDifference[[agentno]:[Difference]],5,FALSE),[@[agt_amt]])

Please refer to the attached, modified example workbook to see the updates.

Regards,
Glenn
EE_Q_28958166.xlsx
0
 
Karen SchaeferAuthor Commented:
Fantastic, thanks a million for all your efforts, this works great and I was able to modify it for the leads count also.

Thanks.

Karen
0
 
Glenn RayExcel VBA DeveloperCommented:
I'm glad I was able to help.  Your extra examples were beneficial.

-Glenn
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now