In Microsoft Excel 2013, I have a list of companies on the main sheet, and details for the companies as different sheets. I am trying to use the front page to reference cells in the company's individual work sheets.
For the most part this is working. The typical formula is ='Company Name'!E52:F52, and this works most of the time! I even learned how to deal with apostrophe's in the work sheet names.
My issue now is there is a company with an ampersand (&) in their name. When I follow the thought process from my previous question, Excel usually asks me to update the link in another file - That is, if I type &&, '&, or even \&.
Using Names for the combined cells is not working, as per the pictures below:How can I reference cells in another worksheet when the worksheet title contains an Ampersand?
2 Answers
By defining a name, you don't have to worry about special characters.
Sheet1 refers to a sheet named, "Tom & Sons".
Selecting A1 in sheet "Tom & Sons", then naming the selection "TOMANDSONS".
Now you can refer to TOMANDSONS which is now a reference pointer.
However, if I refer to cell A1 in sheet "Tom & Sons" as: ='Tom & Sons'!A1 it still works.
In addition, when referring to a formula you'd set it up like this:
Sheet1 -> Sum the region in Sheet'MCAT'.
I can sum the region in two ways:
- =SUM('MCAT'!A1:B5) or (see image)
More to the point, though the point can seem to move with posters, but for anyone reading the question in its future...
There isn't the least little problem here. Not sure what the poster did to create one, but it wasn't any straightforward usage.
You simply type the ampersand in where it belongs. Assuming you typed the REST of it correctly, it comes out right. So ='Jones & Kosby'!A1 does just fine.
Side note: lots of people jump right on "the new thing" as being the cause of a problem before making sure all the "old things" actually were done right. Easy to have dropped one of those single apostrophes, for example, but it must be the ampersand 'cause "that's the only thing I changed." Have to just say NO to that... um... "step"... in things.
Wants to refer to the cell on the ampersand sheet using a company name typed in some cell on the first sheet? Perhaps it's in D1. Then the perfectly normal =INDIRECT("'"&D1&"'!A1") again does the trick. NOTHING odd in there, not even something that needs extra double quoting. Just replace the whole company name with the cell to find the company name in and add some double quotes to set off the beginning and ending strings, and INDIRECT() is very happy to do what you want in a perfectly straightforward manner.
That all said, I do like using Named Ranges (Defined Names) to make my formulas much clearer. Not necessarily easier to maintain as it can be obnoxious to upgrade some 5,000 character formula in the Named Range, but oh so much clearer for anyone reading the formulas.
But there never was any problem in this question unless something I cannot find is missing. (Time does see things deleted here so maybe, but honestly, the way the phrasing goes suggests that is NOT the case.) Again though, there was never any problem with what he wished to do.
