Prev: if is error then "No Data", if not then average range
Next: simple countif formula
From: Mike1558 on 4 Feb 2010 15:50
I tried this and what I get in the cell that I enter PrevSheet(Q7)what shows
up in the cell is #REF!. I have been unable to make this produce a value.
What did I do wrong? I copied the UDF to vis basic like you discribed in the
previous email but there must be something that I did wrong.

Thanks

Mike
--
Thanks
Mike


"Gord Dibben" wrote:

> If you're willing to use a User Defined Function this becomes quite
> easy.......
>
> 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 use......................
>
> 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
>
>
> Gord Dibben MS Excel MVP
>
> On Wed, 3 Feb 2010 18:33:01 -0800, Mike1558
> <Mike1558(a)discussions.microsoft.com> wrote:
>
> >I am creating a payment application form in excel 2007 using windows 7. Each
> >worksheet represents one months invoice. Say I have a formula in "sheet 1/
> >cell Q7" that sums the total billed to date for a particular budget line
> >item. This value will be transfered to a the next months payment application
> >"sheet 2/ cell K7" this becomes the total amount of previous applications,
> >then "sheet 2/ Q7 is =sum(K7,M7,O7) this value is transfered to "sheet 3/ K7,
> >and so on and so on, until the completion of the job. How do I acomplish
> >this.
>
> .
>
From: Gord Dibben on 5 Feb 2010 13:33
Only way I can get #REF! is if I enter the formula on first sheet.

There is no previous sheet in that case.


Gord

On Thu, 4 Feb 2010 12:50:10 -0800, Mike1558
<Mike1558(a)discussions.microsoft.com> wrote:

>I tried this and what I get in the cell that I enter PrevSheet(Q7)what shows
>up in the cell is #REF!. I have been unable to make this produce a value.
>What did I do wrong? I copied the UDF to vis basic like you discribed in the
>previous email but there must be something that I did wrong.
>
>Thanks
>
>Mike

 | 
Pages: 1
Prev: if is error then "No Data", if not then average range
Next: simple countif formula