Reporting Info to New Sheet

[Jennifer Corle]

Using Excel 2003, I am trying to create a sheet (?Reporting?) to compile the project information kept on another sheet (?2009?) in the same workbook. Essentially, once I can get this one thing figured out, I can probably figure out the rest of it. I want to figure out which columns in the ?2009? sheet have a certain Agenda Item and Project Number that match the Project Number on the ?Reporting? sheet and bring back the date that the item was approved from the ?2009? sheet. I tried it with an example that I know should come up with a date, however I?m not getting any results.

Here are the attempts I?ve made so far. I'm just trying to come up with a report, of sorts.

2009 Sheet:
Column A: ?Date? Column D: ?Agenda Item? Column H: ?Project No.?

Reporting Sheet:
Column A: ?Project No.?

=IF(AND('2009'!D21456="Advertise for Bids",'2009'!H2:H1456=A111),'2009'!A2:A1456,"None") RETURN: ?NONE?

=IF(AND('2009'!D21456="Advertise for Bids?,'2009'!H2:H1456=Reporting!A111),'2009'!A2:A1456," None ") RETURN: ?NONE?

=IF('2009'!D21456="Advertise for Bids ",IF('2009'!H2:H1456=Reporting!A111,'2009'!A2:A1456,"None")) RETURN: ?FALSE?

=IF(AND(--('2009'!D21456="Advertise for Bids"),--('2009'!H2:H1456=Reporting!A118)),'2009'!A2:A1456,"None") RETURN: ?NONE?

=IF('2009'!D21456="Advertise for Bids?, IF('2009'!H2:H1456=A111,'2009'!A2:A1456,"None")) RETURN: ?FALSE?


I ended up trying a ?Sumproduct?, and they were all returning a false code.
=SUMPRODUCT(--('2009'!D21246)="Advertise for Bids"),--('2009'!H2:H1246)=('2009'!A111)),('2009'!A2:A1246)
=SUMPRODUCT(--('2009'!h2:h1246)=A111),--('2009'!D21246="Advertise for Bids"),'2009'!A2:A1246)
=SUMPRODUCT('2009'!h2:h1246='Reporting'!A111),('2009'!D21246="Advertise for Bids"),'2009'!A2:A1246)

Thanks all!
Jennifer Corle
City of South Bend
Department of Engineering & Board of Public Works


EggHeadCafe - Software Developer Portal of Choice
ASP.NET Application State and Application Object
http://www.eggheadcafe.com/tutorial...f8-32baafefbb10/aspnet-application-state.aspx

 

[Eduardo]

Hi Jennifer,
try
=SUMPRODUCT(--('2009'!D21246="Advertise for
Bids"),--('2009'!H2:H1246='2009'!A111),('2009'!A2:A1246)
=SUMPRODUCT(--('2009'!h2:h1246=A111),--('2009'!D21246="Advertise for
Bids"),'2009'!A2:A1246)
=SUMPRODUCT(--('2009'!h2:h1246='Reporting'!A111),('2009'!D21246="Advertise
for Bids"),'2009'!A2:A1246)

 

[Jennifer Corle]

Thanks anyway



Jennifer Corle wrote:

Reporting Info to New Sheet
28-Oct-09

Using Excel 2003, I am trying to create a sheet (?Reporting?) to compile the project information kept on another sheet (?2009?) in the same workbook. Essentially, once I can get this one thing figured out, I can probably figure out the rest of it. I want to figure out which columns in the ?2009? sheet have a certain Agenda Item and Project Number that match the Project Number on the ?Reporting? sheet and bring back the date that the item was approved from the ?2009? sheet. I tried it with an example that I know should come up with a date, however I?m not getting any results.

Here are the attempts I?ve made so far. I'm just trying to come up with a report, of sorts.

2009 Sheet:
Column A: ?Date? Column D: ?Agenda Item? Column H: ?Project No.?

Reporting Sheet:
Column A: ?Project No.?

=IF(AND('2009'!D21456="Advertise for Bids",'2009'!H2:H1456=A111),'2009'!A2:A1456,"None") RETURN: ?NONE?

=IF(AND('2009'!D21456="Advertise for Bids?,'2009'!H2:H1456=Reporting!A111),'2009'!A2:A1456," None ") RETURN: ?NONE?

=IF('2009'!D21456="Advertise for Bids ",IF('2009'!H2:H1456=Reporting!A111,'2009'!A2:A1456,"None")) RETURN: ?FALSE?

=IF(AND(--('2009'!D21456="Advertise for Bids"),--('2009'!H2:H1456=Reporting!A118)),'2009'!A2:A1456,"None") RETURN: ?NONE?

=IF('2009'!D21456="Advertise for Bids?, IF('2009'!H2:H1456=A111,'2009'!A2:A1456,"None")) RETURN: ?FALSE?


I ended up trying a ?Sumproduct?, and they were all returning a false code.
=SUMPRODUCT(--('2009'!D21246)="Advertise for Bids"),--('2009'!H2:H1246)=('2009'!A111)),('2009'!A2:A1246)
=SUMPRODUCT(--('2009'!h2:h1246)=A111),--('2009'!D21246="Advertise for Bids"),'2009'!A2:A1246)
=SUMPRODUCT('2009'!h2:h1246='Reporting'!A111),('2009'!D21246="Advertise for Bids"),'2009'!A2:A1246)

Thanks all!
Jennifer Corle
City of South Bend
Department of Engineering & Board of Public Works

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
Silverlight 2: Doing Data Part VII: Custom Binary Serialization
http://www.eggheadcafe.com/tutorial...83-f768d09949de/silverlight-2-doing-data.aspx

 

[Jennifer]

Hi Jennifer,
try
=SUMPRODUCT(--('2009'!D21246="Advertise for
Bids"),--('2009'!H2:H1246='2009'!A111),('2009'!A2:A1246)
=SUMPRODUCT(--('2009'!h2:h1246=A111),--('2009'!D21246="Advertise for
Bids"),'2009'!A2:A1246)
=SUMPRODUCT(--('2009'!h2:h1246='Reporting'!A111),('2009'!D21246="Advertise
for Bids"),'2009'!A2:A1246)

That's pretty much what I ended up doing, thanks! Once I got it all
together, they didn't want the information in the way I presented it.
LOL!

Jennifer