Tough one: office 2008 MAC

[LawrenceIES]

Dear all,

I have an issue that I cannot use Macros for, because we have office 2008 for mac and won't be switching to 2011 anytime soon.

In our timesheets, we have a row for each differnt project/category and a sum for the row at the end.

Problem is this: I want everyone to be able to insert a row if they feel they need to create a new category, but I fear not everyone will be able to fix the sum at the end themselves, and I do not feel like going through all these timesheets to check whether it still totals up.

I had written a nice macro to do this in office 2011 for mac, a button that inserted a row below the selcted cell adn that copied the sum formula. But, as stated above, we will keep on using office 2008 so a macro is out of the question.

Can anybody please help me or at least point me in the right direction? I've heard applescript might be an option but i cannot seem to find enough documentation or examples on the matter.
I have posted this question before but have gotten zero replies so any help or at least a reply is greatly appreciated.

Best,

Lawrence

 

[Domski]

How about changing the layout so that your sums are at the top totalling all the rows below. That way people can fill in as many rows as they like without having to extend any formula.

Dom

 

[RoryA]

Have you thought about using a List and making your last column a Calculated Column? Then when you insert rows, the formula will automatically be populated.

 

[mikerickson]

AppleScript is one option, but it seems that MicroSoft's on-line Mac support is in flux.

My thinking is that dynamic named ranges might be of use, but would like more details about the lay-out and exactly what is being summed.

If you have 2011, could you write a macro to do the "going through all these timesheets to check whether it still totals up" on your machine?

 

[LawrenceIES]

wow! thank you guys for your swift replies!
please find an example file on http://rapidshare.com/files/436674965/TStest.xlsx

Will try the List and/or Dynamic range options (iow find out how those work) and will definitely keep you posted.

Appreciate it!

 

[RoryA]

FYI, you can use macros in 2008 but they have to be XLM and not VBA.

 

[LawrenceIES]

How about changing the layout so that your sums are at the top totalling all the rows below. That way people can fill in as many rows as they like without having to extend any formula.

Dom

Hey Dom,

In my timesheet, I aleady have these "vertical" sums you speak of, but these just count the total hours worked per day.
However, it's more important to know the total time spent on a specific project, (=horizontal sum) as this will be multiplied with cost per hour and foreseen budget ( I have those formulas in a more recent version of the timesheet but i didn't want to overcomplicate the one I posted online as to avoid confusion.)

 

[LawrenceIES]

Have you thought about using a List and making your last column a Calculated Column? Then when you insert rows, the formula will automatically be populated.

Thanks rory,

how would I go about this then?

 

[RoryA]

Select the data, then choose Insert-List... and follow the instructions in the dialog.

 

[LawrenceIES]

Sweet! Works like a charm; thanks a lot Rory.
I now notice that I have another little problem though.

So my last column is a Calculated column, automatically populated with a sum function on each row. However, if I protect these cells, I can no longer insert rows... I suppose this is inevitable when protecting all cells in a column? or is there a workaround?

Thanks