Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A Formula for tracking Data on It's own tab in my spreadsheet?
I'm familliar with the following process to create links and transfer data to
other tabs in on active spreadsheet: Hit the = key in the cell (on the tab) that you want the information to appear in. Go to the tab and cell you want to transfer the relevant data from Hit the ENTER key €śLink€ť created and changes to the cell containing the original value or formula will affect the cell to which its been brought forward. However, I'm attempting to clean up my spreadsheet for better clarity and ease of reading. I would like to do the following: Continue to have the "Master Tab" contain all of the relevant client information, and flags (identifying when I have to follow certain procedures). Create a unique tab (on the same spreadhseet) for all statistical tracking (e.g. number of client's referred to treatment, number of client's seen during a specific month or year, etc). I didn't know if there was a formula I could use which would allow me to do this outside of the standard "linking" process above. I still need to pull my various formulas from the "Master Tab" (e.g. Countif, array's, etc) to create the needed "stats", but I'd like to keep this "statistical Information" off of the Master Tab itself. Thus, if the formulas developed on "Tab 2" (for example) could grab the needed information from the Master Tab (Tab 1) my desired outcome would be accomplished. Thanks for any help! Dan |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A Formula for tracking Data on It's own tab in my spreadsheet?
If you currently have the formulae in your Master sheet, then you can
cut and paste (i.e. move) them to your other sheet - the references will be preserved. Hope this helps. Pete On Jul 5, 11:20 am, Dan the Man wrote: I'm familliar with the following process to create links and transfer data to other tabs in on active spreadsheet: Hit the = key in the cell (on the tab) that you want the information to appear in. Go to the tab and cell you want to transfer the relevant data from Hit the ENTER key "Link" created and changes to the cell containing the original value or formula will affect the cell to which it's been brought forward. However, I'm attempting to clean up my spreadsheet for better clarity and ease of reading. I would like to do the following: Continue to have the "Master Tab" contain all of the relevant client information, and flags (identifying when I have to follow certain procedures). Create a unique tab (on the same spreadhseet) for all statistical tracking (e.g. number of client's referred to treatment, number of client's seen during a specific month or year, etc). I didn't know if there was a formula I could use which would allow me to do this outside of the standard "linking" process above. I still need to pull my various formulas from the "Master Tab" (e.g. Countif, array's, etc) to create the needed "stats", but I'd like to keep this "statistical Information" off of the Master Tab itself. Thus, if the formulas developed on "Tab 2" (for example) could grab the needed information from the Master Tab (Tab 1) my desired outcome would be accomplished. Thanks for any help! Dan |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A Formula for tracking Data on It's own tab in my spreadsheet?
Thanks for the suggestion Pete! I tried that previously, and none of the
referernces are preserved unforuntaely. All numerical outcomes are at "0", and percentage outcomes give me the "#DIV/0!" reference. I also checked the individual cells to see what the formula looked like upon copy and paste to another tab (in the same spreadshhet), and unfortunately "cut and paste" is not referencing the original tab where the data is coming from (only rows and columns on the tab where the information has been pasted to). I also want to insure that as data collects and changes through the months and years, that the tab containing the data will stay up to date (versus cutting and pasting only a snapshot of information which would be true just for that moment). I appreciate your feedback however! Best, Dan "Pete_UK" wrote: If you currently have the formulae in your Master sheet, then you can cut and paste (i.e. move) them to your other sheet - the references will be preserved. Hope this helps. Pete On Jul 5, 11:20 am, Dan the Man wrote: I'm familliar with the following process to create links and transfer data to other tabs in on active spreadsheet: Hit the = key in the cell (on the tab) that you want the information to appear in. Go to the tab and cell you want to transfer the relevant data from Hit the ENTER key "Link" created and changes to the cell containing the original value or formula will affect the cell to which it's been brought forward. However, I'm attempting to clean up my spreadsheet for better clarity and ease of reading. I would like to do the following: Continue to have the "Master Tab" contain all of the relevant client information, and flags (identifying when I have to follow certain procedures). Create a unique tab (on the same spreadhseet) for all statistical tracking (e.g. number of client's referred to treatment, number of client's seen during a specific month or year, etc). I didn't know if there was a formula I could use which would allow me to do this outside of the standard "linking" process above. I still need to pull my various formulas from the "Master Tab" (e.g. Countif, array's, etc) to create the needed "stats", but I'd like to keep this "statistical Information" off of the Master Tab itself. Thus, if the formulas developed on "Tab 2" (for example) could grab the needed information from the Master Tab (Tab 1) my desired outcome would be accomplished. Thanks for any help! Dan |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A Formula for tracking Data on It's own tab in my spreadsheet?
Perhaps the following with get you going in the right direction:
Tools Macro Record New Macro Recorder Shortcut Key = Ctrl + (it can be any letter). Then, Data Import External Data Import Data. Do what you need to do here€¦ This can be your source data€¦. It will be refreshed whenever you click your Control Button to import fresh data. Ive used this technique multiple times and it has served me well. Remember, just reference this sheet, and all of your references will be preserved. Hope that helps. Regards, Ryan--- -- RyGuy "Dan the Man" wrote: Thanks for the suggestion Pete! I tried that previously, and none of the referernces are preserved unforuntaely. All numerical outcomes are at "0", and percentage outcomes give me the "#DIV/0!" reference. I also checked the individual cells to see what the formula looked like upon copy and paste to another tab (in the same spreadshhet), and unfortunately "cut and paste" is not referencing the original tab where the data is coming from (only rows and columns on the tab where the information has been pasted to). I also want to insure that as data collects and changes through the months and years, that the tab containing the data will stay up to date (versus cutting and pasting only a snapshot of information which would be true just for that moment). I appreciate your feedback however! Best, Dan "Pete_UK" wrote: If you currently have the formulae in your Master sheet, then you can cut and paste (i.e. move) them to your other sheet - the references will be preserved. Hope this helps. Pete On Jul 5, 11:20 am, Dan the Man wrote: I'm familliar with the following process to create links and transfer data to other tabs in on active spreadsheet: Hit the = key in the cell (on the tab) that you want the information to appear in. Go to the tab and cell you want to transfer the relevant data from Hit the ENTER key "Link" created and changes to the cell containing the original value or formula will affect the cell to which it's been brought forward. However, I'm attempting to clean up my spreadsheet for better clarity and ease of reading. I would like to do the following: Continue to have the "Master Tab" contain all of the relevant client information, and flags (identifying when I have to follow certain procedures). Create a unique tab (on the same spreadhseet) for all statistical tracking (e.g. number of client's referred to treatment, number of client's seen during a specific month or year, etc). I didn't know if there was a formula I could use which would allow me to do this outside of the standard "linking" process above. I still need to pull my various formulas from the "Master Tab" (e.g. Countif, array's, etc) to create the needed "stats", but I'd like to keep this "statistical Information" off of the Master Tab itself. Thus, if the formulas developed on "Tab 2" (for example) could grab the needed information from the Master Tab (Tab 1) my desired outcome would be accomplished. Thanks for any help! Dan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sending Excel Spreadsheet with Change Tracking | Excel Discussion (Misc queries) | |||
How do I create a payment tracking spreadsheet? | Excel Discussion (Misc queries) | |||
Tracking who accesses a spreadsheet | Excel Discussion (Misc queries) | |||
how to do a recruiting and tracking spreadsheet | Setting up and Configuration of Excel |