Excel help - possible Macro required?
03 May 2007, 09:43 PM
#1
Scooby Regular
Thread Starter
Join Date: May 2002
Posts: 732
Likes: 0
Received 0 Likes
on
0 Posts
Excel help - possible Macro required?
Hi,
I currently have quite a large & complicated spreadsheet (a 3 year financial forecast for a corporate finance company).
At the moment I have to manually input the required monthly funding figure each month to make the figures balance. This has to be done manually as when the month 1 figure is entered it has a knock on effect on months 2 - 36 due to interest costs, funding requirements etc.
I have a formula that calculates the requirement each month (in the cell below where the figure is required) but I cannot use a formula to automatically calculate the figure as it is the final piece in the accounts jigsaw and creates a circular references!
What I need excel to do is automatically input the correct figure for month 1 from the calculation in the cell below then once this is done and the figures for months 2-36 have automatically flowed through and changed take the updated month 2 figure, which will then change months 3-36 etc. etc.
Basically I need Excel to copy 36 results from 1 cell to another individually and inbetween each cell being copied the remaining cells to be recalculated.
Any suggestions chaps as it's driving me mad having to manually enter 36 figures each time I update the forecast
Hope you can understand my explanation LOL
Gareth
I currently have quite a large & complicated spreadsheet (a 3 year financial forecast for a corporate finance company).
At the moment I have to manually input the required monthly funding figure each month to make the figures balance. This has to be done manually as when the month 1 figure is entered it has a knock on effect on months 2 - 36 due to interest costs, funding requirements etc.
I have a formula that calculates the requirement each month (in the cell below where the figure is required) but I cannot use a formula to automatically calculate the figure as it is the final piece in the accounts jigsaw and creates a circular references!
What I need excel to do is automatically input the correct figure for month 1 from the calculation in the cell below then once this is done and the figures for months 2-36 have automatically flowed through and changed take the updated month 2 figure, which will then change months 3-36 etc. etc.
Basically I need Excel to copy 36 results from 1 cell to another individually and inbetween each cell being copied the remaining cells to be recalculated.
Any suggestions chaps as it's driving me mad having to manually enter 36 figures each time I update the forecast
Hope you can understand my explanation LOL
Gareth
03 May 2007, 09:58 PM
#2
Scooby Regular
Join Date: Jun 2002
Location: Berkshire
Posts: 1,280
Likes: 0
Received 0 Likes
on
0 Posts
I've read this a couple of times but I can't quite work out what it is you need... but I am very tired 
Do you have an example you can upload somewhere (fudge the figures if its sensitive)
D
Do you have an example you can upload somewhere (fudge the figures if its sensitive)
D
03 May 2007, 10:53 PM
#3
Scooby Regular
Thread Starter
Join Date: May 2002
Posts: 732
Likes: 0
Received 0 Likes
on
0 Posts
I've uploaded a VERY simple example of what I need to do here
http://myweb.tiscali.co.uk/boogat/example.xls
Hopefully you'll be able to see what I mean
but bare in mind that is a very simple sheet with a lot less tabs/tables as are in the full sheet!
http://myweb.tiscali.co.uk/boogat/example.xls
Hopefully you'll be able to see what I mean
but bare in mind that is a very simple sheet with a lot less tabs/tables as are in the full sheet!
04 May 2007, 01:52 PM
#5
Scooby Regular
Join Date: Jan 2006
Posts: 897
Likes: 0
Received 0 Likes
on
0 Posts
Try This?
Code:
Sub UpdateFigures()
Dim icol As Integer
'1 to 12 (+ 5 for Column E)
For icol = 5 To 16
'First determine if funding req (Check Closing Bank)
'If not - Do nothing
If Cells(40, icol) >= 0 Then
'Otherwise
Else
'Enter the Value in the cell (Absolute Value - Without the minus sign)
Cells(6, icol).Value = Abs(Cells(11, icol).Value)
End If
'Move to next column
Next icol
End Sub
Thread
Thread Starter
Forum
Replies
Last Post
Mattybr5@MB Developments
Full Cars Breaking For Spares
28
28 December 2015 11:07 PM
Mattybr5@MB Developments
Full Cars Breaking For Spares
12
18 November 2015 07:03 AM