A Formula for tracking Data on It's own tab in my spreadsheet?

[Dan the Man]

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

 

[Pete_UK]

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

 

[Dan the Man]

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

 

[ryguy7272]

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. I’ve 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---