refernce to a different work sheet

[David]

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.

 

[Nick Hodge]

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
(e-mail address removed)
www.nickhodge.co.uk

 

[Don Guillett]

I would name the table and use that
=IF(ISERROR(VLOOKUP(mytable,2,0)),"",VLOOKUP($A2,mytable,2,0))

 

[Jim Morris]

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").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]

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").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.

 

[Don Guillett]

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]

BTW. I forgot. NEXT time please start your OWN thread. Messes up the
archives.

 

[David]

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

 

[David]

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]

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

 

[Gord Dibben]

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

 

[Nick Hodge]

No problem

Merry Xmas

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[David]

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)?

 

[Nick Hodge]

David

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

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[Don Guillett]

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

 

[Gord Dibben]

In addition you can Insert>Name>Paste List to get a list of names and ranges
pasted to wherever on the sheet.


Gord

 

[David]

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.

 

[Dave Peterson]

Then add a new worksheet in the workbook with the names.
Then click on Tools|macro|macros...
select ListNames and click Run