Reference a range of cells from one cell.

975 Views 4 Replies 3 Participants Last post by athegn, Jan 17, 2007

athegn

Discussion Starter · Jan 17, 2007

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?

Save

Status: Not open for further replies.

1 - 3 of 5 Posts

athegn

Discussion Starter · #3 · Jan 17, 2007

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

Save

athegn

Discussion Starter · #5 · Jan 17, 2007

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.

See less See more

Save

1 - 3 of 5 Posts

Status: Not open for further replies.

Top Contributors this Month