Posts by Neale
-
-
Re: Extract a set of data to a new worksheet based on conditions
On another sheet the formula will look like this
E2 has date
array entered
=MAX(IF(Sheet1!A:A=Sheet2!E2,Sheet1!C:C))E3 has date
normal formula
=INDEX(Sheet1!C:C,MATCH(E3,Sheet1!A:A,0)) -
Re: Sorting Data
Excel 2007 + 2010 will allow you to sort by colour. You could then put a sequence number in the coloured cells to represent the sort order you require and then resort using that number.
Ctrl + Enter allows you to enter the same number in the selected region.
If you are in Excel 2003
The colour numbers (255,10498160 and 65535) used in the macro will need to be amended to match the colours numbers you have used
Warning: This macro assumes No Entries in column B where the colour is. If there are entries it will need to be amended to put the number in another column.
This macro will put a number in the cell based on the colour. Select the range in Column B and run the macro.
Sub Add_Number_To_Colour()
Dim c
For Each c In Selection
Select Case c.Interior.Color
Case 255 'red
c.Value = 2
Case 10498160 'purple
c.Value = 1
Case 65535 'yellow
c.Value = 3
Case Else 'other colours
c.Value = 4
End Select
Next cEnd Sub
-
Re: Advanced use of Excel's comments
Looks like a macro solution to me when using comments.
Not sure if using photos in comments is the best way to handle this.
You can put a photo in a cell and use range names and PastePictureLink feature to "lookup" a photo.
Link below demonstrates a technique using range images and range names for dashboards - you may be able to apply yo your situation.
http://www.cpaaustralia.com.au/cps/rde/xchg/c…ck-2011-10.html
Regards
Neale
-
Re: Extract a set of data to a new worksheet based on conditions
Check out Advanced Filters - they allow you to used formula in the criteria and you could use MIN and MAX.
Assume ABC for your columns above
If the lists is sorted by date and time then this formula will extract the first price for the day in cell E3.
=INDEX(C:C,MATCH(E3,A:A,0))
Also array formulas can allow you to extract more complex criteria
Assume ABC for your columns above
Max price for the day
Press Ctrl + Shift + Enter after typing=MAX(IF(A:A=E2,C:C))
E2 contains the date you are looking for
Change MAX to min for the lowest price of the day
Hope that helps
Regards
Neale
-
Re: VBA SQL not updating in all situations
I figured it out.
The code uses the "current" folder with the data base name. When you open Excel thru Win Explorer the current folder is My Documents - not the folder of the file - which was assumed to be in the same folder as the database.
I amended the code and defined the folder using ThisWorkbook.Path plus the DB name - now its all good.
-
Re: Problem simplifying a recall look-up over several columns
Hello
I think an array is the only answer. This worked - you will need to fix your references as per below for copying
in yellow cell H86
=SUM(IF($E$32:$DD$41=F86,OFFSET($E$32:$DD$41,0,1),0))
You have to enter this using Ctrl + Shift + Enter to enter as an array - this puts { } around
Regards
Neale
-
Excel 2003 and Access 2003 Windows Xp and WIn 7
I've recently started using SQL via VBA to update Access data tables.
My problem is that if the Excel file (with the VBA SQL code) is opened through Windows Explorer (Excel closed) the code won't update Access at all. All the other macros work ok just the SQL code doesn't update Access.
If the file is opened thru Excel it all works fine - no problems at all. If you have Excel open and then open the file thru Win Explorer it also works ok.
Is there a setting that Excel changes when opening thru Win Explorer?
Any help appreciated.
Regards
Neale
-
Have you looked at VLOOKUP?
Or using INDEX and MATCH togetherthese functions allow you to look up values in a table and extract data from the table based on the value selected.
HTH
-
I've sent a file to dreamboat off line. I couldn't get it to attach to a thread.
-
B1 =date(year(a1),month(a1),1)
B2 =date(year(a1),month(a1)+1,1)-1Hth
-
Hi all
The macro below runs no worries on XL2000 and XLXP but slows down to a wet week when it runs on XL2003.
The macro hides rows in a specific range based on the value of a cell in the range.
Any ideas why?
I may have revert to use autofilter to do the hide if I can't find a solution.
Thanks.
Code
Display MoreSub HideZeroLines() Dim c Application.ScreenUpdating = False ActiveSheet.DisplayAutomaticPageBreaks = False With Worksheets("report").Range("hiderange") .EntireRow.Hidden = False End With For Each c In Worksheets("report").Range("hiderange") If c.Value = 0 Then c.EntireRow.Hidden = True End If Next c ActiveSheet.DisplayAutomaticPageBreaks = False Worksheets("report").Range("b2").Select Application.ScreenUpdating = True End Sub
-
I'd say you need a macro to do that.
Excel won't do that dynamically. You'd need a macro run whenever an entry is made in C1 - it would check the existing list and add it if it couldn't find it.
Alternatively
You could write formula or use a conditional fomat to tell you whether or not the entry is in the list then have a macro copy it to the list when you hit a buttonhth
-
Have you had a look at Data > Consolidation it may do what you want.
hth
-
Welcome to the forum
Goto Tools menu click Options click Edit Tab and uncheck Edit directly in cell. Click OK
It should then work.
HTH
-
I've heard that Access has its own problems (speed) when being used in a multi user network environment. I don't know if newer versions have solved the problems.
I think you have to have the full Access program, there is no "viewer" as for powerpoint. Which usually means you have to buy Office Pro.
There are some add-ins or mods to Excel that make it more network friendly tho I haven't used any personally. I've heard that they can make Excel like a multi dimensional DB.
HTH
-
How about
Sub TryMe()
Dim rCell As Range
Dim rRange As RangeSet rRange = Range("c1", Range("c65536").End(xlUp))
For Each rCell In rRange
If rCell = rCell.Offset(1, -2) Then rCell.Offset(1, -1) = 0
Next rCell
End Sub -
Try
same code with small change - untested
Sub TryMe()
Dim rCell As Range
Dim rRange As Range
Set rRange = Range("A1", Range("A65536").End(xlUp))
For Each rCell In rRange
If rCell = rCell(0, 3) Then rCell(0, 2) = 0
Next rCell
End Sub -
Could it relate to the fact that NOW enters an exact date eg 38052.64 not a specific day 38052. Hence when doing a Match it can't find the date. Try =TRUNC on the cell and that will remove the fraction
HTH
-
Maybe something like this (untested)
HTHYou might need to identify the textbox more exactly as you are moving between sheets.
Private Sub CommandButton1_Click()
Dim cfor each c in worksheets("form").range ("A1:A112")
If c.Value = TextBox1.Text Then
MsgBox "The value you entered is " & Range("A1").Value, vbInformation
End Ifnext c
End Sub