Seeking assistance from Excell Gurus!

[Sir Stinkalot]

Ok I am trying to speed up processes in the office with the aim of making the program do the work and allways produce the right result.

There are two things that I need Excel to do for me at the moment:

1. I would like to have the ability to reference a cell from a previous worksheet everytime I duplicate a new worksheet. The problem is that I need the reference cell to be from the last worksheet every time a new one is generated and not just the first sheet.

2. In these progress claims a retention figure is taken off each claim at a rate of 10% of the claim price until the retention figure reaches 5% of the contract price sum. Is it possible for Excel to run this calculation, even when new worksheets (additional progress claims) are duplicated and stop when the retention reaches 5% of the contract price?

I have attached a roughed version of the file if it helps to explain what I am trying to do.

There are more questions but I will see if we can find a boffin to resolve these ones first.

Cheers

Stinky

[ozwinner]

Wongo's ya man..:2tsup:

Al :cool:

[Honorary Bloke]

The short answer to number 2 is yes. I am out of practice but will look at it later today. You can use a conditional statement "If so and so is more than so and so, then so and so, if not, then stop" kind of thing. Sorry, I'm still trying to wake up. But it can be done. Number 1, not sure, will have to do a little research.:)

[Sir Stinkalot]

Thanks for your input Bob ..... I am sure it is one of those programs that can do so much if you know how to drive it. Unfortunately I only know the real basics as I don't need to use the program much.

[markharrison]

It can be done but it will require programming in VBA to create a function. Unfortunately the Excel programming model is not easy for a novice programmer to pick up. I can give you some more hints if you want but if want me to write, well, I already have a full time job :D

I can do it for you but I will charge.

[kkperth]

Sir Stinkalot

Copy this formula and paste it into cell F16. This is the if statement that Honorary Bloke is talking about and will put 10% on G12 up to but no larger than 5% of G7.

</O:p
<O:p</O:p
=IF((G12/100)*10<(G7/100)*5,(G12/100)*10,IF((G12/100)*10>(G7/100)*5,(G7/100*5)))


If you want to create new sheets with the stuff you want a simple macro could do some of it and possibly VBA could do the rest but I don't know how to use VBA. If you would like I could create a macro for you but it may not do everything you want, let me know and I can have a crack.

However paying someone who really knows what they are doing is normally the best way to get exactly what you want.

kkperth

[ian]

There are two things that I need Excel to do for me at the moment:

1. I would like to have the ability to reference a cell from a previous worksheet everytime I duplicate a new worksheet. The problem is that I need the reference cell to be from the last worksheet every time a new one is generated and not just the first sheet.

2. In these progress claims a retention figure is taken off each claim at a rate of 10% of the claim price until the retention figure reaches 5% of the contract price sum. Is it possible for Excel to run this calculation, even when new worksheets (additional progress claims) are duplicated and stop when the retention reaches 5% of the contract price?

There are more questions but I will see if we can find a boffin to resolve these ones first.

Cheers

StinkySir Stinky
Item 2 is pretty straight forward, see attached spreadsheet

Item 1 requires VBA coding that I know enough about to be dangerous but not enough to solve your problem


Ian

[Sir Stinkalot]

Thank you everybody for your help.
I was thinking that item 1 would be the easy one and two would be tricky but it seems it was the other way around.

I have inserted the formula and it works a treat, not only does it save a little time but more importantly it reduces the risk of error.

As for the accounting side of things the example was from a proforma which is used for one particular contract so I am sure it is right. I deleted a lot of the text as I didn't want to post the entire thing word for word so perhaps thats where some of the confusion lay.

As for point 1 I have two ways around it, either manually change the formular each time a sheet is added (just a matter of getting everybody else to follow) or 2 which is likely the way that I am going to go, would be to insert the excel table into word and then manually update the figure with each new claim. Extra work but given the figure often changes it will not make much difference.

Again thank for your assistance.

[ian]

Sir Stinky

I strongly suggest you check the GST portions of your proforma with your accountant.

I know that if in my day job I got an a progress claim like yours (where retention monies are calculated GST inclusive) it would be rejected.


as to using Word, personally I think Excel is easier. Everytime you create a new progress claim
Edit — Move or Copy Sheet — creater Copy BEFORE current sheet
all you then have to do is copy the VALUE of the payment (cell C19 in my example) into cell C16 (and it's relatively easy to set up a macro to do this)


ian

[Sir Stinkalot]

Thanks Ian,

The proforma is produced by the Royal Australian Institute of Architects to go along with their Simple Works Contract 2002, there is a similar proforma for the Major Works Contract also. As a newbie to all of this I will run with what they recommend.

I do like the idea of having all of the progress claims in one excel file by using the tabs but I am unsure of the best apporach. Is it possible in excel to blank out of view all of the cells that are not in use so when I insert a new sheet it only shows the A4 sized page that it to be printed?

[Wongo]

How much time do I have?

[ian]

Thanks Ian,

I do like the idea of having all of the progress claims in one excel file by using the tabs but I am unsure of the best apporach. Is it possible in excel to blank out of view all of the cells that are not in use so when I insert a new sheet it only shows the A4 sized page that it to be printed?you mean like in the attached?

the unused rows and colums are HIDDEN and to be sure, the Print Area only covers the displayed cells
Then use Print Setup to ensure that it all fits on one page

ian

[Wongo]

Ok this is what I think
1. you run the macro, lets call it “duplicate worksheet”
2. a new sheet will be created and it will be called “PC-02” (or PC-03, PC-04 ….)
3. cell F14 of the new sheet = cell G12 of the previous sheet. (Hence
[PC-02]F14=[PC-01]F12,
[PC-03]F14=[PC-02]F12, ….
4. if cell F16 of the last worksheet >= 5% X G7 then terminate the macro.

Do I understand it correctly?

I will have a go tomorrow. Good night.

[Wongo]

Here is the macro. Open the file and hit alt&F8 to run the macro. The macro is called “add_new_sheet” and it will do the following steps

Copy you last sheet (say PC-02)
Rename the new sheet as PC-03 (or 04,05…)
Place the new sheet at the end, after PC-02
Set the formula of F14 to G12 of the previous sheet (PC-02)

Sorry I still don’t understand the 5&#37; bit.

Hit alt&F11 to see the code

Let me know how you go.

[Sir Stinkalot]

Thanks for all of your help guys ..... I will look at both examples of Wongo and Ian to see if I can work out how it all works. I have inserted kkperth's formula and it seems to be working well, just have to wait until I hit the magical 5&#37; of the contract price mark.


I issued my first progress claim today using the new formulas and it worked well. As with all of these things only time will tell how well it works in reality.


At least I know where to go for my Excel help.


Thanks again one and all.<!-- / message -->

[ozwinner]

Ya gotta just love the free exchange of ideas on this here forum.. :2tsup:

Al :cool:

[Wongo]

Who said it was free.:D