 You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
I know it's a friday but I could do with some help. 🙂
Ok, so I have a sheet with various data about different jobs in it, the first column of that sheet is use for a flag relating to each job. e.g. Each row is something like:
flag. info. info. info. info..... etc.
The flag cell contains either the flag or nothing.
In a different sheet I need to pull out all the info about all of the jobs with the flag. I have tried it with simple IF statements, but for the jobs with no flag you end up with a load of #VALUE cells in the other sheet, looks terrible. Also, tried a method using VLOOKUP which works ok, but I get some duplicates. Could do with using a For loop to go through each cell in the first column and return all data from the row if the flag is found. Not sure on the best way of putting that into excel though. Any pointers would be much appreciated!
Cheers
Auto filter using the flag?
try an ISNA in that there formula..
IF(ISNA(blah,blah,blah...
Why not just sort the sheet by the flag column & cut & paste?
You need a pivot table me thinks. Other thank that it's array formulas which to be honest i have never really understood...
and to get rid of errors being returned when using IF you can use IF(A1="","",xxxxxx) inserting your original IF function in xxxxx and where A1 is the cell you are looking at. That way it will just be a blank cell rather than those ugly errors
Cheers for the quick responses!
The sheet is in date order, so cant really sort it by the flag. Basically the less manual work I have to do the better, so ideally wouldn't need to copy/paste anything.
Ideal solution would be as soon as i manually update the sheet with the job details on the other sheet would automatically update. I will have a look as a few of other suggestions now. 🙂
A simple VB script that runs an auto filter, then copys and pastes to a new sheet will deal with new records, but how much of a hassle just to autofilter and copy and paste is it? 10 seconds work.
VBA?
Sub JobSort()
Range("A1").Select
Do
If ActiveCell.Value = "insert flag value" Then
ActiveCell.EntireRow.Select
Selection.Copy
Sheets("insert destination worksheet name").Select
ActiveSheet.Range("A1048576").End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Sheets("original worksheet name").Select
End If
ActiveCell.Offset(1,0).Select
Loop until IsEmpty(ActiveCell.Offset(0,1))
End sub
isna is what you need to clear up your #value. for instance
=isna(if(3/b1=3,"hello","bob"),"nothing there")