transfering cell values progressively through worksheets

[Mike1558]

I am creating a budget/pay-application template that uses a new worksheet for
each pay-application and the number of worksheets corrisponds to the duration
of the job. Within these worksheets are cells that contain formulas that
produce values and these values need to be entered into a formula that creats
an new value for next pay-application period. Does anyone know how to do
this, any help will be greatly appreciated.

 

[Kassie]

Only with a LOT more information?
--
HTH

Kassie

Replace xxx with hotmail

 

[Mike1558]

What I am doing is, say for the pay-app month May, I have a cell that
contains the total amount of a line item for this draw period. The next cell
in the row contains a formula that gives the results for the amount for this
line item spent to date. To one of the values in "spent to date" cell comes
from the same cell in a previous worksheet "April". Once the "May" worksheet
is done and I move on to create the "June" worksheet the "May" amount now
becomes a value in the "June" worksheet. This happens progressively through
the completion of the job. Do you have a suggestion? Would it be helpful to
see what I am working on?
I really appreciate any help you can give me.
Also I am having an annoying problem with all my excel workbooks, I have
office 2007, when I try to select a single cell it selects multiple cells
then what I do is move the curser to the lower right hand corner of the cell
that I want to select and I have to keep clicking on it until the single cell
that I want is selected. This usually takes several attemps because a cell in
the vacinity of the cell that I want gets selected not the cell that I want.
Any suggestions?

 

[Gord Dibben]

Problem number 1..........updating formulas to refer to previous month.

Copy/paste this UDF to a general module in your workbook.

Function PrevSheet(rg As Range)
n = Application.Caller.Parent.Index
If n = 1 Then
PrevSheet = CVErr(xlErrRef)
ElseIf TypeName(Sheets(n - 1)) = "Chart" Then
PrevSheet = CVErr(xlErrNA)
Else
PrevSheet = Sheets(n - 1).Range(rg.Address).Value
End If
End Function

Example of usage...................

Say you have 12 sheets, sheet1 through sheet12...........sheet names don't
matter.

In sheet1 you have a formula in A10 =SUM(A1:A9)

Select second sheet and SHIFT + Click last sheet

In active sheet A10 enter =SUM(PrevSheet(A10),A1:A9)

Ungroup the sheets.

Each A10 will have the sum of the previous sheet's A10 plus the sum of the
current sheet's A1:A9

Problem number 2..............selection of multiple cells

Try setting the Zoom level a little higher or lower to stop the multiple
cell selecting.


Gord Dibben MS Excel MVP

 

[Mike1558]

Gord
Thanks for your help, I understand what you are doing in the second half of
problem 1 but am unsure of what you want me to do in the 1st half of problem
1. What is a UDF and a general module of a work book and then what does it do
and how do I use it? I am guessing that this is code that is to be entered
into the workbook but I am unsure of how to do it.

 

[Gord Dibben]

If you're not familiar with VBA and macros, see David McRitchie's site for
more on "getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or Ron de De Bruin's site on where to store macros.

http://www.rondebruin.nl/code.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

From there, follow original instructions after these lines.

 

[Mike1558]

Gord

I have been trying (in my spare time) to get this to work. I have copied the
UDF as you discribed but what is now happening is that I have 6 cells that
reference the value in A10 and A10 as well as the 6 cells all display #REF!.
Worksheet 1 has a value in it and the following sheets that I have used the
PrevSheet function all display the #REF!. Have I missed something? I realy
apreciate your help as this has the potenial to be a big time saver in future
templates.

 

[Gord Dibben]

The error could mean there is no previous sheet.

The error could mean that one of the previous sheets has a #REF! error and
is propagating across.

Try opening a new workbook, add the UDF to a general module.

Enter qwerty in A1 of Sheet1

Enter =Prevsheet(A1) in A1 of Sheet2

OK so far?

Work through from there.

OR send me the workbook with some explanation of which cells you want to
refer to previous sheet.

gorddibbATshawDOTca change the obvious.


Gord

 

[Mike1558]

Gord

I emailed you my file let me know if it comes through