Formula convertida en #Valor! Al abrir archivo
A tod@s,
Os escribo ya que me estoy volviendo loco con unas fórmulas que estoy utilizando. Tengo una columna con celdas con diferentes valores (a veces uno solo, y por tanto sin comas) separados por comas de la siguiente manera: 1, 3, 9, 12, 32, 45, 60, 72
En otra hoja tengo una tabla con los nombres a los que se corresponden dichos números. Mi objetivo es tener en una celda los nombres a los que se corresponden los números separados a su vez por comas.
Para esto he creado tantas columnas como número máximo de números hay en una celda. En cada columna voy extrayendo un número y substituyéndolo por el nombre correspondiente con las siguientes fórmulas (para finalmente en otra columna unir todos los nombres y separarlos con una coma):
1/ =IFERROR(VLOOKUP(VALUE(IFERROR(LEFT(AB6;FIND(",";AB6)-1);LEFT(AB6;2)));'List'!$A$2:$B$17;2);"")
2/ =IFERROR(VLOOKUP(VALUE(IFERROR(MID(AB6;SEARCH(",";AB6)+2;SEARCH(",";AB6)-1);""));'List'!$A$2:$B$17;2);"")
3/ =IFERROR(VLOOKUP(VALUE(IFERROR(MID(AB6;SEARCH(",";AB6;SEARCH(",";AB6)+1)+2;SEARCH(",";AB6)-1);""));'List'!$A$2:$B$17;2);"")
4/=IFERROR(VLOOKUP(VALUE(IFERROR(MID(AB6;SEARCH(",";AB6;SEARCH(",";AB6;SEARCH(",";AB6)+1)+1)+2;SEARCH(",";AB6)-1);""));'List'!$A$2:$B$17;2);"")
Esta secuencia de fórmulas las repito hasta 16 veces, y funciona perfectamente. El problema viene cuando vuelvo a abrir el archivo (tras haber guardado y cerrado correctamente). Entonces todas las fórmulas a partir de la quinta han desaparecido y en su lugar aparece "#Value!". Os pongo como ejemplo la quinta y sexta fórmula;
5/=IFERROR(VLOOKUP(VALUE(IFERROR(MID(AB6;SEARCH(",";AB6;SEARCH(",";AB6;SEARCH(",";AB6;SEARCH(",";AB6)+1)+1)+1)+2;SEARCH(",";AB6)-1);""));'List'!$A$2:$B$17;2);"")
6/ =IFERROR(VLOOKUP(VALUE(IFERROR(MID(AB6;SEARCH(",";AB6;SEARCH(",";AB6;SEARCH(",";AB6;SEARCH(",";AB6;SEARCH(",";AB6)+1)+1)+1)+1)+2;SEARCH(",";AB6)-1);""));'List'!$A$2:$B$17;2);"")
No entiendo donde se origina el error, solamente tengo una macro en ese archivo (para crear la función encontrar inverso), aunque he probado eliminando la macro y el error persiste.
¿Alguien tiene idea de como solucionarlo?
4 Respuestas
He probado a guardar el archivo en .xlsx y no aparece el fallo, el problema es que tengo un par de macros que necesitaría usar, con lo cual me he decantado por la solución agregando nuevas columnas y funciona perfecto. Quería evitar utilizar una macro para esto ya que necesitaría que el proceso se realizase automáticamente. Muchísimas gracias de nuevo por la ayuda tan detallada, es un placer encontrar gente así :)
Que tengáis un buen día!
H o l a:
Entiendo que tienes esto en la celda AB6:
3,8,12
Y en la hoja "List" tienes esto
A B
1 3 juan
2 8 pedro
3 12 ana
Y quieres, por ejemplo en la celda AC7, esto:
Juan, pedro, ana
En cuanto a fórmulas excel tiene cierta capacidad, es probable que en un momento funcione pero después ya no reconozca toda la fórmula.
Si gustas te ayudo con la macro para pasar de esto 3,8,12 a juan, pedro, ana
Me comentas.
Si bien no quieres trabajar con macros, se me ocurre que sí puedes definir una función 'personalizada' para obtener la cadena de textos.
Se define en el Editor de macros y se escribe tal cualquier función de Excel.
En la imagen va una idea de lo que expongo:
En la primer tabla separa datos en col a partir de col B (menú Datos, Texto en columnas, separador = coma)
En la 2da tabla, que puede estar ubicada a continuación de la primera obtengo los textos con una función BUSCARV.
Y a continuación obtengo la cadena con la función que acabo de crear y a la que llamé UNIENDO (que tome nota Microsoft ;)
El código que se coloca en un módulo del Editor es :
Function UNIENDO(rgox) As String 'x Elsamatilde For Each cd In rgox If cd <> "" Then cadena = cadena & cd & "," Next cd 'omito la última cadena UNIENDO = Left(cadena, Len(cadena) - 1) End Function
Sdos y espero que la idea también te sirva.
Concuerdo con Ana, parece que el problema se produce porque la fórmula es muy larga. En el libro que pruebo, usando Excel 2010 y con .xlsx, no hay ningún problema. Los errores #VALUE! Aparecen al guardarlo como .xls, como decís, a partir de la columna 5 y en adelante.
Igualmente veo algunos problemas en las fórmulas, porque no trabajan de acuerdo a lo esperado.
Si no quieres ir por la vía de las macros, yo armaría una forma alternativa, con columnas adicionales que indiquen donde están las comas dentro de la celda.
Parto de:
1) La tabla de nombres en la hoja LIST
2) Asumiendo que la celda AB6 tiene los valores ="1, 3, 9, 12, 32, 45, 60, 72" en forma de texto, con hasta 8 valores.
3) Agrego un set de columnas para encontrar las comas:
Celda AD6, LA DEJO VACIA (hay que hacerlo porque es dato para la proxima)
Celda AE6 =FIND(",",$AB6,AD6+1)
Lleno hacia la derecha, hasta Celda AL6
4) Ahora, armo un segundo set de columnas que va a buscar cada nombre a la lista:
Celda AN6=IFERROR(VLOOKUP(VALUE(MID($AB6,MAX(IF(AD6=0,1,AD6+2),1),IFERROR(+AE6-IF(AD6=0,1,AD6+2),100))),List!$A$2:$B$17,2,0),"")
Lleno hacia la derecha la misma fórmula hasta AU6 y listo.
Lo arme para 8 nombres máximo. Lo malo de esta forma es tener que agregar columnas. Lo bueno es que repite la misma fórmula hacia la derecha en cada set de columnas. Y lo mismo para abajo. Para hacerlo con más nombres, simplemente hay que agregar más columnas en ambos sets.