How 2 Link DATA instead of CELL REFERENCE - Pulling My Hair Out!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Here Goes...long story short, I am trying to make Excel work a little like
Access. On first tab of my worksheet labeled "All" is all my data.
Additional tabs contain links of the data from the 'All' tab1 (Party
Invite-tab 2, Golf Invite-tab 3, Alumni-tab 4, etc.)

However...when I sort the data on the 'All' tab, the links pasted on the
other tabs continue to reference the pre-sorted ROW/COLUMN from the 'All' tab
instead of the post-sorted DATA. Therefore data from the links on the other
tabs are useless to me.

Is there a formula to link the DATA in the cell rather than linking the CELL
REFERENCE so that when the 'All' tab is sorted, the links pasted on the other
tabs are updated correctly?

Thanks for your suggestions!
 
Use the INDIRECT function for your linking formulas.

=INDIRECT("All!A"&ROW())


Gord Dibben MS Excel MVP
 
Ok...however can this be done on a 'mass scale' rather than for each
individual row? I have over 1300 rows....(please say
yes...please....please...)
 
Part II

OK. Found a way to use 'non' static cell references to do what I need to do.
NOW....
Is there an array, formula, macro to automatically copy [ using a non-static
cell reference] newly added rows of data from my 'All' tab to the
appropriate "Alumni", "Holiday Invite" or "Golf Invite" tabs, based on the
data in column A1?

For example, if the new row has "Holiday Invite" in column A1 is there a way
to automatically have the entire row added to the "Holiday Invite" tab using
a macro assigned to a button or by running a function or formula?

Many, many thanks to whomever can help me out!!
 
Gord - Thank you SO MUCH for your help. You're making my life so much easier.

Now, Part III....

For the 'non-static' data pasted on the 'linked' tabs, I'd like to be able
to show a blank cell, instead of a '0' (zero) if the source cell on the "All"
tab is blank.

Is there a way to imbed a function, create an array or some such to achieve
this?

Thanks again!
 
Back
Top