Changing values in formulas based on calculations

[tiredtoo]

I am using Concatenate to select 4 different cells. this function is used up to 10 times with each time stepping down 1 row. Works fine when I have 40 rows of data but, sometimes i only have 29 rows of data. Example of my formula below.

=CONCATENATE(B2,", ",B9,", ",B16,", ",B23)
=CONCATENATE(B3,", ",B10,", ",B17,", ",B24)
and so on...
Now is there anyway i can automatically change the cells in the formula base on the number of rows used in the spreadsheet?

Not an expert at excel but i stumble along.

[WA-03 Democrat]

1. If there is I would love to know

You are doing it the only way I know how. Pick each cell for the function.

[padfun]

3. Yes. There should be a ROWS function.

I you would need to make a variable and then have that assigned the Rows number, then use the viable in the concatenate function.

It would be easiest to code it in VBA instead and excel uses VBA very well


But later today, when I get back to my house, I can look at my excel and find it for you. And give you the right syntax.

[Shermann]

4. Try this

=CONCATENATE(B2,IF(ISBLANK(B9), "", ", " ), B9,IF(ISBLANK(B16), "", ", " ), B16,IF(ISBLANK(B23), "", ", " ), B23)

It looks for blanks and adds the commas as necessary. The B2 row can't be blank but B9,B16,and B23 can be. You can extend the formula to the maximum number of columns possible and use the same formula for every row.

[padfun]

5. More...

Here is a link on using the ROWS function:
https://exceljet.net/excel-functions/excel-row-function

When using it in concatenate, Use the syntax B + {variable name} and it should still take you to that cell.
In VBA I use this with the CELLS function like this CELLS(Variable, B). You can even loop it and have it go to each Row in a column.

If you don't know VBA, it is worth exploring. It really isnt that hard and much if it is similar to what you put into a cell. You can google examples and use them in your spreadsheet. Eventually you find yourself just using them without looking them up.

[padfun]

6. OK, Shermanns answer is probably best for you

Since you wont have to delve too deep into it.

[tiredtoo]

7. Thank you all for the advice but,

It appears my question was not really correct. I have a spread sheet with data in between 20 and 40 rows. Once all the data is entered I separate it into 4 equal sets. If each set has 10 rows the B2, B9, B16, b23 works fine. however if the number of rows is smaller, a smaller gap between rows would work better. ie B2, B6, B10, B14. The B2 is always the beginning row. It is the B9 to B6 etc. I would like to find a way to do it without manually changing them.
Again thank you for your support. Not sure how to write a visual program in excel. I know macros are in VB but that's all.