Pedro
asked on
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'
F5 is the maximum number found in AD4:AD13 COLUMN 'ER'
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'
F7 is the minimum number found in AD9:AD13 COLUMN 'ER'
G7 is the maximum l number found in AD9:AD13 column 'ER'
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
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'
F5 is the maximum number found in AD4:AD13 COLUMN 'ER'
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'
F7 is the minimum number found in AD9:AD13 COLUMN 'ER'
G7 is the maximum l number found in AD9:AD13 column 'ER'
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
ASKER
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
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
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
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
ASKER
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.
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.
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(OFFSET(INDIRECT(CELL(
Max SO:
=MAX(OFFSET(INDIRECT(CELL(
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
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(OFFSET(INDIRECT(CELL(
Max SO:
=MAX(OFFSET(INDIRECT(CELL(
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
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):
=MAX(OFFSET(INDIRECT(CELL(
It's not pretty, but it works.
=MAX(OFFSET(INDIRECT(CELL(
It's not pretty, but it works.
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
Again, if you have an actual data example - rather than baseball pitching statistics - that will greatly help providing you a solution.
-Glenn
ASKER
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
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
Several things:
1) Your original post specifies that
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:
=AVERAGE(OFFSET(INDIRECT(C
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:
=AVERAGE(OFFSET(INDIRECT(C
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
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:
=AVERAGE(OFFSET(INDIRECT(C
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:
=AVERAGE(OFFSET(INDIRECT(C
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
ASKER
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"?
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"?
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.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for all your hard work
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