Hi, Can someone help me with this problem. Please refer this spreadsheet.

https://docs.google.com/spreadsheet...

Let say for the table 1, C6 i choose Sam, how can it copy automatically data from table 2 to table 1 (D24:K24 to D6:K6)

Thank you.

message edited by nur11

First, a posting tip: When posting in a Help forum such as this, please try to use a Subject Line that tells us something about your question. If everyone used a generic Subject Line such as "I Need A Formula" we wouldn't be able to tell one question from another and the Archives would essentially be useless.

I have edited your subject to be more relevant to your question.

That said, try this:

Put this in D6 and drag it over to K6 and as far down as you need.

=INDEX($D$24:$K$35,MATCH($C6,$C$24:$C$35,0),COLUMN()-3)

The MATCH function will determine which row of the

lookup_arrayC24:C35 contains the value in C6. It will use that as therow_numargument for the INDEX function.COLUMN()-3 is the

column_numargument for the INDEX function. By using the COLUMN() function as opposed to hard-coding the column number of thelookup_array, you can drag the formula over and thecolumn_numargument will update itself.Let us know if you have any questions.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thanks DerbyDad03 for the reply. But the function did not copy the format cells. Do you have any idea how can I copy the value as well as the format cell (let's say I want to fill the cell with diagonal stripe).

Let's recap: Your subject line says "What formula to copy

datato table?"The text in your post says "...how can it copy automatically

datafrom table 2 to table 1"In your latest response you said: "But the function did not copy the

formatcells."The formula did not copy the format because a formula can not copy a format, but more importantly, you never asked that the format be copied. You asked that the data be copied, so that is the solution that was offered.

OK, so now let's look at your latest question.

There are 2 ways to match the format of Table 1 to Table 2 after the data is pulled from Table 2:

1 - You could use Conditional Formatting on both tables, using the same rules, so that whatever formatted Table 2 would also format Table 1 once the data is in that table.

2 - You could use a macro to copy both the data and the format.

If a macro would be of interest to you, I would need to know if there is anything else you haven't told us about your process.

For example, you said "Let say for the table 1, C6 i choose Sam"

How are you choosing "Sam"? Is there a Data Validation drop down in Column C of Table 1? Is there anything else you think we need to know before we start writing code?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Dear DerbyDad03, Thank you for the idea of using conditional formatting. That's work for me.

But for the formula you gave me earlier, I don't really get for this part

"COLUMN()-3 is the column_num argument for the INDEX function. By using the COLUMN() function as opposed to hard-coding the column number of the lookup_array, you can drag the formula over and the column_num argument will update itself."

It works for the spreadsheet I gave. But, I tried to use the similar formula to another spreadsheet by changing the cell number (the one I gave you is only simplified example). But there is error.

Can you explain further the Column ()-3 part?

I'll start at the beginning, only because I don't know what you already know. The first thing to keep in mind is that the COLUMN() function is not required for the INDEX() function. It is simply used as an automatic way to come up a number that can serve as the column_num argument for the INDEX() function. I understand that the use of the word "column" in so many different ways can be confusing.

- There are Columns in a spreadsheet e.g. A, B, C, which are also 1, 2, 3.

- There are columns in an array e.g. In the array $D$24:$K$35, spreadsheet Column D (which is the same as spreadsheet Column 4) is array column 1, spreadsheet Column E (which is the same as spreadsheet Column 5) is array column 2, etc.

- There is the COLUMN() function which returns the number of the spreadsheet Column. A=1, B=2, etc.

- There is the column_num argument that refers to an array column.I will do my best to refer to spreadsheet Columns with an uppercase C and array columns with a lowercase c to avoid confusion.

Let's look at the COLUMN() function first:

There are 2 ways to use the COLUMN function. One is to include a reference and one is to not. If you use a reference to a cell, the function will return the Column number for that cell.

With a reference:

=COLUMN(D6) will return 4 because Column D is the 4th Column of the worksheet. It doesn't matter where in the spreadsheet you put =COLUMN(D6), it will always return 4 because of the reference to a cell in Column D.

Without a reference:

=COLUMN() will return the Column number for whichever Column the function is used in. e.g. If =COLUMN() is used in Column D, it will return 4, if =COLUMN() is used in Column AJ, it will return 36.

Now, since the function returns a number, you can perform mathematical operations on the result.

If you put =COLUMN()-3 in Column D, you will get 1 because 4 - 3 = 1.

If you put =COLUMN()*2 in Column AJ, you will get 72 because 36 * 2 = 72.

Now, regarding the use of the COLUMN() function in the INDEX function:

The INDEX function requires a column_num argument. The key thing to remember is that the column_num argument refers to a column in the array that is referenced by the INDEX function, not a spreadsheet Column.

For example, in the INDEX function I suggested, the referenced array is $D$24:$K$35. Spreadsheet Column D is array column 1, spreadsheet Column E is array column 2, etc. Therefore, if you want to extract data from Column D (in your case), you have to use a column_num argument of 1 because the column_num argument refers to the column in the array $D$24:$K$35, not Column D of the spreadsheet.

So, if you want to refer to the first column of the array $D$24:$K$35, you need a column_num argument of 1. If you put the INDEX function I suggested in Column D, the COLUMN() function portion will return 4. As noted earlier, 4 - 3 = 1, so your column_num argument resolves to 1. When you drag the INDEX function over to Column E, you will get 5 - 3 = 2, etc. That's what makes the use of the COLUMN() function so helpful/powerful. It will increment the column_num argument by 1 each time, allowing you to pull data from contiguous cells, as shown in your example.

You could hard code those column_num arguments into each individual formula - and in some cases you might have too. For example, if you wanted to pull data from non-contiguous cells or from cells in different order than they are in the array e.g columns 4, 2, 1, 3, etc. it might be difficult to find a "helper" function to create the column_num argument since there is no pattern that Excel can follow.

The overarching concept here is that seemingly unrelated Excel functions can often be combined/nested in order to get Excel to perform tasks that might not seem possible just by looking at the functions individually. In this case, we are using the MATCH function to determine the row_num argument for the INDEX function and the COLUMN() function to determine the column_num argument.

As for your immediate issue, regarding the use of the INDEX function I suggested in a different cell or to reference a different table, my assumption is that you need to change the "-3" to something else if you want to use the COLUMN() function to determine the column_num argument of the INDEX function. As long as COLUMN() "plus or minus some value" resolves to 1, it will refer to the first column of the array referenced by the INDEX function. When it resolves to 2, it will refer to the 2nd column, etc.

Good luck!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Do you think Microsoft Office is too confusing to use?

Discuss in The Lounge

Poll History