Jan 19 2022 11:53 PM
Hola buenas tengo el siguiente caso.
Necesito poner en la celda titulo, lo que se encuentra en la celda B3, se que puedo poner en D2:=B3
Sin embargo si luego quiero quitar la linea 3 me cambia el titulo porque pierdo la referencia.
Necesito poner las horas y los titulos en columnas sin perder la referecia.
Muchas gracias
Jan 20 2022 12:35 AM - edited Jan 20 2022 12:35 AM
@afrutos Use OFFSET. I believe in Spanish it would be like this:
=DESREF(B2;1;0)
Jan 20 2022 12:44 AM
@Riny_van_Eekelen
Yes but the problem is that, if I use like this, and then I wnt you delete row 3, it gives me an REF error
Jan 20 2022 12:56 AM
@afrutos If you enter that formula in D2, as you suggested it will pick-up whatever is one cell down from B2, thus whatever is in B3. Delete row 3 and should the "new row 3" be empty, the formula will return zero but not a #REF.
Otherwise, upload a screenshot with the formula in place.
Jan 20 2022 01:02 AM
Jan 20 2022 01:15 AM
@afrutos Sorry, I don't follow. That offset formula will return anything in B3. If you delete row 3 than whatever was in B4 will have moved up to B3. If you don't want to display the zero, use IF to test the outcome first. Not very pretty, but it will work.
=IF(ISBLANK(OFFSET(B2,1,0)),"",OFFSET(B2,1,0))
Jan 20 2022 01:26 AM
Jan 20 2022 02:10 AM
@afrutos Okay! Now I get it. I'm a big fan of Power Query and such a task can easily be performed with it. See attached. I didn't try to make the query very sophisticated but see if you can get it to work. If you've never worked with PQ, you might find the link below helpful.
https://exceloffthegrid.com/power-query-introduction/
Jan 20 2022 02:34 AM
Jan 20 2022 02:43 AM
@afrutos If you are somewhat familiar with PQ, you should be able to follow the applied steps. If you are new to PQ, you need to do some studying first. Nothing I can explain in easily in this forum. There are plenty resources on line that can explain it better than I.