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
53 Views
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:

Aaron d    25044        WA         218.95  23
Aaron d    25044        WA         330     46
Aaron d    25044        WA         613.5   63
BalanceSheet Data Sample

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])

``````
I AM STUMPED

Thanks,
0
Question by:Karen Schaefer
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 9
• 8

LVL 27

Expert Comment

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:

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

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

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

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

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

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

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

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:

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

Author Comment

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.
Note the Balance Sheet New Adj Amt column is also a calculation formula.

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

0

Author Comment

ID: 41719999
When I place the formula on the Balance sheet I get the following error:

I copy the formula from above and not your sample.
0

Author Comment

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)
``````
0

Author Comment

ID: 41721536
still looking for assistance?
0

LVL 27

Expert Comment

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

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

LVL 27

Accepted Solution

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:

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

Regards,
Glenn
EE_Q_28958166.xlsx
0

Author Closing Comment

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

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

-Glenn
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,â€¦
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
###### Suggested Courses
Course of the Month3 days, left to enroll