What formula to copy data to table?

September 30, 2016 at 01:44:23
Specs: Windows 64
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


See More: What formula to copy data to table?

Reply ↓  Report •


#1
September 30, 2016 at 09:43:14
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_array C24:C35 contains the value in C6. It will use that as the row_num argument for the INDEX function.

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.

Let us know if you have any questions.

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


Reply ↓  Report •

#2
October 3, 2016 at 18:03:03
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).

Reply ↓  Report •

#3
October 3, 2016 at 19:38:12
Let's recap:

Your subject line says "What formula to copy data to table?"

The text in your post says "...how can it copy automatically data from table 2 to table 1"

In your latest response you said: "But the function did not copy the format cells."

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.


Reply ↓  Report •

Related Solutions

#4
October 3, 2016 at 21:09:03
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?


Reply ↓  Report •

#5
October 4, 2016 at 08:42:45
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.


Reply ↓  Report •


Ask Question