Hello,
I have a spreadhseet with listings of all 50 states and its
populations and than I have a 2nd sheet that has each of these states
listed but seperated by 6 rows. What I would like to do is use
vlookup to bring in the populations from sheet 1 to sheet2 but paste
them seperated by 6 rows. For example.
Sheet1:
State Populations
Alabama 1 mil
Alaska 80,000
Arizona 10 mil

Sheet2:
State Populations
Alabama 1 mil
-
-
-
-
-
-
Alaska 80,000
-
-
-
-
-
-
Arizona 10 mil

thanks!

Type your VLOOKUP formula into the cell next to Alabama
Let's say this is B2
Select and copy that cell and the next 6 empty cells
In the name box type B8:B351 and press enter (this selects B8:B351)
Use CTRL+V to paste

I get 351 from (50 states times 7 cells/state) plus 1 since we began in B2
not B1
Adjust if you have Wash Dc as a 'state'
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

wrote in message
...
Hello,
I have a spreadhseet with listings of all 50 states and its
populations and than I have a 2nd sheet that has each of these states
listed but seperated by 6 rows. What I would like to do is use
vlookup to bring in the populations from sheet 1 to sheet2 but paste
them seperated by 6 rows. For example.
Sheet1:
State Populations
Alabama 1 mil
Alaska 80,000
Arizona 10 mil

Sheet2:
State Populations
Alabama 1 mil
-
-
-
-
-
-
Alaska 80,000
-
-
-
-
-
-
Arizona 10 mil

thanks!

On Nov 6, 4:01*pm, "Bernard Liengme"
wrote:
Type your VLOOKUP formula into the cell next to Alabama
Let's say this is B2
Select and copy that cell and the next 6 empty cells
In the name box type B8:B351 and press enter (this selects B8:B351)
Use CTRL+V to paste

I get 351 from (50 states times 7 cells/state) plus 1 since we began in B2
not B1
Adjust if you have Wash Dc as a 'state'
best wishes
--
Bernard V Liengme
Microsoft Excel MVPhttp://people.stfx.ca/bliengme
remove caps from email

wrote in message

...



Hello,
I have a spreadhseet with listings of all 50 states and its
populations and than I have a 2nd sheet that has each of these states
listed but seperated by 6 rows. *What I would like to do is use
vlookup to bring in the populations from sheet 1 to sheet2 but paste
them seperated by 6 rows. *For example.
Sheet1:
State * * * * * Populations
Alabama * * *1 mil
Alaska * * * * 80,000
Arizona * * * *10 mil

Sheet2:
State * * * * *Populations
Alabama * * 1 mil
-
-
-
-
-
-
Alaska * * *80,000
-
-
-
-
-
-
Arizona * * 10 mil

thanks!- Hide quoted text -

- Show quoted text -

I was looking more for a VBA code, but I did try your suggestions. I
was confused with the name box that you mentioned, is that the one on
the Insert function where you could name a range? thanks!