如何将行插入到另一个表的列中?

I'm trying to prefill a template with repeating data in a column like this: Sheet1:

我试图用重复的数据填充模板，比如:Sheet1:

A1
Offer Name:
Offer URL:
Network:
Payout $:
Share%:

Offer Name:
Offer URL:
Network:
Payout $:
Share%:

...

What I'd love to do is insert data from Sheet2, which is in a row: Sheet2:

我想做的是插入来自Sheet2的数据，它是一行:Sheet2:

A1      B1      C1          D1  E1
name1   link1   network1    1   5%
name2   link2   network2    1   5%

and have that prepopulate Sheet1 like this:

像这样预填充Sheet1:

A1          B1
Offer Name: name1
Offer URL:  link1
Network:    network1
Payout $:   1
Share%:     5%

Offer Name: name2
Offer URL:  link2
Network:    network2
Payout $:   1
Share%:     5%

So I could drag the filler down in the Sheet1 and have it prefill as much content as there is in Sheet2. I've tried concatenate, but formula doesn't work.

所以我可以把填充物拖放到Sheet1中，让它像在Sheet2中一样，填充更多的内容。我试过串联，但公式不起作用。

Is it possible to do with a formula?

有可能使用公式吗?

2 个解决方案

#1

Assuming Sheet1 starts in A1 with Offer Name: and name1 is in Sheet2 A1 please try in Sheet1 B1 copied down to suit:

假设Sheet1在A1中以Offer Name开头，name1在Sheet2 A1中，请尝试将Sheet1 B1拷贝下来以适应:

=OFFSET(Sheet2!A$1,INT(ROW()/6),MOD(ROW()-1,6))  

You might series fill Sheet1 ColumnA by selecting A1:A12 and dragging the fill handle down to suit. If desired to remove the 0 from B6, B12 etc. then filter ColumnA to select (Blanks) and delete the zeros. Also use filter if required to format the percentages.

您可以通过选择A1:A12并拖动填充手柄来填充Sheet1 ColumnA。如果需要从B6、B12等中删除0，则过滤ColumnA以选择(空格)并删除0。如果需要格式化百分比，也可以使用filter。

#2

If you're in love in keeping your Sheet1 ordered vertically while still listing your fields vertical as well you're going to have to make some allowances. Like Hambone suggested this would be more rigorously done in VBA. If you still prefer to just use formulas here is a potential solution. For each row item in Sheet1 you would use the following formula:

如果你喜欢让你的Sheet1保持垂直排列，同时也保持你的字段的垂直排列，你将不得不做一些让步。就像Hambone说的那样这在VBA会更严格。如果你仍然喜欢用公式，这是一个可能的解。对于Sheet1中的每一行，您将使用以下公式:

="Offer Name: "&Sheet2!A1&CHAR(10)&"Offer URL: "&Sheet2!B1&CHAR(10)&"Network: "&Sheet2!C1&CHAR(10)&"Payout $: "&Sheet2!D1&CHAR(10)&"Share%: "&Sheet2!E1

The implication is that this will display all the fields for each item in 1 cell with forced carriage returns in between them. This allows you to be able to drag your formula down and capture for all items. You have to set your cells to wrap text to be able to view correctly. Hope this helps. Regards,

其含义是，这将显示一个单元中每个项目的所有字段，并在它们之间返回强制回车。这样你就可以把你的公式拖拽到所有的项目上。您必须设置单元格来包装文本，以便能够正确地查看。希望这个有帮助。问候,