Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Information on one tab to another
I have my general information on a sheet in Excel. I have a colum "I", that
I am going to put a X in. So on sheet 2 I want only the rows that contain X in column I to carry over. I am not sure if this is one of the lookups or an if statement. Thanks Cheyenne |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Information on one tab to another
Either could be used, but you'd have to 'play tricks' with a VLOOKUP, so IF
is better. On the second sheet, something like (this in A2 on Sheet2 to retrieve A2 from Sheet 1 if I2 has an X: =IF(Sheet1!I2="X",Sheet1!A2,"") in B2 =IF(Sheet1!I2="X",Sheet1!B2,"") and so on across the first row. Nice thing about the formula(s) is that they will 'fill' to the right and down the sheet very nicely. You can use Data | Filter | AutoFilter to only display non-blank rows to keep things neat on the second sheet. "Chey" wrote: I have my general information on a sheet in Excel. I have a colum "I", that I am going to put a X in. So on sheet 2 I want only the rows that contain X in column I to carry over. I am not sure if this is one of the lookups or an if statement. Thanks Cheyenne |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Information on one tab to another
Messed up - realized it right after clicking [post]. Put a $ sign in front
of the I in the formulas as =IF(Sheet1!$I2... That will keep that column reference from changing as you fill the formula left to right across the sheet. The rest stays the same. "Chey" wrote: I have my general information on a sheet in Excel. I have a colum "I", that I am going to put a X in. So on sheet 2 I want only the rows that contain X in column I to carry over. I am not sure if this is one of the lookups or an if statement. Thanks Cheyenne |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Information on one tab to another
I did a little experimeting and I guess this wont work. What is happening is
Each day when a "provider" is flagged we put an "A" for day 1 and "B" for day 2 and so on. Since we don't work on the weekends A-Z will work just fine. What I need is a formula that says "A" it will pull the row of information that has an "A" in column I. Each time we want to filter by a different letter will just change the letter in the formula. I tired what you wrote and it works going down but not across. Should I be holding down a key on the keyboard or something? Thanks for you help Cheyenne "JLatham" wrote: Either could be used, but you'd have to 'play tricks' with a VLOOKUP, so IF is better. On the second sheet, something like (this in A2 on Sheet2 to retrieve A2 from Sheet 1 if I2 has an X: =IF(Sheet1!I2="X",Sheet1!A2,"") in B2 =IF(Sheet1!I2="X",Sheet1!B2,"") and so on across the first row. Nice thing about the formula(s) is that they will 'fill' to the right and down the sheet very nicely. You can use Data | Filter | AutoFilter to only display non-blank rows to keep things neat on the second sheet. "Chey" wrote: I have my general information on a sheet in Excel. I have a colum "I", that I am going to put a X in. So on sheet 2 I want only the rows that contain X in column I to carry over. I am not sure if this is one of the lookups or an if statement. Thanks Cheyenne |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Information on one tab to another
You must have missed my second posting, there was an error in the formula
that I posted first and I tried to get a 2nd post in with the fix. Because the Sheet1!I2 does not have a $ in front of the I (as !$I instead of just !I) it lets I change to J (and K and L ...) as you drag it across the sheet. Change that first formula to look like this (In A2 on Sheet2) =IF('Sheet1'!$I2 = "X", 'Sheet1'!A2,"") that formula is correct and will alter itself properly as you drag it across the sheet and/or down the sheet. Note that the single quote marks around the sheet name are only required if the sheet name has a space in it. It doesn't hurt to have them, and if Excel decides they are not needed, it just tosses them away anyhow. "Chey" wrote: I did a little experimeting and I guess this wont work. What is happening is Each day when a "provider" is flagged we put an "A" for day 1 and "B" for day 2 and so on. Since we don't work on the weekends A-Z will work just fine. What I need is a formula that says "A" it will pull the row of information that has an "A" in column I. Each time we want to filter by a different letter will just change the letter in the formula. I tired what you wrote and it works going down but not across. Should I be holding down a key on the keyboard or something? Thanks for you help Cheyenne "JLatham" wrote: Either could be used, but you'd have to 'play tricks' with a VLOOKUP, so IF is better. On the second sheet, something like (this in A2 on Sheet2 to retrieve A2 from Sheet 1 if I2 has an X: =IF(Sheet1!I2="X",Sheet1!A2,"") in B2 =IF(Sheet1!I2="X",Sheet1!B2,"") and so on across the first row. Nice thing about the formula(s) is that they will 'fill' to the right and down the sheet very nicely. You can use Data | Filter | AutoFilter to only display non-blank rows to keep things neat on the second sheet. "Chey" wrote: I have my general information on a sheet in Excel. I have a colum "I", that I am going to put a X in. So on sheet 2 I want only the rows that contain X in column I to carry over. I am not sure if this is one of the lookups or an if statement. Thanks Cheyenne |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
NEED INFORMATION | Excel Discussion (Misc queries) | |||
Information | Excel Discussion (Misc queries) | |||
Information | Excel Discussion (Misc queries) | |||
LOOK UP INFORMATION | Excel Worksheet Functions | |||
Name Box Information | Excel Discussion (Misc queries) |