1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Insert field (e.g., &[Tab]) into worksheet cell

Discussion in 'Business Applications' started by dzonko, Aug 17, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. dzonko

    dzonko Thread Starter

    Joined:
    Aug 24, 2003
    Messages:
    70
    I'd like to be able to insert fields into the cells of an Excel workbook, rather than just in the header / footer.

    I'm going to be completely in shock if this is not possible, but I'm not able to find anything whatsoever.

    Help????

    Thanks,

    John
     
  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Sorry, "insert fields into the cells of an Excel workbook" is not enough info for me (& possibly others).

    W/r/t thread title, if you have John in A1 and Smith in A2 then:

    =A1&CHAR(10)&A2

    in A3 will give you:

    John
    Smith


    provided Wrap Text is "on".

    Can you upload a small file showing (somehow) exactly what you want?
     
  3. dzonko

    dzonko Thread Starter

    Joined:
    Aug 24, 2003
    Messages:
    70
    Thanks for the response; sorry if my original question wasn't clear enough. What I would like to do is specifically to be able to insert into a cell within a worksheet some of the standard fields that one can insert into headers / footers: e.g., file name ("&[File]"), sheet name, ("&[Tab]"), etc.

    For instance, I'd like to be able to insert code that automatically calls the file name into cell A1, which is then automatically updated on printing when the file is changed with a new name.

    I'm attaching (I hope) a sample worksheet with field codes in header and footer; what I'd like to be able to do is insert such info into the body of the sheet.

    Many thanks for any assistance.

    John
     

    Attached Files:

  4. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    For the file name, a formula might work:

    =MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1)

    Or if by "insert code" you mean some VBA (AKA macros), then a BeforePrint procedure in the workbook module:

    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Range("A1") = ThisWorkbook.Name
    End Sub
     
  5. dzonko

    dzonko Thread Starter

    Joined:
    Aug 24, 2003
    Messages:
    70
    Greatly appreciate the reply, and I think we may be getting close. Excel tells me there's a problem with the formula as you've provided it; it generates an error.

    I wasn't familiar with MID, FIND or CELL. After reading the help file I think I understand the approach you're proposing, but don't understand the syntax well enough to if it's a syntax error, or if this approach doesn't actually work. I do find myself wondering whether Excel can FIND within what would at least *appear* as the text-string result of another formula - if I insert the filename (which I didn't even know was possible - thanks for that), does Excel treat that as a text string? That would surprise me slightly.

    Let me give a little more background for where the request is coming from: I work for a non-profit and we have to generate a huge number of project-specific budgets, most of which are extensively circulated and reviewed before they warrant printing for the file. I'm quite fastidious about naming files and sheets, and have long since standardized a template where this and other important info (logo, page info, print date / time, filename and path, etc.) is automated in headers and footers. Because most of the file sharing takes place electronically and most other people in the loop don't know anything about Excel, they never see this info until the doc is printed (and then they say "cool!" and wonder where it came from). What I'm looking for is a way of automatically including the filename (without path) in A1 of each worksheet (and tab in A2) so that it appears on screen in normal view. It may seem like a weird little technical puzzle but it would actually be a pretty big help given the volume of projects / files in question.

    Any more help / suggestions?

    Thanks,

    John
     
  6. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    OK, trying another way.

    =CELL("filename") should give you: path -- file name -- tab name.

    The file name within that should be appear in the "format" [filename.xls].

    So =FIND("]",CELL("filename")) should give you the position of the right bracket within that.

    So =LEFT(CELL("filename"),FIND("]",CELL("filename"))-1) should give you everything up to the right bracket (note the "-1").

    Then =FIND("[",CELL("filename")) should give you the position of the left bracket.

    From there, using a "default" num_chars of 256 for MID should "facilitate":

    =MID(LEFT(CELL("filename"),FIND("]",CELL("filename"))-1),FIND("[",CELL("filename"))+1,256)

    It's a question of building it in stages with some trial & error thrown in until you get to the desired end result, TBH.

    HTH
     
  7. dzonko

    dzonko Thread Starter

    Joined:
    Aug 24, 2003
    Messages:
    70
    Beautiful - thanks.
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/611106

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice