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



## dzonko (Aug 24, 2003)

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


----------



## bomb #21 (Jul 1, 2005)

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?


----------



## dzonko (Aug 24, 2003)

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


----------



## bomb #21 (Jul 1, 2005)

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


----------



## dzonko (Aug 24, 2003)

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


----------



## bomb #21 (Jul 1, 2005)

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


----------



## dzonko (Aug 24, 2003)

Beautiful - thanks.


----------

