How can I move numerous data to any favorite place in other sheet quickly ?

**omid020** · 05-25-2009, 04:44 AM

Hi,

Let`s assume that I have numerous "Serial Number" in "Sheet1" :
http://i40.tinypic.com/2iqbspt.jpg

Now I want to create some cards in "Sheet2" that each card have a serial number that related serial number there is in "Sheet1" :
http://i43.tinypic.com/34fz690.jpg

Please provide me an easy solution for doing it while when I drag one card to copying it then related serial number in sheet1 would be appear in required field. I mean first card has first serial number of sheet1 and second card has second serial number of sheet 1 ; but I want to doing it quickly with dragging .

Thanks

**JBeaucaire** · 05-25-2009, 10:26 AM

A little weird, by try this formula in the Sheet2 cell B2, then pull down as desired:

=INDIRECT("Sheet1!B"&(ROW()+7)/9+1)

**omid020** · 05-25-2009, 01:41 PM

Wow , thanks JBeaucaire

It works fine .
I found that 9 is number of rows in each card in sheet2 but could you please tell me what is 7 and 1 in that formula ? I can`t find these numbers !

**JBeaucaire** · 05-25-2009, 06:40 PM

Put those formulas in the designated cells, then turn on the Formula Auditing Toolbar. This is a useful place to understand new things.

With the cell highlighted, click the Evaluate Formula icon and then start clicking EVALUATE and watch the formula unfold one step at a time. You will see how the 7 and the 1 come into play.

In a nutshell, I'm trying to use the ROW() value to convert into values needed from the other list. The first CARD has the formula in Row2 and the first answer in Row2 on the other sheet. Row(2) = 2. 2 + 7 / 9 = 1. 1 + 1 = 2.

So, the first time around, we convert ROW(2) back into 2 with that math. But look what happens the next time the formula is used, in row(11).

Row(11) = 11. 11 + 7 / 9 = 2. 2 + 1 = 3.

So, we converted 11 into 3. The next time it will convert 20 into 4, so the designation works properly for incrementing down the list 1 row at a time on sheet of cards that is incrementing rows 9 at a time.

Fun with math.