A formula to copy a row from one sheet to another if a certain number is entered in a cell

Please post a file showing expected results as many of use cannot view an (PNG) image: nor can we work with it!

Just uploaded my excel file. I've stripped all of the data out for obvious reasons.

Bump.

Could really use some help on this if anyone knows.

Ah, forgot about the password, its nightingale and to edit the document its test.

I just tried your suggestion and I get an error when I enter a number into column A on sheet 2 as seen below.

ERROR.PNG

I'm not very good with excel to be honest so it might be something simple but any help is appreciated.

I know I must sound like an idiot but I honestly don't know where to put this. I've never seen this format of formula before.

I don't think that formula is what I'm looking for. I need the formula to identify if I entered a specific number in column A in sheet2 that matched a number on column A on sheet1. If it matches, i need it to copy the entire row which had the number on it from sheet1 to sheet2.

Bump.

I've been told that this cannot be done using formulas, not sure if its true. If anyone has a macro to do what i need, that would be great.

Let me understand this:

if you enter for example 6354 in column A of Sheet2, you want the data for that row in Sheet1 to be put in Sheet2?

Yes, exactly.

Will data be deleted from Sheet2?

Or can we add a column in Sheet1 which indicates data needs to be moved to Sheet2 rather than entering a number in Sheet2?

VLOOKUP will work but if the data in column A of Sheet2 is left blank i.e. deleted, will be left with a blank row,

Once the data on sheet2 is completed and no longer a priority, it will be deleted since sheet2 is basically a priority list / short list. It would be preferred that we don't have to add anything to sheet1 but if there is no other way then we won't have much choice.

If there was a column in Sheet1 which had (for example) "A" (add to Sheet2) and "D" (delete) then it possible to make the listing in Sheet2 dynamic by checking Sheet1 for all records which have "A". As records are added/deleted (only need to check for "A" in reality) then the list in Sheet2 will expand/shrink accordingly.

Equally, you do not need to enter data into Sheet2: everything would work from Sheet1.

This is a fairly common way of addressing your need.

So, if i was to do this, how would i go about doing it? Do you have a formula / macro that would work for this? I'm honestly not great with excel so i would appreciate the help if you know.

Like i said, it would be preferable if i didn't have to add further columns to sheet1 but if it has to be done then we have no choice.

With "y" in column T of sheet1

put this in A2 of Sheet2

=IFERROR(INDEX(sheet1!B$2:B$100,SMALL(IF(sheet1!$T$2:$T$100="y",ROW($A$2:$A$10)-ROW($A$2)+1,""),ROWS($A$2:$A2))),"")

Enter with Ctrl+Shift+Enter (array formula)

Copy across and down columns.


Change range (100) to suit as many rows as you think is your likely maximum: don't do more than neessary

Non-array formula (less resource hungry)


=IFERROR(INDEX(sheet1!A$2:A$100,AGGREGATE(15,6,ROW($A$1:$A$100)/(sheet1!$T$2:$T$100="y"),ROW($A1))),"")

I tried what you suggested but it doesn't seem to be working. Its entirely possible I'm doing something wrong but I'm not sure.

As an additional request, we are thinking of setting up a 3rd sheet so is it a possibility to make it so if you enter a 2 in column T it would copy it to sheet2 and if you enter a 3 it would copy it to sheet3?

To answer your additional request:

Put the formula in Sheet2 and change the test to

=IFERROR(INDEX(sheet1!A$2:A$100,AGGREGATE(15,6,ROW($A$1:$A$100)/(sheet1!$T$2:$T$100="2"),ROW($A1))),"")

Put the formula in Sheet3 and change the test to

=IFERROR(INDEX(sheet1!A$2:A$100,AGGREGATE(15,6,ROW($A$1:$A$100)/(sheet1!$T$2:$T$100="3"),ROW($A1))),"")

re the "not working":

Both formulae were tested on your posted file and worked fine: did you do any changes to the formula?

Thanks for the additional formulas

I'm not sure what I'm doing wrong, i've tried both the array and non-array formulas that you sent and I can't get them for work.

Just to go through the steps of what I'm doing:
1. enter number 2 into column T on sheet1
2. paste formula into A2 on sheet2 with Ctrl + Shift + Enter
3. Drag the formula down column A.

Not sure what im doing wrong.

Drag ACROSS rows as well as down columns

@Tony,
Well - when to "array" and not to "array": that is the question!!!

I take it generally the array formulae are faster than AGGREGATE or just this (specific?) case?

I've dragged both across and down and still nothing : /

I can only suggest you post a small sample from the file which is causing the problem.

I've replaced the excel file attachment on the original post with the file I've added the formula to. Can you take a look and possibly see where I'm going wrong?

Remove the quotes from round the 2

=IFERROR(INDEX(sheet1!A$2:A$100,AGGREGATE(15,6,ROW($A$1:$A$100)/(sheet1!$T$2:$T$100=2),ROW($A1))),"")

The "2" is treated as TEXT whereas the value in T is a number. "Mea Culpa" as I did not do that in the formula in posted so my apologies. You did nothing wrong!

Did you see the post from Tony Valko (Forum Guru) who said the array formula is actually slightly faster so you may want to use that instead,

Well, that seems to have done it! Thanks very much for your help. Time to move this over to the original file and hope everything works well. Cheers

Again, my apologies: "schoolboy" error. Thank you for your patience.

If all is OK, could you mark the thread as solved ("Thread Tools" at top of first post)

I'm going to get everything into the original sheet, check everything works ok there and then I'll change it to solved if it all works.

Yes, that's OK.

@tony,
Thank you for the explanation.

I tried a non-scientific test (i.e, no clock timings) on a recent posting with 66,000+ rows and the INDEX version did seem to run quicker.