Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel: with an if condition display a row of cell if true
Hello,
I am a relatively new user and I was asked to set up a contact list for my office. I would like to have one master list on the first worksheet and then kind of query different sections into new worksheets. Is this possible? For example in the main worksheet i would have name, region and supervisor. In the second worksheet I would want only Peel Region enteries to be displayed. The main goal is: If a change something on the first worksheet I would like the other worksheets altered automatically. PLEASE HELP ME. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel: with an if condition display a row of cell if true
Copy the data that you want from the main worksheet and then paste it into the other worksheet but don't just paste it, select "Paste Special" then select "paste as link" hit OK. HTH JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261 View this thread: http://www.excelforum.com/showthread...hreadid=495632 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel: with an if condition display a row of cell if true
Thanks but I want to Query the worksheet. For ex If I add a new listing to
the master worksheet I want it to automatically add itself to the second worksheet. Can this be done? "pinmaster" wrote: Copy the data that you want from the main worksheet and then paste it into the other worksheet but don't just paste it, select "Paste Special" then select "paste as link" hit OK. HTH JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261 View this thread: http://www.excelforum.com/showthread...hreadid=495632 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel: with an if condition display a row of cell if true
Yes, one way is to do what I told you, but instead of selecting a few rows select the entire column then paste into the other worksheet as "link" Paste Special/Paste Link. Another way is to type the formula directly into the other worksheet, somethink like: =IF(SHEET1!B1="","",SHEET1!B1) then copy down as needed. HTH JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261 View this thread: http://www.excelforum.com/showthread...hreadid=495632 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel: with an if condition display a row of cell if true
ok the formula would be for the enite column in the worksheet 2, right?
so, =IF(SHEET1!B1="Peel","",SHEET1!B1) what else do i do? in the second quotations what should i put? so that in worksheet 1, the column labelled Region, if there are peel entries the entire line of data will show in worksheet 2 SORRY AND THANK YOU!!!!!!!! "pinmaster" wrote: Yes, one way is to do what I told you, but instead of selecting a few rows select the entire column then paste into the other worksheet as "link" Paste Special/Paste Link. Another way is to type the formula directly into the other worksheet, somethink like: then copy down as needed. HTH JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261 View this thread: http://www.excelforum.com/showthread...hreadid=495632 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel: with an if condition display a row of cell if true
Actually your formula should look like this: =IF(Sheet1!B1="Peel",Sheet1!B1,"") here are the basics: =IF(condition, if true, if false) but I would go with this: =IF(Sheet!B1="","",Sheet1!B1) open a blank worksheet inside your workbook then select any column and while the column is still selected type my formula in the formula bar then hold down the CTRL key and hit Enter, that will copy the formula in the entire column. Then come back and let us know if that is what your looking for. Note. the quotations are use to leave a cell blank or to check if a cell is blank. Regards JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261 View this thread: http://www.excelforum.com/showthread...hreadid=495632 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel: with an if condition display a row of cell if true
ok so thats now showing me just peel. I need the entire row of info along
with the "check" cell (ie Peel). also there are spaces between the rows (where it doesn't meet the criteria) can i shift them up so that they're are no blanks? "pinmaster" wrote: Actually your formula should look like this: =IF(Sheet1!B1="Peel",Sheet1!B1,"") here are the basics: =IF(condition, if true, if false) but I would go with this: =IF(Sheet!B1="","",Sheet1!B1) open a blank worksheet inside your workbook then select any column and while the column is still selected type my formula in the formula bar then hold down the CTRL key and hit Enter, that will copy the formula in the entire column. Then come back and let us know if that is what your looking for. Note. the quotations are use to leave a cell blank or to check if a cell is blank. Regards JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261 View this thread: http://www.excelforum.com/showthread...hreadid=495632 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel: with an if condition display a row of cell if true
I just had to do something similar at work. I linked my main worksheet to a second worksheet by placing an "X" (or, in your case, "peel") in a column (in my case below, column N) for the entries which I wanted to link to the second worksheet. Here is a sample of the code I used (just apply it to each cell or group of cells in your second sheet for the data that you want linked to your second sheet): =IF('Active Matters'!$N3="X",'Active Matters'!A3,"---") Unfortunately, you can't get rid of the blank rows because they are directly linked to the same rows in your original sheet (or, if someone knows how to reconcile this, please feel free to respond). For my sheet, I didn't want to collapse the rows incase I do put an "X" for those rows on my main sheet to appear on the second sheet in the future, but that might work for your purposes if your sheet isn't constantly fluctuating and growing, like mine is. To make my second sheet look cleaner instead of displaying blank rows, I entered subsquent code so that "---" displays (instead of the code itself or a blank line, which would look busy or confusing). Probably a good idea to lock your cells and password protect the code on the sheet that you are linking to. Once your sheets are working, be careful not to delete any rows or columns in either sheet, and do not insert rows or columns using the insert feature (instead, type all new entries at the very bottom and then sort - use the same practice for deleting data), or else the cells become linked to the wrong cells on your second sheet. Hope this helps. Katy "Seema Yadav" wrote: ok so thats now showing me just peel. I need the entire row of info along with the "check" cell (ie Peel). also there are spaces between the rows (where it doesn't meet the criteria) can i shift them up so that they're are no blanks? "pinmaster" wrote: Actually your formula should look like this: =IF(Sheet1!B1="Peel",Sheet1!B1,"") here are the basics: =IF(condition, if true, if false) but I would go with this: =IF(Sheet!B1="","",Sheet1!B1) open a blank worksheet inside your workbook then select any column and while the column is still selected type my formula in the formula bar then hold down the CTRL key and hit Enter, that will copy the formula in the entire column. Then come back and let us know if that is what your looking for. Note. the quotations are use to leave a cell blank or to check if a cell is blank. Regards JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261 View this thread: http://www.excelforum.com/showthread...hreadid=495632 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
enhanced conditional formatting | Excel Discussion (Misc queries) | |||
Display cell numbers/letters on top and sides of an excel documen. | Excel Worksheet Functions | |||
Why does Excel display 01/12/2005 as 38364 when the cell value is. | Excel Discussion (Misc queries) | |||
how do I display a hyperlink in a seperate cell in excel? | Excel Discussion (Misc queries) |