Excel, assign cells in multiple worksheets from one sheet

H

HBards

Joined
Jan 17, 2018
Messages
1
Reaction score
0
Hi,

I'm trying to use a list of tables in one excel sheet in my workbook to assign the first cell of the rest of the sheets. So - Sheet 1 ("Index") has a list in B2:B124 - 1.1.1, 1.1.2 ... 9.2.6.

I then have 123 further sheets in the workbook and want cell A1 in each of these cells to be the next item in the list in the Index sheet.

I don't have Kutools for excel but can open and run VBA (that's the extent of my VBA abilities though!)

Thanks in advance for your help!
 
what are the names of the sheets in your workbook. The first sheet sheet1 contains "index" that's fine.
If the sheet names are like this Sheet1, Sheet2, Sheet3...upto sheet123.

then following formula is for you.

Assuming your index is located in sheet1 range A1:A123

=OFFSET(Sheet1!$A$1,VALUE(MID(CELL("filename"),FIND("]",CELL("filename"))+6,15))-1,0)
 
Last edited:
Back
Top