Pulling From Another Sheet and Sumproduct 1

[kidnos]

I have sheet 1 which has titles set up in column A. These titles are pulled from Sheet 2 (same column). I also have a date listed at the top of sheet 1 (in the same place as dates in sheet 2, but sheet 2 contains multiple columns for each quarter). I would like to set something up where if the date in sheet 1 matches the date in sheet 2 it pulls the corresponding values from the named rows in sheet 1. I tried using sumproduct but its not working. Is it b/c you have to use quotations and text? Below is sheet 1 and I would like to be able to pull 1Q08 values from sheet 2 (which contains the values for every quarter). Thanks
This is my formula (the first part is the date, the second is row titles, the third is the value (although its only pulling from one column, it s/b pulling from all) :
=SUMPRODUCT((Sheet2!B5:B5=sheet1!B6)*(Sheet2!A1:A6500=Sheet2!A7)*(Sheet2!BA1:BA6500))


1Q08
Revenue
Direct Costs
Gross Profit
Indirect Costs
Stock Based Compensation
Restructure Charge
Legal/Settlement Charge
Total Add-backs
Depr. & Amort.
Amort. of Def'd Comp.
Total Operating Exp
EBIT

 

[AnotherHiggins]

Right off the bat, the first thing I see for you to check on is a problem with your formula:

=SUMPRODUCT((Sheet2!B5:B[red]5[/red] = sheet1!B6) * (Sheet2!A1:A[red]6500[/red] = Sheet2!A7) * (Sheet2!BA1:BA[red]6500[/red]))

Each part of the Sumproduct formula must contain the same number of rows. So try this instead:

=SUMPRODUCT((Sheet2!B5:B[highlight][red]6500[/red][/highlight] = sheet1!B6) * (Sheet2!A1:A[red]6500[/red] = Sheet2!A7) * (Sheet2!BA1:BA[red]6500[/red]))


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[kidnos]

John, thanks for the response. I tried that and It still kicking me a #VALUE!
Sheet 1 is named QTR COMP and Sheet 2 is named IS. This is my formula:
=SUMPRODUCT((IS!B1:B6500=B6)*(IS!A1:A6500=A7)*(IS!BA1:BA6500))

Also, the date goes across in columns, so I want it to search the columns and return the value in that row. I think in this case it is searching only one column and therefore only one date.

 

[SkipVought]

Hi,

Check your VALUES in IS!BA1:BA6500. Any ERROR values?

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[kidnos]

No ERROR values that I can see. I even narrowed the range and changed the formula to search text, so: =SUMPRODUCT((IS!B1:B300 ="1Q08")*(IS!A1:A300 ="Revenue")*(IS!BA1:BA300)) and still nothing.

 

[SkipVought]

Please post a sample of the data from sheet2, several representative rows.

You mentiond DATES, but all I see in your examples is TEXT, 1Q08: NOT a good collating format.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[kidnos]

I think it has something to do with the text I have in the column its pulling from. If I remove all of the data above "revenue" from the source column then the formula works. This is the data from sheet 2 ot IS.

Column A:
XYZ Co.
Income Statement (000)
FYE - ???


Revenue
Direct Costs
Gross Profit
Indirect Costs
Stock Based Compensation
Restructure Charge
Legal/Settlement Charge
Total Add-backs
Depr. & Amort.
Amort. of Def'd Comp.
Total Operating Exp
EBIT


Column BA:

BA

Jun
1Q08
$133,481
112,329
$21,152
9,423
$0
0
0
$0
1,609
75
$123,436
$10,045

 

[kidnos]

It seems like there would be an easier way to search across across a row for text (Q108) and pull the value of a cell in that column if the text in column A matches that on the first sheet.I think the searching across the row is whats causing the problem. Perhaps there is a way to retrieve what column contains Q1'08 and then have that column reference included in the sumproduct?
Thanks for the help on this.

 

[SkipVought]

No COLUMN B sample data????

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[SkipVought]

ANSWER:

You have [red]TEXT[/red] in IS!BA1:BA6500

!!!!

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}