............ Foro Excel
Si no lo has hecho aún: REGISTRATE!!!

Generar una hoja indice

Ver el tema anterior Ver el tema siguiente Ir abajo

Generar una hoja indice

Mensaje por cardazu el Jue Jun 10, 2010 4:41 pm

Hola, a ver si pueden ayudarme con este problema: tengo un libro, donde cada hoja representa a cada alumno de la escuela, el la cual están volcados los datos personales y calificaciones del alumno, bien, quisiera tener en la primer hoja el listado completo ordenado de todos los alumnos, y que al hacer doble clic en cualquiera de ellos, excel me habra la hoja correspondiente al nombre marcado. Esa sería mi idea, no se si es posible hacerla o si hay otra más simple...Muchas gracias

cardazu

Masculino Cantidad de envíos : 9
Edad : 59
Ciudad - Pais : Argentina
Version de Excel : xp-2003
Fecha de inscripción : 28/06/2009

Volver arriba Ir abajo

Re: Generar una hoja indice

Mensaje por GalileoGali el Jue Jun 10, 2010 5:42 pm

No recomiendo esto de Hojas Individuales para almacenar datos. Va en directa contradiccion con la Estructura de Bases de Datos. En su lugar yo haria: 1) Tabla completa de Datos en Hoja Auxiliar y 2) Hoja principal para Mostrar cada alumno elegido a partir de una Validacion del tipo lista Desplegable que alimenta a Tantos BUSCARV como campos quiera mostrar en esa caratula

con 2 hojas arreglo todo

_________________
GalileoGali
M.A.P. 2010-2013
Microsoft Active Professional

GalileoGali
Admin

Masculino Cantidad de envíos : 1962
Edad : 61
Ciudad - Pais : QUIROGA, Argentina
Version de Excel : 2000-2003-2007-2010
Fecha de inscripción : 24/01/2008

http://excelgali.mejorforo.net

Volver arriba Ir abajo

Re: Generar una hoja indice

Mensaje por moises melgar el Vie Jun 11, 2010 1:13 pm

exactamente, como dice el master eso te ahorraria mucho tiempoy te daria una muy buena presentacion y mejor administracion de tus datos.
Pro que si la harias como es tu idea originala tendrias que generar puros hipervinculos para cada alumno que te lleve del indice a cada hoja y eso si que seria tardado.

moises melgar
Jr_Moderator

Masculino Cantidad de envíos : 512
Edad : 28
Ciudad - Pais : Coatzacoalcos - Mexico
Version de Excel : 2007 - 2010
Fecha de inscripción : 25/05/2010

Volver arriba Ir abajo

Generar una hoja indice

Mensaje por cardazu el Sáb Jun 12, 2010 10:35 am

De acuerdo, seguiré tu consejo, ahota vuelvo a preguntar,,,por cada alumno tendré que usar cerca de 200 lineas,(sería como una ficha donde figura los datos personales, una eventual foto, y las calificaciones de los seis años), cuál sería la fórmula para que al hacer clic en la hoja índice sobre el nombre de un alumno, me aparesca en pantalla la ficha de dicho alumno?..Nuevamente muchas gracias....

cardazu

Masculino Cantidad de envíos : 9
Edad : 59
Ciudad - Pais : Argentina
Version de Excel : xp-2003
Fecha de inscripción : 28/06/2009

Volver arriba Ir abajo

Re: Generar una hoja indice

Mensaje por jairga el Sáb Jun 12, 2010 10:40 am

una opcion: ACCESS

jairga
Moderador
Moderador

Masculino Cantidad de envíos : 163
Edad : 39
Ciudad - Pais : Cartagena-Barranquilla(Colombia)
Version de Excel : XP-2003-2007
Fecha de inscripción : 11/03/2008

Volver arriba Ir abajo

Re: Generar una hoja indice

Mensaje por GalileoGali el Sáb Jun 12, 2010 2:08 pm

sube un xls con tan solo dos hojas de alumnos (datos ficticios, mejor) y la Hoja resumen que esperas. Si cada alumnos tiene una hoja, como se llama cada hoja?
¿cual es el criterio de nomenclado de hojas ficha?

_________________
GalileoGali
M.A.P. 2010-2013
Microsoft Active Professional

GalileoGali
Admin

Masculino Cantidad de envíos : 1962
Edad : 61
Ciudad - Pais : QUIROGA, Argentina
Version de Excel : 2000-2003-2007-2010
Fecha de inscripción : 24/01/2008

http://excelgali.mejorforo.net

Volver arriba Ir abajo

Re: Generar una hoja indice

Mensaje por Alef-Thau el Dom Jun 20, 2010 7:44 am

Buenas, tu lo que buscas es esto:

Código:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
'ocultamos el procedimiento, para que
'no se vea la ejecución del macro
Application.ScreenUpdating = False
'pasamos a una variable el rango de datos
'en este caso toda la columna A. Si solo fuera
'un área determinada entre unas pocas celdas,
'tendríamos que cambiar el rango, de tal forma que
'si por ejemplo fuese de A1 a A20, pondríamos esto:
'Set datos = Range("A1:A20")
Set Datos = Range("B2:B1000000")
'Si seleccionamos una celda de ese rango
'(en este caso, toda la columna A), y además
'esa celda no está vacía (porque si está vacía
'no crearemos una hoja sin nombre), entonces...
If Union(Target, Datos).Address = Datos.Address And ActiveCell <> "" Then
'creamos una hoja con el nombre que haya
'en esa celda que hemos seleccionado, siempre
'y cuando no exista ya, partiendo de otra
'hoja ya existente.
hoja_de_calculo = ActiveCell.Value
'seleccionamos la hoja con ese nombre
Sheets(hoja_de_calculo).Select
'si no existe esa hoja, la creamos
'a partir de otra ya existente
If ActiveSheet.Name <> hoja_de_calculo Then
'quitaremos los caracteres no permitidos en
'el nombre de las hojas, por si las moscas
hoja_de_calculo = Replace(hoja_de_calculo, ":", "")
hoja_de_calculo = Replace(hoja_de_calculo, "/", "")
hoja_de_calculo = Replace(hoja_de_calculo, "", "")
hoja_de_calculo = Replace(hoja_de_calculo, "?", "")
hoja_de_calculo = Replace(hoja_de_calculo, "*", "")
hoja_de_calculo = Replace(hoja_de_calculo, "[", "")
hoja_de_calculo = Replace(hoja_de_calculo, "]", "")
'volvemos a comprobar que queda "algo" después de
'eliminar los carecteres raros
If hoja_de_calculo <> "" Then
'copiaremos la Hoja2 (nombre interno VBA)
'así si le cambian el nombre a la hoja (pestaña)
'no tendremos ningún problema
Hoja12.Select
'la pegamos al final (tras la última hoja)
Hoja12.Copy After:=Sheets(Sheets.Count)
'le cambiamos el nombre, y le pondremos
'el que figura en la celda seleccionada
'pero para evitar problemas con la longitud
'del nombre que haya en la celda, limitaremos
'el nombre de la hoja, a los primeros 31 caracteres,
ActiveSheet.Name = Left(hoja_de_calculo, 31)
'Ponemos en una celda, el nombre
'de la hoja de cálculo (por ejemplo en A1)
Range("K1").Select
End If
Else
'si ya existe una hoja con ese nombre,
'nos situamos en ella
hoja_de_calculo.Select
End If
'finalizamos el condicional que comprueba
'si nos hemos situado en una celda del rango
End If
'limpiamos el objeto
Set Datos = Nothing
'Mostramos el procedimiento
Application.ScreenUpdating = True
End Sub

Esto lo que te hace es que, partiendo de una hoja índice donde tendrías a los alumnos te llevaría a una ficha de cada alumno y a su vez te serviría (esta hoja índice) para recopilar la información más relevante de cada alumno.
Lo puedes hacer muy vistoso pero como te dicen sería más rápido (en ejecución) una base de datos, en excel no access, y después usar tablas dinámicas. Yo tuve funcionando la primer manera (fichas individuales) con un listado de unas 400 fichas (400 hojas de publicaciones mas otras 10 de datos o así) y se hacía muy pesado pero eso sí, funcionaba. El tamaño que me llego a alcanzar el libro de excel era de 6 MB.

Explico el funcionamiento. Creas un libro, en la hoja que quieras meter el código VBA (por ejemplo Hoja1) pones lo que seria el listado índice de alumnos y los datos que quieras recopilar mas a la vista en una tabla, modificas el código para ajustarlo a tu tabla índice y lo colocas en la sección código de esta hoja. Creas una plantilla en otra hoja (Hoja2) en esta pones los datos que serán la ficha del alumno, no se calificaciones, comportamientos, tu sabrás mejor XDD.
Ahora vuelves al índice, en la columna de alumnos metes el primer nombre de alumno, te mueves a la celda siguiente o anterior y regresas a la celda del nombre de alumno y te creará solo la ficha del primer alumno y se posicionará en esa hoja-ficha. ahora si regresas al índice y al situarte sobre ese mismo alumno te lleva a su ficha, y así con cada alumno que crees. Pero ya te digo si son 20 o 30 podras bien pero como sean muchos se te hará muy lenta la apertura del archivo.

Espero que te sirva.

Saludos

Alef-Thau

Masculino Cantidad de envíos : 22
Edad : 52
Ciudad - Pais : España
Version de Excel : actualmente 2010
Fecha de inscripción : 23/05/2009

Volver arriba Ir abajo

Re: Generar una hoja indice

Mensaje por cardazu el Dom Jun 20, 2010 9:31 am

Gracias, probaré de que forma me es mas util

cardazu

Masculino Cantidad de envíos : 9
Edad : 59
Ciudad - Pais : Argentina
Version de Excel : xp-2003
Fecha de inscripción : 28/06/2009

Volver arriba Ir abajo

Una sugerencia

Mensaje por Alejandro62 el Jue Jun 24, 2010 8:11 pm

Esta seria mi sugerencia para tratar de resolver lo indicado en tu primer post, crear hipervinculos. Al pinchar una vez en cualquier vinculo azul te llevara a la hoja correspondiente.

Saludos.


Alejandro62

Masculino Cantidad de envíos : 37
Edad : 54
Ciudad - Pais : Mexico, Estado de Mexico
Version de Excel : Excel 2010
Fecha de inscripción : 21/11/2009

Volver arriba Ir abajo

Re: Generar una hoja indice

Mensaje por Alef-Thau el Vie Jun 25, 2010 7:07 am

El otro día puse el código pero como era desde el trabajo y estaba muy liado no me dio tiempo a ponerlo bien explicado, así que ahora (si no es tarde, o para otro que lo necesite) lo pondré mejor explicado.
Creamos en la hoja Índice (hoja1), una tabla con los datos que queremos que tenga ese índice en las cabeceras y la convertimos en tabla (es mejor que sea tabla así va copiando sola las formulas y formatos); una vez la tengamos creada pasamos a crear en la Hoja2 la Plantilla. En esta creamos también las cabeceras de los datos que queramos poner y en la segunda fila metemos las posibles operaciones de calculo que lleven (es importante meter las operaciones antes de crear la tabla, ya que así mete todas perfectamente, así como también poner formatos antes de aplicar tabla). Una vez creadas cabeceras, formatos y operaciones seleccionamos el total de los datos (todas las columnas de datos y las 2 filas) y aplicamos el estilo de tabla. Ponemos una "forma pre-diseñada" para situar un enlace a la Hoja1 (Indice) que nos llevara al indice directamente.

Volvemos a la hoja1 (Listado) y allí en la fila de datos de Código metemos el formato de Validación de Datos con la formula:
Código:
=Contar.Si(A2;$A$2:A50;A2)+1
Donde hace lo siguiente: primero mira el valor de Ax y lo compara con los valores que hay de A2 a A50(o hasta donde nosotros metamos el índice) que sea igual a Ax +1, si es el mismo entonces nos muestra un aviso de error de datos, con esto conseguimos no duplicar códigos.
Ocultamos la Hoja2 (Plantilla) para que en caso de que entre alguien que no sepa como va la cosa no nos pueda toquetear la plantilla o por error podamos borrarla.
Vamos a la sección de Programador y allí en VBA metemos el siguiente código en la Hoja1 la de "Índice":
Código:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
'ocultamos el procedimiento, para que
'no se vea la ejecución del macro
Application.ScreenUpdating = False
'mostramos la plantilla que usaremos para crear
'las fichas y que previamente estará oculta (si es la
'primera vez debemos ocultarla nosotros).
Hoja2.Visible = xlSheetVisible
'pasamos a una variable el rango de datos
'en este caso los rangos B2 a B50. Si fuera
'fuera una columna entera pondríamos esto:
'Set datos = Range("B:B")
Set Datos = Range("B2:B50")
'Si seleccionamos una celda de ese rango
'y además esa celda no está vacía
'(porque si está vacía no crearemos una hoja sin nombre), entonces...
If Union(Target, Datos).Address = Datos.Address And ActiveCell <> "" Then
'creamos una hoja con el nombre que haya
'en esa celda que hemos seleccionado, siempre
'y cuando no exista ya, partiendo de otra
'hoja ya existente.
hoja_de_calculo = ActiveCell.Value
'seleccionamos la hoja con ese nombre
Sheets(hoja_de_calculo).Select
'si no existe esa hoja, la creamos
'a partir de otra ya existente
If ActiveSheet.Name <> hoja_de_calculo Then
'quitaremos los caracteres no permitidos en
'el nombre de las hojas, por si las moscas
hoja_de_calculo = Replace(hoja_de_calculo, ":", "")
hoja_de_calculo = Replace(hoja_de_calculo, "/", "")
hoja_de_calculo = Replace(hoja_de_calculo, "", "")
hoja_de_calculo = Replace(hoja_de_calculo, "?", "")
hoja_de_calculo = Replace(hoja_de_calculo, "*", "")
hoja_de_calculo = Replace(hoja_de_calculo, "[", "")
hoja_de_calculo = Replace(hoja_de_calculo, "]", "")
'volvemos a comprobar que queda "algo" después de
'eliminar los carecteres raros
If hoja_de_calculo <> "" Then
'copiaremos la Hoja2 (nombre interno VBA)
'así si le cambian el nombre a la hoja (pestaña)
'no tendremos ningún problema
Hoja2.Select
'la pegamos al final (tras la última hoja)
Hoja2.Copy After:=Sheets(Sheets.Count)
'le cambiamos el nombre, y le pondremos
'el que figura en la celda seleccionada
'pero para evitar problemas con la longitud
'del nombre que haya en la celda, limitaremos
'el nombre de la hoja, a los primeros 31 caracteres,
ActiveSheet.Name = Left(hoja_de_calculo, 31)
Else
'si ya existe una hoja con ese nombre,
'nos situamos en ella
hoja_de_calculo.Select
'finalizamos el condicional que comprueba
'si nos hemos situado en una celda del rango
End If
End If
End If
'limpiamos el objeto
Set Datos = Nothing
'ocultamos la plantilla para que así no la modifiquemos
'o borremos por equivocación.
Hoja2.Visible = xlSheetHidden
'Mostramos el procedimiento
Application.ScreenUpdating = True
End Sub

Ya tenemos la base hecha, ahora solo queda rellenar, a la vista solo tenemos una hoja que se llama Indice y nada más, en esta empezamos a meter los datos: Código unitario para alumno, nombre y apellido (os) y el siguiente campo que tengamos, regresamos a Nombre y apellidos y entonces es cuando nos creará la ficha a partir de la plantilla que tenemos oculta con el nombre del alumno en la solapa de la nueva Hoja. Vamos "jugando" con el tabulador y pasamos a la siguiente fila que nos duplicará, ponemos el nuevo código, si por error ponemos uno que ya existe la formula de validación nos saltará y nos avisará del código duplicado, ponemos nombre y siguientes datos y regresamos al nombre del alumno, nos crea la segunda ficha.
Ahora cada vez que marquemos un nombre de alumno nos llevará a su ficha directamente y desde allí mediante la forma pre-diseñada volveremos a el Indice.

Muy importante, la ventaja que tiene esto frente al uso de enlaces, es que de esta manera podemos mover el libro donde queramos, así como cambiarle el nombre ya que no va a afectar para nada al indice. A parte no tenemos que andar creando hojas y enlaces desde cada una, ya lo hace el solito :-) . Lo que si afectará sera al boton de regresar (la forma pre-diseñada) pero incluso si miras el código y lo reformas un poco puedes aplicar el mismo para que con la forma te lleve a la hoja indice y así no te afecte para nada el cambio de ubicación o nombre. No te lo puse de esta manera para que investigaras y de paso mostrarte las dos posibles formas de hacer el indice, con VBA y con enlaces a hojas, tu has de ver que te es más fácil para ti.
Podeis ver una "recreación" en el video y bajaros el ejemplo para toquetearlo a gusto.

Dejo un flash, no muy nítido pero que sirve para darse una idea (se ve pésimo pero se ve XD)y el archivo plantilla para ver su funcionamiento.

[Tienes que estar registrado y conectado para ver este vínculo]

Hoja de Ejemplo

Saludos y espero que os guste y sirva.


Última edición por Alef-Thau el Vie Jun 25, 2010 9:06 am, editado 7 veces (Razón : No se que pasa no va el flash.... al final lo consegui con youtube aunque con muy mala calidad)

Alef-Thau

Masculino Cantidad de envíos : 22
Edad : 52
Ciudad - Pais : España
Version de Excel : actualmente 2010
Fecha de inscripción : 23/05/2009

Volver arriba Ir abajo

Re: Generar una hoja indice

Mensaje por GalileoGali el Vie Jun 25, 2010 6:05 pm

Código:
Option Explicit

Sub CrearHyperLink(ancla As Range, subAdd As String)

ancla.Parent.Hyperlinks.Add Anchor:=ancla, Address:="", SubAddress:= _
        "'" & subAdd & "'!A1", TextToDisplay:=subAdd
End Sub


Sub crearLista()
Dim sh As Worksheet
Dim subAd As String, textDisplay As String
Dim ancl As Range
Range("A:A").Clear
Sheets("Resumen").Range("A1") = "Lista de Alumnos" 'Cambiar en todo Caso

For Each sh In ThisWorkbook.Worksheets
    If sh.Name <> "Resumen" Then    'Cambiar "Resumen" por el Nombre de la Hoja para la Lista
        subAd = sh.Name
        Set ancl = Sheets("Resumen").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
        CrearHyperLink ancl, subAd
    End If
Next sh

End Sub

_________________
GalileoGali
M.A.P. 2010-2013
Microsoft Active Professional

GalileoGali
Admin

Masculino Cantidad de envíos : 1962
Edad : 61
Ciudad - Pais : QUIROGA, Argentina
Version de Excel : 2000-2003-2007-2010
Fecha de inscripción : 24/01/2008

http://excelgali.mejorforo.net

Volver arriba Ir abajo

Re: Generar una hoja indice

Mensaje por Contenido patrocinado Hoy a las 11:27 pm


Contenido patrocinado


Volver arriba Ir abajo

Ver el tema anterior Ver el tema siguiente Volver arriba


 
Permisos de este foro:
No puedes responder a temas en este foro.