PDA

View Full Version : Solved: placing formula in the same cell (D7) of all tabs

marreco
02-17-2013, 01:54 PM
Hi.

how to insert a formula in the same cell several spreadsheets?

I have a file with more than 40 tabs.

I need to insert a formula (= VLOOKUP (B6, DARF! \$ B \$ 9: \$ T \$ 569,19,0) in cell D7 of all (Unlike the tab "White"), guides File

Trebor76
02-17-2013, 02:06 PM
Hi marreco,

Do you need a macro or are you happy to do it manually (only requires about 3 or 4 steps)?

Robert

marreco
02-17-2013, 02:54 PM
Hi.
I fumbled in explaining.

I got by, but she always is calculating according to the first tab.
This spreadsheet posted note that all the tabs in cell B6 pull tab of the same name, which is the first sheet. But if I press F2 and then enter it pulls it right, what would be the correct one.

See my file
http://www.sendspace.com/file/p7eknz
I do not know why but I do not even compressing conseguio post my file on the forum.

Trebor76
02-17-2013, 03:02 PM
I got by

I'll take that as the issue is solved then as the link you provided seems to need an application to be run for it to work which I don't want to do.

Robert

marreco
02-17-2013, 03:08 PM
Hi.

see my formula
=MID(CELL("filename");SEARCH("]";CELL("filename"))+1;20)

Trebor76
02-17-2013, 03:47 PM
=MID(CELL("filename");SEARCH("]";CELL("filename"))+1;20)

Yes, that will return the active sheet name :confused:

marreco
02-17-2013, 03:51 PM
then I did it in more than 100 guides, but I need to return the name of the tab that is in the formula, but only returns the name of the first, the rest retrna always the first tab.
example.
I'm with the formula in the "two" then the formula returns "two"
I'm with the formula in the "three" then the formula returns "tres"

and so on

Trebor76
02-17-2013, 04:23 PM
Sorry, I'm afraid you've lost me :(

I'm sure someone else on the forum will be able to help.

Robert

marreco
02-17-2013, 04:28 PM
Hi
solved this way
Sub test()
For Each ws In Sheets
ws.Activate
ws.Range("B6") = ws.Name
Next
End Sub

Trebor76
02-17-2013, 04:49 PM
That's great!!

Note you don't have to select the tab to return it's name to cell B6, i.e.

Option Explicit
Sub test()
Dim ws As Worksheet

For Each ws In ThisWorkbook.Sheets
ws.Range("B6") = ws.Name
Next ws

End Sub

Robert

marreco
02-17-2013, 04:59 PM
Hi.
very good!

thank you!