# Changing Column Reference in Formula

#### RJB

##### Board Regular
Creating a formula to find the lowest singular number in a column when the column reference changes. Anwere would be tie if more than one occurence of the lowest number is found.
RowCol.xls
ABCDE
214274
33384
42495
515106
6
Sheet1

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
This is hideus. Do *not* look directly at the formula!

=IF(COUNTIF(OFFSET(A:A,0,A2,,1),MIN(OFFSET(A:A,0,A2,,1)))=1,MIN(OFFSET(A:A,0,A2,,1)),"Tie")

Where A2 holds your column number.

Check back often for better responses....

Not pretty but it does work. Now I am going to put it in a more difficult environment and see what happens.

thanks.

Anyone has a VBA idea I will take a look at it.

Code:
``````Sub GetMin()
Dim r As Range, MyMin
Set r = Columns([A2].Value)
MyMin = Application.WorksheetFunction.Min(r)
If Application.WorksheetFunction.CountIf(r, MyMin) > 1 Then
[A3] = "TIE"
Else
[A3] = MyMin
End If
Set r = Nothing
End Sub``````

The formula in the above reply works great if columns/data are adjacent to one another. In the actual application the column reference is on a separate worksheet from the data sets and the data sets have empty cell between them as well as other columns in front of them.
RowCol.xls
ABCDEFGH
234274
33385
42495
94584
10Tie3384
112495
1255106
13
14
164184
173387
182495
1965106
Sheet1

Will they always A] Start in the same column and, B] always have 1 blank column in between?

A=yes B=no

Is everyhing in E condidered 1 data set?

Replies
12
Views
793
Replies
3
Views
144
Replies
3
Views
286
Replies
3
Views
243
Replies
2
Views
208

1,203,069
Messages
6,053,352
Members
444,655
Latest member
didr

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back