refernce to a different work sheet

In one of the worksheets in my xls file, I have this formula:

=IF(ISERROR(VLOOKUP($A2,$K$1:$M$31,2,0)),"",VLOOKUP($A2,$K$1:$M$31,2,0)) in 
columns B and C

The lookup table is in (on) the same worksheet.  But I'd like to move the 
table to another worksheet in the .xls file.  If I do that, how must I 
change the formula?  The table I am referencing is K1 - M31. 


0
david_vcp (5)
12/24/2006 10:10:16 AM
excel 39879 articles. 2 followers. Follow

17 Replies
624 Views

Similar Articles

[PageSpeed] 58

David

=IF(ISERROR(VLOOKUP($A2,Sheet2!$K$1:$M$31,2,0)),"",VLOOKUP($A2,Sheet2!$K$1:$M$31,2,0))

I've used Sheet2 as an example, change as necessary

-- 
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
www.nickhodge.co.uk


"David" <david_vcp@hotmail.com> wrote in message 
news:9tudnby5HPiRzxPYnZ2dnUVZ_vC3nZ2d@comcast.com...
> In one of the worksheets in my xls file, I have this formula:
>
> =IF(ISERROR(VLOOKUP($A2,$K$1:$M$31,2,0)),"",VLOOKUP($A2,$K$1:$M$31,2,0)) 
> in columns B and C
>
> The lookup table is in (on) the same worksheet.  But I'd like to move the 
> table to another worksheet in the .xls file.  If I do that, how must I 
> change the formula?  The table I am referencing is K1 - M31.
> 

0
12/24/2006 10:55:32 AM
I would name the table and use that
=IF(ISERROR(VLOOKUP(mytable,2,0)),"",VLOOKUP($A2,mytable,2,0))


-- 
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"David" <david_vcp@hotmail.com> wrote in message 
news:9tudnby5HPiRzxPYnZ2dnUVZ_vC3nZ2d@comcast.com...
> In one of the worksheets in my xls file, I have this formula:
>
> =IF(ISERROR(VLOOKUP($A2,$K$1:$M$31,2,0)),"",VLOOKUP($A2,$K$1:$M$31,2,0)) 
> in columns B and C
>
> The lookup table is in (on) the same worksheet.  But I'd like to move the 
> table to another worksheet in the .xls file.  If I do that, how must I 
> change the formula?  The table I am referencing is K1 - M31.
> 


0
dguillett1 (2487)
12/24/2006 2:15:17 PM
I have a very simple question about Excel macros. I am a real beginner in 
Excel macros. A macro I created appears below. Note the boldface red text. 
My question is how to change this macro so it will execute not on 2000 rows, 
but on the number of rows that actually have data. For example, if only rows 
1 through 350 have data, then I need to put something in place of the red 
text that evaluates to 350 automatically. I would prefer not to have to 
select 350 rows to have this macro work, but if I have to do a select before 
running the macro then I will do so. Thanks in advance for any help.


Columns("A:A").ColumnWidth = 18
Columns("B:B").ColumnWidth = 30
Columns("C:C").ColumnWidth = 18
Columns("D:D").ColumnWidth = 10
Columns("E:E").ColumnWidth = 1
Columns("F:F").ColumnWidth = 3
Columns("G:G").ColumnWidth = 20
Columns("H:H").ColumnWidth = 10
Range("D1:F2000").Select
Selection.NumberFormat = "0"
Range("A1:A2000").Select
Selection.NumberFormat = "@"
Range("A1").Select 


0
12/24/2006 4:46:03 PM
Since you posted in plain old text (a very good thing!), there are no lines that
show up in red.

But if you can pick out a column that has data in it when that row is used, you
can use something like:

dim LastRow as long

with activesheet
   'I used column A
   lastrow = .cells(.rows.count,"A").end(xlup).row
   .Columns("A:A").ColumnWidth = 18
   .Columns("B:B").ColumnWidth = 30
   .Columns("C:C").ColumnWidth = 18
   .Columns("D:D").ColumnWidth = 10
   .Columns("E:E").ColumnWidth = 1
   .Columns("F:F").ColumnWidth = 3
   .Columns("G:G").ColumnWidth = 20
   .Columns("H:H").ColumnWidth = 10
   .Range("D1:F" & lastrow).NumberFormat = "0"
   .Range("A1:A" & lastrow).NumberFormat = "@"
end with

You'll find that you don't have to select ranges to work with them.  It'll make
your code easier to understand later -- and work a bit quicker.



Jim Morris wrote:
> 
> I have a very simple question about Excel macros. I am a real beginner in
> Excel macros. A macro I created appears below. Note the boldface red text.
> My question is how to change this macro so it will execute not on 2000 rows,
> but on the number of rows that actually have data. For example, if only rows
> 1 through 350 have data, then I need to put something in place of the red
> text that evaluates to 350 automatically. I would prefer not to have to
> select 350 rows to have this macro work, but if I have to do a select before
> running the macro then I will do so. Thanks in advance for any help.
> 
> Columns("A:A").ColumnWidth = 18
> Columns("B:B").ColumnWidth = 30
> Columns("C:C").ColumnWidth = 18
> Columns("D:D").ColumnWidth = 10
> Columns("E:E").ColumnWidth = 1
> Columns("F:F").ColumnWidth = 3
> Columns("G:G").ColumnWidth = 20
> Columns("H:H").ColumnWidth = 10
> Range("D1:F2000").Select
> Selection.NumberFormat = "0"
> Range("A1:A2000").Select
> Selection.NumberFormat = "@"
> Range("A1").Select

-- 

Dave Peterson
0
petersod (12004)
12/24/2006 5:09:37 PM
This can be executed from ANYWHERE in the workbook using the WITH statement. 
Just fill in the sheet name.

Sub FixSheetFormat()
With Worksheets("yoursheet")
 .Range("a1,c1").EntireColumn.ColumnWidth = 18
 .Range("d1,h1").EntireColumn.ColumnWidth = 10
 .Range("b1").EntireColumn.ColumnWidth = 30
 .Range("e1").EntireColumn.ColumnWidth = 1
 .Range("f1").EntireColumn.ColumnWidth = 3
 .Range("g1").EntireColumn.ColumnWidth = 20
lr = .Cells(.Rows.count, "a").End(xlUp).Row
 .Range("D1:F" & lr).NumberFormat = "0"
 .Range("A1:A" & lr).NumberFormat = "@"
End With
End Sub

-- 
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Jim Morris" <jim.morris@globalraptor.com> wrote in message 
news:%23B6CFs3JHHA.4992@TK2MSFTNGP04.phx.gbl...
>I have a very simple question about Excel macros. I am a real beginner in 
>Excel macros. A macro I created appears below. Note the boldface red text. 
>My question is how to change this macro so it will execute not on 2000 
>rows, but on the number of rows that actually have data. For example, if 
>only rows 1 through 350 have data, then I need to put something in place of 
>the red text that evaluates to 350 automatically. I would prefer not to 
>have to select 350 rows to have this macro work, but if I have to do a 
>select before running the macro then I will do so. Thanks in advance for 
>any help.
>
>
> Columns("A:A").ColumnWidth = 18
> Columns("B:B").ColumnWidth = 30
> Columns("C:C").ColumnWidth = 18
> Columns("D:D").ColumnWidth = 10
> Columns("E:E").ColumnWidth = 1
> Columns("F:F").ColumnWidth = 3
> Columns("G:G").ColumnWidth = 20
> Columns("H:H").ColumnWidth = 10
> Range("D1:F2000").Select
> Selection.NumberFormat = "0"
> Range("A1:A2000").Select
> Selection.NumberFormat = "@"
> Range("A1").Select
> 


0
dguillett1 (2487)
12/24/2006 5:13:10 PM
BTW. I forgot. NEXT time please start your OWN thread. Messes up the 
archives.

-- 
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Jim Morris" <jim.morris@globalraptor.com> wrote in message 
news:%23B6CFs3JHHA.4992@TK2MSFTNGP04.phx.gbl...
>I have a very simple question about Excel macros. I am a real beginner in 
>Excel macros. A macro I created appears below. Note the boldface red text. 
>My question is how to change this macro so it will execute not on 2000 
>rows, but on the number of rows that actually have data. For example, if 
>only rows 1 through 350 have data, then I need to put something in place of 
>the red text that evaluates to 350 automatically. I would prefer not to 
>have to select 350 rows to have this macro work, but if I have to do a 
>select before running the macro then I will do so. Thanks in advance for 
>any help.
>
>
> Columns("A:A").ColumnWidth = 18
> Columns("B:B").ColumnWidth = 30
> Columns("C:C").ColumnWidth = 18
> Columns("D:D").ColumnWidth = 10
> Columns("E:E").ColumnWidth = 1
> Columns("F:F").ColumnWidth = 3
> Columns("G:G").ColumnWidth = 20
> Columns("H:H").ColumnWidth = 10
> Range("D1:F2000").Select
> Selection.NumberFormat = "0"
> Range("A1:A2000").Select
> Selection.NumberFormat = "@"
> Range("A1").Select
> 


0
dguillett1 (2487)
12/24/2006 5:13:57 PM
"Nick Hodge" <nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS> wrote in message 
news:u1MZLo0JHHA.5104@TK2MSFTNGP06.phx.gbl...
> David
>
> =IF(ISERROR(VLOOKUP($A2,Sheet2!$K$1:$M$31,2,0)),"",VLOOKUP($A2,Sheet2!$K$1:$M$31,2,0))
>
> I've used Sheet2 as an example, change as necessary

Works, super.  .XLS file is a lot cleaner looking now that the table is in a 
different sheet from where I do data entry.  Thanks mucho.

Your pal,
Dave

>
> -- 
> HTH
> Nick Hodge
> Microsoft MVP - Excel
> Southampton, England
> nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
> www.nickhodge.co.uk
>
>
> "David" <david_vcp@hotmail.com> wrote in message 
> news:9tudnby5HPiRzxPYnZ2dnUVZ_vC3nZ2d@comcast.com...
>> In one of the worksheets in my xls file, I have this formula:
>>
>> =IF(ISERROR(VLOOKUP($A2,$K$1:$M$31,2,0)),"",VLOOKUP($A2,$K$1:$M$31,2,0)) 
>> in columns B and C
>>
>> The lookup table is in (on) the same worksheet.  But I'd like to move the 
>> table to another worksheet in the .xls file.  If I do that, how must I 
>> change the formula?  The table I am referencing is K1 - M31.
>>
> 


0
david_vcp (5)
12/24/2006 6:01:28 PM
"Don Guillett" <dguillett1@austin.rr.com> wrote in message 
news:%23TBEvX2JHHA.536@TK2MSFTNGP02.phx.gbl...
>I would name the table and use that
> =IF(ISERROR(VLOOKUP(mytable,2,0)),"",VLOOKUP($A2,mytable,2,0))

I'm assuming that you meant

=IF(ISERROR(VLOOKUP($A2mytable,2,0)),"",VLOOKUP($A2,mytable,2,0))

??

In any event, how do you name a table?

>
>
> -- 
> Don Guillett
> SalesAid Software
> dguillett1@austin.rr.com
> "David" <david_vcp@hotmail.com> wrote in message 
> news:9tudnby5HPiRzxPYnZ2dnUVZ_vC3nZ2d@comcast.com...
>> In one of the worksheets in my xls file, I have this formula:
>>
>> =IF(ISERROR(VLOOKUP($A2,$K$1:$M$31,2,0)),"",VLOOKUP($A2,$K$1:$M$31,2,0)) 
>> in columns B and C
>>
>> The lookup table is in (on) the same worksheet.  But I'd like to move the 
>> table to another worksheet in the .xls file.  If I do that, how must I 
>> change the formula?  The table I am referencing is K1 - M31.
>>
>
> 


0
david_vcp (5)
12/24/2006 6:19:57 PM
my typo, then your typo

=IF(ISERROR(VLOOKUP($A2mytable,2,0)),"",VLOOKUP($A2,mytable,2,0))
=IF(ISERROR(VLOOKUP($A2,mytable,2,0)),"",VLOOKUP($A2,mytable,2,0))
Several ways to name anything.
1. Highlight all the cells in the area to be named>goto the NAME box (to the 
left of the formula box)>type in a name of your choice>touch enter key.
2. edit>name>define>name it in the tot box>in the refers to box type in the 
reference. You may like to create a self-adjusting formula such as
=offset($a$1,1,0,counta($a:$a),6)
look in the help index for OFFSET

-- 

Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"David" <david_vcp@hotmail.com> wrote in message 
news:SfqdnftSP4lOWRPYnZ2dnUVZ_s-rnZ2d@comcast.com...
>
> "Don Guillett" <dguillett1@austin.rr.com> wrote in message 
> news:%23TBEvX2JHHA.536@TK2MSFTNGP02.phx.gbl...
>>I would name the table and use that
>> =IF(ISERROR(VLOOKUP(mytable,2,0)),"",VLOOKUP($A2,mytable,2,0))
>
> I'm assuming that you meant
>
> =IF(ISERROR(VLOOKUP($A2mytable,2,0)),"",VLOOKUP($A2,mytable,2,0))
>
> ??
>
> In any event, how do you name a table?
>
>>
>>
>> -- 
>> Don Guillett
>> SalesAid Software
>> dguillett1@austin.rr.com
>> "David" <david_vcp@hotmail.com> wrote in message 
>> news:9tudnby5HPiRzxPYnZ2dnUVZ_vC3nZ2d@comcast.com...
>>> In one of the worksheets in my xls file, I have this formula:
>>>
>>> =IF(ISERROR(VLOOKUP($A2,$K$1:$M$31,2,0)),"",VLOOKUP($A2,$K$1:$M$31,2,0)) 
>>> in columns B and C
>>>
>>> The lookup table is in (on) the same worksheet.  But I'd like to move 
>>> the table to another worksheet in the .xls file.  If I do that, how must 
>>> I change the formula?  The table I am referencing is K1 - M31.
>>>
>>
>>
>
> 


0
dguillett1 (2487)
12/24/2006 6:30:31 PM
Select the range K1:M31 on Sheet2

Insert>Name>Define

Type in "mytable"(no quotes) in the name dialog and OK.


Gord Dibben  MS Excel MVP

On Sun, 24 Dec 2006 10:19:57 -0800, "David" <david_vcp@hotmail.com> wrote:

>
>"Don Guillett" <dguillett1@austin.rr.com> wrote in message 
>news:%23TBEvX2JHHA.536@TK2MSFTNGP02.phx.gbl...
>>I would name the table and use that
>> =IF(ISERROR(VLOOKUP(mytable,2,0)),"",VLOOKUP($A2,mytable,2,0))
>
>I'm assuming that you meant
>
>=IF(ISERROR(VLOOKUP($A2mytable,2,0)),"",VLOOKUP($A2,mytable,2,0))
>
>??
>
>In any event, how do you name a table?
>
>>
>>
>> -- 
>> Don Guillett
>> SalesAid Software
>> dguillett1@austin.rr.com
>> "David" <david_vcp@hotmail.com> wrote in message 
>> news:9tudnby5HPiRzxPYnZ2dnUVZ_vC3nZ2d@comcast.com...
>>> In one of the worksheets in my xls file, I have this formula:
>>>
>>> =IF(ISERROR(VLOOKUP($A2,$K$1:$M$31,2,0)),"",VLOOKUP($A2,$K$1:$M$31,2,0)) 
>>> in columns B and C
>>>
>>> The lookup table is in (on) the same worksheet.  But I'd like to move the 
>>> table to another worksheet in the .xls file.  If I do that, how must I 
>>> change the formula?  The table I am referencing is K1 - M31.
>>>
>>
>> 
>

0
Gord
12/24/2006 6:33:34 PM
No problem

Merry Xmas

-- 
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
www.nickhodge.co.uk


"David" <david_vcp@hotmail.com> wrote in message 
news:s8ydneqcvtTjXRPYnZ2dnUVZ_hy3nZ2d@comcast.com...
>
> "Nick Hodge" <nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS> wrote in message 
> news:u1MZLo0JHHA.5104@TK2MSFTNGP06.phx.gbl...
>> David
>>
>> =IF(ISERROR(VLOOKUP($A2,Sheet2!$K$1:$M$31,2,0)),"",VLOOKUP($A2,Sheet2!$K$1:$M$31,2,0))
>>
>> I've used Sheet2 as an example, change as necessary
>
> Works, super.  .XLS file is a lot cleaner looking now that the table is in 
> a different sheet from where I do data entry.  Thanks mucho.
>
> Your pal,
> Dave
>
>>
>> -- 
>> HTH
>> Nick Hodge
>> Microsoft MVP - Excel
>> Southampton, England
>> nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
>> www.nickhodge.co.uk
>>
>>
>> "David" <david_vcp@hotmail.com> wrote in message 
>> news:9tudnby5HPiRzxPYnZ2dnUVZ_vC3nZ2d@comcast.com...
>>> In one of the worksheets in my xls file, I have this formula:
>>>
>>> =IF(ISERROR(VLOOKUP($A2,$K$1:$M$31,2,0)),"",VLOOKUP($A2,$K$1:$M$31,2,0)) 
>>> in columns B and C
>>>
>>> The lookup table is in (on) the same worksheet.  But I'd like to move 
>>> the table to another worksheet in the .xls file.  If I do that, how must 
>>> I change the formula?  The table I am referencing is K1 - M31.
>>>
>>
>
> 

0
12/24/2006 8:38:20 PM
"Gord Dibben" <gorddibbATshawDOTca> wrote in message 
news:jnhto2tvh3lam3k6eu6od3v6prtetpmsd3@4ax.com...
> Select the range K1:M31 on Sheet2
>
> Insert>Name>Define
>
> Type in "mytable"(no quotes) in the name dialog and OK.

Super, I named the table and adjusted the formula, as Don Guillett 
suggested.  Cool stuff!

Is there a way to see a list of all the "named areas" in a sheet (or xls 
file)?

>
>
> Gord Dibben  MS Excel MVP
>
> On Sun, 24 Dec 2006 10:19:57 -0800, "David" <david_vcp@hotmail.com> wrote:
>
>>
>>"Don Guillett" <dguillett1@austin.rr.com> wrote in message
>>news:%23TBEvX2JHHA.536@TK2MSFTNGP02.phx.gbl...
>>>I would name the table and use that
>>> =IF(ISERROR(VLOOKUP(mytable,2,0)),"",VLOOKUP($A2,mytable,2,0))
>>
>>I'm assuming that you meant
>>
>>=IF(ISERROR(VLOOKUP($A2mytable,2,0)),"",VLOOKUP($A2,mytable,2,0))
>>
>>??
>>
>>In any event, how do you name a table?
>>
>>>
>>>
>>> -- 
>>> Don Guillett
>>> SalesAid Software
>>> dguillett1@austin.rr.com
>>> "David" <david_vcp@hotmail.com> wrote in message
>>> news:9tudnby5HPiRzxPYnZ2dnUVZ_vC3nZ2d@comcast.com...
>>>> In one of the worksheets in my xls file, I have this formula:
>>>>
>>>> =IF(ISERROR(VLOOKUP($A2,$K$1:$M$31,2,0)),"",VLOOKUP($A2,$K$1:$M$31,2,0))
>>>> in columns B and C
>>>>
>>>> The lookup table is in (on) the same worksheet.  But I'd like to move 
>>>> the
>>>> table to another worksheet in the .xls file.  If I do that, how must I
>>>> change the formula?  The table I am referencing is K1 - M31.
>>>>
>>>
>>>
>>
> 


0
david_vcp (5)
12/25/2006 2:47:27 AM
David

You can see them all in that same dialog under Insert>Name>Define...

-- 
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
www.nickhodge.co.uk


"David" <david_vcp@hotmail.com> wrote in message 
news:YZKdncUj5eVbphLYnZ2dnUVZ_t2tnZ2d@comcast.com...
>
> "Gord Dibben" <gorddibbATshawDOTca> wrote in message 
> news:jnhto2tvh3lam3k6eu6od3v6prtetpmsd3@4ax.com...
>> Select the range K1:M31 on Sheet2
>>
>> Insert>Name>Define
>>
>> Type in "mytable"(no quotes) in the name dialog and OK.
>
> Super, I named the table and adjusted the formula, as Don Guillett 
> suggested.  Cool stuff!
>
> Is there a way to see a list of all the "named areas" in a sheet (or xls 
> file)?
>
>>
>>
>> Gord Dibben  MS Excel MVP
>>
>> On Sun, 24 Dec 2006 10:19:57 -0800, "David" <david_vcp@hotmail.com> 
>> wrote:
>>
>>>
>>>"Don Guillett" <dguillett1@austin.rr.com> wrote in message
>>>news:%23TBEvX2JHHA.536@TK2MSFTNGP02.phx.gbl...
>>>>I would name the table and use that
>>>> =IF(ISERROR(VLOOKUP(mytable,2,0)),"",VLOOKUP($A2,mytable,2,0))
>>>
>>>I'm assuming that you meant
>>>
>>>=IF(ISERROR(VLOOKUP($A2mytable,2,0)),"",VLOOKUP($A2,mytable,2,0))
>>>
>>>??
>>>
>>>In any event, how do you name a table?
>>>
>>>>
>>>>
>>>> -- 
>>>> Don Guillett
>>>> SalesAid Software
>>>> dguillett1@austin.rr.com
>>>> "David" <david_vcp@hotmail.com> wrote in message
>>>> news:9tudnby5HPiRzxPYnZ2dnUVZ_vC3nZ2d@comcast.com...
>>>>> In one of the worksheets in my xls file, I have this formula:
>>>>>
>>>>> =IF(ISERROR(VLOOKUP($A2,$K$1:$M$31,2,0)),"",VLOOKUP($A2,$K$1:$M$31,2,0))
>>>>> in columns B and C
>>>>>
>>>>> The lookup table is in (on) the same worksheet.  But I'd like to move 
>>>>> the
>>>>> table to another worksheet in the .xls file.  If I do that, how must I
>>>>> change the formula?  The table I am referencing is K1 - M31.
>>>>>
>>>>
>>>>
>>>
>>
>
> 

0
12/25/2006 9:59:52 AM
to make a list with the addresses
Sub listnames()
For Each n In ThisWorkbook.Names
lr = Cells(Rows.Count, "a").End(xlUp).Row + 1
Cells(lr, 1) = n.Name
Cells(lr, 2) = n
Next
End Sub


-- 
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"David" <david_vcp@hotmail.com> wrote in message 
news:YZKdncUj5eVbphLYnZ2dnUVZ_t2tnZ2d@comcast.com...
>
> "Gord Dibben" <gorddibbATshawDOTca> wrote in message 
> news:jnhto2tvh3lam3k6eu6od3v6prtetpmsd3@4ax.com...
>> Select the range K1:M31 on Sheet2
>>
>> Insert>Name>Define
>>
>> Type in "mytable"(no quotes) in the name dialog and OK.
>
> Super, I named the table and adjusted the formula, as Don Guillett 
> suggested.  Cool stuff!
>
> Is there a way to see a list of all the "named areas" in a sheet (or xls 
> file)?
>
>>
>>
>> Gord Dibben  MS Excel MVP
>>
>> On Sun, 24 Dec 2006 10:19:57 -0800, "David" <david_vcp@hotmail.com> 
>> wrote:
>>
>>>
>>>"Don Guillett" <dguillett1@austin.rr.com> wrote in message
>>>news:%23TBEvX2JHHA.536@TK2MSFTNGP02.phx.gbl...
>>>>I would name the table and use that
>>>> =IF(ISERROR(VLOOKUP(mytable,2,0)),"",VLOOKUP($A2,mytable,2,0))
>>>
>>>I'm assuming that you meant
>>>
>>>=IF(ISERROR(VLOOKUP($A2mytable,2,0)),"",VLOOKUP($A2,mytable,2,0))
>>>
>>>??
>>>
>>>In any event, how do you name a table?
>>>
>>>>
>>>>
>>>> -- 
>>>> Don Guillett
>>>> SalesAid Software
>>>> dguillett1@austin.rr.com
>>>> "David" <david_vcp@hotmail.com> wrote in message
>>>> news:9tudnby5HPiRzxPYnZ2dnUVZ_vC3nZ2d@comcast.com...
>>>>> In one of the worksheets in my xls file, I have this formula:
>>>>>
>>>>> =IF(ISERROR(VLOOKUP($A2,$K$1:$M$31,2,0)),"",VLOOKUP($A2,$K$1:$M$31,2,0))
>>>>> in columns B and C
>>>>>
>>>>> The lookup table is in (on) the same worksheet.  But I'd like to move 
>>>>> the
>>>>> table to another worksheet in the .xls file.  If I do that, how must I
>>>>> change the formula?  The table I am referencing is K1 - M31.
>>>>>
>>>>
>>>>
>>>
>>
>
> 


0
dguillett1 (2487)
12/25/2006 1:10:46 PM
In addition you can Insert>Name>Paste List to get a list of names and ranges
pasted to wherever on the sheet.


Gord


On Mon, 25 Dec 2006 09:59:52 -0000, "Nick Hodge"
<nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS> wrote:

>David
>
>You can see them all in that same dialog under Insert>Name>Define...

0
Gord
12/25/2006 7:10:45 PM
"Don Guillett" <dguillett1@austin.rr.com> wrote in message 
news:%23DtsVYCKHHA.1248@TK2MSFTNGP03.phx.gbl...
> to make a list with the addresses
> Sub listnames()
> For Each n In ThisWorkbook.Names
> lr = Cells(Rows.Count, "a").End(xlUp).Row + 1
> Cells(lr, 1) = n.Name
> Cells(lr, 2) = n
> Next
> End Sub

Hmm, a macro.  But when I followed these instructions, I saw no output.  I 
need to do something else?
  1.. On the Tools menu in Microsoft Excel, point to Macro, and then click 
Visual Basic Editor.
  2.. On the Insert menu, click Module.
  3.. Type or copy your code into the code window of the module.
  4.. If you want to run the macro (macro: An action or a set of actions you 
can use to automate tasks. Macros are recorded in the Visual Basic for 
Applications programming language.) from the module window, press F5.
  5.. When you're finished writing your macro, click Close and Return to 
Microsoft Excel on the File menu.


>
>
> -- 
> Don Guillett
> SalesAid Software
> dguillett1@austin.rr.com
> "David" <david_vcp@hotmail.com> wrote in message 
> news:YZKdncUj5eVbphLYnZ2dnUVZ_t2tnZ2d@comcast.com...
>>
>> "Gord Dibben" <gorddibbATshawDOTca> wrote in message 
>> news:jnhto2tvh3lam3k6eu6od3v6prtetpmsd3@4ax.com...
>>> Select the range K1:M31 on Sheet2
>>>
>>> Insert>Name>Define
>>>
>>> Type in "mytable"(no quotes) in the name dialog and OK.
>>
>> Super, I named the table and adjusted the formula, as Don Guillett 
>> suggested.  Cool stuff!
>>
>> Is there a way to see a list of all the "named areas" in a sheet (or xls 
>> file)?
>>
>>>
>>>
>>> Gord Dibben  MS Excel MVP
>>>
>>> On Sun, 24 Dec 2006 10:19:57 -0800, "David" <david_vcp@hotmail.com> 
>>> wrote:
>>>
>>>>
>>>>"Don Guillett" <dguillett1@austin.rr.com> wrote in message
>>>>news:%23TBEvX2JHHA.536@TK2MSFTNGP02.phx.gbl...
>>>>>I would name the table and use that
>>>>> =IF(ISERROR(VLOOKUP(mytable,2,0)),"",VLOOKUP($A2,mytable,2,0))
>>>>
>>>>I'm assuming that you meant
>>>>
>>>>=IF(ISERROR(VLOOKUP($A2mytable,2,0)),"",VLOOKUP($A2,mytable,2,0))
>>>>
>>>>??
>>>>
>>>>In any event, how do you name a table?
>>>>
>>>>>
>>>>>
>>>>> -- 
>>>>> Don Guillett
>>>>> SalesAid Software
>>>>> dguillett1@austin.rr.com
>>>>> "David" <david_vcp@hotmail.com> wrote in message
>>>>> news:9tudnby5HPiRzxPYnZ2dnUVZ_vC3nZ2d@comcast.com...
>>>>>> In one of the worksheets in my xls file, I have this formula:
>>>>>>
>>>>>> =IF(ISERROR(VLOOKUP($A2,$K$1:$M$31,2,0)),"",VLOOKUP($A2,$K$1:$M$31,2,0))
>>>>>> in columns B and C
>>>>>>
>>>>>> The lookup table is in (on) the same worksheet.  But I'd like to move 
>>>>>> the
>>>>>> table to another worksheet in the .xls file.  If I do that, how must 
>>>>>> I
>>>>>> change the formula?  The table I am referencing is K1 - M31.
>>>>>>
>>>>>
>>>>>
>>>>
>>>
>>
>>
>
> 


0
david_vcp (5)
12/25/2006 7:43:43 PM
Then add a new worksheet in the workbook with the names.
Then click on Tools|macro|macros...
select ListNames and click Run 


David wrote:
> 
> "Don Guillett" <dguillett1@austin.rr.com> wrote in message
> news:%23DtsVYCKHHA.1248@TK2MSFTNGP03.phx.gbl...
> > to make a list with the addresses
> > Sub listnames()
> > For Each n In ThisWorkbook.Names
> > lr = Cells(Rows.Count, "a").End(xlUp).Row + 1
> > Cells(lr, 1) = n.Name
> > Cells(lr, 2) = n
> > Next
> > End Sub
> 
> Hmm, a macro.  But when I followed these instructions, I saw no output.  I
> need to do something else?
>   1.. On the Tools menu in Microsoft Excel, point to Macro, and then click
> Visual Basic Editor.
>   2.. On the Insert menu, click Module.
>   3.. Type or copy your code into the code window of the module.
>   4.. If you want to run the macro (macro: An action or a set of actions you
> can use to automate tasks. Macros are recorded in the Visual Basic for
> Applications programming language.) from the module window, press F5.
>   5.. When you're finished writing your macro, click Close and Return to
> Microsoft Excel on the File menu.
> 
> >
> >
> > --
> > Don Guillett
> > SalesAid Software
> > dguillett1@austin.rr.com
> > "David" <david_vcp@hotmail.com> wrote in message
> > news:YZKdncUj5eVbphLYnZ2dnUVZ_t2tnZ2d@comcast.com...
> >>
> >> "Gord Dibben" <gorddibbATshawDOTca> wrote in message
> >> news:jnhto2tvh3lam3k6eu6od3v6prtetpmsd3@4ax.com...
> >>> Select the range K1:M31 on Sheet2
> >>>
> >>> Insert>Name>Define
> >>>
> >>> Type in "mytable"(no quotes) in the name dialog and OK.
> >>
> >> Super, I named the table and adjusted the formula, as Don Guillett
> >> suggested.  Cool stuff!
> >>
> >> Is there a way to see a list of all the "named areas" in a sheet (or xls
> >> file)?
> >>
> >>>
> >>>
> >>> Gord Dibben  MS Excel MVP
> >>>
> >>> On Sun, 24 Dec 2006 10:19:57 -0800, "David" <david_vcp@hotmail.com>
> >>> wrote:
> >>>
> >>>>
> >>>>"Don Guillett" <dguillett1@austin.rr.com> wrote in message
> >>>>news:%23TBEvX2JHHA.536@TK2MSFTNGP02.phx.gbl...
> >>>>>I would name the table and use that
> >>>>> =IF(ISERROR(VLOOKUP(mytable,2,0)),"",VLOOKUP($A2,mytable,2,0))
> >>>>
> >>>>I'm assuming that you meant
> >>>>
> >>>>=IF(ISERROR(VLOOKUP($A2mytable,2,0)),"",VLOOKUP($A2,mytable,2,0))
> >>>>
> >>>>??
> >>>>
> >>>>In any event, how do you name a table?
> >>>>
> >>>>>
> >>>>>
> >>>>> --
> >>>>> Don Guillett
> >>>>> SalesAid Software
> >>>>> dguillett1@austin.rr.com
> >>>>> "David" <david_vcp@hotmail.com> wrote in message
> >>>>> news:9tudnby5HPiRzxPYnZ2dnUVZ_vC3nZ2d@comcast.com...
> >>>>>> In one of the worksheets in my xls file, I have this formula:
> >>>>>>
> >>>>>> =IF(ISERROR(VLOOKUP($A2,$K$1:$M$31,2,0)),"",VLOOKUP($A2,$K$1:$M$31,2,0))
> >>>>>> in columns B and C
> >>>>>>
> >>>>>> The lookup table is in (on) the same worksheet.  But I'd like to move
> >>>>>> the
> >>>>>> table to another worksheet in the .xls file.  If I do that, how must
> >>>>>> I
> >>>>>> change the formula?  The table I am referencing is K1 - M31.
> >>>>>>
> >>>>>
> >>>>>
> >>>>
> >>>
> >>
> >>
> >
> >

-- 

Dave Peterson
0
petersod (12004)
12/26/2006 12:06:35 AM
Reply:

Similar Artilces:

Moving thru sheets in Excel
Hi I'm new to this community but use excel a lot. I'm stumped by this one though : suppose I have column in one particular sheet with say all of the names of the top footlball teams in individual cells in alphabetical order down the column. I also, say, have another sheet in the same workbook with a similar column of the same names but in this case it's their position in a league . I would like a macro so that I can move the cursor on a team in my first column, then cnrtl"x" and I move to the same team on my next sheet. I can't work this one out to save my liv...

Out of Office- not working externally
Hi, OoO suddendly stopped working to emails sent from external domains (ie OoO is only working within our own exchange domain). I then done the following: Using the Exchange System Manager, open Global Settings > Internet Message Formats, right click "default" (on the right hand panel). Select Properties > Advanced. Tick the "allow out of office responses" box. Click apply/ok. This box wasn't checked, but since I have checked it the problem still occurs. Does it take a while for it to be come effective? Anyone have any ideas? Thanks Hi, This should wor...

Different charts for different salesmen
I have a spreadsheet which shows, in rows, the type of business brought in by different salesmen. It also has a column which has each salesman's initials in it. So the seller in each row can be identified. The relevant part of the spreadsheet looks like this Initials Date Product Initials Date Product Initials Date Product Initials Date Product I want to produce a chart of the business types sold by each salesman. Obviously I can do it for the whole firm but how do I produce a chart for each salesman? Thanks Rob Graham You need to prep the data. You could simply filter the dat...

highlight cells not working with wireless mouse
Excel 2007- When highlight a cell using a wireless mouse, it highlights more than one cell. For example, I want to highlight cell D7; but it also highlights D8,9 and 10. I can't get it to highlight the one cell. Is this a problem with Excel 2007? No, it is a problem with your mouse. I have a wireless mouse and have no trouble selecting a single cell. Is that what 'highlight' means? best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "cpajdc" <cpajdc@discussions.microsoft.com> wrote in message news:A1A4E886...

Copying spreadsheets-did not work
Still don't have a clear answer. The problem is this... I,m trying to save only a portion of a spreadsheet I have created to another folder. Each time I copy and paste, it copies the entire spreadsheet and not just what I have highlited. Dave, using |goto|special|visible cells only....had no effect. please advise It's better to stay in the same thread. Do you have both workbooks open in the same excel instance. Close excel (as many as you have open). Then open excel Then file|open your first workbook then file|open the second workbook Then try the copy|paste. tazsdad wrote...

converting PDF to Excel Sheet?
Hello, I would like to convert a PDF to Excel using a simple linux command line tool. Anyone know if that is possible? The tool coul also be a windows tool, but I am looking for a tool that is completel automated, with no user intevention. The reason is that I have a program that downloads PDFs every hour o so, and I would like to convert that pdf to an excel sheet so i coul grab data off it easily. Or maybe, does anyone know of an easier way to grab tables informatio off PDFS? thank -- Message posted from http://www.ExcelForum.com Take a look at PDF and Excel http://www.mvps...

Return to main sheet
Hello All, I was wondering if there is a way to always return to my default main sheet after a macro runs. What I have is the following: 1. Three sheets (Master, Name, Invoice) 2. Macro to create additional Invoice tab (Invoice2, Invoice3, and etc...) 3. After the invoice macro runs, it has the last invoice sheet in focus. What I want is, once the macro executes, to return focus back to Master. Is this possible? Thanks for any and all help with this charles On Nov 14, 1:20 pm, "Charles Reid" <cr...@satx.rr.com> wrote: > Hello All, > > I was wondering if...

Excel 2007 display and print are different for different users
I have 2 users that open the same spreadsheet. The spreadsheet shading looks quite different on both displays and prinnts the different shading. They are both using the same 2007 version of Excel and printing to the same printer ...

Permissions set on Word files differ from other permissions
We have a peer-to-peer network in Windows XP Pro. On ONE of our computers, Word documents do not get the same permissions for sharing that all other files get, or that all files on all other computers get. As a result, we can't copy Word files from this one computer. That computer is set to give "Everyone" "Full Control"; yet the individual Word files keep getting set to NOT give "Everyone" "Full Control". Why would Word override the other security settings, and how can I make it stop? ...

Reverse a Macro
Thanks for considering this problem! Is there a quick and simple way to reverse the events performed by macro. Hopfully there is a type of "back button" process, short of writing th macro in reverse which can be very time consuming on complicate macros. Thanks.... -- spydo ----------------------------------------------------------------------- spydor's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2843 View this thread: http://www.excelforum.com/showthread.php?threadid=49746 As far as I know there's no simple way to reverse the actions of a ma...

Labels-creating different labels on the SAME page
I would like to print a set of labels, however, I do not want each label to be exactly the same as my sheet of address labels are. I would like to number the labels and print a sheet where each of the 30 labels on the sheet has a different number on it. How do I make each label on one sheet different? Use a mail merge. Numbers instead of addressees. :-) -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "camionneur" <camionneur@discussions.microsoft.com> wrote in message news:B8ECA494-24C7-44DE-...

How to fill a sheet in Excel2003 with CSV data?
I use server-side calculations in an OWC(Office spreadsheet component) send the resulting data as string (formatted as CSV) to my Client. Ho can I fast fill an Excelsheet(2003) with this data passed as string Must I loop through the CSV data? When saved as file (.csv) on th server I can open the .CSV manually on the client fast like opening a ordinary .xla file. But how do I do it programmatically when the cs data is in a string on my VBA client? Cannot find the correc propertie -- Message posted from http://www.ExcelForum.com If you paste it into Excel you can use Text to Columns same as ...

accessing sheets stored in add-in
I have an add-in that I built, with some worksheet templates in it. I want my co-workers to be able to use the toolbar that I built for the add-in to access the sheets that are in the add-in. Kind of like unhiding them. How can I do this with a macro attached to the menu item? Jay Are you sure an addin is the best way to go? The addin is a hidden workbook. And to make it so that the user can see the worksheet, you'll have to change the .isaddin property to false. Then you can display the worksheet that you want. Option Explicit Sub testme01() ThisWorkbook.IsAddin = False &...

Different Domains with Different GALS
I have read the some options and guides as to how to approach setting up AD and Exchange to host multiple mail domains. Basically AD is separated by OU and Exchange by security ACL - this from MSEXCHANGE.ORG. Has anyone set this kind of thing? The goal is basically have separate GALS for the users and not see the other GAL. Microsoft has the service provider kit, but we are NOT service providers - but a company with multiple subsidiaries. On a high-level, how should we set this up? Thank you. ...

Edit : Find....not working between sheets
Hello, I have created a workbook that shows supplies for 20 differen cabinets. Each cabinet is listed on a worksheet. When I want to fin a particular item, I select all the sheets and go to Edit, Find an type in the item and it takes me to the correct cabinet. The problem am currently running into, is that if there are the same item i multiple cabinets, it just goes to the last one, instead of starting a the first one and continuing forth. So....If I have gloves of differen types in cabinet 1, 4, and 7 and perform this function, it should tak me to 1 and then when I click on Next, it s...

Different TAX schedules in stores
Hi Does anyone know if you have one store set as "Tax Exclusive" and all other stores set as "Tax Inclusive" will this cause a problem with HQ? Thanks hi Gaz, Every store has it own preferences and paramters so there is no problem and HQ will work fine in this. "Gaz" wrote: > Hi > Does anyone know if you have one store set as "Tax Exclusive" and all other > stores set as "Tax Inclusive" will this cause a problem with HQ? > Thanks Thanks Akber I should have been more clear though, I knwo it won't cause a problem with th...

finding differing numbers.
How do I in a column of numbers some in duplication, how can i get a list off the entries which reflects these numbers but not in duplication. ie "numbers" 1, 1, 2, 3, 4, 5, 1, 5, 3, 5, 2, 1, 2. "result" 1, 2, 3, 4, 5 Thanks Chris Hi one way: - select your column - choose 'Data - Filter Advanced Filter' - choose a new range and 'unique entries' -- Regards Frank Kabel Frankfurt, Germany curleyc wrote: > How do I in a column of numbers some in duplication, how can i get a > list off the entries which reflects these numbers but not in > duplicat...

Day and time difference
I am trying to get the difference between date and time. Example: Start Date End Date Completed within 12-1-09 8:00 AM 12-3-09 8:30 AM no. of days hh:mm How can i get it to calculate the difference between the no. of days and the time -- Shweta Srivastava Hi, look into CPearson web http://www.cpearson.com/Excel/DateTimeWS.htm "Shweta Srivastava" wrote: > I am trying to get the difference between date and time. > Example: > > Start Date End Date Completed within...

Referncing Problem...
Hi all, I would really like the help for this problem, please!!! In my XL macro, I am referncing another XL file to get some macros from it. The problem is that one of these macros closes the XL file that contains it. I could not modify this macro to prevent it from doing that. The problem is that I am getting an error which says that the file could not be closed because it is being by other files (which is my macro). How can I solve this issue!? How can I call a macro from another file, which closes the XL file that contains it without getting this dependecny error!!!! Tahnx in advance ...

How to make different cells fill in automatically from one entry?
Hi, I have a workbook with three sheets: Activity, Expenses, Income. I only want to enter data on the Activity sheet. I select the date, I select the DESCRIPTION (matches the sheets Expenses and Income) and I select the Category (Categories match columns on Expenses and Income sheets). I already set up the selections, the Category is a data value dependent (indirect) function. When I enter data on the Activity sheet I want it to appear automatically where it belongs on the Expenses or Income sheets. - in the row for the date (already there) in the column for the Category select...

Taking out duplicates from a single sheet
I have a sheet with 12k rows with multiple duplicate records. Is there a formula driven way to delete the duplicate records? This is a list of companies with multiple contacts, so I am looking for a way for example to take the 6 or 7 identical records of John Smith and leave the other 5 or 6 contacts for the same company. Now that I'm typing this I'm realizing that in a list this big the likely hood that we have the same first and last name is pretty high. I'm sure I need to provide more detail but I don't know exactly what I would need to provide. Thanks, PZan -- Pok...

hyperlinks in different drives
Hi, I'm creating a spreadsheet that hyperlinks to about 50 differen documents. Eventually, i want a CD to contain the excel file and al the hyperlinked documents. To get the hyperlinks to run from the cd, had to change all of the hyperlinks within my spreadsheet to the E: (this is the drive where my burner is). I then inserted the cd into different computer (D:\) and none of my hyperlinks work anymore? I there anyway to make sure that the excel hyperlinks open to the file stored on the cd, regardless of which drive it is in? thanks in advance, chri -- cdroot438 ------------------...

Fill color in Excell 2003 does not work?
I have upgraded from Excell 2000 to 2003. I also have purchased a Compaq nx9600 with a 17" wide screen display. For some reason the "Fill Color" formating/shading tool will not work. Nor will changing the text color. Any suggestions to fix problem? Hi Mike, See MSKB article # 320531: 'Changes to the fill color and the fill pattern are not displayed in Office programs' http://support.microsoft.com/default.aspx?scid=kb;en-us;320531 --- Regards, Norman "Mike Golfer" <Mike Golfer@discussions.microsoft.com> wrote in message news:F63E2613-C065-4379...

Does Word / Office 2003 work in Windows 7
Can anyone tell me if Word / Office 2003 works reliably and stably in Windows 7? It is time for a new computer, and Windows XP is generally unavailable on the new computers and I simply do not wish to spend the time to learn a new set of menus in Word / Office 2007. As a general gripe, the biggest benefit of Windows over the years is that the menus were always similar between Windows versions and most of the programs that ran on Windows. Re-arranging the entire menu structure for no reason seems more like a move to discourage upgrades rather than promote them. -- Than...

Different
Can you create different "personalities" in Outlook like you can in Eudora? That is, can you set up several different addresses to send from? How? I couldn't find anything in the online Support Center and I don't use Outlook (my boss does). Thanks! Tools-> E-mail Accounts... Additional instructions depends on the version of Outlook. -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -Backup and Restore -Create an Office XP CD slipstreamed with Service Pack 3 ----- "justturn" <anonymous@discussions.microsoft.com> wrote in ...