Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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! |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel formula i'm pulling my hair out | Excel Discussion (Misc queries) | |||
Pulling my hair out here | Excel Worksheet Functions | |||
Pulling my hair out, need some help building a formula | Excel Worksheet Functions | |||
combo box problem I am pulling my hair out about!!!! | Excel Worksheet Functions | |||
Pulling hair out with VLOOKUP | Excel Worksheet Functions |