using named range in a formula

[rhombus4]

=VLOOKUP(B2,B2:D5,2,0)Is it possible to have a named range in a formula. On a sheet I have a list of names but want to include them in formula above so that I can copy it manually to each sheetIf sheets were sheet1 sheet2 sheet3 etc how would it be used in formula.Thanks

 

[T. Valko]

=VLOOKUP(B2,B2:D5,2,0)Is it possible to have a named range in a formula. On a sheet I have a list of names but want to include them in formula above so that I can copy it manually to each sheetIf sheets were sheet1 sheet2 sheet3 etc how would it be used in formula.Thanks

Maybe this...

A1 = the name of a named range

=VLOOKUP(B2,INDIRECT(A1),2,0)

Note however, that will not work if the named range is a dynamic range defined using functions like OFFSET.

 

[rhombus4]

Maybe this...

A1 = the name of a named range

=VLOOKUP(B2,INDIRECT(A1),2,0)

Note however, that will not work if the named range is a dynamic range defined using functions like OFFSET.

Thanks, would this work on tab names as well havent created the named ranges yet but they will match the tab names.was going to create a sheet with the tab names in order A1:A50 then name them but may be easier if I could do it with tab names

 

[T. Valko]

Thanks, would this work on tab names as well havent created the named ranges yet but they will match the tab names.was going to create a sheet with the tab names in order A1:A50 then name them but may be easier if I could do it with tab names

For a variable sheet name you do it like this...

A1 = some sheet name like Sheet10

=VLOOKUP(B2,INDIRECT("'"&A1&"'!B2:D5"),2,0)

Which resolves to:

=VLOOKUP(B2,'Sheet10'!$B$2:$D$5,2,0)

 

[rhombus4]

For a variable sheet name you do it like this...

A1 = some sheet name like Sheet10

=VLOOKUP(B2,INDIRECT("'"&A1&"'!B2:D5"),2,0)

Which resolves to:

=VLOOKUP(B2,'Sheet10'!$B$2:$D$5,2,0)

Would I be able to copy that formula downI have 50 plus sheets. in a first sheet I want to create the formula in say Cell A2 then copy formula down so it will look in each sheet and do the lookup for the cellsEach sheet has the data in the same place i.e. B2:D5 so those parts of the formula would be fixed. its just the tabs that need inputting in formula then being able to copy down, sorry for the confusion

 

[T. Valko]

Would I be able to copy that formula downI have 50 plus sheets. in a first sheet I want to create the formula in say Cell A2 then copy formula down so it will look in each sheet and do the lookup for the cellsEach sheet has the data in the same place i.e. B2:D5 so those parts of the formula would be fixed. its just the tabs that need inputting in formula then being able to copy down, sorry for the confusion

Yes, you can copy it down the column.

You will probably need to make the reference to the lookup value absolute:

=VLOOKUP(B$2,INDIRECT("'"&A1&"'!B2:D5"),2,0)

 

[rhombus4]

Yes, you can copy it down the column.

You will probably need to make the reference to the lookup value absolute:

=VLOOKUP(B$2,INDIRECT("'"&A1&"'!B2:D5"),2,0)

Thanks

 

[T. Valko]

Thanks

You're welcome!

 

[rhombus4]

You're welcome!

=VLOOKUP(B$2,INDIRECT("'"&A1&"'!B2:D5"),2,0)I have the names of the sheets down the left hand side and copied down the formula. If i need to add an if statement to the beginning how do I add the names of tabs.e.g. if cell C5 was blank on sheet 2 I need a blank =if(""&A1&"!c5="","",(VLOOKUP(B$2,INDIRECT("'"&A1&"'!B2:D5"),2,0))not sur ethe correct format above

 

[T. Valko]

=VLOOKUP(B$2,INDIRECT("'"&A1&"'!B2:D5"),2,0)I have the names of the sheets down the left hand side and copied down the formula. If i need to add an if statement to the beginning how do I add the names of tabs.e.g. if cell C5 was blank on sheet 2 I need a blank =if(""&A1&"!c5="","",(VLOOKUP(B$2,INDIRECT("'"&A1&"'!B2:D5"),2,0))not sur ethe correct format above

Try it like this...
=IF(INDIRECT("'"&A1&"'!C5")="","",VLOOKUP(B$2,INDIRECT("'"&A1&"'!B2:D5"),2,0))