Hi Daniel
The way I would tackle this would be to use Pivot Tables along with a VBA
macro.
I would have an extra data sheet with a copy of one of the sheets data, and
from this construct a Dynamic Range for the Data which would be sued as the
source for a Pivot Table which showed the data I wanted.
In a loop in the macro, I would copy each of the 6 sets of data for the 6
sheets in turn, Refresh the Pivot and via VBA copy the data I wanted to my
report sheet in the column for Q1.
Continue the loop until all 6 Quarters have been covered
--
Regards
Roger Govier
"cardan" wrote in message
...
On Nov 22, 3:55 pm, "Harald Staff" wrote:
Hi
Don't use formulas. This is what a pivot table does.
HTH. Best wishes Harald
"djcmisc" wrote in message
...
Hello, I am trying to figure a formula that will search a range of
data and sum the values of a specific column based on the column name
and row name. I have a table of 7000+ companies listed in column B.
Column A is the State where the company is located. In Row 1, are
accounting codes, each representing things like, Net Income, Revenue,
Expenses etc... There are 60+ fields of codes.
I would like to write a formula that will search the data table,
recognize the column identifiers ("Net Income" for example) and then
sum all those values of all the banks located in New York.
I have been using the Index Match formula to pull out info on each
individual company. Here is an example the formula I have been using.
INDEX('2Q 2009'!$C$2:$BE$7000,MATCH(G$10,'2Q 2009'!$B$2:$B
$7000,0),MATCH($C31,'2Q 2009'!$C$1:$BE$1,0)) "G$10" = The company
name & $C31 = the accounting code
This works great to find the information on a specific company, as far
as finding and summing the column of all companies in a specific state
is where I am lost. Summing the Index Match on a specific criteria
would be ideal, as the output sheet for the data is formatted
differently and I have to do this numerous time for each quarter.
Any assistance would be great. Thanks....
Hi Harald. Thank you for the quick response. The issue is the way I
present the data. I have 6 quarters of data (each 7000x50 table is in
different worksheets) represents a quarter and I need to show all
quarters side by side. I am skeptical of linking cells to a pivot
table. Are my concerns valid? Thanks again..
__________ Information from ESET Smart Security, version of virus
signature database 5641 (20101123) __________
The message was checked by ESET Smart Security.
http://www.eset.com
__________ Information from ESET Smart Security, version of virus signature database 5641 (20101123) __________
The message was checked by ESET Smart Security.
http://www.eset.com