copy (automatically) data from one cell into 2 cells in another sheet

[dishguy05]

Hi, i would like to have a spreadsheet with 2 sheets (each sheet will have headers)

I will paste numbers in sheet1, and i want those numbers to be copied into 2 cells in sheet2

let's say in sheet1, i enter the following
1
2
3

in sheet2, i want to see this
header1, header2, header3
1-a, 1-0, sometext
1-b, 1-0, sometext
2-a, 2-0, sometext
2-b, 2-1, sometext
3-a, 3-0, sometext
3-b, 3-1, sometext

i tried to create a formula, but if i highlight 2 rows and drag down, it skips rows from sheet one. I'm not too familiar with vba scripting and i was hoping to find a way this can be done using formulas.. but if scripting is the way to go, i can manage that as well.

thanks in advance for your help.

[azumi]

Maybe more clear if you give us the sample workbook, try upload it at the forum, maybe member can give you the solution.

To attach, click "GO Advanced" button and find paperclip button to attach the file

Cheers

[dishguy05]

thanks for your reply. i've added a sample spreadsheet.

there are 2 sheets in the workbook (sheet1 and sheet2)
in sheet1, it is going to be a table, where i can paste data in column D starting at D2. A, B, C are going to be static so when i paste data in D2 it will prefill the other stuff as needed.

Example:
Header1 , Header2 , Header3 , Header4
Static_text1, Static_text2, Static_text3, Num1
Static_text1, Static_text2, Static_text3, Num2
Static_text1, Static_text2, Static_text3, ....
Static_text1, Static_text2, Static_text3, NumX


In sheet2, i want 2 rows created for each row which has data in Sheet1, column D

Example:
header4, header5, Header6
Num1-x, Num1:0, Static-text
Num1-y, Num1:1, Static-text
Num2-x, Num2:0, Static-text
Num2-y, Num2:1, Static-text
..., ..., ...
NumX-x, NumX:0, Static-text
NumX-y, NumX:1, Static-text

[azumi]

Header 4 is ok, but Header 5 and 6 whereis come from? I dont see it at Sheet 1, need to clarify

[dishguy05]

the headers in sheet1 and sheet2 are separate (different) so i put it as 1,2,3,4 in sheet1 and 5,6,7 in sheet2... i think i might have a typo, but the headers would be completely different in both sheets.

[azumi]

And you need to retrieve data based on what? if based on column header is possible, but its different, i think its difficult to achieve

[dishguy05]

i will enter data in sheet1, column D

in sheet 2, the data in column D should be used to add -x, -y, :0, :1