# need code to pull data from one table and insert into another

would like code for each cell in order to pull necessary data from the tables on the right and make the necessary calculations to achieve the results show as follows:

The sales person shown in B7 means that data for this area will be taken from the table labeled "weaver" to the right. The data corresponds as follows:

B6 is simply the DATA found in Z14
C5 is the sum of all data from X4:X13 found in          column "W"  hence the result 5
D5 is them sum of all data from Y4:Y13                      column "L"
E5 is the minimum number found in Z4:Z13       column 'ERA'
G5 is the average of all number found in AD4:AD13     column 'ER'
H5 is the minimum number found in AF4:AF13       column 'SO'
I5  is the maximum number found in AF4:AF13       COLUMN 'SO'

C7 is the sum of all data from X9:X13 found in          column "W"  hence the result 5
D7 is them sum of all data from Y9:Y13                      column "L"
E7 is the average of data found in Z9:Z13                  column 'ERA'
H7 is the minimum number found in AF9:AF13       column 'SO'
I7  is the maximum number found in AF9:AF13       COLUMN 'SO'

The same will be done for the side of "Salazar" only this time the data will taken from table with his name (i.e. Salazar). and put into the correspoding cells within the table.
DataCalculations.xlsx
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Excel VBA DeveloperCommented:
The attached file is an updated version of your example workbook, containing literal Excel functions which satisfy your requirements.  There's nothing particularly difficult here, just MIN, MAX, and AVERAGE functions.

That said, your "sales person" data arrangement here is not open-ended and new formulas would have to be manually reconstructed if there are changes to the data tables in either size or position.  And if new "sales person" reporting is required, that would also mean new manual creation of formulas.

If this is merely an academic exercise, then this should suffice.  But if there is an actual work need that requires expandability, you'll need to provide a more concrete example.

Regards,
-Glenn
EE-DataCalculations.xlsx
0
Author Commented:
Glen,

Thanks for your post. It makes me realize I forgot to metion one important factor.

The sales name will change from time to time and thus I require code that will look at the Name in fields B7 and J7 then find the table with that name and then fill out the fields as previously mentioned. The way you did it requires me to find the name and fill out the data myself each time. I am looking to automate the finding and filling process.

I hope this clarifies what is needed.

Thanks
0
Excel VBA DeveloperCommented:
That's exactly what I was referring to in my previous post.  This data layout is not condusive to extracting the kinds of information you want easily.   To make this a more functional report, you want to organize your source information in a single data table.

You'd only have to make some minor changes - like adding a "sales person" name to a new column and inserting that name for each row/record.  Then you could use various modified functions to query that data and report.

So, your data would now look something like this:

Now you can use new functions to return the results AND use PivotTables as well to summarize your data.  I've attached an example file that demonstrates both.   Note that the PivotTable shows overall results, but you could filter to the last five games by using either a report filter on "Games" or a slicer to pick the last five.  Also, note that the MIN and MAX functions are array functions (use [Ctrl]+[Alt]+[Enter] to complete) and show curly brackets {} to indicate as such.

{Edit:  I've updated all the functions and annotated them in the attached file}

Regards,
-Glenn
EE-DataCalculations.xlsx
0
Author Commented:
Thanks for the input but the changes you propose would only serve to confuse since you've made changes to the original data fields and merged tables.

Once the amount of data required is input it will get confusing to do it that way.

I prefer to keep the tables separated since they will need updating and merged tables will be difficult to update. You,ve also eliminated the last row in each table.

I believe there is a way for it pull data from the table based on the table name I just do not know how to code it.

The code I need would use Table name and column or  row name to access data.

Someone has to know how to do this.
0
Excel VBA DeveloperCommented:
One can certainly use structure references to return results, but the inclusion of a manual total row makes it problematic.

For example, the win total for any given "sales person" - assuming that the table "name" is the same is:
=SUM(name[W])/2
You have to divide by two since the total row contains the sum of wins as well.  Same goes for the Losses.
=SUM(name[L])/2

The last ERA posted (in the Total row):
=INDEX(name,ROWS(name),6)

The Minimum ER and Minimum SO counts are easy enough:
=MIN(name[ER])
=MIN(name[SO])

But your Maximum ER and SO counts won't work because the manual total row is considered part of the #Data and those values will be returned if one uses the MAX functions.  So you have to revert to some indexing:
Max ER:
Max SO:

Then you have the second set of formulas that appear to focus on the last five games of results.  You can't really use structure references here because there is no provision for that in Excel.  As with the MAX functions, some indexing needs to be used.

And here's where I'll stop.  Because after doing several of these calculations, the example numbers you show don't match up with the stats.  Additionally, the total row ERA shows a value that does not correlate to the numbers above.

If you can clarify that, and state that this is indeed the direction you want to go, I'll provide all the remaining formulas for you in an un-modified workbook.

Regards,
-Glenn
0
Excel VBA DeveloperCommented:
PS.  If you also wanted to use the names in cells B7 and J7 as references to the table names, they would have to also be wrapped in INDIRECT functions in order to work.  For example (cell F5, maximum ER):

It's not pretty, but it works.
0
Excel VBA DeveloperCommented:
PPS  Please review again your requests for columns E, F, and G.  You specify minimum, maximum, and average in various ways across each column that do not appear to relate to the column headers.

Again, if you have an actual data example - rather than baseball pitching statistics - that will greatly help providing you a solution.

-Glenn
0
Author Commented:
I think the best way to get the results I need is to use excel table and column names instead of using explicit cell references as it appears we've been doing up until now. For instance,

Instead of using explicit cell references                                         Excel uses table and column names

=Sum(C2:C7)                                                                                            =SUM(DeptSales[Sales Amount])

Using the data in the table provided I use the following formula in E5:

=AVERAGE(WEAVER[ERA])

Where weaver is the table name and ERA is the header label. The result I get is 3.61
which is different from the manual average of cells Z4:Z13. Thus, one flaw is that it does not give me the same result and two I also need the table name to change based on the value in cell B7.

So Cell B7 contains the table name that should automatically be inserted into the formula.  I believe a variable or reference is the way but don't know how.

Also I need a way to specify the the second row of data (i.e. E7) should only account for the last 5 items of data. (i.e. Z9:Z13)

The link below may help to see the stuff I am  using, I just keep getting different calculations from the manual calculation using it.

http://office.microsoft.com/en-us/excel-help/use-structured-references-in-excel-table-formulas-HA102749547.aspx?CTT=1
DataCalculations.xlsx
0
Excel VBA DeveloperCommented:
Several things:

1) Your original post specifies that
E5 is the minimum number found in Z4:Z13       column 'ERA'
Now you're saying it's the average.  My previous post pointed this discrepancy out.  These requirements need to be double-checked and corrected before continuing.

2) As stated previously, the reason that structured referencing like:
=AVERAGE(WEAVER[ERA])
does not return the average of the 10 games is becuase you have added an 11th row of data.  Calling it a "Total" row does not distinguish it from the other data and its values are also being considered.  If you insist on keeping that row - rather than using a Table Total row - then you have to revert to using indexing functions to return the actual average:

3) I also noted in my follow-up post that in order to reference the table name via the cell value (ex. B7) you have to use INDIRECT.  Using the above example:

Again, I strongly urge that you consider combining all your source data into one Excel Table so that you can greatly simplify the formulas needed to report.

Regards,
-Glenn
0
Author Commented:
If E5 says "ERA"  then it is average I look for. Either way that is simple enough that I can correct it to say average, sum etc.

What I need help with is replacing "weaver" so it is a variable that fills in whatever is in that cell then looks for data in the table with that name.

Will the answer you have as # 3 above look for data in a table that has the name in cell "B7"?
0
Excel VBA DeveloperCommented:
Will the answer you have as # 3 above look for data in a table that has the name in cell "B7"?

Yes.
INDIRECT(\$B\$7) returns "WEAVER" and can be used to reference the Table/Range with that same name.
0
Author Commented:
Maybe I do not understand how this formula works. When I plug in the formula in 'E5' I get "0.20" instead of the expected 3.59. However, the same formula in 'M5' yields 4.79 which is fine considering I got 4.80 as the answer. (See attached file)

What am I missing? same formula different results?

P.S. I do not believe that combining all the source data into one Excel Table will work because each may need updating and it would nearly impossible to distinguish between data sets when they are combined as you suggested.
DataCalculations.xlsx
0
Excel VBA DeveloperCommented:
Your formula does not work because you have an additional blank column in the table "WEAVER" that does not exist in the table "Salazar".  I removed this blank column in my example file in order to keep the tables consistent.  To make the formula work in your case:

If you are going to use separate tables in this manner, they should be identically constructed.  Furthermore, they should NOT have a manual total row added, but rather include a Table Total Row (Menu: Table Tools - Check "Total Row" box).

See the attached example to see how one would reference the tables in this manner.

I can no longer provide assistance on this topic.  The best solution to your reporting issue will be to have one single data table that can be queried using simpler formulas (ex., not requiring INDIRECT referencing) and/or PivotTables.  It is not hard to use data filters on tables to isolate individual data sets as you require (ex., filter on player name to just see their records).

Good Luck,
-Glenn
EE-DataCalculations2.xlsx
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Thanks for all your hard work
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.

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.