# Cambiar posición celdas

Occasional Contributor

# Cambiar posición celdas

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

9 Replies

# Re: Cambiar posición celdas

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

=DESREF(B2;1;0)

# Re: Cambiar posición celdas

@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

# Re: Cambiar posición celdas

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

# Re: Cambiar posición celdas

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?

# Re: Cambiar posición celdas

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

# Re: Cambiar posición celdas

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

# Re: Cambiar posición celdas

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

# Re: Cambiar posición celdas

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

# Re: Cambiar posición celdas

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