How to build a dynamic matrix table with user defined no.of rows and no.of columns

[santhosh51]

If i want to increase or decrease the no. of rows or no. of columns of a table , how can i do that ? ...... IT should be dynamic . Im creating a tool for R&D .... if they want to reduce or increase the parameters of the table ie no. of rows and column , how can i do that ?

[codeslizer]

Are you trying to select rows and columns of a table which has rows and columns dynamic in nature?

[tehneXus]

Hello,

by "Table" you mean a ListObject? Usually a table re-sizes automatically when you fill an adjacent cell.

[arlu1201]

santosh,

Its against the forum rules to create duplicate threads. I have merged them for you now.

[santhosh51]

I will be building 12*12 matrix table . But if the user doesnot want to use 12*12 instead if he wants to use only 6*6 matrix table . In this case i need something where the user mentions no. of rows*np.of columns then the table is created according to the user mentioned data . how can i do that ?

[codeslizer]

Santhosh,

You can use OFFSET function to create a dynamic named range, which I believe can cater your issues.

<first_cell_ref> : is the first cell from where the table actually starts. Basically the top-left-most cell. Gives the idea from where to start
<fully_filled_range_rows> : is the column having continuous filled rows. It basically gets the row count.
<fully_filled_range_cols> : is the row having continuous filled columns. It basically gets the column count.

Please Login or Register  to view this content.

This gives you a dynamic table that keeps growing both ways whenever a new column or rows are added.

[tehneXus]

And to let the user specify the dimensions he wants use sth like in this file: CreateTable.xlsm

contains:

Please Login or Register  to view this content.

[santhosh51]

hI TEHNEXUS ;

i HAVE ATTACHED THE the table with conditions . Can you please check this excel table sheet and correct it for me . I have specified the conditions in the excel sheet

[tehneXus]

Hi,

please check: Dynamic TABLE WITH DATA CONDITONS .xlsm

[santhosh51]

Many Thanks. You gave exactly what I wanted .

Hi,

please check: Attachment 248755

[santhosh51]

Hi Tehnexus ,

I need to change some things in the document .

1,Colour = I have changed the colours for the input and output columns u can see in the attached file . I want the colours to be like in the current attached file .
2. Initially the font colour for the numbers are red for the input, violet for the output . Now i want to change the fontcolour of the numbers to all black .

3. It is the important change which i want to do . If the customer enters the name of the criteria in the criteria row it should also automotically come into the criteria column .

[tehneXus]

Check: 636FB000.xlsm

1. Colors changed
2. When user changes a row "heading" in column 1 of the table, the same text will be pasted to the appropriate column heading and vice versa
3. Apart from the resize button, the table will automatically resize and apply color if the user enters test into 1 row below the last in column 1 OR 1 column to the right next to the last column

[santhosh51]

Hi Tehnexus,

THis document is getting bigger and bigger . Confusing a lot . I have attached the document . THe thing is everything should be dynamic . I can do that normally but i Cant do it dynamically . Please check the document u will understand the logic.

Regards
Santhosh

[santhosh51]

Hi Tehnexus,

THis document is getting bigger and bigger . Confusing a lot . I have attached the document . THe thing is everything should be dynamic . I can do that normally but i Cant do it dynamically . Please check the document u will understand the logic.

Regards
Santhosh

[santhosh51]

Check: Attachment 249538

1. Colors changed
2. When user changes a row "heading" in column 1 of the table, the same text will be pasted to the appropriate column heading and vice versa
3. Apart from the resize button, the table will automatically resize and apply color if the user enters test into 1 row below the last in column 1 OR 1 column to the right next to the last column

Hi Tehnexus ,

I have made some corrections personally to the table . I want some more ocrretions to do But it is difficult for me to break the code . So i propose to you to help with some changes which i needed .

1. I need the Name table that is header column and row to be empty .
2. If i click resize button i need the previously entered value to automatically return to null values .
2a) If its possible u can do that for the other two tables also
3. I should be allowed to enter values or names only in the Green tables not in the Grey Tables
4a). If possible please connect the resize function to other tables also .

Point number 2a and 4a are not compuslory but would be better if i get the function too . If possible please i need the doucment as son as possible


Thanks Regards
santhosh