Forum Discussion
I did it, guess so. The External Referencing.
Hey guys,
Thanks for your replies.
And eventually I made it work, in a very amateur & dumb manner.
So our problem is that we manage our accounts every day, here we have today's book and the one from yesterday.
And there are so many tags, we need to copy & paste the balance sitting in A2 from tag X, Book Yesterday to A2 under tag X Book Today. It's annoying and we made mistakes from time to time. So we decided to do something.
1) Our first solution is to link A2 Book Today to A2 Book Yesterday, after updating it we break the link then yesterday's balance is remained in Book Today. Pretty good.
But, we have too many tags - cell() can trace the tag name but it seems that you can't embed that in a link, which means we need to link them one by one, and update them when a tag name changes.
Crap.
2) Then I found indirect(), which can work together with cell(), meaning that we don't have to key in tag names separately. Cool.
The bad news is, indirect() creates a dynamic link - you close Book Yesterday, you lose its value in Book Today. Yes, there's a solution - just copy & paste the value to A2. But still, you have to repeat the step for each single worksheet.
Scrap that!
3) Eventually I turned to macro. Hmm, don't know where to start, there should be some key words or technical terms but obviously I have no idea. So I was just, looking for something relevant, blindly. And soon I found something like loop through worksheets. And there you go, it solved my problem.
Indirect() can read the value but can't save the value. Instead of manually copy&paste the balance in each worksheet, I tried macro and it works.
Here the solution.
A1=Balance
A2=INDIRECT("[book2.xlsx]"&(MID(CELL("filename",A2),FIND("]",CELL("filename",A2))+1,255))&"!A2")
Followed by the Macro
Sub WorksheetLoop()
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Range("A1") = "Balance" Then
ws.Range("A2").Copy
ws.Range("A2").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If
Next
End Sub
Hit the button & all dynamic values in A2s are replaced by fixed figures.
Sorted!
Apparently it's not the best but it works, and this is just what we need for now, so we'll be using it for a while.
So if you need, take & try it, it might be useful at work.
Just a reminder please use
(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255))
to pick up a worksheet name.
No using get.cell() function with name manager. These two formulas, although give you the same answer, seems to work differently. With get.cell() you may encounter issues when you try to save your figures with the macro above.
Don't really know what's happening inside there but so far as I know, get.cell() doesn't link to the tag name dynamically - if you change the tag name, (MID(CELL(Find will update itself instantaneously. Get.cell() on the other hand, displays the old answer till you double-click & activate the cell again, which is likely the root.
Good luck,
Dummy810
10 Replies
Deleted I love your comment your losing faith in your ability ! Ha Ha you will keep trying ! Touch Wood Touch Wood ! Not one person speaks like that here ! Hey Guy Machines are doing us in each day ! and we get paid to help the darn things ! You are fine and you will do fine ! JUST BREATH !
Deleted
Yes, INDIRECT doesn't work with closed files. The workaround could be, if without VBA, formula like
= IF(<current sheet>="Sheet1", [Book2.xlsx]Sheet1!A1, IF(<current sheet>="Sheet2", [Book2.xlsx]Sheet2!A1, IF(<current sheet>="Sheet3", [Book2.xlsx]Sheet3!A1, IF(<current sheet>="Sheet4", [Book2.xlsx]Sheet4!A1, IF(<current sheet>="Sheet5", [Book2.xlsx]Sheet5!A1 )))))where <current sheet> you may pick-up from CELL() formula. Even better is to keep helper cells for each sheet with current sheet name and use reference on it. Not very elegant but it could work if you don't have a hundreds of worksheets and don't add/remove sheets, at least frequently.
Hi Sergei thanks for your reply,
Your suggestion is awesome, and yeah the truth is that we do change our tags very.... often, that's why I want to embed a formula to help tag names update themselves automatically.
To your knowledge, is that possible to apply this formula (MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255))
in yours, so we don't have to update worksheet names manually.
Regards.
Deleted
Hi,
I guess this formula returns current sheet name. Yes, sure, you may use it instead of <current sheet>. But again, it's much better not to complicate the formula but use helper cell. Take it in the place where end-user can't see, in addition you may hide column/row with it, add your formula into this cell and in formula with links use reference on this helper cell.
