Rackin' my brain

[Walking Shorts]

I know this is a relatively simple question, and I know I've managed to do something like this before, but it's not quite coming to my mind right now...

First off, how do you join text from two different cells in such a way that only PART of the words show up? Example, lets say I have two cells, b1 and b2. b1 says "December" and b2 says "2006", and I want "dec06" to show up in b3, what's the formula I should use?

And second, going based on what I just asked, is it also possible I could use the result to make a reference to another workbook? Sorry if I'm being a little confusing, I'll elaborate. Let's say I have a workbook named "dec06.xls", and I want to cell reference, say, cell c4 of that workbook. But I don't want to just type "dec06.xls" into my reference, instead I want the formula to refer to either cells b1 and b2, or just b3 from my example above for the name of the file.

Thanks in advance for any help dudes :wink:

 

[just_jon]

If you are going to be referencing a single cell, then you can download the Morefunc addin and use INDIRECT.EXT

The Morefunc add-in is a free download, available at: this site.

There is also the PULL function , code below

<font face=Courier New><SPAN style="color:#007F00">'----- begin VBA -----</SPAN>
<SPAN style="color:#00007F">Function</SPAN> pull(xref <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
  <SPAN style="color:#007F00">'inspired by Bob Phillips and Laurent Longre</SPAN>
  <SPAN style="color:#007F00">'but written by Harlan Grove</SPAN>
  <SPAN style="color:#007F00">'-----------------------------------------------------------------</SPAN>
  <SPAN style="color:#007F00">'Copyright (c) 2003 Harlan Grove.</SPAN>
  <SPAN style="color:#007F00">'</SPAN>
  <SPAN style="color:#007F00">'This code is free software; you can redistribute it and/or modify</SPAN>
  <SPAN style="color:#007F00">'it under the terms of the GNU General Public License as published</SPAN>
  <SPAN style="color:#007F00">'by the Free Software Foundation; either version 2 of the License,</SPAN>
  <SPAN style="color:#007F00">'or (at your option) any later version.</SPAN>
  <SPAN style="color:#007F00">'-----------------------------------------------------------------</SPAN>
  <SPAN style="color:#007F00">'2004-05-30</SPAN>
  <SPAN style="color:#007F00">'still more fixes, this time to address apparent differences between</SPAN>
  <SPAN style="color:#007F00">'XL8/97 and later versions. Specifically, fixed the InStrRev call,</SPAN>
  <SPAN style="color:#007F00">'which is fubar in later versions and was using my own hacked version</SPAN>
  <SPAN style="color:#007F00">'under XL8/97 which was using the wrong argument syntax. Also either</SPAN>
  <SPAN style="color:#007F00">'XL8/97 didn't choke on CStr(pull) called when pull referred to an</SPAN>
  <SPAN style="color:#007F00">'array while later versions do, or I never tested the 2004-03-25 fix</SPAN>
  <SPAN style="color:#007F00">'against multiple cell references.</SPAN>
  <SPAN style="color:#007F00">'-----------------------------------------------------------------</SPAN>
  <SPAN style="color:#007F00">'2004-05-28</SPAN>
  <SPAN style="color:#007F00">'fixed the previous fix - replaced all instances of 'expr' with 'xref'</SPAN>
  <SPAN style="color:#007F00">'also now checking for initial single quote in xref, and if found</SPAN>
  <SPAN style="color:#007F00">'advancing past it to get the full pathname [dumb, really dumb!]</SPAN>
  <SPAN style="color:#007F00">'-----------------------------------------------------------------</SPAN>
  <SPAN style="color:#007F00">'2004-03-25</SPAN>
  <SPAN style="color:#007F00">'revised to check if filename in xref exists - if it does, proceed;</SPAN>
  <SPAN style="color:#007F00">'otherwise, return a #REF! error immediately - this avoids Excel</SPAN>
  <SPAN style="color:#007F00">'displaying dialogs when the referenced file doesn't exist</SPAN>
  <SPAN style="color:#007F00">'-----------------------------------------------------------------</SPAN>


  <SPAN style="color:#00007F">Dim</SPAN> xlapp <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>, xlwb <SPAN style="color:#00007F">As</SPAN> Workbook
  <SPAN style="color:#00007F">Dim</SPAN> b <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, r <SPAN style="color:#00007F">As</SPAN> Range, C <SPAN style="color:#00007F">As</SPAN> Range, n <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>


  <SPAN style="color:#007F00">'** begin 2004-05-30 changes **</SPAN>
  <SPAN style="color:#007F00">'** begin 2004-05-28 changes **</SPAN>
  <SPAN style="color:#007F00">'** begin 2004-03-25 changes **</SPAN>
  n = InStrRev(xref, "\")


  <SPAN style="color:#00007F">If</SPAN> n > 0 <SPAN style="color:#00007F">Then</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Mid(xref, n, 2) = "\[" <SPAN style="color:#00007F">Then</SPAN>
      b = Left(xref, n)
      n = InStr(n + 2, xref, "]") - n - 2
      <SPAN style="color:#00007F">If</SPAN> n > 0 <SPAN style="color:#00007F">Then</SPAN> b = b & Mid(xref, Len(b) + 2, n)


    <SPAN style="color:#00007F">Else</SPAN>
      n = InStrRev(Len(xref), xref, "!")
      <SPAN style="color:#00007F">If</SPAN> n > 0 <SPAN style="color:#00007F">Then</SPAN> b = Left(xref, n - 1)


    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>


    <SPAN style="color:#007F00">'** key 2004-05-28 addition **</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Left(b, 1) = "'" Then b = Mid(b, 2)


    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
    <SPAN style="color:#00007F">If</SPAN> n > 0 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">If</SPAN> Dir(b) = "" <SPAN style="color:#00007F">Then</SPAN> n = 0
    Err.Clear
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0


  <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>


  <SPAN style="color:#00007F">If</SPAN> n <= 0 <SPAN style="color:#00007F">Then</SPAN>
    pull = <SPAN style="color:#00007F">CVErr</SPAN>(xlErrRef)
    <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Function</SPAN>
  <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
  <SPAN style="color:#007F00">'** end 2004-03-25 changes **</SPAN>
  <SPAN style="color:#007F00">'** end 2004-05-28 changes **</SPAN>


  pull = Evaluate(xref)


  <SPAN style="color:#007F00">'** key 2004-05-30 addition **</SPAN>
  <SPAN style="color:#00007F">If</SPAN> IsArray(pull) <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Function</SPAN>
  <SPAN style="color:#007F00">'** end 2004-05-30 changes **</SPAN>


  <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">CStr</SPAN>(pull) = <SPAN style="color:#00007F">CStr</SPAN>(CVErr(xlErrRef)) <SPAN style="color:#00007F">Then</SPAN>
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> CleanUp   <SPAN style="color:#007F00">'immediate clean-up at this point</SPAN>


    <SPAN style="color:#00007F">Set</SPAN> xlapp = CreateObject("Excel.Application")
    <SPAN style="color:#00007F">Set</SPAN> xlwb = xlapp.Workbooks.Add  <SPAN style="color:#007F00">'needed by .ExecuteExcel4Macro</SPAN>


    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>    <SPAN style="color:#007F00">'now clean-up can wait</SPAN>


    n = InStr(<SPAN style="color:#00007F">In</SPAN>Str(1, xref, "]") + 1, xref, "!")
    b = Mid(xref, 1, n)


    <SPAN style="color:#00007F">Set</SPAN> r = xlwb.Sheets(1).Range(Mid(xref, n + 1))


    <SPAN style="color:#00007F">If</SPAN> r <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
      pull = xlapp.ExecuteExcel4Macro(xref)


    <SPAN style="color:#00007F">Else</SPAN>
      <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> C In r
        C.Value = xlapp.ExecuteExcel4Macro(b & C.Address(1, 1, xlR1C1))
      <SPAN style="color:#00007F">Next</SPAN> C


      pull = r.Value


    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>


CleanUp:
    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> xlwb <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> xlwb.Close 0
    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> xlapp <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> xlapp.Quit
    <SPAN style="color:#00007F">Set</SPAN> xlapp = <SPAN style="color:#00007F">Nothing</SPAN>


  <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>


<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>

If your separate files are only single sheets, might be far better off putting them in a single workbook.