+ Reply to Thread
Results 1 to 5 of 5

Need formula for sheet & cell reference

  1. #1
    MPH
    Guest

    Need formula for sheet & cell reference

    Help
    Have a list of sheet names and want to create a formula to use sheet name
    as a reference in a formula. Have tried "Indirect" but haven't succeeded
    with that or any other.
    For example

    sheet names listed vertically i.e. inv1, inv1, inv3 etc.
    formula needed to refer to "sheet name"[Inv1] AND "cell reference"[g10]

    Have used SheetOffset in VBA but want to use actual sheet name, rather than
    offset.
    For this file it is not possible to use consolidation feature.

    Will appreciate any help.
    TIA
    Malcolm Hughes




  2. #2
    Gilbert De Ceulaer
    Guest

    Re: Need formula for sheet & cell reference

    A1 = sheetname
    G10 = cell
    Then formula is = indirect(" ' "& A1 & " '! "&g10)
    OK
    Gilbert
    [email protected]

    "MPH" <[email protected]> wrote in message
    news:pUvGd.551$D%[email protected]...
    > Help
    > Have a list of sheet names and want to create a formula to use sheet name
    > as a reference in a formula. Have tried "Indirect" but haven't succeeded
    > with that or any other.
    > For example
    >
    > sheet names listed vertically i.e. inv1, inv1, inv3 etc.
    > formula needed to refer to "sheet name"[Inv1] AND "cell reference"[g10]
    >
    > Have used SheetOffset in VBA but want to use actual sheet name, rather
    > than
    > offset.
    > For this file it is not possible to use consolidation feature.
    >
    > Will appreciate any help.
    > TIA
    > Malcolm Hughes
    >
    >
    >




  3. #3
    MPH
    Guest

    Re: Need formula for sheet & cell reference

    Thanks, but
    That solution produces #REF.
    The G10 I want is the G10 in sheetname i.e. sheetname x, cell G10;
    sheetname y, cell G10 etc.
    Still confused.
    Thanks




    "Gilbert De Ceulaer" <[email protected]> wrote in message
    news:%23g6GtP%23%[email protected]...
    > A1 = sheetname
    > G10 = cell
    > Then formula is = indirect(" ' "& A1 & " '! "&g10)
    > OK
    > Gilbert
    > [email protected]
    >
    > "MPH" <[email protected]> wrote in message
    > news:pUvGd.551$D%[email protected]...
    > > Help
    > > Have a list of sheet names and want to create a formula to use sheet

    name
    > > as a reference in a formula. Have tried "Indirect" but haven't

    succeeded
    > > with that or any other.
    > > For example
    > >
    > > sheet names listed vertically i.e. inv1, inv1, inv3 etc.
    > > formula needed to refer to "sheet name"[Inv1] AND "cell reference"[g10]
    > >
    > > Have used SheetOffset in VBA but want to use actual sheet name, rather
    > > than
    > > offset.
    > > For this file it is not possible to use consolidation feature.
    > >
    > > Will appreciate any help.
    > > TIA
    > > Malcolm Hughes
    > >
    > >
    > >

    >
    >




  4. #4
    Dave Peterson
    Guest

    Re: Need formula for sheet & cell reference

    How about:

    =INDIRECT("'"&A1&"'!g10")





    MPH wrote:
    >
    > Thanks, but
    > That solution produces #REF.
    > The G10 I want is the G10 in sheetname i.e. sheetname x, cell G10;
    > sheetname y, cell G10 etc.
    > Still confused.
    > Thanks
    >
    > "Gilbert De Ceulaer" <[email protected]> wrote in message
    > news:%23g6GtP%23%[email protected]...
    > > A1 = sheetname
    > > G10 = cell
    > > Then formula is = indirect(" ' "& A1 & " '! "&g10)
    > > OK
    > > Gilbert
    > > [email protected]
    > >
    > > "MPH" <[email protected]> wrote in message
    > > news:pUvGd.551$D%[email protected]...
    > > > Help
    > > > Have a list of sheet names and want to create a formula to use sheet

    > name
    > > > as a reference in a formula. Have tried "Indirect" but haven't

    > succeeded
    > > > with that or any other.
    > > > For example
    > > >
    > > > sheet names listed vertically i.e. inv1, inv1, inv3 etc.
    > > > formula needed to refer to "sheet name"[Inv1] AND "cell reference"[g10]
    > > >
    > > > Have used SheetOffset in VBA but want to use actual sheet name, rather
    > > > than
    > > > offset.
    > > > For this file it is not possible to use consolidation feature.
    > > >
    > > > Will appreciate any help.
    > > > TIA
    > > > Malcolm Hughes
    > > >
    > > >
    > > >

    > >
    > >


    --

    Dave Peterson

  5. #5
    MPH
    Guest

    Re: Need formula for sheet & cell reference

    That works fine, thanks a lot.
    Please disregard earlier message concerning the formula not copying as
    expected; had calc switched off.

    Many thanks for your assistance.

    "Dave Peterson" <[email protected]> wrote in message
    news:[email protected]...
    > How about:
    >
    > =INDIRECT("'"&A1&"'!g10")
    >
    >
    >
    >
    >
    > MPH wrote:
    > >
    > > Thanks, but
    > > That solution produces #REF.
    > > The G10 I want is the G10 in sheetname i.e. sheetname x, cell G10;
    > > sheetname y, cell G10 etc.
    > > Still confused.
    > > Thanks
    > >
    > > "Gilbert De Ceulaer" <[email protected]> wrote in message
    > > news:%23g6GtP%23%[email protected]...
    > > > A1 = sheetname
    > > > G10 = cell
    > > > Then formula is = indirect(" ' "& A1 & " '! "&g10)
    > > > OK
    > > > Gilbert
    > > > [email protected]
    > > >
    > > > "MPH" <[email protected]> wrote in message
    > > > news:pUvGd.551$D%[email protected]...
    > > > > Help
    > > > > Have a list of sheet names and want to create a formula to use

    sheet
    > > name
    > > > > as a reference in a formula. Have tried "Indirect" but haven't

    > > succeeded
    > > > > with that or any other.
    > > > > For example
    > > > >
    > > > > sheet names listed vertically i.e. inv1, inv1, inv3 etc.
    > > > > formula needed to refer to "sheet name"[Inv1] AND "cell

    reference"[g10]
    > > > >
    > > > > Have used SheetOffset in VBA but want to use actual sheet name,

    rather
    > > > > than
    > > > > offset.
    > > > > For this file it is not possible to use consolidation feature.
    > > > >
    > > > > Will appreciate any help.
    > > > > TIA
    > > > > Malcolm Hughes
    > > > >
    > > > >
    > > > >
    > > >
    > > >

    >
    > --
    >
    > Dave Peterson




+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1