Hi there

I hope someone could shed some light for me, I have designed a template to
allow people to capture expenses spent by project and time spent by resources
along with their rates and other info ALL in one tab for 36 months!!!

I have not used any vba as I am not good at it and hard to understand by
others if something gone wrong with the codes while I am not here (as it was
in the old template - hence I have to re-design the whole thing).

Basically, I have one tab for all entries with 36 months across for actual,
budget and forecast (i.e. 108+ columns used), then allow people to enter
names, departments, rates and grades etc downwards. It has variance functions
and other basic caluculations for year to date, project to date and current
month look up for the reporting functions. It consists of hlookup, vlookup,
indirect formulas and sumproducts, hence I have locked all the cells with
formulas.

Due to the complications of the workbook and all the calculations for the
reports, I disallow people to add or delete any rows or columns as will mess
up my referencing for all calculations. However, the user group wants all the
reporting functions I have in the workbook but also allow them to add and
delete (in the middle of entries) as well as reducing the size of the file. I
run our of ideas and wondering if anyone could help me with my headaches....

Not sure how I can simplify my problems here :( sorry if i don't write
clearly enough...Please help!!

Thanks a lot

Vivi

Use name ranges on the worksheet like

DeptData = "$D:$D"

You can add this manually by going to worksheet menu Insert - Names - Define

the from VBA use the following

DeptCol = Range("DeptData").column


"vivi" wrote:

Hi there

I hope someone could shed some light for me, I have designed a template to
allow people to capture expenses spent by project and time spent by resources
along with their rates and other info ALL in one tab for 36 months!!!

I have not used any vba as I am not good at it and hard to understand by
others if something gone wrong with the codes while I am not here (as it was
in the old template - hence I have to re-design the whole thing).

Basically, I have one tab for all entries with 36 months across for actual,
budget and forecast (i.e. 108+ columns used), then allow people to enter
names, departments, rates and grades etc downwards. It has variance functions
and other basic caluculations for year to date, project to date and current
month look up for the reporting functions. It consists of hlookup, vlookup,
indirect formulas and sumproducts, hence I have locked all the cells with
formulas.

Due to the complications of the workbook and all the calculations for the
reports, I disallow people to add or delete any rows or columns as will mess
up my referencing for all calculations. However, the user group wants all the
reporting functions I have in the workbook but also allow them to add and
delete (in the middle of entries) as well as reducing the size of the file. I
run our of ideas and wondering if anyone could help me with my headaches....

Not sure how I can simplify my problems here :( sorry if i don't write
clearly enough...Please help!!

Thanks a lot

Vivi

Thanks Joel

But I don't understand what you are suggesting, sorry I am not very good
with vba...

Can you please clarify for me please sorry

"joel" wrote:

Use name ranges on the worksheet like

DeptData = "$D:$D"

You can add this manually by going to worksheet menu Insert - Names - Define

the from VBA use the following

DeptCol = Range("DeptData").column


"vivi" wrote:

Hi there

I hope someone could shed some light for me, I have designed a template to
allow people to capture expenses spent by project and time spent by resources
along with their rates and other info ALL in one tab for 36 months!!!

I have not used any vba as I am not good at it and hard to understand by
others if something gone wrong with the codes while I am not here (as it was
in the old template - hence I have to re-design the whole thing).

Basically, I have one tab for all entries with 36 months across for actual,
budget and forecast (i.e. 108+ columns used), then allow people to enter
names, departments, rates and grades etc downwards. It has variance functions
and other basic caluculations for year to date, project to date and current
month look up for the reporting functions. It consists of hlookup, vlookup,
indirect formulas and sumproducts, hence I have locked all the cells with
formulas.

Due to the complications of the workbook and all the calculations for the
reports, I disallow people to add or delete any rows or columns as will mess
up my referencing for all calculations. However, the user group wants all the
reporting functions I have in the workbook but also allow them to add and
delete (in the middle of entries) as well as reducing the size of the file. I
run our of ideas and wondering if anyone could help me with my headaches....

Not sure how I can simplify my problems here :( sorry if i don't write
clearly enough...Please help!!

Thanks a lot

Vivi