Placing values into summary sheet

[Max]

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,BR45,BY45,CF45,CM45,CT45,DA45,DH45,DO45,DV45,EC45,EJ45,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

 

[Guest]

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.php?nav=400&text=430

Kind regards,

Alex

 

[Max]

Thanks a lot for the info.
Well i was only expecting if someone could share the VBA code in this forum
max