+ Reply to Thread
Results 1 to 71 of 71

Cell Reference help required

  1. 08-05-2005, 04:58 AM #1
    Raja
    Raja is offline
    Registered User
    Join Date
    08-04-2005
    Posts
    7

    Exclamation 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)
    .
    .
    .
    .
    .
    D5 of sheet Aug02 to D4 of sheet Aug01, ('Aug01'!D5)


    All the sheets are chain linked like this till the first sheet (Aug01). I could do this manually, how ever any revisions to the format, I have to redo all the links again. I am looking for a formula instead of “('Aug29'!D5)” to be able to link the previous sheet.

    Hope I explained it clear. I am not a tech preson and I am not sure I am clear. Please ask me if I am not clear.

    Please let me know if there is some thing that helps me with this.
    Thanks
    Raja
    Reply With QuoteRegister To Reply
  2. 08-05-2005, 06:05 AM #2
    Mangesh Yadav
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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)


    Reply With QuoteRegister To Reply
  3. 08-05-2005, 06:43 AM #3
    Raja
    Raja is offline
    Registered User
    Join Date
    08-04-2005
    Posts
    7
    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

    [QUOTE=Mangesh Yadav]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
    Reply With QuoteRegister To Reply
  4. 08-05-2005, 07:05 AM #4
    Bob Phillips
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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)


    Reply With QuoteRegister To Reply
  5. 08-05-2005, 07:52 AM #5
    Krishnakumar
    Krishnakumar is offline
    Forum Contributor
    Join Date
    02-19-2005
    Location
    Gurgaon,India
    MS-Off Ver
    2007,2010,2013
    Posts
    180
    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
    Kris
    Reply With QuoteRegister To Reply
  6. 08-05-2005, 09:05 AM #6
    Mangesh Yadav
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  7. 08-05-2005, 11:05 AM #7
    Bob Phillips
    Guest

    Re: Cell Reference help required

    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" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  8. 09-05-2005, 10:05 PM #8
    Mangesh Yadav
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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)


    Reply With QuoteRegister To Reply
  9. 09-05-2005, 10:05 PM #9
    Bob Phillips
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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)


    Reply With QuoteRegister To Reply
  10. 09-05-2005, 10:05 PM #10
    Mangesh Yadav
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  11. 09-05-2005, 10:05 PM #11
    Bob Phillips
    Guest

    Re: Cell Reference help required

    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" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  12. 09-05-2005, 11:05 PM #12
    Mangesh Yadav
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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)


    Reply With QuoteRegister To Reply
  13. 09-05-2005, 11:05 PM #13
    Bob Phillips
    Guest

    Re: Cell Reference help required

    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" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  14. 09-05-2005, 11:05 PM #14
    Mangesh Yadav
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  15. 09-05-2005, 11:05 PM #15
    Bob Phillips
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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)


    Reply With QuoteRegister To Reply
  16. 09-06-2005, 12:05 AM #16
    Mangesh Yadav
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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)


    Reply With QuoteRegister To Reply
  17. 09-06-2005, 12:05 AM #17
    Bob Phillips
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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)


    Reply With QuoteRegister To Reply
  18. 09-06-2005, 12:05 AM #18
    Mangesh Yadav
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  19. 09-06-2005, 12:05 AM #19
    Bob Phillips
    Guest

    Re: Cell Reference help required

    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" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  20. 09-06-2005, 01:05 AM #20
    Bob Phillips
    Guest

    Re: Cell Reference help required

    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" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  21. 09-06-2005, 01:05 AM #21
    Mangesh Yadav
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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)


    Reply With QuoteRegister To Reply
  22. 09-06-2005, 01:05 AM #22
    Bob Phillips
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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)


    Reply With QuoteRegister To Reply
  23. 09-06-2005, 01:05 AM #23
    Mangesh Yadav
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  24. 09-06-2005, 02:05 AM #24
    Mangesh Yadav
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  25. 09-06-2005, 02:05 AM #25
    Bob Phillips
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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)


    Reply With QuoteRegister To Reply
  26. 09-06-2005, 02:05 AM #26
    Mangesh Yadav
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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)


    Reply With QuoteRegister To Reply
  27. 09-06-2005, 02:05 AM #27
    Bob Phillips
    Guest

    Re: Cell Reference help required

    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" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  28. 09-06-2005, 03:05 AM #28
    Mangesh Yadav
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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)


    Reply With QuoteRegister To Reply
  29. 09-06-2005, 03:05 AM #29
    Mangesh Yadav
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  30. 09-06-2005, 03:05 AM #30
    Bob Phillips
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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)


    Reply With QuoteRegister To Reply
  31. 09-06-2005, 03:05 AM #31
    Bob Phillips
    Guest

    Re: Cell Reference help required

    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" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  32. 09-06-2005, 04:05 AM #32
    Mangesh Yadav
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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)


    Reply With QuoteRegister To Reply
  33. 09-06-2005, 04:05 AM #33
    Bob Phillips
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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)


    Reply With QuoteRegister To Reply
  34. 09-06-2005, 04:05 AM #34
    Mangesh Yadav
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  35. 09-06-2005, 04:05 AM #35
    Bob Phillips
    Guest

    Re: Cell Reference help required

    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" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  36. 09-06-2005, 06:05 AM #36
    Bob Phillips
    Guest

    Re: Cell Reference help required

    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" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  37. 09-06-2005, 06:05 AM #37
    Mangesh Yadav
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  38. 09-06-2005, 06:05 AM #38
    Mangesh Yadav
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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)


    Reply With QuoteRegister To Reply
  39. 09-06-2005, 06:05 AM #39
    Bob Phillips
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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)


    Reply With QuoteRegister To Reply
  40. 09-06-2005, 07:05 AM #40
    Bob Phillips
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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)


    Reply With QuoteRegister To Reply
  41. 09-06-2005, 07:05 AM #41
    Mangesh Yadav
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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)


    Reply With QuoteRegister To Reply
  42. 09-06-2005, 07:05 AM #42
    Bob Phillips
    Guest

    Re: Cell Reference help required

    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" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  43. 09-06-2005, 07:05 AM #43
    Mangesh Yadav
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  44. 09-06-2005, 10:05 AM #44
    Mangesh Yadav
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  45. 09-06-2005, 10:05 AM #45
    Bob Phillips
    Guest

    Re: Cell Reference help required

    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" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  46. 09-06-2005, 10:05 AM #46
    Bob Phillips
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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)


    Reply With QuoteRegister To Reply
  47. 09-06-2005, 10:05 AM #47
    Mangesh Yadav
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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)


    Reply With QuoteRegister To Reply
  48. 09-06-2005, 11:05 AM #48
    Mangesh Yadav
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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)


    Reply With QuoteRegister To Reply
  49. 09-06-2005, 11:05 AM #49
    Bob Phillips
    Guest

    Re: Cell Reference help required

    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" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  50. 09-06-2005, 11:05 AM #50
    Mangesh Yadav
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  51. 09-06-2005, 11:05 AM #51
    Bob Phillips
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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)


    Reply With QuoteRegister To Reply
  52. 09-06-2005, 12:05 PM #52
    Bob Phillips
    Guest

    Re: Cell Reference help required

    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" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  53. 09-06-2005, 12:05 PM #53
    Mangesh Yadav
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  54. 09-06-2005, 12:05 PM #54
    Bob Phillips
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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)


    Reply With QuoteRegister To Reply
  55. 09-06-2005, 12:05 PM #55
    Mangesh Yadav
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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)


    Reply With QuoteRegister To Reply
  56. 09-06-2005, 02:05 PM #56
    Mangesh Yadav
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  57. 09-06-2005, 02:05 PM #57
    Bob Phillips
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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)


    Reply With QuoteRegister To Reply
  58. 09-06-2005, 02:05 PM #58
    Mangesh Yadav
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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)


    Reply With QuoteRegister To Reply
  59. 09-06-2005, 02:05 PM #59
    Bob Phillips
    Guest

    Re: Cell Reference help required

    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" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  60. 09-06-2005, 03:05 PM #60
    Mangesh Yadav
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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)


    Reply With QuoteRegister To Reply
  61. 09-06-2005, 03:05 PM #61
    Bob Phillips
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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)


    Reply With QuoteRegister To Reply
  62. 09-06-2005, 03:05 PM #62
    Mangesh Yadav
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  63. 09-06-2005, 03:05 PM #63
    Bob Phillips
    Guest

    Re: Cell Reference help required

    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" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  64. 09-06-2005, 06:05 PM #64
    Mangesh Yadav
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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)


    Reply With QuoteRegister To Reply
  65. 09-06-2005, 06:05 PM #65
    Bob Phillips
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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)


    Reply With QuoteRegister To Reply
  66. 09-06-2005, 06:05 PM #66
    Mangesh Yadav
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  67. 09-06-2005, 06:05 PM #67
    Bob Phillips
    Guest

    Re: Cell Reference help required

    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" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  68. 09-06-2005, 07:05 PM #68
    Bob Phillips
    Guest

    Re: Cell Reference help required

    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" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  69. 09-06-2005, 07:05 PM #69
    Mangesh Yadav
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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
    >


    Reply With QuoteRegister To Reply
  70. 09-06-2005, 07:05 PM #70
    Bob Phillips
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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)


    Reply With QuoteRegister To Reply
  71. 09-06-2005, 07:05 PM #71
    Mangesh Yadav
    Guest

    Re: Cell Reference help required

    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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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)


    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1