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

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)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
wrote in message
ups.com...
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...

Could someone show me the correct syntax for this.

Cheers many thanks in advance.

Hi just found IDIRECT just before I read your message.


It works great thanks.

Cheers for you help
:)

Glad it worked out !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

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

Hi Simon

Try

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


Regards

Roger Govier



wrote:

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??