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

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!

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

Use the INDIRECT function for your linking formulas.

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


Gord Dibben MS Excel MVP


On Wed, 6 Jun 2007 12:59:01 -0700, NineuhtOK
wrote:

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!

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

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...)


"Gord Dibben" wrote:

Use the INDIRECT function for your linking formulas.

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


Gord Dibben MS Excel MVP


On Wed, 6 Jun 2007 12:59:01 -0700, NineuhtOK
wrote:

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!

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

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!!



"shellseekerok" wrote:

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...)


"Gord Dibben" wrote:

Use the INDIRECT function for your linking formulas.

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


Gord Dibben MS Excel MVP


On Wed, 6 Jun 2007 12:59:01 -0700, NineuhtOK
wrote:

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!

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

Enter the formula in A1 of a sheet and drag/copy down 1300 rows.


Gord

On Fri, 8 Jun 2007 07:41:03 -0700, shellseekerok
wrote:

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...)


"Gord Dibben" wrote:

Use the INDIRECT function for your linking formulas.

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


Gord Dibben MS Excel MVP


On Wed, 6 Jun 2007 12:59:01 -0700, NineuhtOK
wrote:

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!

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

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!



"Gord Dibben" wrote:

Enter the formula in A1 of a sheet and drag/copy down 1300 rows.


Gord

On Fri, 8 Jun 2007 07:41:03 -0700, shellseekerok
wrote:

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...)


"Gord Dibben" wrote:

Use the INDIRECT function for your linking formulas.

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


Gord Dibben MS Excel MVP


On Wed, 6 Jun 2007 12:59:01 -0700, NineuhtOK
wrote:

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!

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

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


Gord


On Fri, 8 Jun 2007 14:31:00 -0700, shellseekerok
wrote:

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!



"Gord Dibben" wrote:

Enter the formula in A1 of a sheet and drag/copy down 1300 rows.


Gord

On Fri, 8 Jun 2007 07:41:03 -0700, shellseekerok
wrote:

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...)


"Gord Dibben" wrote:

Use the INDIRECT function for your linking formulas.

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


Gord Dibben MS Excel MVP


On Wed, 6 Jun 2007 12:59:01 -0700, NineuhtOK
wrote:

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!