1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Reference a range of cells from one cell.

Discussion in 'Business Applications' started by athegn, Jan 17, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. athegn

    athegn Thread Starter

    Joined:
    Aug 19, 2006
    Messages:
    13
    Using Excell I have a table, on worksheet 'Attendees', with First names in column A and Last names in column B.

    I have another worksheet that I use to print place names. Place names have to be printed 8 per sheet.

    So I have a formula, on worksheet 'Placenames', in cell E7 '=Attendees!A2' and in F7 'Attendees!B2' then down for the next 7 rows. The actual place name cells use the formulae =E7&" "&F7 etc

    I amend cell E7 to start at the next 8 names e.g E10 and drag/copy E7 over F7 then drag/copy E7 & F7 down over the following 7 rows.

    I would like formulae in cells F7 and E8-F14 that would reference cell E7. I would then only have to amend Cell E7 and the place name fields would be updated.

    Would I use INDIRECT, OFFSET or some other formulae in these cells?

    Can anyone please advise?
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,836
    Rather than using a formula I would use a Visula Basic Macro.
    Copy the first 8 rows (2 columns), paste at your print destination.
    Print the selection.
    Move down 8 rows, copy and paste to your print destination.
    Print the selection.
    And so on until you have a cell with nothing in it.
     
  3. athegn

    athegn Thread Starter

    Joined:
    Aug 19, 2006
    Messages:
    13
    Thank you for your reply.

    However the problem is that the actual printed name cells are not contiguous. They are inter dispersed with other cells.

    Each place name label consists of one fixed text row, one graphic row, one more fixed text row then finally one row of combined first and last name, in two columns; 4 labels deep per page making 8 labels per page

    The formula =E7&" "&F7 is in the 5th row of the first column and =E8&" "F8 is in the 5th row of the second column, the formula =E9&" "F9 is in the 10th Row of the first column and so on.

    Any further advice is appreciated
     
  4. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Is there perhaps a sample to which you could upload as to show us your data structure? That would be helpful. Sounds like you could use a structural reorganization.
     
  5. athegn

    athegn Thread Starter

    Joined:
    Aug 19, 2006
    Messages:
    13
    I have not got anywhere to upload the spreadsheet to.

    This is a quick and dirty for a club. I am doing the printing this time but want to get some else to do so in future so getting them to edit one cell will reduce the likelyhood of errors.

    I am given a comma delimited text file of the first and last names, about 100. Quick import brings those in to worksheet 'Attendees" in columns A and B.

    The place names label area is designed to a given layout. the print area is 20 rows deep by two columns wide.

    The only problem is getting 8 combined first and last names from 'Attendees' into the 5th, 10th, 15th and 20th rows of the two columns of the print area on the printing worksheet. I could import the text file onto the same worksheet as the print area but that is immaterial.

    I hold a formula in the "5th, 10th, 15th and 20th rows of the two columns of the print area." e.g =E7&" "F7 in cell A5 on the print worksheet.

    Cell E7 holds the formula '=Attendees!A2'

    I want cell F7 to look at the 'A2' part of this formula and add 1 column and zero rows to that formula; this formula to be in cell F7. So in effect the formula in F7 reads '=Attendees!B2'

    Cell E8 should look at cell E7 and add 1 row but zero columns to the formula in E7. So in effect the formula in E8 reads '=Attendees!A3'

    So I will have 15 cells that refer back to cell E7 and decided from that cells reference address what cell information they are going to use.

    At present I edit cell E7 the drag/copy first to F7 then drag/copy E7 and F7 down to F14.

    It is the drag/copy I wish to eliminate

    Does this help explain my requirement.
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/535960

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice