+ Reply to Thread
Results 1 to 12 of 12

Can I concatenate text in cells to make a working formula?

  1. #1
    Matt S. R.
    Guest

    Can I concatenate text in cells to make a working formula?

    I want to be able to change the name of a file that is used to update data
    without using edit > links > change source, for an existing link. I would
    like to be able to just enter in the file name by hand in one cell and have
    another cell use that new filename to complete it's formula. If I simply
    link cell B1 in the file I am working in to cell A1 on a tab called
    "Projection" in a file called "Nov05" on a network drive "S:", the formula in
    the cell might look like:

    ='S:\Projections\Fall 2005\[Nov05.xls]Projection'!$A$1

    What I would like to be able to do is in Cell A1 of the file I am working
    in, be able to hand write the file name (or full path if necessary) and have
    the formula in cell B1 not link directly to the source file but link to cell
    A1 in the same file, to know which source file to get the data from. For
    example, I would want cell A1 to maybe have 'S:\Projections\Fall
    2005\[Nov05.xls]Projection'! in it while cell b1 would have the cell location
    of the data. Cell B1 would look to cell A1 to get the path/file/tab to go to
    and might just look like:

    =A1$A$1

    instead of a direct link which would look like:

    ='S:\Projections\Fall 2005\[Nov05.xls]Projection'!$A$1

    I've tried several ways to combine the data in different cells to get a
    working formula and tried to use the Help function to no avail. If this is
    even possible to do something like this, I would really appreciate any help.
    Thanks.

    Matt




  2. #2
    Duke Carey
    Guest

    RE: Can I concatenate text in cells to make a working formula?

    Assuming you've got the path, filename, ans sheetname all in cell A1 in the
    form

    'S:\Projections\Fall 2005\[Nov05.xls]Projection'!

    and B1 contains a text value that looks like a cell reference, i.e., B10,
    then use

    =INDIRECT(a1&b1)


    "Matt S. R." wrote:

    > I want to be able to change the name of a file that is used to update data
    > without using edit > links > change source, for an existing link. I would
    > like to be able to just enter in the file name by hand in one cell and have
    > another cell use that new filename to complete it's formula. If I simply
    > link cell B1 in the file I am working in to cell A1 on a tab called
    > "Projection" in a file called "Nov05" on a network drive "S:", the formula in
    > the cell might look like:
    >
    > ='S:\Projections\Fall 2005\[Nov05.xls]Projection'!$A$1
    >
    > What I would like to be able to do is in Cell A1 of the file I am working
    > in, be able to hand write the file name (or full path if necessary) and have
    > the formula in cell B1 not link directly to the source file but link to cell
    > A1 in the same file, to know which source file to get the data from. For
    > example, I would want cell A1 to maybe have 'S:\Projections\Fall
    > 2005\[Nov05.xls]Projection'! in it while cell b1 would have the cell location
    > of the data. Cell B1 would look to cell A1 to get the path/file/tab to go to
    > and might just look like:
    >
    > =A1$A$1
    >
    > instead of a direct link which would look like:
    >
    > ='S:\Projections\Fall 2005\[Nov05.xls]Projection'!$A$1
    >
    > I've tried several ways to combine the data in different cells to get a
    > working formula and tried to use the Help function to no avail. If this is
    > even possible to do something like this, I would really appreciate any help.
    > Thanks.
    >
    > Matt
    >
    >
    >


  3. #3
    rmellison
    Guest

    RE: Can I concatenate text in cells to make a working formula?

    Try this in B1:

    =INDIRECT(A1&"$A$1")

    You will need to enter the correct path, file name and sheet name in cell
    A1, using apostrophes and excamation marks where they would usually appear in
    a normal link. This will link to 'S:\Projections\Fall
    2005\[Nov05.xls]Projection'!$A$1 as per your example.

    To elaborate you could use something like this in, say, D1:

    =INDIRECT("'"&A1&"["&B1&"]"&C1&"'!"&$A$1)

    Where A1 is your path, B1 is your filename and C1 is your sheet name.

    HTH


    "Matt S. R." wrote:

    > I want to be able to change the name of a file that is used to update data
    > without using edit > links > change source, for an existing link. I would
    > like to be able to just enter in the file name by hand in one cell and have
    > another cell use that new filename to complete it's formula. If I simply
    > link cell B1 in the file I am working in to cell A1 on a tab called
    > "Projection" in a file called "Nov05" on a network drive "S:", the formula in
    > the cell might look like:
    >
    > ='S:\Projections\Fall 2005\[Nov05.xls]Projection'!$A$1
    >
    > What I would like to be able to do is in Cell A1 of the file I am working
    > in, be able to hand write the file name (or full path if necessary) and have
    > the formula in cell B1 not link directly to the source file but link to cell
    > A1 in the same file, to know which source file to get the data from. For
    > example, I would want cell A1 to maybe have 'S:\Projections\Fall
    > 2005\[Nov05.xls]Projection'! in it while cell b1 would have the cell location
    > of the data. Cell B1 would look to cell A1 to get the path/file/tab to go to
    > and might just look like:
    >
    > =A1$A$1
    >
    > instead of a direct link which would look like:
    >
    > ='S:\Projections\Fall 2005\[Nov05.xls]Projection'!$A$1
    >
    > I've tried several ways to combine the data in different cells to get a
    > working formula and tried to use the Help function to no avail. If this is
    > even possible to do something like this, I would really appreciate any help.
    > Thanks.
    >
    > Matt
    >
    >
    >


  4. #4
    rmellison
    Guest

    RE: Can I concatenate text in cells to make a working formula?

    Correction:
    =INDIRECT("'"&A1&"["&B1&"]"&C1&"'!"&"$A$1")


    "rmellison" wrote:

    > Try this in B1:
    >
    > =INDIRECT(A1&"$A$1")
    >
    > You will need to enter the correct path, file name and sheet name in cell
    > A1, using apostrophes and excamation marks where they would usually appear in
    > a normal link. This will link to 'S:\Projections\Fall
    > 2005\[Nov05.xls]Projection'!$A$1 as per your example.
    >
    > To elaborate you could use something like this in, say, D1:
    >
    > =INDIRECT("'"&A1&"["&B1&"]"&C1&"'!"&$A$1)
    >
    > Where A1 is your path, B1 is your filename and C1 is your sheet name.
    >
    > HTH
    >
    >
    > "Matt S. R." wrote:
    >
    > > I want to be able to change the name of a file that is used to update data
    > > without using edit > links > change source, for an existing link. I would
    > > like to be able to just enter in the file name by hand in one cell and have
    > > another cell use that new filename to complete it's formula. If I simply
    > > link cell B1 in the file I am working in to cell A1 on a tab called
    > > "Projection" in a file called "Nov05" on a network drive "S:", the formula in
    > > the cell might look like:
    > >
    > > ='S:\Projections\Fall 2005\[Nov05.xls]Projection'!$A$1
    > >
    > > What I would like to be able to do is in Cell A1 of the file I am working
    > > in, be able to hand write the file name (or full path if necessary) and have
    > > the formula in cell B1 not link directly to the source file but link to cell
    > > A1 in the same file, to know which source file to get the data from. For
    > > example, I would want cell A1 to maybe have 'S:\Projections\Fall
    > > 2005\[Nov05.xls]Projection'! in it while cell b1 would have the cell location
    > > of the data. Cell B1 would look to cell A1 to get the path/file/tab to go to
    > > and might just look like:
    > >
    > > =A1$A$1
    > >
    > > instead of a direct link which would look like:
    > >
    > > ='S:\Projections\Fall 2005\[Nov05.xls]Projection'!$A$1
    > >
    > > I've tried several ways to combine the data in different cells to get a
    > > working formula and tried to use the Help function to no avail. If this is
    > > even possible to do something like this, I would really appreciate any help.
    > > Thanks.
    > >
    > > Matt
    > >
    > >
    > >


  5. #5
    Dave Peterson
    Guest

    Re: Can I concatenate text in cells to make a working formula?

    The function you want to use is =indirect().

    The bad news is that =indirect() will return an error if that other workbook
    isn't open.

    Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed
    workbook.

    You can find the function at Harlan's FTP site:
    ftp://members.aol.com/hrlngrv/
    Look for pull.zip

    Matt S. R. wrote:
    >
    > I want to be able to change the name of a file that is used to update data
    > without using edit > links > change source, for an existing link. I would
    > like to be able to just enter in the file name by hand in one cell and have
    > another cell use that new filename to complete it's formula. If I simply
    > link cell B1 in the file I am working in to cell A1 on a tab called
    > "Projection" in a file called "Nov05" on a network drive "S:", the formula in
    > the cell might look like:
    >
    > ='S:\Projections\Fall 2005\[Nov05.xls]Projection'!$A$1
    >
    > What I would like to be able to do is in Cell A1 of the file I am working
    > in, be able to hand write the file name (or full path if necessary) and have
    > the formula in cell B1 not link directly to the source file but link to cell
    > A1 in the same file, to know which source file to get the data from. For
    > example, I would want cell A1 to maybe have 'S:\Projections\Fall
    > 2005\[Nov05.xls]Projection'! in it while cell b1 would have the cell location
    > of the data. Cell B1 would look to cell A1 to get the path/file/tab to go to
    > and might just look like:
    >
    > =A1$A$1
    >
    > instead of a direct link which would look like:
    >
    > ='S:\Projections\Fall 2005\[Nov05.xls]Projection'!$A$1
    >
    > I've tried several ways to combine the data in different cells to get a
    > working formula and tried to use the Help function to no avail. If this is
    > even possible to do something like this, I would really appreciate any help.
    > Thanks.
    >
    > Matt
    >
    >


    --

    Dave Peterson

  6. #6
    Matt S. R.
    Guest

    Re: Can I concatenate text in cells to make a working formula?

    Thanks Dave! That is what I found.. I get #REF when the source isnt open.
    I'll check into the "Pull" you pointed out. Thanks!

    "Dave Peterson" wrote:

    > The function you want to use is =indirect().
    >
    > The bad news is that =indirect() will return an error if that other workbook
    > isn't open.
    >
    > Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed
    > workbook.
    >
    > You can find the function at Harlan's FTP site:
    > ftp://members.aol.com/hrlngrv/
    > Look for pull.zip
    >
    > Matt S. R. wrote:
    > >
    > > I want to be able to change the name of a file that is used to update data
    > > without using edit > links > change source, for an existing link. I would
    > > like to be able to just enter in the file name by hand in one cell and have
    > > another cell use that new filename to complete it's formula. If I simply
    > > link cell B1 in the file I am working in to cell A1 on a tab called
    > > "Projection" in a file called "Nov05" on a network drive "S:", the formula in
    > > the cell might look like:
    > >
    > > ='S:\Projections\Fall 2005\[Nov05.xls]Projection'!$A$1
    > >
    > > What I would like to be able to do is in Cell A1 of the file I am working
    > > in, be able to hand write the file name (or full path if necessary) and have
    > > the formula in cell B1 not link directly to the source file but link to cell
    > > A1 in the same file, to know which source file to get the data from. For
    > > example, I would want cell A1 to maybe have 'S:\Projections\Fall
    > > 2005\[Nov05.xls]Projection'! in it while cell b1 would have the cell location
    > > of the data. Cell B1 would look to cell A1 to get the path/file/tab to go to
    > > and might just look like:
    > >
    > > =A1$A$1
    > >
    > > instead of a direct link which would look like:
    > >
    > > ='S:\Projections\Fall 2005\[Nov05.xls]Projection'!$A$1
    > >
    > > I've tried several ways to combine the data in different cells to get a
    > > working formula and tried to use the Help function to no avail. If this is
    > > even possible to do something like this, I would really appreciate any help.
    > > Thanks.
    > >
    > > Matt
    > >
    > >

    >
    > --
    >
    > Dave Peterson
    >


  7. #7
    Matt S. R.
    Guest

    RE: Can I concatenate text in cells to make a working formula?

    Thanks so much for the replies.

    It kinda works. I used both methods that Duke Carey and you suggested and
    they both give me a #REF. But, If I open the file I am linking to, and the
    path now becomes simply '[Nov05.xls]Projection'!, and I only use that in my
    formula it works. But as soon as I close it and the path becomes the much
    longer one with Drives and folders, it #REFs out also. So, for some reason,
    when the source file is not open and I am using the full path, it will not
    work. In summary:

    Full path is:

    ='S:\Projections\Fall 2005\[Nov05.xls]Projection'!$A$1

    and if I have 'S:\Projections\Fall 2005\[Nov05.xls]Projection'! in one cell
    like A1 and $A$1 in another cell like B1, and in cell C1 I type
    =Indirect(A1&B1) and the source file is not open I get a #REF. Likewise, If
    I have the same 'S:\Projections\Fall 2005\[Nov05.xls]Projection'! in cell A1
    and in Cell B1 I type =Indirect(A1&"$A$1") it also gives me a #REF. But when
    I open the source file and the path is now ='[Nov05.xls]Projection'!$A$1 and
    in cell A1 I write [Nov05.xls]Projection'! and do the same 2 methods it does
    work.

    Any ideas why it doesnt like going through network dirves when the file is
    closed? Both the file I am working in and the source file is on the same S:
    drive. Thanks!



    "rmellison" wrote:

    > Correction:
    > =INDIRECT("'"&A1&"["&B1&"]"&C1&"'!"&"$A$1")
    >
    >
    > "rmellison" wrote:
    >
    > > Try this in B1:
    > >
    > > =INDIRECT(A1&"$A$1")
    > >
    > > You will need to enter the correct path, file name and sheet name in cell
    > > A1, using apostrophes and excamation marks where they would usually appear in
    > > a normal link. This will link to 'S:\Projections\Fall
    > > 2005\[Nov05.xls]Projection'!$A$1 as per your example.
    > >
    > > To elaborate you could use something like this in, say, D1:
    > >
    > > =INDIRECT("'"&A1&"["&B1&"]"&C1&"'!"&$A$1)
    > >
    > > Where A1 is your path, B1 is your filename and C1 is your sheet name.
    > >
    > > HTH
    > >
    > >
    > > "Matt S. R." wrote:
    > >
    > > > I want to be able to change the name of a file that is used to update data
    > > > without using edit > links > change source, for an existing link. I would
    > > > like to be able to just enter in the file name by hand in one cell and have
    > > > another cell use that new filename to complete it's formula. If I simply
    > > > link cell B1 in the file I am working in to cell A1 on a tab called
    > > > "Projection" in a file called "Nov05" on a network drive "S:", the formula in
    > > > the cell might look like:
    > > >
    > > > ='S:\Projections\Fall 2005\[Nov05.xls]Projection'!$A$1
    > > >
    > > > What I would like to be able to do is in Cell A1 of the file I am working
    > > > in, be able to hand write the file name (or full path if necessary) and have
    > > > the formula in cell B1 not link directly to the source file but link to cell
    > > > A1 in the same file, to know which source file to get the data from. For
    > > > example, I would want cell A1 to maybe have 'S:\Projections\Fall
    > > > 2005\[Nov05.xls]Projection'! in it while cell b1 would have the cell location
    > > > of the data. Cell B1 would look to cell A1 to get the path/file/tab to go to
    > > > and might just look like:
    > > >
    > > > =A1$A$1
    > > >
    > > > instead of a direct link which would look like:
    > > >
    > > > ='S:\Projections\Fall 2005\[Nov05.xls]Projection'!$A$1
    > > >
    > > > I've tried several ways to combine the data in different cells to get a
    > > > working formula and tried to use the Help function to no avail. If this is
    > > > even possible to do something like this, I would really appreciate any help.
    > > > Thanks.
    > > >
    > > > Matt
    > > >
    > > >
    > > >


  8. #8
    Matt S. R.
    Guest

    Re: Can I concatenate text in cells to make a working formula?

    Dave,

    I got the PULL.zip and extracted it tomy desktop. Now Im not sure what to
    do with the PULL.bas file I have now.

    Matt

    "Dave Peterson" wrote:

    > The function you want to use is =indirect().
    >
    > The bad news is that =indirect() will return an error if that other workbook
    > isn't open.
    >
    > Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed
    > workbook.
    >
    > You can find the function at Harlan's FTP site:
    > ftp://members.aol.com/hrlngrv/
    > Look for pull.zip
    >
    > Matt S. R. wrote:
    > >
    > > I want to be able to change the name of a file that is used to update data
    > > without using edit > links > change source, for an existing link. I would
    > > like to be able to just enter in the file name by hand in one cell and have
    > > another cell use that new filename to complete it's formula. If I simply
    > > link cell B1 in the file I am working in to cell A1 on a tab called
    > > "Projection" in a file called "Nov05" on a network drive "S:", the formula in
    > > the cell might look like:
    > >
    > > ='S:\Projections\Fall 2005\[Nov05.xls]Projection'!$A$1
    > >
    > > What I would like to be able to do is in Cell A1 of the file I am working
    > > in, be able to hand write the file name (or full path if necessary) and have
    > > the formula in cell B1 not link directly to the source file but link to cell
    > > A1 in the same file, to know which source file to get the data from. For
    > > example, I would want cell A1 to maybe have 'S:\Projections\Fall
    > > 2005\[Nov05.xls]Projection'! in it while cell b1 would have the cell location
    > > of the data. Cell B1 would look to cell A1 to get the path/file/tab to go to
    > > and might just look like:
    > >
    > > =A1$A$1
    > >
    > > instead of a direct link which would look like:
    > >
    > > ='S:\Projections\Fall 2005\[Nov05.xls]Projection'!$A$1
    > >
    > > I've tried several ways to combine the data in different cells to get a
    > > working formula and tried to use the Help function to no avail. If this is
    > > even possible to do something like this, I would really appreciate any help.
    > > Thanks.
    > >
    > > Matt
    > >
    > >

    >
    > --
    >
    > Dave Peterson
    >


  9. #9
    Chip Pearson
    Guest

    Re: Can I concatenate text in cells to make a working formula?

    In the VBA editor, go to the File menu, choose Import File, and
    choose the Pull.bas file.


    --
    Cordially,
    Chip Pearson
    Microsoft MVP - Excel
    Pearson Software Consulting, LLC
    www.cpearson.com


    "Matt S. R." <[email protected]> wrote in message
    news:[email protected]...
    > Dave,
    >
    > I got the PULL.zip and extracted it tomy desktop. Now Im not
    > sure what to
    > do with the PULL.bas file I have now.
    >
    > Matt
    >
    > "Dave Peterson" wrote:
    >
    >> The function you want to use is =indirect().
    >>
    >> The bad news is that =indirect() will return an error if that
    >> other workbook
    >> isn't open.
    >>
    >> Harlan Grove wrote a UDF called PULL that will retrieve the
    >> value from a closed
    >> workbook.
    >>
    >> You can find the function at Harlan's FTP site:
    >> ftp://members.aol.com/hrlngrv/
    >> Look for pull.zip
    >>
    >> Matt S. R. wrote:
    >> >
    >> > I want to be able to change the name of a file that is used
    >> > to update data
    >> > without using edit > links > change source, for an existing
    >> > link. I would
    >> > like to be able to just enter in the file name by hand in
    >> > one cell and have
    >> > another cell use that new filename to complete it's formula.
    >> > If I simply
    >> > link cell B1 in the file I am working in to cell A1 on a tab
    >> > called
    >> > "Projection" in a file called "Nov05" on a network drive
    >> > "S:", the formula in
    >> > the cell might look like:
    >> >
    >> > ='S:\Projections\Fall 2005\[Nov05.xls]Projection'!$A$1
    >> >
    >> > What I would like to be able to do is in Cell A1 of the
    >> > file I am working
    >> > in, be able to hand write the file name (or full path if
    >> > necessary) and have
    >> > the formula in cell B1 not link directly to the source file
    >> > but link to cell
    >> > A1 in the same file, to know which source file to get the
    >> > data from. For
    >> > example, I would want cell A1 to maybe have
    >> > 'S:\Projections\Fall
    >> > 2005\[Nov05.xls]Projection'! in it while cell b1 would have
    >> > the cell location
    >> > of the data. Cell B1 would look to cell A1 to get the
    >> > path/file/tab to go to
    >> > and might just look like:
    >> >
    >> > =A1$A$1
    >> >
    >> > instead of a direct link which would look like:
    >> >
    >> > ='S:\Projections\Fall 2005\[Nov05.xls]Projection'!$A$1
    >> >
    >> > I've tried several ways to combine the data in different
    >> > cells to get a
    >> > working formula and tried to use the Help function to no
    >> > avail. If this is
    >> > even possible to do something like this, I would really
    >> > appreciate any help.
    >> > Thanks.
    >> >
    >> > Matt
    >> >
    >> >

    >>
    >> --
    >>
    >> Dave Peterson
    >>




  10. #10
    Matt S. R.
    Guest

    Re: Can I concatenate text in cells to make a working formula?

    Thanks Chip. I managed to do what you said, however I do not know anything
    about VB and am assuming I need to know how to do a little programming to get
    the use out of it, which I do not. Thanks for the help anyway.

    "Chip Pearson" wrote:

    > In the VBA editor, go to the File menu, choose Import File, and
    > choose the Pull.bas file.
    >
    >
    > --
    > Cordially,
    > Chip Pearson
    > Microsoft MVP - Excel
    > Pearson Software Consulting, LLC
    > www.cpearson.com
    >
    >
    > "Matt S. R." <[email protected]> wrote in message
    > news:[email protected]...
    > > Dave,
    > >
    > > I got the PULL.zip and extracted it tomy desktop. Now Im not
    > > sure what to
    > > do with the PULL.bas file I have now.
    > >
    > > Matt
    > >
    > > "Dave Peterson" wrote:
    > >
    > >> The function you want to use is =indirect().
    > >>
    > >> The bad news is that =indirect() will return an error if that
    > >> other workbook
    > >> isn't open.
    > >>
    > >> Harlan Grove wrote a UDF called PULL that will retrieve the
    > >> value from a closed
    > >> workbook.
    > >>
    > >> You can find the function at Harlan's FTP site:
    > >> ftp://members.aol.com/hrlngrv/
    > >> Look for pull.zip
    > >>
    > >> Matt S. R. wrote:
    > >> >
    > >> > I want to be able to change the name of a file that is used
    > >> > to update data
    > >> > without using edit > links > change source, for an existing
    > >> > link. I would
    > >> > like to be able to just enter in the file name by hand in
    > >> > one cell and have
    > >> > another cell use that new filename to complete it's formula.
    > >> > If I simply
    > >> > link cell B1 in the file I am working in to cell A1 on a tab
    > >> > called
    > >> > "Projection" in a file called "Nov05" on a network drive
    > >> > "S:", the formula in
    > >> > the cell might look like:
    > >> >
    > >> > ='S:\Projections\Fall 2005\[Nov05.xls]Projection'!$A$1
    > >> >
    > >> > What I would like to be able to do is in Cell A1 of the
    > >> > file I am working
    > >> > in, be able to hand write the file name (or full path if
    > >> > necessary) and have
    > >> > the formula in cell B1 not link directly to the source file
    > >> > but link to cell
    > >> > A1 in the same file, to know which source file to get the
    > >> > data from. For
    > >> > example, I would want cell A1 to maybe have
    > >> > 'S:\Projections\Fall
    > >> > 2005\[Nov05.xls]Projection'! in it while cell b1 would have
    > >> > the cell location
    > >> > of the data. Cell B1 would look to cell A1 to get the
    > >> > path/file/tab to go to
    > >> > and might just look like:
    > >> >
    > >> > =A1$A$1
    > >> >
    > >> > instead of a direct link which would look like:
    > >> >
    > >> > ='S:\Projections\Fall 2005\[Nov05.xls]Projection'!$A$1
    > >> >
    > >> > I've tried several ways to combine the data in different
    > >> > cells to get a
    > >> > working formula and tried to use the Help function to no
    > >> > avail. If this is
    > >> > even possible to do something like this, I would really
    > >> > appreciate any help.
    > >> > Thanks.
    > >> >
    > >> > Matt
    > >> >
    > >> >
    > >>
    > >> --
    > >>
    > >> Dave Peterson
    > >>

    >
    >
    >


  11. #11
    Dave Peterson
    Guest

    Re: Can I concatenate text in cells to make a working formula?

    If you're new to macros, you may want to read David McRitchie's intro at:
    http://www.mvps.org/dmcritchie/excel/getstarted.htm

    But if you put:
    'S:\Projections\Fall 2005\[Nov05.xls]Projection'!
    in A1
    and
    A1
    in B1

    Then you could use:
    =pull(a1&b1)
    in any cell

    One word of warning.

    Look at the cell A1.
    If you don't see the leading apostrophe, then excel is "eating it up" to force
    text.

    Put this in A1.
    ''S:\Projections\Fall 2005\[Nov05.xls]Projection'!

    You should be able to see both leading apostrophes in the formula bar, but only
    one in cell.

    An alternative:
    Put
    S:\Projections\Fall 2005\[Nov05.xls]Projection
    in A1
    put A1 in B1 (I find that confusing!)
    then use:
    =pull("'"&A1&"'!"&B1)
    in your formula cell.




    Matt S. R. wrote:
    >
    > Thanks Chip. I managed to do what you said, however I do not know anything
    > about VB and am assuming I need to know how to do a little programming to get
    > the use out of it, which I do not. Thanks for the help anyway.
    >
    > "Chip Pearson" wrote:
    >
    > > In the VBA editor, go to the File menu, choose Import File, and
    > > choose the Pull.bas file.
    > >
    > >
    > > --
    > > Cordially,
    > > Chip Pearson
    > > Microsoft MVP - Excel
    > > Pearson Software Consulting, LLC
    > > www.cpearson.com
    > >
    > >
    > > "Matt S. R." <[email protected]> wrote in message
    > > news:[email protected]...
    > > > Dave,
    > > >
    > > > I got the PULL.zip and extracted it tomy desktop. Now Im not
    > > > sure what to
    > > > do with the PULL.bas file I have now.
    > > >
    > > > Matt
    > > >
    > > > "Dave Peterson" wrote:
    > > >
    > > >> The function you want to use is =indirect().
    > > >>
    > > >> The bad news is that =indirect() will return an error if that
    > > >> other workbook
    > > >> isn't open.
    > > >>
    > > >> Harlan Grove wrote a UDF called PULL that will retrieve the
    > > >> value from a closed
    > > >> workbook.
    > > >>
    > > >> You can find the function at Harlan's FTP site:
    > > >> ftp://members.aol.com/hrlngrv/
    > > >> Look for pull.zip
    > > >>
    > > >> Matt S. R. wrote:
    > > >> >
    > > >> > I want to be able to change the name of a file that is used
    > > >> > to update data
    > > >> > without using edit > links > change source, for an existing
    > > >> > link. I would
    > > >> > like to be able to just enter in the file name by hand in
    > > >> > one cell and have
    > > >> > another cell use that new filename to complete it's formula.
    > > >> > If I simply
    > > >> > link cell B1 in the file I am working in to cell A1 on a tab
    > > >> > called
    > > >> > "Projection" in a file called "Nov05" on a network drive
    > > >> > "S:", the formula in
    > > >> > the cell might look like:
    > > >> >
    > > >> > ='S:\Projections\Fall 2005\[Nov05.xls]Projection'!$A$1
    > > >> >
    > > >> > What I would like to be able to do is in Cell A1 of the
    > > >> > file I am working
    > > >> > in, be able to hand write the file name (or full path if
    > > >> > necessary) and have
    > > >> > the formula in cell B1 not link directly to the source file
    > > >> > but link to cell
    > > >> > A1 in the same file, to know which source file to get the
    > > >> > data from. For
    > > >> > example, I would want cell A1 to maybe have
    > > >> > 'S:\Projections\Fall
    > > >> > 2005\[Nov05.xls]Projection'! in it while cell b1 would have
    > > >> > the cell location
    > > >> > of the data. Cell B1 would look to cell A1 to get the
    > > >> > path/file/tab to go to
    > > >> > and might just look like:
    > > >> >
    > > >> > =A1$A$1
    > > >> >
    > > >> > instead of a direct link which would look like:
    > > >> >
    > > >> > ='S:\Projections\Fall 2005\[Nov05.xls]Projection'!$A$1
    > > >> >
    > > >> > I've tried several ways to combine the data in different
    > > >> > cells to get a
    > > >> > working formula and tried to use the Help function to no
    > > >> > avail. If this is
    > > >> > even possible to do something like this, I would really
    > > >> > appreciate any help.
    > > >> > Thanks.
    > > >> >
    > > >> > Matt
    > > >> >
    > > >> >
    > > >>
    > > >> --
    > > >>
    > > >> Dave Peterson
    > > >>

    > >
    > >
    > >


    --

    Dave Peterson

  12. #12
    Matt S. R.
    Guest

    Re: Can I concatenate text in cells to make a working formula?

    Thanks Dave! I'll try that out. And thanks for the link.

    Matt

    "Dave Peterson" wrote:

    > If you're new to macros, you may want to read David McRitchie's intro at:
    > http://www.mvps.org/dmcritchie/excel/getstarted.htm
    >
    > But if you put:
    > 'S:\Projections\Fall 2005\[Nov05.xls]Projection'!
    > in A1
    > and
    > A1
    > in B1
    >
    > Then you could use:
    > =pull(a1&b1)
    > in any cell
    >
    > One word of warning.
    >
    > Look at the cell A1.
    > If you don't see the leading apostrophe, then excel is "eating it up" to force
    > text.
    >
    > Put this in A1.
    > ''S:\Projections\Fall 2005\[Nov05.xls]Projection'!
    >
    > You should be able to see both leading apostrophes in the formula bar, but only
    > one in cell.
    >
    > An alternative:
    > Put
    > S:\Projections\Fall 2005\[Nov05.xls]Projection
    > in A1
    > put A1 in B1 (I find that confusing!)
    > then use:
    > =pull("'"&A1&"'!"&B1)
    > in your formula cell.
    >
    >
    >
    >
    > Matt S. R. wrote:
    > >
    > > Thanks Chip. I managed to do what you said, however I do not know anything
    > > about VB and am assuming I need to know how to do a little programming to get
    > > the use out of it, which I do not. Thanks for the help anyway.
    > >
    > > "Chip Pearson" wrote:
    > >
    > > > In the VBA editor, go to the File menu, choose Import File, and
    > > > choose the Pull.bas file.
    > > >
    > > >
    > > > --
    > > > Cordially,
    > > > Chip Pearson
    > > > Microsoft MVP - Excel
    > > > Pearson Software Consulting, LLC
    > > > www.cpearson.com
    > > >
    > > >
    > > > "Matt S. R." <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > > Dave,
    > > > >
    > > > > I got the PULL.zip and extracted it tomy desktop. Now Im not
    > > > > sure what to
    > > > > do with the PULL.bas file I have now.
    > > > >
    > > > > Matt
    > > > >
    > > > > "Dave Peterson" wrote:
    > > > >
    > > > >> The function you want to use is =indirect().
    > > > >>
    > > > >> The bad news is that =indirect() will return an error if that
    > > > >> other workbook
    > > > >> isn't open.
    > > > >>
    > > > >> Harlan Grove wrote a UDF called PULL that will retrieve the
    > > > >> value from a closed
    > > > >> workbook.
    > > > >>
    > > > >> You can find the function at Harlan's FTP site:
    > > > >> ftp://members.aol.com/hrlngrv/
    > > > >> Look for pull.zip
    > > > >>
    > > > >> Matt S. R. wrote:
    > > > >> >
    > > > >> > I want to be able to change the name of a file that is used
    > > > >> > to update data
    > > > >> > without using edit > links > change source, for an existing
    > > > >> > link. I would
    > > > >> > like to be able to just enter in the file name by hand in
    > > > >> > one cell and have
    > > > >> > another cell use that new filename to complete it's formula.
    > > > >> > If I simply
    > > > >> > link cell B1 in the file I am working in to cell A1 on a tab
    > > > >> > called
    > > > >> > "Projection" in a file called "Nov05" on a network drive
    > > > >> > "S:", the formula in
    > > > >> > the cell might look like:
    > > > >> >
    > > > >> > ='S:\Projections\Fall 2005\[Nov05.xls]Projection'!$A$1
    > > > >> >
    > > > >> > What I would like to be able to do is in Cell A1 of the
    > > > >> > file I am working
    > > > >> > in, be able to hand write the file name (or full path if
    > > > >> > necessary) and have
    > > > >> > the formula in cell B1 not link directly to the source file
    > > > >> > but link to cell
    > > > >> > A1 in the same file, to know which source file to get the
    > > > >> > data from. For
    > > > >> > example, I would want cell A1 to maybe have
    > > > >> > 'S:\Projections\Fall
    > > > >> > 2005\[Nov05.xls]Projection'! in it while cell b1 would have
    > > > >> > the cell location
    > > > >> > of the data. Cell B1 would look to cell A1 to get the
    > > > >> > path/file/tab to go to
    > > > >> > and might just look like:
    > > > >> >
    > > > >> > =A1$A$1
    > > > >> >
    > > > >> > instead of a direct link which would look like:
    > > > >> >
    > > > >> > ='S:\Projections\Fall 2005\[Nov05.xls]Projection'!$A$1
    > > > >> >
    > > > >> > I've tried several ways to combine the data in different
    > > > >> > cells to get a
    > > > >> > working formula and tried to use the Help function to no
    > > > >> > avail. If this is
    > > > >> > even possible to do something like this, I would really
    > > > >> > appreciate any help.
    > > > >> > Thanks.
    > > > >> >
    > > > >> > Matt
    > > > >> >
    > > > >> >
    > > > >>
    > > > >> --
    > > > >>
    > > > >> Dave Peterson
    > > > >>
    > > >
    > > >
    > > >

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