Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Placing values into summary sheet
Dears,
I have the following questions I have two tabs in a excel workbook "SummarySheet" and "ResourceProjections" On the summarysheet i do show the summary with the project name and the total number of hours worked on that project for that week. My problem is everytime i add a project in ResourceProjections i should manually come to summarysheet and give reference to that project and the values. I want to achieve 2 things here 1 What i want is the code which will try to find the entry for a project name in Colmn B in Resourceprojections(as column B will hav project name) and place the value in Summary sheet. For ex when it finds entry for "Project2" in B27 it will automatically place it in B13 of the Summarysheet. Similarly when it finds the entry for "Project3" in any row of B it should again copy in B15 of Summary sheet and so on B17,B19 etc. 2 I want also teh values to be automatically copied to respective cells besides that project in summarysheet. Ex: when an entry is found in ColumnE (it will always be "Total Hrs/Week") then for that row it shuld go to subsequent columns which is constant always once defined as it is based on calendar should be copied Now when it finds for Project2 an entry in ColumnE at E45 then should get values of "F45,G45,N45,U45,AB45,AI45,AP45,AW45,BD45,BK45,BR4 5,BY45,CF45,CM45,CT45,DA45,DH45,DO45,DV45,EC45,EJ4 5,EQ45,EX45" and place then from C13 onwards in Summary sheet. Similarly for Project3 and so on. I would want the code to be dynamic so that tomorrow if i do it for 2010 it should work. Is this a good idea or not please suggest or if any better way to do this |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Placing values into summary sheet
Hi there
The problem in Excel is that references to sheets or Workbooks are fix and not dynamic. To dynamically add data from a dynamic number of sheets you need to write VBA code which collects the data and places it in a new sheet or which creates the references. I have written a program which is shareware and which could solve the problem. You can put define names in every workbook and the program collects the data over the names in one many workbooks and creates a list in a new workbook. Then you could record a macro and directly copy/paste the code in the program so it will be excecuted every time you start the report. The program is in German but I can help you with that. Unfortunately the program is not free of charge but has a 30 day evaluation period. Download-Link: http://www.excelspezialist.ch/index....v=400&text=430 Kind regards, Alex ------------------------------------ Excel-Spezialist www.excelspezialist.ch ------------------------------------ "Max" schrieb im Newsbeitrag ... Dears, I have the following questions I have two tabs in a excel workbook "SummarySheet" and "ResourceProjections" On the summarysheet i do show the summary with the project name and the total number of hours worked on that project for that week. My problem is everytime i add a project in ResourceProjections i should manually come to summarysheet and give reference to that project and the values. I want to achieve 2 things here 1 What i want is the code which will try to find the entry for a project name in Colmn B in Resourceprojections(as column B will hav project name) and place the value in Summary sheet. For ex when it finds entry for "Project2" in B27 it will automatically place it in B13 of the Summarysheet. Similarly when it finds the entry for "Project3" in any row of B it should again copy in B15 of Summary sheet and so on B17,B19 etc. 2 I want also teh values to be automatically copied to respective cells besides that project in summarysheet. Ex: when an entry is found in ColumnE (it will always be "Total Hrs/Week") then for that row it shuld go to subsequent columns which is constant always once defined as it is based on calendar should be copied Now when it finds for Project2 an entry in ColumnE at E45 then should get values of "F45,G45,N45,U45,AB45,AI45,AP45,AW45,BD45,BK45,BR4 5,BY45,CF45,CM45,CT45,DA45,DH45,DO45,DV45,EC45,EJ4 5,EQ45,EX45" and place then from C13 onwards in Summary sheet. Similarly for Project3 and so on. I would want the code to be dynamic so that tomorrow if i do it for 2010 it should work. Is this a good idea or not please suggest or if any better way to do this |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Placing values into summary sheet
Thanks a lot for the info.
Well i was only expecting if someone could share the VBA code in this forum max " wrote: Hi there The problem in Excel is that references to sheets or Workbooks are fix and not dynamic. To dynamically add data from a dynamic number of sheets you need to write VBA code which collects the data and places it in a new sheet or which creates the references. I have written a program which is shareware and which could solve the problem. You can put define names in every workbook and the program collects the data over the names in one many workbooks and creates a list in a new workbook. Then you could record a macro and directly copy/paste the code in the program so it will be excecuted every time you start the report. The program is in German but I can help you with that. Unfortunately the program is not free of charge but has a 30 day evaluation period. Download-Link: http://www.excelspezialist.ch/index....v=400&text=430 Kind regards, Alex ------------------------------------ Excel-Spezialist www.excelspezialist.ch ------------------------------------ "Max" schrieb im Newsbeitrag ... Dears, I have the following questions I have two tabs in a excel workbook "SummarySheet" and "ResourceProjections" On the summarysheet i do show the summary with the project name and the total number of hours worked on that project for that week. My problem is everytime i add a project in ResourceProjections i should manually come to summarysheet and give reference to that project and the values. I want to achieve 2 things here 1 What i want is the code which will try to find the entry for a project name in Colmn B in Resourceprojections(as column B will hav project name) and place the value in Summary sheet. For ex when it finds entry for "Project2" in B27 it will automatically place it in B13 of the Summarysheet. Similarly when it finds the entry for "Project3" in any row of B it should again copy in B15 of Summary sheet and so on B17,B19 etc. 2 I want also teh values to be automatically copied to respective cells besides that project in summarysheet. Ex: when an entry is found in ColumnE (it will always be "Total Hrs/Week") then for that row it shuld go to subsequent columns which is constant always once defined as it is based on calendar should be copied Now when it finds for Project2 an entry in ColumnE at E45 then should get values of "F45,G45,N45,U45,AB45,AI45,AP45,AW45,BD45,BK45,BR4 5,BY45,CF45,CM45,CT45,DA45,DH45,DO45,DV45,EC45,EJ4 5,EQ45,EX45" and place then from C13 onwards in Summary sheet. Similarly for Project3 and so on. I would want the code to be dynamic so that tomorrow if i do it for 2010 it should work. Is this a good idea or not please suggest or if any better way to do this |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Make all values of a 3D named range appear on summary sheet | Excel Discussion (Misc queries) | |||
Copy two summary ranges to master summary sheet | Excel Programming | |||
Placing values in a range on a non-active sheet | Excel Programming | |||
How do I reference values from 200 worksheets onto a summary sheet | Excel Discussion (Misc queries) | |||
Placing an array on the active sheet | Excel Programming |