Vlookup Question

[c0087]

[TABLE="width: 350"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2010
[/TD]
[TD]=vlookup($B1,'Sheet1'.$A$1:$D$4,2,0)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2011[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2012[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


This is the ideal formula I'm using and it returns what I need. Is there a way to drag this down where the Sheet reference increases as well? ('Sheet1' will automatcially increase to 'Sheet 2' in 2011, to 'Sheet3' in 2012, etc.?

Thanks in advance

 

[PCL]

You may use a macro
You want to drag down, column A from row1 until the last row ??
It a always start with Sheet1 ?

 

[JumboCactuar]

You can put a list of Sheetnames in a column, example starting at H1

Then in your VLOOKUP sheet reference part use INDIRECT(H1)

 

[c0087]

You can put a list of Sheetnames in a column, example starting at H1

Then in your VLOOKUP sheet reference part use INDIRECT(H1)

Do i need to put the sheet names in parenthesis?

I tried this: =vlookup($B1,INDIRECT(H1).$A$1:$D$4,2,0)) but not working


H1 = Sheet1
H2 = Sheet2

etc

 

[c0087]

Thanks for the reply, but I don't use macros

 

[sheetspread]

=vlookup($B1,indirect("Sheet" & row(a1) &"!$A$1:$D$4"),2,0)


(if the sheet names are literally sheet1, sheet2, sheet3......)

 

[c0087]

No their names alternate. Some are years 2003 - 2017, and some are 1.9, 1.10, 1.11 etc.

I edited mine to look like yours assuming "Sheet" would be "2003", but #REF error

=vlookup($B1,INDIRECT("2003"&"!$A$1:$D$4)2,0)

also tried this just in case, but figured the row part was not needed.

=vlookup($B1,INDIRECT("2003" & ROW(A1) &"!$A$1:$D$4)2,0)

 

[c0087]

sorry I am getting a #NAME ? error

 

[sheetspread]

Type whatever the sheet name is in A1:

Excel 2010
	A	B	C
1	First Page 1	7	5
Sheet5

Cell Formulas
Range	Formula
B1	=VLOOKUP($C1,INDIRECT("'"&A1&"'!$A$1:$D$4"),2,0)

 

[c0087]

Type whatever the sheet name is in A1:

Excel 2010

	A	B	C
First Page 1

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"]5[/TD]

</tbody>

Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=VLOOKUP($C1,INDIRECT("'"&A1&"'!$A$1:$D$4"),2,0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

You're THE MAN. Thank you