indirect?
hi guys , this is my problem: i got two worksheets and sheet 1 has got datas and sheet two is exactly as sheet 1 and the datas are reference to sheet 1. so in sheet 1 A1, the value is 5 in sheet 2 A1 , the formula used is =sheet1!A1 the formula applies for the column A in sheet 2 . the problem is : if i delete any rows( shift rows up) in sheet 1 , sheet 2 will return #REF! Is there any way of preventing that? -- cjjoo ------------------------------------------------------------------------ cjjoo's Profile: http://www.excelforum.com/member.php...o&userid=26916 View this thread: http://www.excelforum.com/showthread...hreadid=487511 |
indirect?
Hi!
Try this: =INDIRECT("Sheet1!A1") That will always refer to Sheet1 A1 no matter if you insert rows or delete rows. Biff "cjjoo" wrote in message ... hi guys , this is my problem: i got two worksheets and sheet 1 has got datas and sheet two is exactly as sheet 1 and the datas are reference to sheet 1. so in sheet 1 A1, the value is 5 in sheet 2 A1 , the formula used is =sheet1!A1 the formula applies for the column A in sheet 2 . the problem is : if i delete any rows( shift rows up) in sheet 1 , sheet 2 will return #REF! Is there any way of preventing that? -- cjjoo ------------------------------------------------------------------------ cjjoo's Profile: http://www.excelforum.com/member.php...o&userid=26916 View this thread: http://www.excelforum.com/showthread...hreadid=487511 |
indirect?
If i use the indirect formula, i cant just click and drag for the different rows i am refering to . Is there a solution to this? -- cjjoo ------------------------------------------------------------------------ cjjoo's Profile: http://www.excelforum.com/member.php...o&userid=26916 View this thread: http://www.excelforum.com/showthread...hreadid=487511 |
indirect?
Hi!
Try this: =INDIRECT("Sheet1!A"&ROWS($1:1)) Biff "cjjoo" wrote in message ... If i use the indirect formula, i cant just click and drag for the different rows i am refering to . Is there a solution to this? -- cjjoo ------------------------------------------------------------------------ cjjoo's Profile: http://www.excelforum.com/member.php...o&userid=26916 View this thread: http://www.excelforum.com/showthread...hreadid=487511 |
indirect?
what i am doing is that i want to picked out some datas in sheet 1 that fulfil a certain criteria(e.g those that has "apples") but in sheet two, with the indirect function, i cant delete the blank rows . Is there a way out for this? the scenario: sheet 1: apples oranges apples orange sheet 2 apples apples in sheet two , i want to delete the empty rows but i realised that it is not possible. Pls advise -- cjjoo ------------------------------------------------------------------------ cjjoo's Profile: http://www.excelforum.com/member.php...o&userid=26916 View this thread: http://www.excelforum.com/showthread...hreadid=487511 |
indirect?
You could use a formula like this in sheet 2
IF(ISERROR(SMALL(IF(Sheet1!$A1:$A20="apples",ROW($ A1:$A20),""),ROW($A1:$A20) )),"", INDEX(Sheet1!$A$1:$A$20,SMALL(IF(Sheet1!$A1:$A20=" apples",ROW($A1:$A20),""), ROW($A1:$A20)))) To enter it, select the number of rows you think you might need for items to be copied, then enter the formula in the formula bar, and commit it with Ctrl-Shift-Enter -- HTH RP (remove nothere from the email address if mailing direct) "cjjoo" wrote in message ... what i am doing is that i want to picked out some datas in sheet 1 that fulfil a certain criteria(e.g those that has "apples") but in sheet two, with the indirect function, i cant delete the blank rows . Is there a way out for this? the scenario: sheet 1: apples oranges apples orange sheet 2 apples apples in sheet two , i want to delete the empty rows but i realised that it is not possible. Pls advise -- cjjoo ------------------------------------------------------------------------ cjjoo's Profile: http://www.excelforum.com/member.php...o&userid=26916 View this thread: http://www.excelforum.com/showthread...hreadid=487511 |
All times are GMT +1. The time now is 06:21 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com