Need help with formula

T

Tim

Hi,

In Column "N" in my worksheet named "names" I have a list
with names from N1:N350.
In the same workbook there are 350 sheets named Sheet1,
Sheet2.Sheet350.
I need a formula to send the names in column N on
sheet "names" to cell A1 on sheets 1,2,3.350.
For example the name on cell N1 on sheet "names" goes to
cell A1 on sheet1, the name on cell N2 on sheet "names"
goes to cell A1 on sheet 2.and so on.
 
F

Frank Kabel

Hi
not really possible with a formula. A formula can't 'send' data to
other cells. This would require either VBA or a formula in each A1 cell
of your sheets.
If your sheets are all names 'sheetNNN' you may try the following
formula in each of these A1 cells:
=OFFSET('names'!$N$1,--SUBSTITUTE(MID(CELL("Filename",A1),FIND("]",CELL
("Filename",A1))+1,255),"sheet","")-1,0)
and copy this formula to all A1 cells.
Not tested but to simplify this entry try:
- select cell A1 on sheet 1
- hold down the SHIFT key and select sheet350
- enter the formula in Cell A1 and hit CTRL+ENTER
- now ungroup the sheets
 
B

Biff

Nice one, Frank!

Just a note of interest.

If you use the default sheet names - Sheet1,Sheet2, then the reference to
"sheet" in the SUBSTITUE function must also appear as "Sheet". The formula
didn't work until I made that very slight correction.

Also, for the Op, the file must have already been saved at least once for
this to work.

Great logic!

Biff

Frank Kabel said:
Hi
not really possible with a formula. A formula can't 'send' data to
other cells. This would require either VBA or a formula in each A1 cell
of your sheets.
If your sheets are all names 'sheetNNN' you may try the following
formula in each of these A1 cells:
=OFFSET('names'!$N$1,--SUBSTITUTE(MID(CELL("Filename",A1),FIND("]",CELL
("Filename",A1))+1,255),"sheet","")-1,0)
and copy this formula to all A1 cells.
Not tested but to simplify this entry try:
- select cell A1 on sheet 1
- hold down the SHIFT key and select sheet350
- enter the formula in Cell A1 and hit CTRL+ENTER
- now ungroup the sheets


--
Regards
Frank Kabel
Frankfurt, Germany

Tim said:
Hi,

In Column "N" in my worksheet named "names" I have a list
with names from N1:N350.
In the same workbook there are 350 sheets named Sheet1,
Sheet2.Sheet350.
I need a formula to send the names in column N on
sheet "names" to cell A1 on sheets 1,2,3.350.
For example the name on cell N1 on sheet "names" goes to
cell A1 on sheet1, the name on cell N2 on sheet "names"
goes to cell A1 on sheet 2.and so on.
Click to expand...
Click to expand...
 
F

Frank Kabel

Hi Biff
Nice one, Frank!
Click to expand...
Thanks :)
If you use the default sheet names - Sheet1,Sheet2, then the reference to
"sheet" in the SUBSTITUE function must also appear as "Sheet". The formula
didn't work until I made that very slight correction.

Also, for the Op, the file must have already been saved at least once for
this to work.
Click to expand...

Good points. I messed the case in the SUBTITUTE formula :)

Frank
 
Top