Posts by h1h

Re: Retrieve values in cells below a lookup cell

=OFFSET(Sales!A1;MATCH(B3;Sales!A:A;FALSE);3)

Re: Multiple worksheets summary

put some sample data into sheet Summary (not links!!!) and re-upload to give us an idea what it will look like. The now blank sheet is not sufficient.

Re: help with public variables involving multiple modules

from MS Visual Basic Help:

Variables declared using the Public statement are available to all procedures in all modules in all applications unless Option Private Module is in effect; in which case, the variables are public only within the project in which they reside.

Re: Alternative to Nested If - Then with more than 7 functions

if not two of the conditions can be true at the same time, you can concatenate the results of several IFsm or nested IFs.
e.g.: =IF(B15="A";"X";"") & IF(C15="B";"Y";"")
the result will be either X or Y. However if both conditions are fullfilled the result would be XY.

Should this not help I suggest you build a userdefined function.

Re: drop down list of formulas

there is no way of inserting a formula via data validation, as far as I know. It is called data validation. after all. A formula is not data.
In your example the extra column has the advantage, that you not only see results but also what they are based on. The caveate is a wider sheet. You'll have to choose.
Cheers

Re: Summing Negative and Positive Numbers Separately Across Numerous Columns

the SUMPRODUCT can be used to overcome the lack of possible arguments in SUMIF function. (In older XL Versions which I am still useing) You can see that the ranges are of the same size (a must) the functions goes through the ranges kind of step by step. In our case it checks wether the criteria "=AT2" is given. if yes the result of the next check is multiplied by 1, if no by 0. The next step checks if the value is below 0, if yes the next check is again multiplied by 1, if not 0. The last part is "just a range" without check, so the value is multiplied be the former results. If any of those was 0 the result will be zero, This is done for each cell in the range and the results are added up). In our example think of it as "if the range in the helper line has the same controll number AND id the value is larger (or smaller) than 0 THE add, else do not add.
From what I have heard the latest XL version has an improved SUMIF function. I do not yet know how that one works.

Have a nice weekend

Re: Trying to reverse plot order of primary and secondary axes in VBA

not sure, but try within the outer WITH
.Axes(xlValue).ReversePlotOrder = True

Re: Summing Negative and Positive Numbers Separately Across Numerous Columns

cell AN5 should read =SUMPRODUCT(($D$2:$AL$2=AN$2)*($D5:$AL5>0)*($D5:$AL5))

AT5 =SUMPRODUCT(($D$2:$AL$2=AT$2)*($D5:$AL5<0)*($D5:$AL5))

($ was missing to have the range d2:al2 be absoluze. Sorry)

Re: drop down list of formulas

see the yellow cells. Check Menue data - validation for details of List

Re: Summing Negative and Positive Numbers Separately Across Numerous Columns

see new row 2. The columns belonging together as far as your problem is concerned are marked with 1 (could be any marker). This row may of course be hidden should it disturb appearence.

Re: Summing Negative and Positive Numbers Separately Across Numerous Columns

I suggest you insert a helper row, e.g. above your current row 2. In that row you mark all columns belonging together incl. the ones holding the results. Then you can use these formulas
=SUMPRODUCT((D$2:AL$2=AN$2)*($D5:$AL5>0)*($D5:$AL5))
=SUMPRODUCT((J$2:AR$2=AT$2)*($D5:$AL5<0)*($D5:$AL5))
you may allways hide the helper row to keep the looks as they were before.

Re: drop down list of formulas

enter the function as needed, but instead of 10, 100 etc. e ref to the column next to it. In that column use data validation with a list of the desired arguments, i.e. 10, 100, etc.

Re: Function to lookup data in another sheet based on data

have a look at VLOOKUP function. Try the function wizzard.

Re: Saving a worksheet as a new workbook

try this

Sub SaveAsExcel()
Dim WS As Worksheet
Dim MyDay As String
Dim MyMonth As String
Dim MyYear As String
Dim MyPath As String
Dim MyFileName As String
Dim MyCellContent As Range
Application.ScreenUpdating = False


MyDay = Day(Date)
MyMonth = Month(Date)
MyYear = Year(Date)
MyPath = "\\Ho-01-ncham\"
' use declared Windows API function to set the path
SetCurrentDirectoryA (MyPath)




Set WS = ActiveSheet
Set MyCellContent = WS.Range("B3")


MyFileName = "Agent " & Range("A2").Text & " " & Format$(Date, "mm-dd-yyyy")


Application.CutCopyMode = False
Application.WindowState = xlMinimized
' ChDir MyPath


If CInt(Application.Version) <= 11 Then
ActiveWorkbook.SaveAs Filename:= _
MyFileName, _
ReadOnlyRecommended:=True, _
CreateBackup:=False
Else
ActiveWorkbook.SaveAs Filename:= _
MyFileName, FileFormat:=xlExcel8, _
ReadOnlyRecommended:=True, _
CreateBackup:=False
End If


With WS.UsedRange
.Copy
.PasteSpecial xlPasteValues
End With




Application.ScreenUpdating = True
ActiveWorkbook.Save
' you can remove the following alert if you like
MsgBox ("Worksheet Saved as: " & MyFileName & vbCrLf & vbCrLf & "To: " & MyPath)
End Sub

Re: Saving a worksheet as a new workbook

I do not see why this code should overwrite the original file as long as the new file name is not by chane the same os the oroginal one.

Re: Summing Negative and Positive Numbers Separately Across Numerous Columns

for negativ =SUMIF(A3:F3;"<0")
for positiv =SUMIF(A3:F3;">0")

ammend the range as needed

Re: Lookup &amp; Sum Offset in Row

As far as I am concerned you should reconsider the layout of your data. It apperas that one line consists of several individual sales-records. Instead of having e.g. 10 such recorda ccross you should have one line for each sale, i.e. 10 lines. Then you could easily use SUMIF function etc.

Re: Auto-Fill column based on data from a row

=OFFSET(Sheet1!$G$2;0;(ROW()-1)*8)

Re: How to know the row offset of the current cell from the top most cell?

x = ActiveCell.Row

Re: Find maximum value of row, and find corresponding column name

=OFFSET($A$1;0;MATCH(MAX(B2:F2);A2:F2;FALSE)-1)

if your sample data starts in A1