Tom's Guide | Tom's Hardware | Tom's Games
 |
 |
 |
Hi, I'm trying to set up a spreadsheet whereby data input into Cells C4 to C54 in worksheet 2 is mirrored/copied to Cells C7 to C57 in Worksheet 4, but automatically sorted into ascending order in Worksheet 4. Afraid I don't have any VB experience. Is this do-able without VB or should I just give up ? As I won't always be inputting 50 data points I would need it to not recognise blank cells within the C4 - C54 range in Worksheet 2 too.
Many Thanks in Advance
What kind of data?
Is it numbers or text or dates or what?
This seems to work for numbers. Put it in Sheet4!C7 and drag it down to Sheet4!C57.
=IF(NOT(ISERROR(LARGE(Sheet2!$C$4:$C$54,COUNTA(Sheet2!$C$4:$C$54)-(ROW()-7)))),LARGE(Sheet2!$C$4:$C$54,COUNTA(Sheet2!$C$4:$C$54)-(ROW()-7)),"")
0 
Thanks that works great. The only thing I am grappling with now is that I had a number of formulae linked to the cells in worksheet 4, that before showed blank if there was no value but now show #VALUE! or #NUM! even if there is no value in the sorted cells C7 to C57.
Before I had the following =IF(C40>=1E-100,((C40-C58)/C59)," "). What can I add to this to insert no value if no value in column C but if a value in Column C insert the outcome of the equation ,C40-C58/C59.
Thanks Again
0
First, the IF statement you are using does not "insert no value" into the cell. It actually inserts a space.
To insert "no value", use "" instead of " ".
The may look the same in the cell, but they are very different in the eyes of Excel.
To prove this to yourself, put this formula in B1:
=IF($A$1="",""," ")Put this in C1:
=LEN(B1)
When A1 is empty, B1 will look blank and C1 will display 0.
When you put something in A1, B1 will still look blank, but C1 will display 1, since there is now 1 character in B1.
This may not matter in your case, but it can make a big difference in other cases.
Second, is C40>=1E-100 an actual calculation or are you just using it to see if there is a value in C40?
If you just want to see if there is a value in C40, I would replace that with C40<>"", as in:
=IF(C40<>"",((C40-C58)/C59)," ")
Actually, that should be:
=IF(C40<>"",((C40-C58)/C59),"") unless you really want the space.
If you are really checking to see if the value is >=1E-100, then use the same method I used in the formula I suggested for your original question. The ISERROR function can be used in an IF statement to return different values depending on whether an error exists or not.
Use IF(ISERROR...) to return "" if the result of the formula would have been an error.
=IF(ISERROR((C40-C58)/C59),"",IF(C40>=1E-100,((C40-C58)/C59)," "))
Third,
*** Dad Mode On ***Since you were getting an error and didn't know how to fix it, that tells me that you blindly copied my first formula into your workbook without trying to figure out how it worked. If you had taken my suggested formula apart, you might have discovered how the ISERROR function worked, and may have made the connection that it could be used to fix your current issue. A good way to see how a formula works (or to determine why you are getting an error) is to use the Evaluate Formula feature found under Tools...Formula Auditing. With this feature you can use the Evaluate button to step through a formula and watch it calculate each portion until it either produces an error within the formula itself or shows the final result. As soon as see an error anywhere in the formula, that's the error that will be displayed in the cell, unless it is dealt with via the ISERROR function. Getting down into the details of how a suggested formula or macro works is a great way to learn Excel.
*** Dad Mode Off ***
Let me know if you have any further issues.
0
Thanks again and for this detailed reply. For the record I
did try to work through the formula as I managed to use it
elsewhere but in reverse, but admittedly I got a bit
confused by some of the functions. However, I think I've
learnt alot more than I knew yesterday so I appreciate it.
The >=1E-100 was intended to check if there was a value
so I'll change it to how you suggest.As I won't be at work for the weekend I won't get a
chance to try your suggestions until Monday.Thanks
0
Hi,
I've stumbled upon another problem and thought I'd try asking this forum again seeing as the advice given before has been very useful.
I've been trying to compile a formula but I keep getting an error that I can't figure out. I've tried variations and used the formula audit tool and clearly there are errors but I'm not too sure how to rectify them or in fact whether I'm using the correct function for what I want to do?
I have cell C63 in Worksheet 1 that is set up to say either "YES" or "NO" depending on the outcome of a formula in a different worksheet.
Then in Cell C65 (also worksheet 1) I would like this to show
"YES" if Cell C63 (worksheet 1) is "YES" and Cell E10 (Worksheet 3) is "YES"
"NO" if Cell C63 (worksheet 1) is "YES" and Cell E10 (Worksheet 3) is "NO"
"YES" if Cell C63 (worksheet 1) is "NO" and Cell E18 (Worksheet 3) is "YES"
and "NO" if Cell C63 (worksheet 1) is "NO" and Cell E18 (Worksheet 3) is "NO"
The formula I have been trying with is below. Apologies if this is obvious but I've tried different variations but I'm not getting anywhere with my limited knowledge of excel.
=IF((C63="YES")*('3. OUTLIER TEST'!E10="YES"),"YES",""),IF((C63="YES")*('3. OUTLIER TEST'!E10="NO"),"NO",""),IF((C63="NO")*(*'3. OUTLIER TEST'!E18="NO"),"NO",""),IF((C63="NO")*('3. OUTLIER TEST'!E18="YES"),"YES",""))
Many Thanks in advance
0
First, in an effort to keep the archives of forums of this type a bit more organized, it is best if you start a new thread when you have a new question. There are 2 main reasons for this:
1 - If folks are browsing the forum via Subject lines, it helps if the Subject matches the content. A person looking for info on constructing a Nested If statement would probably not open a thread entitled Excel Sorting.
2 - If someone has been ignoring a particular thread, they will never see the off-subject question. They wouldn't be able to help if they wanted to or they wouldn't learn from the answer.
Since this forum frowns on multiple posts with the same content, I'll take a shot at answering your question in this thread, but I'd like to suggest that in the future you start a new thread when you have a new question.
OK, without providing an exact solution to your problem, I'll give you the "concepts" and let you try to construct what you need.
It appears that you need a Nested If statement where each value_if_true is based on more than one criteria.
It looks like you are trying to use the * operator to AND criteria together. I think you should simply use the AND function.
IF(AND(A1="YES",B1="YES"),"YES",IF(AND(A1="YES",B1="NO"),"NO",IF(AND(etc...
0
Hi, I’m facing a problem similar to alaska of copying an array of data, say, A1:F40 (texts & numbers) from worksheet 1 to worksheet 2 with automatically sorting into ascending order. I tried using VB but didn’t work. Can anyone help?
Many Thanks.
0
I think we need a little more detail.
Copying A1:A40 with automatically sorting into ascending order makes sense, but how would you sort A1:F40 in ascending order?
Is the table sorted as a whole? If so, left to right, row by row or up and down, column by column?
If the table isn't sorted as a whole, is each column (or row) sorted individually?
0
You also said "texts & numbers".
How are numbers sorted with relation to text?
Maybe you could provide a small table (3 x 3?) showing the non-sorted and then the sorted result so we know what you are looking for.
0
For example, "5 x 4" table in Worksheet 1:
p 12 51 45
b 56 78 61
(no data) 12 96 100
(no data) 2 75 100
1 54 14 7Only a cell contains data in the 1st column will be copied in worksheet 2 and sorted automatically in ascending order.
1 54 14 7
b 56 78 61
p 12 51 45Many Thanks!
0
Do you see why I asked for an example?
Nothing in your first 2 posts said anything about not copying/sorting data if Sheet1 Column A didn't contain data, and it certainly wasn't clear that you were only sorting on Column A, i.e. not sorting all of the data "row by row".
The more detail you provide when asking a question, the better chance that you'll get a workable solution.
OK, so based on your criteria, I don't think it can be done with formulae.
This Worksheet_Change macro seems to work. If a change is made anywhere within the Sheet1 A1:F40 table, the Sheet2 A1:F40 table will be cleared and rebuilt based on the new data on Sheet1.
Let me know if it works for you.
Private Sub Worksheet_Change(ByVal Target As Range)
'Check to see if change was within the A1:F40 table
If Not Application.Intersect(Range(Target.Address), Range("A1:F40")) Is Nothing Then
'Clear existing data in table in Sheet2 A1:F40
Sheets(2).Range("A1:F40").ClearContents
'Loop through Sheet1 Table and Copy
'Rows With Data in Column A to Sheet2
For sRow = 1 To Sheets(1).Range("A1:F40").Rows.Count
If Cells(sRow, "A") <> "" Then
dRow = dRow + 1
Cells(sRow, "A").EntireRow.Copy Destination:=Sheets(2).Cells(dRow, "A")
End If
Next
'Sort Sheet2 A1:F40
Sheets(2).Range("A1:F40").Sort Key1:=Sheets(2).Range("A1"), Order1:=xlAscending
End If
End Sub
0
Hi,
At the beginning of this thread derbydad posted me the following formula which worked and solved my query.
=IF(NOT(ISERROR(LARGE('1.PLANNING SCENARIO'!$C$7:$C$56,COUNTA('1.PLANNING SCENARIO'!$C$7:$C$56)-(ROW()-3)))),LARGE('1.PLANNING SCENARIO'!$C$7:$C$56,COUNTA('1.PLANNING SCENARIO'!$C$7:$C$56)-(ROW()-3)),"")
I have been trying to mirror this formula to work with another column on a different worksheet. The formula I am using is as follows, where all I have done is chage the worksheet and cell references:
=IF(NOT(ISERROR(LARGE('2. OUTLIER TEST'!$B$3:$B$52,COUNTA('2. OUTLIER TEST'!$B$3:$B$52)-(ROW()-3)))),LARGE('2. OUTLIER TEST'!$B$3:$B$52,COUNTA('2. OUTLIER TEST'!$B$3:$B$52)-(ROW()-3)),"")
It does sort and paste the data however it seems to paste the results part way down the column after the blank cells that have no data in the range B3:B52 in worksheet "2. OUTLIER TEST". I can't figure how to alter the formula or in fact see why it is doing this when it worked fine on the original one.
As always appreciate any comments/advice
0  |
 |
 |
This post is quite old and has been locked from receiving new replies. Please create a new posting instead.
| Ads by Google |
