index / lookup / match / text formula
 

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 B2:D4, 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

index / lookup / match / text formula
 

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

"JB2010" wrote:

Hi


yeah, really i do want a limitless listing going off down the page of all
those refs that are over 150. i just thought it was easier to start this
thread with just saying one cell returned!


i cant begin to imagine how i would write the code to do something like that?


jb

"Toppers" wrote:

What would you want if two (or more) cells meet your condition.


I think you will need VBA for this as the data will be random i.e. not
sorted ascending or descending order so MATCH (or "LOOKUPS") etc not would
work on "nearest" value searches.

index / lookup / match / text formula
 

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

"Toppers" wrote:

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

"JB2010" wrote:

hi Toppers



missed the fact that you had posted two different codes. my previous message
(about it listing rows twice), refers to the macro "Find_Row2COL" which
throws the answers to another sheet.

the macro "Find_Row2COLx" (that puts answers on the same sheet) does not
have the duplication problem but does still list all row results regardless.

hope this clears up any confusion


many thanks

jb

"JB2010" wrote:

Hi Toppers

The columns side of that works perfectly, but there seems to be a problem
with the row side.


the result brings up Sheet 2 col A listing all of the row headings twice
regardless of whether they are listed as TRUE or FALSE, but it only lists a
column heading in Sheet 2 col B where FALSE is actually found in the data
source. so it ends up looking like this;


A B
1 Row Col
2 Sales
3 Sales
4 Costs
5 Costs Mar
6 Total
7 Total


my vb is not good enough to spot what is working correctly on the column
aspects of the code that needs to be applied to the row aspects so the result
ends up looking like this;

A B
1 Row Col
2
3
4
5 Costs Mar
6
7

once again, really appreciate your help on this


jb

index / lookup / match / text formula
 

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

"JB2010" wrote:

hi Toppers



missed the fact that you had posted two different codes. my previous message
(about it listing rows twice), refers to the macro "Find_Row2COL" which
throws the answers to another sheet.

the macro "Find_Row2COLx" (that puts answers on the same sheet) does not
have the duplication problem but does still list all row results regardless.

hope this clears up any confusion


many thanks

jb

"JB2010" wrote:

Hi Toppers

The columns side of that works perfectly, but there seems to be a problem
with the row side.


the result brings up Sheet 2 col A listing all of the row headings twice
regardless of whether they are listed as TRUE or FALSE, but it only lists a
column heading in Sheet 2 col B where FALSE is actually found in the data
source. so it ends up looking like this;


A B
1 Row Col
2 Sales
3 Sales
4 Costs
5 Costs Mar
6 Total
7 Total


my vb is not good enough to spot what is working correctly on the column
aspects of the code that needs to be applied to the row aspects so the result
ends up looking like this;

A B
1 Row Col
2
3
4
5 Costs Mar
6
7

once again, really appreciate your help on this


jb

index / lookup / match / text formula
 

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

"Toppers" wrote:

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

"JB2010" wrote:

Hi


yeah, really i do want a limitless listing going off down the page of all
those refs that are over 150. i just thought it was easier to start this
thread with just saying one cell returned!


i cant begin to imagine how i would write the code to do something like that?


jb

"Toppers" wrote:

What would you want if two (or more) cells meet your condition.


I think you will need VBA for this as the data will be random i.e. not
sorted ascending or descending order so MATCH (or "LOOKUPS") etc not would
work on "nearest" value searches.