Apple Users
Related: About this forumChanging values in formulas based on calculations
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
(3,273 posts)You are doing it the only way I know how. Pick each cell for the function.
Response to tiredtoo (Original post)
padfun This message was self-deleted by its author.
padfun
(1,857 posts)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
(8,698 posts)=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
(1,857 posts)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
(1,857 posts)Since you wont have to delve too deep into it.
tiredtoo
(2,949 posts)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.