Is your computer an Open Invitation for HACKERS? Download free version of Sparktrust Inspector to identify vulnerabilities and problems on your computer (and even your WEBSITES)!
I have a couple of spreadsheet questions. 1.) I have two worksheets. How can I make a calculation in one worksheet and have the second worksheet automatically updated with the new information? 2.) I have a worksheet with conditional formatting for the color pattern (based on Number in each cell). I would like to have the same cell color patterns for the Variance which is stored in another worksheet. How do I copy the same color pattern to a separate worksheet only without the conditional formatting?
Linking Excel Worksheets
The ability to link two worksheets in Excel is useful as changes made in the first worksheet are automatically reflected in the second worksheet. When used correctly, this can ensure that all related worksheets are based on the most current information. For example, if you have a master spreadsheet that contains current prices for your company's products and each salesperson used a linked spreadsheet to create quotes, whenever you update the master price list, the spreadsheets used for quoting prices would automatically have the latest prices.
Here's how to do it:
· Go to the primary worksheet and select the cell (or range of cells) that you want to link to the secondary worksheet. Use the "copy" command to copy the cell or cells to the clipboard.
· Go to the secondary worksheet and select the cell that should contain the linked data. Use the "Paste Special" icon to open the Paste menu.
· Choose "Paste Link."
Is your computer an Open Invitation for HACKERS? Download free version of Sparktrust Inspector to identify vulnerabilities and problems on your computer (and even your WEBSITES)!
Now, whenever you change the value of the primary cell or cells, the secondary cell or cells in your other worksheet will also be changed. The underlying formula looks similar to this (with B2 representing the specific cell location):
='[WorksheetName.xlsx]CellLabel'!B2
Copying Conditional Formatting
As far as copying the conditional formatting to another worksheet goes, you have a few options. The first is to use the Format Painter tool. However, while the Format Painter will apply the color pattern to your new list, it will also apply all other formatting options which it sounds like you do not want.
The next option is to see if the original conditional formatting color pattern is a stock "color scale." If so, choose that same color scale for your new list. To do this, you'll need to click Conditional Formatting > Color Scales and then look for your pattern in the list.
A third option involves using the Rules Manager to create a new rule based on the existing one. This is more complicated and will involve some experimentation. To do view an existing rule, highlight your original list, click Conditional Formatting > Manage Rules > Edit Rule. This will show you the exact settings for the current rule. Write down the specifics and then go into the new worksheet where you can try recreating the rule and tweaking it to meet your specific needs. Highlight the cells and click Conditional Formatting> New Rule and try to recreate the color scale and add any other specific rules you'd like to apply.
Is your computer an Open Invitation for HACKERS? Download free version of Sparktrust Inspector to identify vulnerabilities and problems on your computer (and even your WEBSITES)!



