Cell Reference help required
 

Hello All,

I have a problem with Cell reference between spreadsheets. Help would
be highly appreciated.

Here is what I need.

I have 30 different spreadsheets in a workbook. Worksheet names (Aug01,
Aug02, Aug 03….Aug30). All the sheets are similar. I need to refer the
totals of the previous worksheet in the current one.

Example:

D5 of sheet Aug30 to D4 of sheet Aug29, ('Aug29'!D5)
D5 of sheet Aug29 to D4 of sheet Aug28, ('Aug28'!D5)

Run this VBA code

Sub RunThis()

ReDim ShtNames(0 To Worksheets.Count - 1)
i = 0
For Each sht In Worksheets
ShtNames(i) = sht.Name
i = i + 1
Next

For j = 1 To UBound(ShtNames)
Worksheets(ShtNames(j)).Range("D5") = "=" & ShtNames(j - 1) & "!D4"
Next

End Sub

This will do the following:
In Aug2 sheet cell D5, it will put =Aug1!D4
and so on.

Mangesh



"Raja" wrote in message
...

Hello All,

I have a problem with Cell reference between spreadsheets. Help would
be highly appreciated.

Here is what I need.

I have 30 different spreadsheets in a workbook. Worksheet names (Aug01,
Aug02, Aug 03..Aug30). All the sheets are similar. I need to refer the
totals of the previous worksheet in the current one.

Example:

D5 of sheet Aug30 to D4 of sheet Aug29, ('Aug29'!D5)
D5 of sheet Aug29 to D4 of sheet Aug28, ('Aug28'!D5)

Dear Mangesh,

Thanks a lot for the code. I am a dummie ..so please explain me on
where and how to run the code.

I am using MS office 2000

Raja

Mangesh Yadav Wrote:
Run this VBA code

Sub RunThis()

ReDim ShtNames(0 To Worksheets.Count - 1)
i = 0
For Each sht In Worksheets
ShtNames(i) = sht.Name
i = i + 1
Next

For j = 1 To UBound(ShtNames)
Worksheets(ShtNames(j)).Range("D5") = "=" & ShtNames(j - 1) & "!D4"
Next

End Sub

This will do the following:
In Aug2 sheet cell D5, it will put =Aug1!D4
and so on.

Mangesh


--
Raja


------------------------------------------------------------------------
Raja's Profile: http://www.excelforum.com/member.php...o&userid=25901
View this thread: http://www.excelforum.com/showthread...hreadid=393232

Nice simple solution :-)

=IF(RIGHT(MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255),2)<"
01",INDIRECT("'"&LEFT(MID(CELL("Filename",A1),FIND ("]",CELL("Filename",A1))+
1,255),LEN(MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255))-2)&
TEXT(VALUE(RIGHT(MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255
),2))-1,"00")&"'!D5"),"")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Raja" wrote in message
...

Hello All,

I have a problem with Cell reference between spreadsheets. Help would
be highly appreciated.

Here is what I need.

I have 30 different spreadsheets in a workbook. Worksheet names (Aug01,
Aug02, Aug 03..Aug30). All the sheets are similar. I need to refer the
totals of the previous worksheet in the current one.

Example:

D5 of sheet Aug30 to D4 of sheet Aug29, ('Aug29'!D5)
D5 of sheet Aug29 to D4 of sheet Aug28, ('Aug28'!D5)

Hi,

I think Bob's formula can be shortened to like this,

=IF(RIGHT(x,2)="01","",INDIRECT("Aug"&TEXT(--RIGHT(x,2)-1,"00")&"!D5"))

where x,

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

HTH


--
Krishnakumar


------------------------------------------------------------------------
Krishnakumar's Profile: http://www.excelforum.com/member.php...o&userid=20138
View this thread: http://www.excelforum.com/showthread...hreadid=393232

If you have not used Bob's formula as yet, then to use my code, simply press
Alt F11 in excel. This opens the VBE. Go to Insert, and insert a new module.
Place the code here. And click on the right-pointed triangle to run it.

Mangesh



"Raja" wrote in message
...

Dear Mangesh,

Thanks a lot for the code. I am a dummie ..so please explain me on
where and how to run the code.

I am using MS office 2000

Raja

Mangesh Yadav Wrote:
Run this VBA code

Sub RunThis()

ReDim ShtNames(0 To Worksheets.Count - 1)
i = 0
For Each sht In Worksheets
ShtNames(i) = sht.Name
i = i + 1
Next

For j = 1 To UBound(ShtNames)
Worksheets(ShtNames(j)).Range("D5") = "=" & ShtNames(j - 1) & "!D4"
Next

End Sub

This will do the following:
In Aug2 sheet cell D5, it will put =Aug1!D4
and so on.

Mangesh


--
Raja


------------------------------------------------------------------------
Raja's Profile:
http://www.excelforum.com/member.php...o&userid=25901
View this thread: http://www.excelforum.com/showthread...hreadid=393232

Yeah but assumes Aug. Mine will work with Aug, Sep, etc, which I am sure is
what the OP would want. Put that back in and it is no shorter.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Krishnakumar"
wrote in message
news:Krishnakumar.1taimi_1123243557.8972@excelforu m-nospam.com...

Hi,

I think Bob's formula can be shortened to like this,

=IF(RIGHT(x,2)="01","",INDIRECT("Aug"&TEXT(--RIGHT(x,2)-1,"00")&"!D5"))

where x,

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

HTH


--
Krishnakumar


------------------------------------------------------------------------
Krishnakumar's Profile:
http://www.excelforum.com/member.php...o&userid=20138
View this thread: http://www.excelforum.com/showthread...hreadid=393232