+ Reply to Thread
Results 1 to 11 of 11

Returning list values

  1. #1
    Ron Coderre
    Guest

    RE: Returning list values

    Try this:

    On Sheet 1
    A6: =INDEX(Sheet2!$A:$A,MAX((ROW(A6)-6)*11,1),0)

    Copy that formula down as needed.

    Does that help?

    --
    Regards,
    Ron


    "bruner" wrote:

    > I have a list of names on sheet 2 of a worksheet that extends from A1 to
    > A249. I want to return name 1 on sheet 1 in A6, name 2 in A17, name 3 in
    > A28, etc. What formula can I use to do this?
    >
    > I figured out how to do it with offset when i'm copying and pasting for each
    > line (A1, A2, A3, A4, etc.), but not for every 11 rows without it skipping to
    > the 11th name on the list? Any suggestions?


  2. #2
    bruner
    Guest

    RE: Returning list values

    No, that doesn't work basd on my original quetion. It returns the 11th value
    in my list. I want to return first value in the list on A6, second entry in
    the list on A17, third value in the list on A28, etc.

    I can't just copy down, because there is important data in between the 11
    rows that I will copy over.


    "Ron Coderre" wrote:

    > Try this:
    >
    > On Sheet 1
    > A6: =INDEX(Sheet2!$A:$A,MAX((ROW(A6)-6)*11,1),0)
    >
    > Copy that formula down as needed.
    >
    > Does that help?
    >
    > --
    > Regards,
    > Ron
    >
    >
    > "bruner" wrote:
    >
    > > I have a list of names on sheet 2 of a worksheet that extends from A1 to
    > > A249. I want to return name 1 on sheet 1 in A6, name 2 in A17, name 3 in
    > > A28, etc. What formula can I use to do this?
    > >
    > > I figured out how to do it with offset when i'm copying and pasting for each
    > > line (A1, A2, A3, A4, etc.), but not for every 11 rows without it skipping to
    > > the 11th name on the list? Any suggestions?


  3. #3
    Ron Coderre
    Guest

    RE: Returning list values

    I think I have it this time:

    On Sheet2, put this formula in a cell in row 6 in a column that is blank:
    =INDEX(Sheet1!$A:$A,(((ROW()-6)/11)+1),1)

    Note: that formula should return the 1st item in the list on Sheet1.

    Copy that formula into rows 17, 28, etc.

    Now, copy from the first formula down through the last formula (including
    the blank cells).

    Select Cell A6 and
    Edit>PasteSpecial
    -->select formulas
    -->select Skip Blanks
    Click the [OK] button
    Press the [Esc] key to exit Copy/Paste mode.

    Does that help?

    --
    Regards,
    Ron


    "bruner" wrote:

    > No, that doesn't work basd on my original quetion. It returns the 11th value
    > in my list. I want to return first value in the list on A6, second entry in
    > the list on A17, third value in the list on A28, etc.
    >
    > I can't just copy down, because there is important data in between the 11
    > rows that I will copy over.
    >
    >
    > "Ron Coderre" wrote:
    >
    > > Try this:
    > >
    > > On Sheet 1
    > > A6: =INDEX(Sheet2!$A:$A,MAX((ROW(A6)-6)*11,1),0)
    > >
    > > Copy that formula down as needed.
    > >
    > > Does that help?
    > >
    > > --
    > > Regards,
    > > Ron
    > >
    > >
    > > "bruner" wrote:
    > >
    > > > I have a list of names on sheet 2 of a worksheet that extends from A1 to
    > > > A249. I want to return name 1 on sheet 1 in A6, name 2 in A17, name 3 in
    > > > A28, etc. What formula can I use to do this?
    > > >
    > > > I figured out how to do it with offset when i'm copying and pasting for each
    > > > line (A1, A2, A3, A4, etc.), but not for every 11 rows without it skipping to
    > > > the 11th name on the list? Any suggestions?


  4. #4
    bruner
    Guest

    RE: Returning list values

    I think i'm tracking with you, but i'm now a little confused. Which formula
    goes on sheet 2 and which one goes on sheet1?

    "Ron Coderre" wrote:

    > I think I have it this time:
    >
    > On Sheet2, put this formula in a cell in row 6 in a column that is blank:
    > =INDEX(Sheet1!$A:$A,(((ROW()-6)/11)+1),1)
    >
    > Note: that formula should return the 1st item in the list on Sheet1.
    >
    > Copy that formula into rows 17, 28, etc.
    >
    > Now, copy from the first formula down through the last formula (including
    > the blank cells).
    >
    > Select Cell A6 and
    > Edit>PasteSpecial
    > -->select formulas
    > -->select Skip Blanks
    > Click the [OK] button
    > Press the [Esc] key to exit Copy/Paste mode.
    >
    > Does that help?
    >
    > --
    > Regards,
    > Ron
    >
    >
    > "bruner" wrote:
    >
    > > No, that doesn't work basd on my original quetion. It returns the 11th value
    > > in my list. I want to return first value in the list on A6, second entry in
    > > the list on A17, third value in the list on A28, etc.
    > >
    > > I can't just copy down, because there is important data in between the 11
    > > rows that I will copy over.
    > >
    > >
    > > "Ron Coderre" wrote:
    > >
    > > > Try this:
    > > >
    > > > On Sheet 1
    > > > A6: =INDEX(Sheet2!$A:$A,MAX((ROW(A6)-6)*11,1),0)
    > > >
    > > > Copy that formula down as needed.
    > > >
    > > > Does that help?
    > > >
    > > > --
    > > > Regards,
    > > > Ron
    > > >
    > > >
    > > > "bruner" wrote:
    > > >
    > > > > I have a list of names on sheet 2 of a worksheet that extends from A1 to
    > > > > A249. I want to return name 1 on sheet 1 in A6, name 2 in A17, name 3 in
    > > > > A28, etc. What formula can I use to do this?
    > > > >
    > > > > I figured out how to do it with offset when i'm copying and pasting for each
    > > > > line (A1, A2, A3, A4, etc.), but not for every 11 rows without it skipping to
    > > > > the 11th name on the list? Any suggestions?


  5. #5
    Ron Coderre
    Guest

    RE: Returning list values

    Here's the way I read it...

    You have a list on Sheet1 that will be read by formulas on Sheet2.

    On Sheet2, you need formulas in rows 6, 17, 28, etc (every 11 rows) that
    read sequential items from the Sheet1 list. Consequently, the formulas go in
    Sheet2.

    Does that clear up the confusion?

    --
    Regards,
    Ron


    "bruner" wrote:

    > I think i'm tracking with you, but i'm now a little confused. Which formula
    > goes on sheet 2 and which one goes on sheet1?
    >
    > "Ron Coderre" wrote:
    >
    > > I think I have it this time:
    > >
    > > On Sheet2, put this formula in a cell in row 6 in a column that is blank:
    > > =INDEX(Sheet1!$A:$A,(((ROW()-6)/11)+1),1)
    > >
    > > Note: that formula should return the 1st item in the list on Sheet1.
    > >
    > > Copy that formula into rows 17, 28, etc.
    > >
    > > Now, copy from the first formula down through the last formula (including
    > > the blank cells).
    > >
    > > Select Cell A6 and
    > > Edit>PasteSpecial
    > > -->select formulas
    > > -->select Skip Blanks
    > > Click the [OK] button
    > > Press the [Esc] key to exit Copy/Paste mode.
    > >
    > > Does that help?
    > >
    > > --
    > > Regards,
    > > Ron



    > > > >
    > > > > "bruner" wrote:
    > > > >
    > > > > > I have a list of names on sheet 2 of a worksheet that extends from A1 to
    > > > > > A249. I want to return name 1 on sheet 1 in A6, name 2 in A17, name 3 in
    > > > > > A28, etc. What formula can I use to do this?
    > > > > >
    > > > > > I figured out how to do it with offset when i'm copying and pasting for each
    > > > > > line (A1, A2, A3, A4, etc.), but not for every 11 rows without it skipping to
    > > > > > the 11th name on the list? Any suggestions?


  6. #6
    bruner
    Guest

    Returning list values

    I have a list of names on sheet 2 of a worksheet that extends from A1 to
    A249. I want to return name 1 on sheet 1 in A6, name 2 in A17, name 3 in
    A28, etc. What formula can I use to do this?

    I figured out how to do it with offset when i'm copying and pasting for each
    line (A1, A2, A3, A4, etc.), but not for every 11 rows without it skipping to
    the 11th name on the list? Any suggestions?

  7. #7
    Ron Coderre
    Guest

    RE: Returning list values

    Try this:

    On Sheet 1
    A6: =INDEX(Sheet2!$A:$A,MAX((ROW(A6)-6)*11,1),0)

    Copy that formula down as needed.

    Does that help?

    --
    Regards,
    Ron


    "bruner" wrote:

    > I have a list of names on sheet 2 of a worksheet that extends from A1 to
    > A249. I want to return name 1 on sheet 1 in A6, name 2 in A17, name 3 in
    > A28, etc. What formula can I use to do this?
    >
    > I figured out how to do it with offset when i'm copying and pasting for each
    > line (A1, A2, A3, A4, etc.), but not for every 11 rows without it skipping to
    > the 11th name on the list? Any suggestions?


  8. #8
    bruner
    Guest

    RE: Returning list values

    No, that doesn't work basd on my original quetion. It returns the 11th value
    in my list. I want to return first value in the list on A6, second entry in
    the list on A17, third value in the list on A28, etc.

    I can't just copy down, because there is important data in between the 11
    rows that I will copy over.


    "Ron Coderre" wrote:

    > Try this:
    >
    > On Sheet 1
    > A6: =INDEX(Sheet2!$A:$A,MAX((ROW(A6)-6)*11,1),0)
    >
    > Copy that formula down as needed.
    >
    > Does that help?
    >
    > --
    > Regards,
    > Ron
    >
    >
    > "bruner" wrote:
    >
    > > I have a list of names on sheet 2 of a worksheet that extends from A1 to
    > > A249. I want to return name 1 on sheet 1 in A6, name 2 in A17, name 3 in
    > > A28, etc. What formula can I use to do this?
    > >
    > > I figured out how to do it with offset when i'm copying and pasting for each
    > > line (A1, A2, A3, A4, etc.), but not for every 11 rows without it skipping to
    > > the 11th name on the list? Any suggestions?


  9. #9
    Ron Coderre
    Guest

    RE: Returning list values

    I think I have it this time:

    On Sheet2, put this formula in a cell in row 6 in a column that is blank:
    =INDEX(Sheet1!$A:$A,(((ROW()-6)/11)+1),1)

    Note: that formula should return the 1st item in the list on Sheet1.

    Copy that formula into rows 17, 28, etc.

    Now, copy from the first formula down through the last formula (including
    the blank cells).

    Select Cell A6 and
    Edit>PasteSpecial
    -->select formulas
    -->select Skip Blanks
    Click the [OK] button
    Press the [Esc] key to exit Copy/Paste mode.

    Does that help?

    --
    Regards,
    Ron


    "bruner" wrote:

    > No, that doesn't work basd on my original quetion. It returns the 11th value
    > in my list. I want to return first value in the list on A6, second entry in
    > the list on A17, third value in the list on A28, etc.
    >
    > I can't just copy down, because there is important data in between the 11
    > rows that I will copy over.
    >
    >
    > "Ron Coderre" wrote:
    >
    > > Try this:
    > >
    > > On Sheet 1
    > > A6: =INDEX(Sheet2!$A:$A,MAX((ROW(A6)-6)*11,1),0)
    > >
    > > Copy that formula down as needed.
    > >
    > > Does that help?
    > >
    > > --
    > > Regards,
    > > Ron
    > >
    > >
    > > "bruner" wrote:
    > >
    > > > I have a list of names on sheet 2 of a worksheet that extends from A1 to
    > > > A249. I want to return name 1 on sheet 1 in A6, name 2 in A17, name 3 in
    > > > A28, etc. What formula can I use to do this?
    > > >
    > > > I figured out how to do it with offset when i'm copying and pasting for each
    > > > line (A1, A2, A3, A4, etc.), but not for every 11 rows without it skipping to
    > > > the 11th name on the list? Any suggestions?


  10. #10
    bruner
    Guest

    RE: Returning list values

    I think i'm tracking with you, but i'm now a little confused. Which formula
    goes on sheet 2 and which one goes on sheet1?

    "Ron Coderre" wrote:

    > I think I have it this time:
    >
    > On Sheet2, put this formula in a cell in row 6 in a column that is blank:
    > =INDEX(Sheet1!$A:$A,(((ROW()-6)/11)+1),1)
    >
    > Note: that formula should return the 1st item in the list on Sheet1.
    >
    > Copy that formula into rows 17, 28, etc.
    >
    > Now, copy from the first formula down through the last formula (including
    > the blank cells).
    >
    > Select Cell A6 and
    > Edit>PasteSpecial
    > -->select formulas
    > -->select Skip Blanks
    > Click the [OK] button
    > Press the [Esc] key to exit Copy/Paste mode.
    >
    > Does that help?
    >
    > --
    > Regards,
    > Ron
    >
    >
    > "bruner" wrote:
    >
    > > No, that doesn't work basd on my original quetion. It returns the 11th value
    > > in my list. I want to return first value in the list on A6, second entry in
    > > the list on A17, third value in the list on A28, etc.
    > >
    > > I can't just copy down, because there is important data in between the 11
    > > rows that I will copy over.
    > >
    > >
    > > "Ron Coderre" wrote:
    > >
    > > > Try this:
    > > >
    > > > On Sheet 1
    > > > A6: =INDEX(Sheet2!$A:$A,MAX((ROW(A6)-6)*11,1),0)
    > > >
    > > > Copy that formula down as needed.
    > > >
    > > > Does that help?
    > > >
    > > > --
    > > > Regards,
    > > > Ron
    > > >
    > > >
    > > > "bruner" wrote:
    > > >
    > > > > I have a list of names on sheet 2 of a worksheet that extends from A1 to
    > > > > A249. I want to return name 1 on sheet 1 in A6, name 2 in A17, name 3 in
    > > > > A28, etc. What formula can I use to do this?
    > > > >
    > > > > I figured out how to do it with offset when i'm copying and pasting for each
    > > > > line (A1, A2, A3, A4, etc.), but not for every 11 rows without it skipping to
    > > > > the 11th name on the list? Any suggestions?


  11. #11
    Ron Coderre
    Guest

    RE: Returning list values

    Here's the way I read it...

    You have a list on Sheet1 that will be read by formulas on Sheet2.

    On Sheet2, you need formulas in rows 6, 17, 28, etc (every 11 rows) that
    read sequential items from the Sheet1 list. Consequently, the formulas go in
    Sheet2.

    Does that clear up the confusion?

    --
    Regards,
    Ron


    "bruner" wrote:

    > I think i'm tracking with you, but i'm now a little confused. Which formula
    > goes on sheet 2 and which one goes on sheet1?
    >
    > "Ron Coderre" wrote:
    >
    > > I think I have it this time:
    > >
    > > On Sheet2, put this formula in a cell in row 6 in a column that is blank:
    > > =INDEX(Sheet1!$A:$A,(((ROW()-6)/11)+1),1)
    > >
    > > Note: that formula should return the 1st item in the list on Sheet1.
    > >
    > > Copy that formula into rows 17, 28, etc.
    > >
    > > Now, copy from the first formula down through the last formula (including
    > > the blank cells).
    > >
    > > Select Cell A6 and
    > > Edit>PasteSpecial
    > > -->select formulas
    > > -->select Skip Blanks
    > > Click the [OK] button
    > > Press the [Esc] key to exit Copy/Paste mode.
    > >
    > > Does that help?
    > >
    > > --
    > > Regards,
    > > Ron



    > > > >
    > > > > "bruner" wrote:
    > > > >
    > > > > > I have a list of names on sheet 2 of a worksheet that extends from A1 to
    > > > > > A249. I want to return name 1 on sheet 1 in A6, name 2 in A17, name 3 in
    > > > > > A28, etc. What formula can I use to do this?
    > > > > >
    > > > > > I figured out how to do it with offset when i'm copying and pasting for each
    > > > > > line (A1, A2, A3, A4, etc.), but not for every 11 rows without it skipping to
    > > > > > the 11th name on the list? Any suggestions?


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1