Reference Previous Sheet

  • Thread starter Thread starter simonmarkjones
  • Start date Start date
S

simonmarkjones

Hi I've found on this newsgroup a function to get the previous sheet
name

Function PrevSheet()
On Error GoTo EndFunction
Application.Volatile True
PrevSheet = Application.Caller.Worksheet.Previous.Name
Exit Function
EndFunction:
With Application.Caller.Parent.Parent.Worksheets
PrevSheet = .Item(.Count).Name
End With
End Function

when i put =PrevSheet() i get the previous sheets name

What i want to be able to do is use this in place of directly
referencing the sheet name in my code as follows...
=IF(G4-J4<0,G4-J4,G4-J4)+'Week 1'!H4
So i want to be able to replace Week 1 with PrevSheet but i've tried
and falied on this. eg...

=IF(G4-J4<0,G4-J4,G4-J4)+PrevSheet()!H4
Could someone show me the correct syntax for this.

Cheers many thanks in advance.
 
Instead of ..
=IF(G4-J4<0,G4-J4,G4-J4)+PrevSheet()!H4
Click to expand...

Maybe try something like:
=IF(G4-J4<0,G4-J4,G4-J4+INDIRECT("'"&PrevSheet()&"'!H4"))

(lightly tested, seems to work ok if we use INDIRECT)

--
 
Hi just found IDIRECT just before I read your message.


It works great thanks.

Cheers for you help
:)
 
Although it works fine i now wish to be able to drag down the cell so i
get
=IF(G4-J4<0,G4-J4,G4-J4)+INDIRECT("'"&PrevSheet()&"'!H4")
=IF(G5-J5<0,G5-J5,G5-J5)+INDIRECT("'"&PrevSheet()&"'!H5")
=IF(G6-J6<0,G6-J6,G6-J6)+INDIRECT("'"&PrevSheet()&"'!H6") etc...

I IF part works. However the indirect bit stays at H4 eg..
=IF(G5-J5<0,G5-J5,G5-J5)+INDIRECT("'"&PrevSheet()&"'!H4")

I really dont want to have to manually go to each and change that
because i have hundreds of rows. Any suggestions?

Cheers in advance
 
I'm guessing because H4 is between is in between the quotes ""
 
With regaurds to changing the cell reference..
I've sort of got a hashed together solution

In A1 i write =PrevSheet()
In B1..B100 etc i write H4 , H5 ... H100

In the C colm i put
="'"&$A$1&"'"&"!"&B1
="'"&$A$1&"'"&"!"&B100 etc...

In the D colm i put =INDIRECT(C1), =INDIRECT(C2) ... =INDIRECT(C100)

This gives me the values i want and works but its a bit of messing
about!

Is there any easier ways to do this??
 
Hi Simon

Try

=IF(G4-J4<0,G4-J4,G4-J4)+INDIRECT("'"&PrevSheet()&"'!H"&ROW())


Regards

Roger Govier
 
Glad you got the incrementer part working
from Roger's suggestion (Thanks, Roger !)

Perhaps just a slight refinement to that would be to use
in the starting cell (which may not necessarily be in row 4):

=IF(G4-J4<0,G4-J4,G4-J4+
INDIRECT("'"&PrevSheet()&"'!H"&ROW($A$1:A1)+3))

Then the formula can be copied down to increment nicely as required

The slightly longish incrementer: .. ROW($A$1:A1)+3
will evaluate to: 1 + 3 = 4 in the starting cell
(the "3" is just an arithmetic adjustment to make the start number 4)
and, when copied down, would evaluate to: 5, 6, 7 ...

The expression is also robust against any subsequent row insertions
which may happen above the starting cell compared to using ROW()
(just one way I picked up from here and adopted since)
 
Hi Max

I may be wrong, but I don't think you need the $ around the first part
of the reference., I think it should be
ROW(A1:A1)+3 if it is to increment properly as you copy down.

Also, I am interested in your assertion that it makes the formula more
robust against row insertions.
In my experience, I get errors when using your method if rows are then
inserted, whereas, I don't get an error when I just use ROW().

For example, with 10,20,30,40,50,60,70,80,90,100 in cells A17:A26 and
the formula in
B16 =SUM(INDIRECT("$A$17:A"&ROW()))
C16 =SUM(INDIRECT("$A$17:A"&ROW(A1:A1)+16))
D16 =SUM(INDIRECT("$A$17:A"&ROW($A$1:A1)+16))
Copying these down through rows 18:26 gives the following results
10,30,60,100,150,210,280,360,450,550
10,30,60,100,150,210,280,360,450,550
10,10,10,10,10,10,10,10,10,10

Now, insert a new row at row 14, (data now in A18:A27 and formulae moved
accordingly) and I get the following results
10,30,60,100,150,210,280,360,450,550
0,10,30,60,100,150,210,280,360,450
0,0,0,0,0,0,0,0,0,0

Now insert a new row at 21 and I get
10,30,60, ,100,150,210,280,360,450,550
0,10,30, ,60,60,100,150,210,280,360
0,0,0, ,0,0,0,0,0,0,0

What am I doing differently to you?

Regards

Roger Govier
 
Thanks for the response, Roger. My apologies for the confusion. The
mistake was all mine. I just realized that I had made a *major* goof in
that last response, it should have read as:

ROWS($A$1:A1)+3
not
ROW($A$1:A1)+3
(there was a missing "S" everywhere, sheesh ..)

So the suggested formula should have read as:

=IF(G4-J4<0,G4-J4,G4-J4+
INDIRECT("'"&PrevSheet()&"'!H"&ROWS($A$1:A1)+3))

and the line
The slightly longish incrementer: .. ROW($A$1:A1)+3
Click to expand...

should have read as:
 
Hi Max

Thanks for your response. To be honest, I had never noticed that there
was a ROW() and a ROWS() function, and you are absolutely right, with
ROWS, then the $A$1:A1 notation does do the summation.

I still question the validity of this providing the correct solution
however, when rows are inserted.

For example, with 10,20,30,40,50,60,70,80,90,100 in cells A17:A26 and
the formula in
B16 =SUM(INDIRECT("$A$17:A"&ROW()))
C16 =SUM(INDIRECT("$A$17:A"&ROWS(A1:A1)+16))

Copying these down through rows 18:26 gives the following results
10,30,60,100,150,210,280,360,450,550
10,30,60,100,150,210,280,360,450,550

Now, insert a new row at row 14, (data now in A18:A27 and formulae moved
accordingly) and I get the following results
10,30,60,100,150,210,280,360,450,550
0,10,30,60,100,150,210,280,360,450

Now insert a new row at 21 and I get
10,30,60, ,100,150,210,280,360,450,550
0,10,30, ,60,60,100,150,210,280,360

So I still question that this form of offset provides a more robust
solution, or am I totally missing something?

Regards

Roger Govier
 
Hi Roger,

Perhaps a finer qualification is that it would protect
against row insertions made at the top row (row1)

For example, suppose the numbers 1 - 7 are listed in Sheet2's A4:A10

Then in say Sheet1, we have:
In A4: =INDIRECT("Sheet2!A"&ROW())
In B4: =INDIRECT("Sheet2!A"&ROWS($A$1:A1)+3)

A4:B4 copied to B10 yields identical correct results in both cols: 1 - 7

We insert a new top row (select A1, click Insert > Rows)
so that A4:B10 shifts down to A5:B11

A5:A10 would return: 2 - 6, with a zero in A11
(assuming Sheet2's A11 is empty)

while B5:B10 continues to return the "pre-top row insertion" results: 1 - 7
 
Hi Max

I can see that, and at first sight that sounds great.
But, and I'm sorry to be pedantic, try inserting a new row at Row2.
Then you get 2,3,4,5,6,7,0
and 1,3,4,5,6,7,0

Insert another row at row 2, and you get
3,4,5,6,7,0,0
1,4,5,6,7,0,0

Therefore, the use of the Absolute $A$1 only protects against the
insertion at Row 1, but does nothing for protecting any other
insertion(s) compared with using just Row() and as such I really cannot
see that it is worth elongated the formula just for this single case.

Regards

Roger Govier
 
Roger Govier said:
.. Therefore, the use of the Absolute $A$1 only protects against the
insertion at Row 1, but does nothing for protecting any other
insertion(s) compared with using just Row() and as such I really cannot
see that it is worth elongated the formula just for this single case.
Click to expand...

... Agreed <g> !
 
Roger Govier wrote...
I can see that, and at first sight that sounds great.
But, and I'm sorry to be pedantic, try inserting a new row at Row2. ....
Therefore, the use of the Absolute $A$1 only protects against the
insertion at Row 1, but does nothing for protecting any other
insertion(s) compared with using just Row() and as such I really cannot
see that it is worth elongated the formula just for this single case.
Click to expand...
....

Start off with your original setup, {10;20;30;40;50;60;70;80;90;100} in
A17:A26. Then write the textrefs starting in B17:E17 (I'm adding one).

B17:
="A17:A"&ROW()

C17:
="A17:A"&ROW(A1)+16

D17:
="A17:A"&ROWS(A$1:A1)+16

E17:
="A17:A"&ROWS(A$1:A17)

Insert a row at row 14. All the text refs still begin in row 17. That's
an error in all of them if there were any number in A16 initially.

The *safe* way to do this is to avoid using INDIRECT in the first
place. This is MUCH BETTER done using OFFSET.

=SUM(OFFSET(SheetX!$A$17,0,0,ROWS(SheetX!A$17:A17),1))

Now insert or delete rows anywhere.
 
Hi Harlan

Thanks for that, it is much better and it is totally robust.
=SUM(OFFSET(SheetX!$A$17,0,0,ROWS(SheetX!A$17:A17),1))
Click to expand...

One minor amendment, to pick up the 10 row range it needs modifying to

=SUM(OFFSET(SheetX!$A$17,0,0,ROWS(SheetX!A$17:A26),1))


Regards

Roger Govier
 
Roger Govier wrote...
....
One minor amendment, to pick up the 10 row range it needs modifying to

=SUM(OFFSET(SheetX!$A$17,0,0,ROWS(SheetX!A$17:A26),1))
Click to expand...
....

My formula was meant to be the topmost sum, A17:A17. Drag it down 9
more rows (so 10 in total), and the bottommost will be the formula you
show.
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Relative reference to a cell on a previous sheet 1
Need help with formulae comparing date-based numbers 2
Function Help 3
Cells w/ user defined function do not auto update 2
Returning a YES or NO if there is a value on a range of cells 0
=IF((I4="R");H4;IF((H4>0);H4*E4;IF((I4=0);J4*E4;IF((J4>H4);J4*E4) 2
problem with formulas (2) 7
incremented numbering 4

Back
Top