[RESUELTO] Vincular datos externos error calculo

[xanaesx]

Hola, tengo una hoja llamada LISTA_CONSULTA que toma los datos de la hoja LISTA_BASE que es la que conecta con datos externos htm(=celda hoja de datos externos LISTA_BASE) y la hoja de datos externos (LISTA_BASE) en donde se descarga desde un archivo .htm
El problema es que cuando actualizo los datos externos(LISTA_BASE) y se modifican la cantidad de filas en la hoja LISTA_CONSULTA en donde tengo las celdas referenciadas me muestra #REF¡ o #NOMBRE?
En LISTA_CONSULTA de la fila 16 a la 5513 habia datos con el archivo descargado anteriormente, pero al descargar un archivo nuevo de menos catidad de filas deja todo ese espacio con errores y recien en la fila 5514 continua referenciando a la fila 22 de la hoja LISTA_BASE
Adjunto archivo .RAR con hojas CALC y los 2 archivos html que uso de prueba.

Desde ya gracias.

Saludos


Editado por el moderador (mauricio) para cambiar el "todo mayúsculas" del título. Si no lo has hecho aún por favor lee la Guía de supervivencia

[fornelasa]

Hola xanaesx,
Mira, yo en lo personal te entendi.............................................................................................................nada.
Tienes una hoja llamada LISTA_BASE en blanco que nos dice ............................................................................nada.
Tienes una hoja llamada LISTA_CONSULTA con datos como =LISTA_BASE.A#REF!#ref!#ref! que nos dicen.......................nada.
Tienes una macro que lo unico que hace es seleccionar la celda A2 de la hoja LISTA_CONSULTA o sea ..........................nada.
Considero que tu consulta no es clara y lo unico que dice es.............................................................................nada.

o sea nada de nada


Por favor trata de ser un poco mas clara(o) y adjunta un archivo ejemplo mas correcto.

Bueno, eso creo yo.

Saludos, Federico.

[xanaesx]

Aqui vuelvo a plantear el caso nuevamente tratando de ser un poco mas claro.
Tengo un documento Calc en donde en la Hoja1 hay 500 filas con columnas A hasta J en donde las celdas tienen la siguiente formula (=Hoja2.A1) correspondiente a cada una del rango, significa que cada celda de la Hoja1 es igual a cada celda de la Hoja2.
Pero en la Hoja2 los DATOS estan vinculados a DATOS externos, los cuales varian por ejemplo hoy los datos de esta Hoja2 ocupan 500 filas y mañana ocupan solo 100 filas, en la Hoja1 me muestra error #REF! en las 400 filas restantes, guardo el documento lo vuelvo abrir completo la HOJA2 con DATOS hasta la fila 300 y en la Hoja1 no me muestra los datos ingresados recientemente sigue mostrando #REF! en las filas 101 a la 500.
El problema es que la Hoja2 al vincular a datos externos estos varian en su cantidad de filas segun los datos al momento de actualizar los datos contenidos; entonces para la Hoja1 la cantidad de filas es como si hubieran sido eliminadas y no se actualiza las referencias a la nueva cantidad de filas contenidas en Hoja2.
Mi pregunta es como hacer para que la variacion de la cantidad de filas con DATOS contenidos en la Hoja2 que son DATOS que estan vinculados a DATOS externos no elimine filas sino que las actualice rellenando con mayor o menor cantidad de datos segun corresponda para que no muestre #REF! en Hoja1.
Aqui adjunto tres documentos con los errores sucesivos PRUEBA_1, PRUEBA_2 y PRUEBA_3.
Estoy usando OpenOffice.org 3.4
Desde ya gracias.
Saludos

[SLV-es]

No entiendo lo que quieres hacer, pero a ver si esto te puede ayudar:

El error #REF es un error de referencia: haces referencia a una celda que, uyyyyy, ya no está !!!!

La celda no está por que ha sido eliminada, no borrado su contenido, sino eliminada (la celda, o la fila, o la columna, o la hoja, o el documento que la contiene)

Quizás, antes de importar los nuevos datos, "elimines" las celdas de la hoja2, en lugar de borrar su contenido y sustituirlo por el nuevo, sin eliminar ni insertar celdas o filas o columnas. Por eso se muestra el error #REF.

Saludos

[xanaesx]

El problema es que al actualizar los datos externos solo elimina las filas, lo que necesitaria hacer es que al actualizar datos externos borre los cambios en las filas y no que las elimine porque sino me muestra #REF.

[xiseme]

Ummm ... es al revés: no elimines las filas y así no tendrás error #REF

Elucubremos un poco.
Supongamos que no podemos/queremos hacer las cosas de la (mejor) manera directa esto es, no eliminar celdas si son origen de otras y copiar de nuevo las fórmulas si se eliminan.
Supongamos que tenemos una Hoja1 del archivo SoloMio.ods que necesariamente tiene que tomar datos de la Hoja2 del archivo DeOtro.ods
Supongamos que no tenemos control sobre DeOtro.ods y que ese otro no es disciplinado y elimina celdas, columnas y filas sin importarle que eso cause problemas (releer lo antes dicho por Salva) en las hojas vinculadas.
Si no puede ser directamente, tendremos que complicarlo con indirectas

Supongamos que ...

La celda de SoloMio.ods.Hoja1.A100 siempre debe tomar el valor de la DeOtro.ods.Hoja2.C230

A100 =INDIRECTO("'file:///ruta/donde/esta/DeOtro.ods'#$Hoja2.C230")
Las celdas de la colunma A sean reflejo de la Z de DeOtro.ods

A1 =INDIRECTO("'file:///ruta/donde/esta/DeOtro.ods'#$Hoja2.Z"&FILA())
A2 =INDIRECTO("'file:///ruta/donde/esta/DeOtro.ods'#$Hoja2.Z"&FILA())
....
Queremos complicarlos más y las celdas de las primeras 26 columnas toman los valores de las homólogas de DeOtro.ods (y no queremos "hacer" 26 fórmulas)

A1 =INDIRECTO("'file:///ruta/donde/esta/DeOtro.ods'#$Hoja2."&MID("ABCDEFGHIJKLMNOPQRSTUVXYZ";COLUMNA();1)&FILA())
A2 =INDIRECTO("'file:///ruta/donde/esta/DeOtro.ods'#$Hoja2."&MID("ABCDEFGHIJKLMNOPQRSTUVXYZ";COLUMNA();1)&FILA())
....
Zx =INDIRECTO("'file:///ruta/donde/esta/DeOtro.ods'#$Hoja2."&MID("ABCDEFGHIJKLMNOPQRSTUVXYZ";COLUMNA();1)&FILA())

[Editado] Versión corta:
Si en vez de =Hoja2.A100 usamos la fórmula =INDIRECTO("Hoja2.A100") no dará error aunque se eliminen filas/columnas en la Hoja2.

[fornelasa]

Hola..
Si aun no queda entonces vamos centrando aun mas el problema, estamos de acuerdo que lo que sugiere Salva es cierto, ok bien, entonces bajo esa premisa yo pregunto:
¿de que forma o de que manera vinculas la Hoja2 con los datos externos?, el problema se ve viene de ahi.

Saludos, federico.

[xanaesx]

El documento Calc lo haria para instalarlo en varias PCs que estan ubicadas en distinas ciudades para que puedan consultar manteniendo los datos actualizados vinculando con datos a un archivo .html alojado en una pagina web.
Lo que quiero hacer es un documento con 2 Hojas una llamada Consulta y la otra Datos.
La hoja Consulta la utilizo para mostrar los datos ordenados con filtros automaticos y algunos calculos, esta esta protegida para que no puedan modificarla y toma los datos de la hoja Datos.
La hoja Datos vincula con datos externos que son adquiridos desde un archivo .html alojado en una pagina web, el archivo .html lo genero desde una hoja calc.ods que la guardo como documento.html que la selecciono desde la opcion Archivo>Guardar como>Tipo
Estos datos son de una lista de articulos varios que contiene codigo, descripcion, precios, etc. cada dato en su columna correspondiente, este archivo varia en cantidad de filas segun las actualizaciones que se van realizando en algun momento pueden ser mas filas en otro mas filas y despues nuevamente menos filas.
Es aqui en donde aparece el problema de que al actualizar los datos vinculados al archivo web .hml la variacion en la cantidad de filas en el archivo en lugar de rellenar las celdas existentes es como si las eliminara y genera el error #REF! en la hoja Consulta.
Como comentario hice la prueba vinculando al archivo en forma local y el error es el mismo.
Tambien hice pruebas con MS XLS y funciona bien no aparece el error al actualizar los datos es como que reacomoda automaticamente la cantidad de filas al nuevo archivo descargado.

[mriosv]

¿Como tienes vinculados los datos?, en Menú/Editar/Vínculos no hay nada.

[fornelasa]

Hola xanaesx,
En mi opinión la solución fue dada ya por Salva.
Solo por si no le entendimos, pongo esta formula a manera de ejemplo:
En la Hoja Consulta celda A1 (o en la celda que mas te acomode) introduce esta formula
=INDIRECTO("Datos."&DIRECCIÓN(FILA();COLUMNA()))
y despues copiala a donde gustes
Cuando actualices los datos ya no debe dar el error.

En la formula tal vez debas cambiar el punto y coma por coma ( ; por , )

Es un ejemplo.

Saludos, Federico.

[xanaesx]

Hola, antes que todo les quiero agradecer a todos los que me ayudaron.
Les comento que ya tengo terminado el documento calc llamado LISTA_GENERAL_ACTUALIZABLE.ods y esta funcionado bien, he hecho varias pruebas y funciona correctamente al actualizar los datos externos desde un archivo .html desde mi pagina web.
Tambien adjunto una imagen con la opcion de MS XLS 2003 que hace innecesario utilizar la formula =INDIRECTO("LISTA_BASE."&MID("ABCDEFGHIJKLMNOPQRSTUVXYZ";COLUMNA();1)&FILA()) esto lo descubri al haciendo el mismo archivo para MS XLS, ya que tambien tuve que realizar el documento en este formato .XLS para las personas que utilicen este producto.
Seria interesante si calc pudiera tener esta opcion.
Tambien en el archivo esta la opcion para descargar el documento calc terminado y funcionando.
Saludos

[mriosv]

Me he perdido, ¿porque hay que utilizar INDIRECTO()?
Una formula de matriz directa debería de funcionar sin ningún problema.
En:
LISTA_CONSULTA.A3 introducir LISTA_BASE.A3:J10003 finalizando con Mayusc+Ctrl+Enter

Por otra parte, puesto a usar macros, creo es mas fácil prescindir de la hoja LISTA_CONSULTA, y activar el filtro tras actualizar.

[xiseme]

Pues en mi caso, no soy capaz de reproducir ese error #REF por cambios en los datos vinculados.
He puesto una lista_base con más registros 7000. He guardado mi copia local.
Reabro archivo actualizando vínculos (5970 en lugar de 7000) y no aparece error.
Sí cuando elimino filas de LISTA_BASE en mi copia local (a lo que no le encuentro sentido) Con la manera matricial que nos enseña mriosv ni siquiera en este último caso (eliminando 100 filas del medio aparecería N#D en las 100 del final, nunca en el rango "útil")
Aún así para "protegerse" ante la eliminación de filas, mejor INDIRECTO("LISTA_BASE.";DIRECCIÓN(FILA();COLUMNA()) como sugiere FORNELASA.

Ya que duplicamos la listas, particularmente encuentro preferible, por más versátil, que lista_consulta busque en lista_base en vez de una simple copia.
Primera columna la referencia {=LISTA_BASE.A3:A10003} ó INDIRECTO("LISTA_BASE." & DIRECCIÓN(FILA();1))
Esto permitiría cuando convenga, obtener datos calculados para cada referencia. P.E. si en lugar de lineal, el descuento fuese diferente según fabricante/familia/marca podría hacerse que la columna PrecioSinIVA= PrecioBaseSinIVA x Dto_fabricante/familia/marca, o columnas con datos combinados, ...

Es posible que sea útil ir directamente desde la selección en Lista_consulta y una validez de datos en B2 de BuscarXCodigo, por seleccionarlo de una lista.

LISTA_GENERAL_ACTUALIZABLE_modif.ods

(114.62 KiB) 255 veces

[xanaesx]

Muy interesante las modificaciones que propone xiseme, la voy a poner a prueba, me gusto la opcion de la macro ir a ficha de codigo seleccionado, ya que no interfiere con el ingreso manual del codigo en la hoja BUSCARxCODIGO y es una opcion mas.
Respecto al tema de los errores #REF ya no aparecen por haber utilizado la formula INDIRECTO, ya que si utilizamos referencia directa si aparecen errores al actualizar datos externos por la diferencia de filas entre una hoja y otra, por lo menos ese era el gran problema que tenia con este archivo.
Estuve probando la sugerencia de mriosv que dice Una formula de matriz directa debería de funcionar sin ningún problema.
En:
LISTA_CONSULTA.A3 introducir LISTA_BASE.A3:J10003 finalizando con Mayusc+Ctrl+Enter
y si funciona tambien otro gran aporte.
Saludos