# 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?

#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.

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?

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