Information on one tab to another

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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.
 
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
 
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.
 
Back
Top