Problema con #¡REF! en buscarv, al usar macro

[blackexcel8]

Encontré esta web y me gustó mucho, explico mi caso

 

-use una macro, para que se genere una nueva pestaña y ahí se cree una tabla dinámica, basada en los datos de otra hoja, es decir la macro crea la pestaña y la tabla dinámica, para esto la macro también elimina cualquier pestaña que se llame "tabla dinámica" antes de crear la nueva, elimina la anterior y genera la hoja y la tabla dinámica nueva.

 

-a su vez hay otra hoja (llamada “datos”) que tiene una columna donde use la formula buscarv, y la formula busca un dato en la pestaña que creo la macro automáticamente, la que se llama “tabla dinámica”

 

=SI.ERROR(BUSCARV(B5,'tabla dinamica'!A:AH,2,0),0)

 

Bueno la formula funciona correcta y me arroja el dato, hasta ahí todo bien, le problema viene es que la pestaña “datos” donde está la formula buscarv, esa hoja se queda asi formulada, esa hoja no se tocara nunca más, y cada que la tabla dinámica se cree con la macro, el buscarv, tomara los datos nuevos, así debería funcionar.

PROBLEMA: cuando se ejecuta de nuevo la macro que elimina la pestaña de “tabla dinámica” y a su vez la misma macro crea de nuevo la pestaña “tabla dinámica” pero con los datos actualizados, entonces todas las fórmulas de buscarv en la hoja

de datos, les aparece el error “,#¡REF”

 

=SI.ERROR(BUSCARV(B5,#¡REF!A:AH,2,0),0)

 

Entiendo que es porque la formula no encuentra la referencia al momento que la macro elimina la hoja, pero aun cuando la macro crea de nuevo la hoja “tabla dinámica” con el mismo nombre y datos nuevos, pues la fórmula de buscarv, ya no se corrige, se queda con el error  “,#¡REF”

Necesito un poco de ayuda, si es que se puede hacer algo, como que la fórmula de buscarv, se actualice automáticamente con la nueva hoja de “tabla dinámica”

[Héctor Miguel]

tu apreciación es correcta, al eliminar la hoja "tabla dinamica", cualquier referencia a ella se convierte en #¡REF! => aun si la creas de nuevo :(

una alternativa es cambiar la forma de referencia en tu función (p.ej.)
de: =SI.ERROR(BUSCARV(B5,'tabla dinamica'!A:AH,2,0),0)
_a: =SI.ERROR(BUSCARV(B5,INDIRECTO("'tabla dinamica'!a:ah"),2,0),0)

el error volverá (momentáneamente) hasta que la hoja "tabla dinamica" sea (RE)creada

por cierto, si solo buscas en DOS columnas, no tiene sentido buscar en 34 ("A:AH"), por que no solo "A:B" ?