index / lookup / match / text formula

[Guest]

hi there


i am trying to build a formula that will reference the row & column headings
of a cell ref depending on its contents.

for example, lets say ive got a table like this;
A B C D
1 Jan Feb Mar
2 Sales 100 120 155
3 Costs 80 95 120
4 Total 20 35 35


i am looking to put together what i think will be a merge of LookUp, Index,
Match & Text formulas in an unused cell, E5, to say;

search in cells B24, if any value is over 150, return the text of the
column heading then &" "& then the text of the row heading.


in this instance, cell E5 would therefore say "Mar Sales".


i have seen IndexMatch formulas work the other way (i.e. I specific "Mar" &
"Sales" & it returns the values (155), but never this way round


anyone got any ideas? please let me know if you need any more info


cheers

jb

 

[Guest]

What is the range of your data (rows/columns) and do want the output to be a
list of headings in two columns?

 

[Guest]

hi Topper


that code is still giving me all the row names, but i have found a way to
program round that & just get only the references with both a row & col entry
input, so i am sorted.


thanks VERY much for your help with all this


jb

 

[Guest]

Try ... I had ovelooked your TRUE/FALSE requirement:

If you still have problems mail sheet to me ( toppers at
REMOVETHISjohntopley.fsnet.co.uk).

Sub Find_Row2COLx()

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim x As Variant
Dim findRng As Range
Dim r As Long, c As Integer, rr As Long

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

Set findRng = ws1.Range("i46:r62")

Findval = Application.InputBox("Enter value to be found", Type:=4)


rr = 49

With ws1

x = .Range("i46:r62")

For r = 1 To UBound(x, 1)
For c = 1 To UBound(x, 2)
If x(r, c) = Findval Then
rr = rr + 1
.Cells(rr, "T") = .Cells(r + 45, "F")
.Cells(rr, "U") = .Cells(7, c + 8)
End If
Next c
Next r
End With

End Sub

 

[Guest]

Try this:

Input: Sheet1
Output: Sheet2

Checks if >= value

HTH

Sub Find_Row2COL()

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim x As Variant
Dim findRng As Range
Dim r As Long, c As Integer, rr As Long

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

Set findRng = Application.InputBox("Enter Range to be searched", Type:=8)
Findval = Application.InputBox("Enter value to be found", Type:=1)

ws2.Columns("A:B").ClearContents
rr = 1
ws2.Cells(1, 1).Resize(1, 2) = Array("Row heading", "Column heading")

x = ws1.Range(findRng.Address)

For r = 2 To UBound(x, 1)
For c = 2 To UBound(x, 2)
If x(r, c) >= Findval Then
rr = rr + 1
ws2.Cells(rr, 1) = x(r, 1)
ws2.Cells(rr, 2) = x(1, c)
End If
Next c
Next r

End Sub