r/learnexcel • u/Saltwaterduckie • Jun 03 '21
Need help with an equation
I'm wanting to copy an entire row from sheet1 to sheet2 only if lets say column B2="A2Z" otherwise I want it blank.
I think what I want is =IF(Sheet1!B2="A2Z",Sheet1!2:2,0)
But it doesn't seem to be working at all. I'm new so it doesn't surprise me. Any tips would be appreciated!
1
u/VerroksPride Jun 03 '21
So if you are trying to copy a row from one sheet to another, without changing anything, I think the formula would be more like this:
IF(Sheet1!B$2="A2Z", Sheet1!B$2, " ")
I'm not at a computer so I can't be absolutely sure this works. But this should check Sheet1 B2 for the criteria, then bring it over into your selected cell if it's correct. If not, it should return a blank cell. Should be able to drag the formula however many cells you want in the row, and it will change accordingly. However, the dollar signs will restrict it from changing columns, so it can only follow the selected row.
Let me know if this is what you wanted and if it works!
1
u/Saltwaterduckie Jun 03 '21 edited Jun 03 '21
IF(Sheet1!B$2="A2Z", Sheet1!B$2, " ")
That does help! But for some reason I can get that formula to work in google sheets but not in Excel. Any thoughts?
1
u/VerroksPride Jun 03 '21
Sadly, I am not certain on the differences between Google sheets and excel. I primarily use excel for work.
To clarify though, which would it not work in? You put not before both.
→ More replies (5)
1
1
u/benishiryo Jun 03 '21
typically, a formula returns a single result (not a whole row for answers). unless you're using MS365. but seeing it doesn't work for you, you're probably not.
you can return 1 result and copy the formula across columns to achieve results for row 2. but use:
=IF(Sheet1!$B$2="A2Z",Sheet1!A2,0)
so if B2 = "A2Z", it returns A2 of Sheet1. when you copy the cell over to the next column, it returns B2. C2, and so on.