Referencing Ordered Cells

[coldcast]

Hello!

I'm trying to work with some raw data and the data is organized in the following way:

SHEET1

A name is in cell A8, with an Address on cell A9, a Telephone number on A10.

SHEET2

I want to have a column in the second sheet called "NAMES" and under it, I reference the name on the other sheet by typing ='SHEET1'!A8

But now, I want to reference the next name, which is on A13. How can I make it easy for me to copy and paste a formula, so that it adds the names every 5 cells down? I dont want to manually type in every cell 'SHEET1'!A13, then 'SHEET1'!A18 etc. etc.


What are your suggestions? Thank you for your help!

 

[Tom Urtis]

Suppose on Sheet2, in cell A1 is the header label NAMES. You said you want a formula on Sheet2, which based on my assumption would start in cell A2, to return whatever is in cell A8 on Sheet1. Then, cell A3 on Sheet2 would return whatever is in cell A13 on Sheet1, cell A4 on Sheet2 would return whatever is in cell A18 on Sheet1, and so on. For one way to do this, in cell A2 of Sheet2 you can enter and copy down this formula:

=OFFSET(Sheet1!$A$3,ROW(A2)*5-5,0)

 

[coldcast]

Thank you, that worked just as I wanted!!!

 

[Smilechild793]

Hello,

I have to track various trainings for work. There is an extreme amount of data I have to track/update that extends across two workbooks.

Is there a way to update info on a master sheet/workbook to automatically update in another sheet/workbook?

What I've done in the first wkbk is:
1. Organize the data by job title/department, then frequency and training name.
1A. Then placed X's in the cells for trainings that employees have completed and linked that to other sheets individually divided by departments for ease of view.

For the second wkbk:
1. I have to be able to enter into a program called target solutions.
2. To do so, I need the name, department, and trainings completed by that person listed out.
2A. I have done that by creating a drop down list with every training and have been individually going in and updating those drop downs with the correct training that they have completed referencecd from the 1st workbook (still currently updating to match the X's in the first workbook.. which is why I'm asking for help. I need an easier automatic way to do so).

Would it be possible to email you the workbooks I'm working with? It is kind of confusing without seeing it..

P.S. Im placing this on this thread because your message box is full