I have a workbook with a "master worksheet" and 4 worksheets that reference all the information in the master. The purpose of the master sheet requires resorting on a quarterly basis. My issue is the format of the referenced cells on the other 4 worksheets. The resort changes the position of the referenced information but it does not pick up all the formats which leaves me with miss matched formats between the master and the other sheets. Can I use a function to reference a cells value and its formats?
To specifically answer your last question on whether you can reference a cell's format, the answer is yes, by using the
CELL function in your formula.
The values that this function returns are listed on this page:
You could try putting together a hack macro that resets your cell references. If the position changes you'll need to get Excel to locate whatever column/row that you are interested in using some sort of search.
Another way is to leave the master spreadsheet alone and recreate the 4 other guys each time you get an updated master, all with VBA.
I think I have found a way around it --
- Copy source cell
- right click, paste special, use "Linked Picture"
the catch is:
the result will only show the area within the source cell. i.e if your source cell's text exceeds the width, it will only show the parts that's in the area of the source cell. just ensure that your source column is wide enough and it'll be ok
I'm using MS 2010 :) hope this will be helpful!
This may look a little cumbersome, but it is fairly quick, especially for keyboard users. Maybe MS will come up with an option to paste links WITH formatting soon. But this method also still allows content of formulas - NOT original text to be searchable, as well as accessible for the blind and others who use screen readers, whereas pasting as a picture does not.
First, Copy Formatting
Hold down Ctrl and press A three times (pressing A the first time selects all text; the second time it selects the entire sheet; the third time, it selects the formatting)
A. Then press Ctrl+C to copy
- Paste it into the new sheet with Ctrl+V. Although this TEMPORARILY keeps the text as-is, you have now pasted any cell-sizing, borders, fill, font preferences, and so forth.
Next, Delete Cells With Extraneous Data
- Select blocks of cells that may have data unnecessary to the new sheet or workbook
- Where possible, delete entire rows and columns. One or more rows can be quickly selected by highlighting one or more cells within that block of extraneous data, then pressing Shift+Spacebar
- Alternately, select a group of columns by highlighting one or more cells and pressing Ctrl+Spacebar
- Delete those cells by pressing Alt, and while letters still appear along the top, press E (edit). On newer versions of Office, a message will appear telling you to continue typing. Then press D for Delete. This is the old keyboard shortcut to delete cells, and it still works in Office 2016 (thank you, Microsoft!)
Note: If you are not deleting one or more entire rows or columns, you will be prompted to choose which direction you want to shift the remaining cells
Now Delete All Data to Make Room for Links to Original Spreadsheet
- Press Ctrl+A (just once this time)
- Press Delete
Finally, Select and Link Desired Data
- Back to the original spreadsheet, select and copy the data you wish to link to, noting that you may be selecting blank cells in the upper left. This is fine
- Go to the first desired cell on the new sheet (remember, it may be an empty cell), and choose Home > Paste > Special and choose to Link the data
- If you accidentally pasted in the wrong spot, note where the top left cell should be, then Delete or Ctrl+Z (undo), go to that cell, and Paste > Special > Link again