software

In column A I have group titles. In column B I have the individual
elements. So for example, the location in ColA is quot;Italyquot;, and then the
company names located in Italy are in ColB. I have merged the rows in
Col A so that if I hide some but not all the rows corresponding to the
companies, the location (quot;Italyquot;) appears for those still showing.

I would like to be able to use VLookup so I could type in a company
name and it would return the location. However, because it is a merged
cell, it returns quot;0quot;. Anyone got any stunning ideas how I can do this
or will I need to unmerge in order to get a result??

Cheers

Reg
Not sure about your stup, are you typing INTO a merged cell, or looking
up a table of merged cells?
Valid is

=vlookup(mergedcell,table,2,false)

=vlookup(A1amp;B1,table,2,false)

does this help?

--

Regnab Wrote:
gt; In column A I have group titles. In column B I have the individual
gt; elements. So for example, the location in ColA is quot;Italyquot;, and then
gt; the
gt; company names located in Italy are in ColB. I have merged the rows in
gt; Col A so that if I hide some but not all the rows corresponding to the
gt; companies, the location (quot;Italyquot;) appears for those still showing.
gt;
gt; I would like to be able to use VLookup so I could type in a company
gt; name and it would return the location. However, because it is a merged
gt; cell, it returns quot;0quot;. Anyone got any stunning ideas how I can do this
gt; or will I need to unmerge in order to get a result??
gt;
gt; Cheers
gt;
gt; Reg--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
View this thread: www.excelforum.com/showthread...hreadid=539128I'm looking up the value that is in a merged cell. So just say there
are 3 companies in Italy - ComA, ComB and ComC in B1, B2 and B3. A1:A3
has been merged to display quot;Italyquot;. I want to use VLookup so I can
enter ComA and it will display quot;Italyquot;. Starting to think it may not be
possible with a merged cell...

Thanks for your help,

RegNo because merged A1:A3 = A1, there is no A2 or A3 with any value and all
references to them will always return a zero, however a vlookup looks up in
the leftmost column and returns its value from indexed columns to the right
so even if it would be possible to use merged cells it wouldn't work

You can use index(A2:A5,match(lookup_value,B2:B5,0)) but not with merged
cells in A

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
quot;It is a good thing to follow the first law of holes;
if you are in one stop digging.quot; Lord Healeyquot;Regnabquot; gt; wrote in message ups.com...
gt; I'm looking up the value that is in a merged cell. So just say there
gt; are 3 companies in Italy - ComA, ComB and ComC in B1, B2 and B3. A1:A3
gt; has been merged to display quot;Italyquot;. I want to use VLookup so I can
gt; enter ComA and it will display quot;Italyquot;. Starting to think it may not be
gt; possible with a merged cell...
gt;
gt; Thanks for your help,
gt;
gt; Reg
gt;

I think it would only be possible with VB code, with a Worksheet change
event on column B (or the pressing of a button) to trigger a Filter
parameter being set for column A to the value in column A of the row
concerned.

Does that sound like what you need. ie, that a change to an item in
column B (ComA) causes a filter to be set on the value in column A
(Italy)?

--

Peo Sjoblom Wrote:
gt; No because merged A1:A3 = A1, there is no A2 or A3 with any value and
gt; all
gt; references to them will always return a zero, however a vlookup looks
gt; up in
gt; the leftmost column and returns its value from indexed columns to the
gt; right
gt; so even if it would be possible to use merged cells it wouldn't work
gt;
gt; You can use index(A2:A5,match(lookup_value,B2:B5,0)) but not with
gt; merged
gt; cells in A
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; Excel 95 - Excel 2007
gt; Northwest Excel Solutions
gt; www.nwexcelsolutions.com
gt; quot;It is a good thing to follow the first law of holes;
gt; if you are in one stop digging.quot; Lord Healey
gt;
gt;
gt; quot;Regnabquot; gt; wrote in message
gt; ups.com...
gt; gt; I'm looking up the value that is in a merged cell. So just say there
gt; gt; are 3 companies in Italy - ComA, ComB and ComC in B1, B2 and B3.
gt; A1:A3
gt; gt; has been merged to display quot;Italyquot;. I want to use VLookup so I can
gt; gt; enter ComA and it will display quot;Italyquot;. Starting to think it may not
gt; be
gt; gt; possible with a merged cell...
gt; gt;
gt; gt; Thanks for your help,
gt; gt;
gt; gt; Reg
gt; gt;--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
View this thread: www.excelforum.com/showthread...hreadid=539128
-note, test this on a spare copy of your workbook!-

to test that, - on the required sheet, - rightmouse the tab, and select
View Code, - then copy

Code:
--------------------

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
' stop events
Application.EnableEvents = False
On Error Resume Next
If Not Intersect(Target, Range(quot;B:Bquot;)) Is Nothing Then
Dim iRow As Integer, iLastRow As Integer
iRow = Target.Row
iLastRow = Range(quot;A65536quot;).End(xlUp).Row
ActiveSheet.AutoFilterMode = False
Range(quot;A1:Aquot; amp; iLastRow).Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=Range(quot;aquot; amp; iRow).Value
End If
Application.EnableEvents = True
On Error GoTo 0
End Sub

--------------------

into there, and then change an item in column B

This assumes that you have headers on row 1, quot;Italyquot; etc in column A,
and quot;ComAquot; etc in column B

HTH

--

Bryan Hessey Wrote:
gt; I think it would only be possible with VB code, with a Worksheet change
gt; event on column B (or the pressing of a button) to trigger a Filter
gt; parameter being set for column A to the value in column A of the row
gt; concerned.
gt;
gt; Does that sound like what you need. ie, that a change to an item in
gt; column B (ComA) causes a filter to be set on the value in column A
gt; (Italy)?
gt;
gt; ----
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
View this thread: www.excelforum.com/showthread...hreadid=539128Thanks for all the input guys.

Like I said, the main reason I wanted a merged cell was so if I hid
some cells, quot;Italyquot; would always be displayed. The solution I came up
with was to unmerge the cells, put 'Italy' in each cell, but then fit a
text box over the top of the 3 cells with quot;Italyquot; displayed. So it
looked merged, and when a cell was hidden the text box auto shrunk, but
the vlookup still worked. A little time consuming but not a bad
solution I thought....

Cheers

Reg