Hi
I am working with a worksheet having data range from A1 to N10000.
I want to copy entire row from this sheet to second sheet automatically using a formula/function not VBA when a certain cell e.g. K100 in that row is blank.
Thanks
Hi
I am working with a worksheet having data range from A1 to N10000.
I want to copy entire row from this sheet to second sheet automatically using a formula/function not VBA when a certain cell e.g. K100 in that row is blank.
Thanks
Last edited by Mian USman; 04-14-2013 at 01:20 AM.
So in A1 of your second sheet, something like this would work for you?
=IF(Sheet1!$K$100="",Sheet1!A$1,"")
Copy across.
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Dear Fotis,
i am sorry that i could not make it clear.
i am attaching the sample file. Sheet 1 is the source sheet and i want the result on sheet 2 (as I mentioned) if J8 & J11 in sheet 1 is blank and so on. if i enter a number in J8 or J11 then in sheet 2 the row sould not appear.
Attached file is correct too, but not so good.
New way:
=IF('1'!$J2="",IF('1'!A2="","",'1'!A2),"")
I edit the formula,at sheet "2".
A2 =IF('1'!$J2="",IF('1'!A2="","",'1'!A2),"")
then expend it to area you want.
Last edited by wenqq3; 04-12-2013 at 04:33 AM.
-If the problem is solved, please mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved".
-Always upload a workbook before start your question
To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.
+++ If my answer(s) helped you, please add me reputation by click on * +++
So try this ARRAY formula in A2.
=IFERROR(INDEX('1'!A$2:A$1000;SMALL(IF('1'!$J$2:$J$1000="";ROW('1'!A$2:A$1000)-1);ROW('1'!A1)));"")
As you have empty cells, formula gives you many zeroes as result. So i have a Conditional formatting rule for zeroes numbers..
It worked.
Thanks a lot Fotis
I really appreciate your effort
Thanks again.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks