Concatenar en formula

Según tu ejemplo, en la celda B2, quieres que aparezca el valor de la celda D10 pero de la hoja que está en la celda A1, ¿es correcto?

Instrucciones para Función
1. Abre tu hoja de excel
2. Para abrir VBa y poder pegar la función, Presiona Alt + F11
3. En el menú elige Insertar / Módulo
4. En el panel del lado derecho copia la función
5. En cualquier celda utiliza la función, como cualquier otra función de excel

Function hoja(param As Range)
hoja = Sheets(param.Value).Range("D10")
End Function

Ejemplo: escribe en la celda B2 lo siguiente

=hoja(A1)

Presiona enter y la función "hoja", te regresará el valor de la celda D10 de la hoja que está en la celda A1

Saludos. Dam

Si es lo que necesitas.

Hola de antemano muchas gracias por tu atención y la respuesta es la indicada para un único caso, he creado varias funciones para cada caso y ejecutarlas con una macro pero al momento de insertar la función en su celda respectiva me da como resultado falso y no la función como tal (=hoja(A1))

Sub ingrearfuncion()

ThisWorkbook.Activate
range("B9").Select
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Activate
Loop
ActiveCell.Offset(0, 0) = ActiveCell.FormulaR1C1 = "=oja(RC[-1])"
ActiveCell.Offset(0, 1) = ActiveCell.FormulaR1C1 = "=ojaa(RC[-2])"
ActiveCell.Offset(0, 2) = ActiveCell.FormulaR1C1 = "=ojaaa(RC[-3])"
ActiveCell.Offset(0, 3) = ActiveCell.FormulaR1C1 = "=ojaaaa(RC[-4])"
End
End Sub

Espero me hayas entendido

gracias..

Disculpa pero no entiendo lo que necesitas, tu pregunta original a lo segundo que planteas es diferente, puedes replantear la pregunta original y decirme que necesitas exactamente.

Procura poner ejemplos reales, ya que en la pregunta original el ejemplo que pusiste se soluciona con la macro que te envié

Saludos. Dam

hola de nuevo, si es cierto tu repuesta fue acertada.

el código que me envistes me sirve para traer el dato de un rango en este caso d10 de la hoja cuyo nombre se encuentra en la columna a, pero al igual necesitaba traer otros datos de sta misma hoja en este caso e10,f10,g8.

Para explicarte en detalle; cuando voy a colocar la función =oja(d10) me tocaba hacerlo manualmente lo que intentaba hacer era que se ubicara el la ultima celda vacía dela columna b.. y tomara como referencia el nombre de la celda a respectiva

range("B9").Select
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Activate

ActiveCell.Offset(0, 0) =

y allí me copiara la primera función FormulaR1C1 = "=oja(RC[-1])" para esa celda,

luego

ActiveCell.Offset(0, 1) = se uvicara en la segunda celda ala derecha y me copiara la segunda función creada ActiveCell.FormulaR1C1 = "=ojaa(RC[-2]).

y asi sucesivamente hasta la cuarta función esto con el fin de hacerlo para los cuatro casos de una vez.

pero la función como tal no me la copia en la celda que le indico, a cambio me sale como resultado falso.

Espero me hayas entendido.

Disculpa por quitarte un poco de tu tiempo.

Dios te bendiga

nota:

Si gustas te puedo mandar una hoja con el ejemplo.