Identify the position of the first empty cell

Asked

Viewed 1,235 times

1

I have a web integration that automatically sends values to my spreadsheet.

In one of my columns I have a URL that is concatenated with the value of a column ID on the side. I need to add in this URL value, the position of the cell next to this example:

A1: =CONCATENAR("http://www.teste.com.br/?id=";B1)

The formula would need to identify the B1 position and not the B1 value. By default all values in column B are empty.

Know some way?

Edit:

It would be as follows: A1: www.teste.com.br/id= and B1: ""

+----------------------+----+
|          A1          | B1 |
+----------------------+----+
| www.teste.com.br/id= |    |
+----------------------+----+

(has no value in B1, is empty)

Result I intend to obtain:

www.teste.com.br/id=B1

The only detail is that I cannot use a formula with reference to some fixed cell as for example LIN(B1) I cannot use because when my code is sending a new line, it will no longer be B1 and yes B2.

The logic I thought was to find in column B the first empty value.

excel

2 answers

1

To obtain the number of last row of column B, i.e., return the value of the last row filled in column B. The following formula is used: =SEERRO(PROC(2;1/(B:B<>"");LIN(B:B));1)

So the final formula is: =CONCATENAR("http://www.teste.com.br/?id=";"B"&SEERRO(PROC(2;1/(B:B<>"");LIN(B:B));1))

Where the result is seen in the example below: http://www.teste.com.br/?id=B5

Resultado

  • That’s not exactly it... : =CONCATENAR("http://www.teste.com.br/?id=B";LIN(B1)) Has some form of the result display concatenation with the value of cell B1 instead of concatenating the text B1 ?

  • Give me an example of the cell value B1. What you have in cell B1 and what you want to result.

  • It would be as follows: A1: www.teste.com.br/id= B1: ""(has no value in B1, is empty) : www.teste.com.br/id=B1 The only detail, is that I cannot use a formula with reference to some fixed cell as for example LIN(B1) I cannot use because when my code is sending a new line, it will no longer be B1 and yes B2. The logic I thought was to find in the column B the first empty value.

  • @Fábiojunioalvessousa check the edition.

  • 1

    Thank you @danieltakeshi for the effort in trying to help me! After all, the logic of identifying an empty cell would not work in practice, because whenever you insert a die in the next column, the formula would identify the next empty cell by constantly changing the reference. I was able to do otherwise, identify only the cell reference on the side, making this simple way: =CONCATENAR("www.teste.com.br/id=";INDIRETO("B"&LIN()))

0

You need to use the function ENDEREÇO:

=CONCATENAR("http://www.teste.com.br/?id=";ENDEREÇO(1,2,4))

Browser other questions tagged excel

You are not signed in. Login or sign up in order to post.