Cambiar posición celdas

Occasional Contributor

Hola buenas tengo el siguiente caso.

afrutos_1-1642665103202.png

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

 

9 Replies

@afrutos Use OFFSET. I believe in Spanish it would be like this:

=DESREF(B2;1;0)

@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

@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.

 

Yes, sorry it gives me a cero, but how can I use the formula to obtain what I want when I delete the following row?

@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))

I will try to explain myself better,
what I would like to achieve is to put in D2 what is in B3, and once it is put, delete row 3 because I would already have the data I need in row 2.
So that instead to have it like this :
fecha inicio fin titulo
01/10/2021 9:00
01/10/2021 LA CONSTRUCCIÓN DE UN IMPERIO
01/10/2021 9:55
01/10/2021 ASÍ EN EL BARRIO COMO EN EL CIELO. TELENOVELA

It would look like this
fecha inicio titulo
01/10/2021 9:00 LA CONSTRUCCIÓN DE UN IMPERIO
01/10/2021 9:55 ASÍ EN EL BARRIO COMO EN EL CIELO. TELENOVELA

@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/ 

Riny_van_Eekelen_0-1642673399115.png

 

Oh thank you and once in PQ what did you you? I mean what steps you did?

@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.