| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reporting Info to New Sheet
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'!D2:D1456="Advertise for Bids",'2009'!H2:H1456=A111),'2009'!A2:A1456,"None" ) RETURN: ?NONE? =IF(AND('2009'!D2:D1456="Advertise for Bids?,'2009'!H2:H1456=Reporting!A111),'2009'!A2:A1 456," None ") RETURN: ?NONE? =IF('2009'!D2:D1456="Advertise for Bids ",IF('2009'!H2:H1456=Reporting!A111,'2009'!A2:A145 6,"None")) RETURN: ?FALSE? =IF(AND(--('2009'!D2:D1456="Advertise for Bids"),--('2009'!H2:H1456=Reporting!A118)),'2009'!A2:A1456, "None") RETURN: ?NONE? =IF('2009'!D2:D1456="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'!D2:D1246)="Advertise for Bids"),--('2009'!H2:H1246)=('2009'!A111)),('2009'!A2:A1246) =SUMPRODUCT(--('2009'!h2:h1246)=A111),--('2009'!D2:D1246="Advertise for Bids"),'2009'!A2:A1246) =SUMPRODUCT('2009'!h2:h1246='Reporting'!A111),('20 09'!D2:D1246="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/tutorials...ion-state.aspx |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Reporting Info to New Sheet
Hi Jennifer,
try =SUMPRODUCT(--('2009'!D2:D1246="Advertise for Bids"),--('2009'!H2:H1246='2009'!A111),('2009'!A2:A1246) =SUMPRODUCT(--('2009'!h2:h1246=A111),--('2009'!D2:D1246="Advertise for Bids"),'2009'!A2:A1246) =SUMPRODUCT(--('2009'!h2:h1246='Reporting'!A111),('2009'!D2:D124 6="Advertise for Bids"),'2009'!A2:A1246) "Jennifer Corle" wrote: 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'!D2:D1456="Advertise for Bids",'2009'!H2:H1456=A111),'2009'!A2:A1456,"None" ) RETURN: ?NONE? =IF(AND('2009'!D2:D1456="Advertise for Bids?,'2009'!H2:H1456=Reporting!A111),'2009'!A2:A1 456," None ") RETURN: ?NONE? =IF('2009'!D2:D1456="Advertise for Bids ",IF('2009'!H2:H1456=Reporting!A111,'2009'!A2:A145 6,"None")) RETURN: ?FALSE? =IF(AND(--('2009'!D2:D1456="Advertise for Bids"),--('2009'!H2:H1456=Reporting!A118)),'2009'!A2:A1456, "None") RETURN: ?NONE? =IF('2009'!D2:D1456="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'!D2:D1246)="Advertise for Bids"),--('2009'!H2:H1246)=('2009'!A111)),('2009'!A2:A1246) =SUMPRODUCT(--('2009'!h2:h1246)=A111),--('2009'!D2:D1246="Advertise for Bids"),'2009'!A2:A1246) =SUMPRODUCT('2009'!h2:h1246='Reporting'!A111),('20 09'!D2:D1246="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/tutorials...ion-state.aspx . |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Figured it out
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'!D2:D1456="Advertise for Bids",'2009'!H2:H1456=A111),'2009'!A2:A1456,"None" ) RETURN: ?NONE? =IF(AND('2009'!D2:D1456="Advertise for Bids?,'2009'!H2:H1456=Reporting!A111),'2009'!A2:A1 456," None ") RETURN: ?NONE? =IF('2009'!D2:D1456="Advertise for Bids ",IF('2009'!H2:H1456=Reporting!A111,'2009'!A2:A145 6,"None")) RETURN: ?FALSE? =IF(AND(--('2009'!D2:D1456="Advertise for Bids"),--('2009'!H2:H1456=Reporting!A118)),'2009'!A2:A1456, "None") RETURN: ?NONE? =IF('2009'!D2:D1456="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'!D2:D1246)="Advertise for Bids"),--('2009'!H2:H1246)=('2009'!A111)),('2009'!A2:A1246) =SUMPRODUCT(--('2009'!h2:h1246)=A111),--('2009'!D2:D1246="Advertise for Bids"),'2009'!A2:A1246) =SUMPRODUCT('2009'!h2:h1246='Reporting'!A111),('20 09'!D2:D1246="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/tutorials...oing-data.aspx |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Reporting Info to New Sheet
On Oct 28, 11:28*am, Eduardo
wrote: Hi Jennifer, try =SUMPRODUCT(--('2009'!D2:D1246="Advertise for Bids"),--('2009'!H2:H1246='2009'!A111),('2009'!A2:A1246) =SUMPRODUCT(--('2009'!h2:h1246=A111),--('2009'!D2:D1246="Advertise for Bids"),'2009'!A2:A1246) =SUMPRODUCT(--('2009'!h2:h1246='Reporting'!A111),('2009'!D2:D124 6="Advertise for Bids"),'2009'!A2:A1246) "Jennifer Corle" wrote: 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'!D2:D1456="Advertise for Bids",'2009'!H2:H1456=A111),'2009'!A2:A1456,"None" ) * * RETURN: ?NONE? =IF(AND('2009'!D2:D1456="Advertise for Bids?,'2009'!H2:H1456=Reporting!A111),'2009'!A2:A1 456," None ") * * *RETURN: ?NONE? =IF('2009'!D2:D1456="Advertise for Bids ",IF('2009'!H2:H1456=Reporting!A111,'2009'!A2:A145 6,"None")) * RETURN: ?FALSE? =IF(AND(--('2009'!D2:D1456="Advertise for Bids"),--('2009'!H2:H1456=Reporting!A118)),'2009'!A2:A1456, "None") * RETURN: ?NONE? =IF('2009'!D2:D1456="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'!D2:D1246)="Advertise for Bids"),--('2009'!H2:H1246)=('2009'!A111)),('2009'!A2:A1246) =SUMPRODUCT(--('2009'!h2:h1246)=A111),--('2009'!D2:D1246="Advertise for Bids"),'2009'!A2:A1246) =SUMPRODUCT('2009'!h2:h1246='Reporting'!A111),('20 09'!D2:D1246="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/tutorials...3c-4097-aaf8-3... . 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| reporting value from other sheet | Excel Worksheet Functions | |||
| pivot reporting wrong info-riv | Excel Discussion (Misc queries) | |||
| Annual Wages Sheet to pick up info from Time Sheet | Excel Worksheet Functions | |||
| Finding Info from sheet 1 and removing only those rows from sheet | Excel Discussion (Misc queries) | |||
| How do I compare info in on sheet to info in another? | Excel Discussion (Misc queries) | |||
Linear Mode
