# 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:

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,
###### Who is Participating?

Excel 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:

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

Regards,
Glenn
EE_Q_28958166.xlsx
0

Excel 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:

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

BI ANALYSTAuthor 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

Excel 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

BI ANALYSTAuthor 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

Excel 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

BI ANALYSTAuthor 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

Excel 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

Excel 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:

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

BI ANALYSTAuthor 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.
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

BI ANALYSTAuthor Commented:
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

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

BI ANALYSTAuthor Commented:
still looking for assistance?
0

Excel 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

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

BI ANALYSTAuthor 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

Excel VBA DeveloperCommented:
I'm glad I was able to help.  Your extra examples were beneficial.

-Glenn
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.