Solved

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

Posted on 2016-07-18
17
35 Views
Last Modified: 2016-07-21
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
Comment
Question by:Karen Schaefer
  • 9
  • 8
17 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 41718171
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
 

Author Comment

by:Karen Schaefer
ID: 41719390
Thanks Glen you got it exactly,  Please note I will need to readjust the Tot_leads in the same way also.
Thanks,

Karen
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 41719679
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
 

Author Comment

by:Karen Schaefer
ID: 41719687
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 41719699
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
 

Author Comment

by:Karen Schaefer
ID: 41719768
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 41719905
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 41719927
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:Karen Schaefer
ID: 41719992
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
 

Author Comment

by:Karen Schaefer
ID: 41719999
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
 

Author Comment

by:Karen Schaefer
ID: 41720010
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
 

Author Comment

by:Karen Schaefer
ID: 41721536
still looking for assistance?
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 41721779
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
 

Author Comment

by:Karen Schaefer
ID: 41721895
thanks for your response, still getting the error message about the formula being text- see previous post.
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 41723600
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
 

Author Closing Comment

by:Karen Schaefer
ID: 41723776
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 41723847
I'm glad I was able to help.  Your extra examples were beneficial.

-Glenn
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now