Excel, assign cells in multiple worksheets from one sheet


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!
 
Ad

Advertisements

Joined
Feb 21, 2018
Messages
216
Reaction score
86
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:

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top