Sep 20 2019 07:24 AM - edited Sep 23 2019 05:23 PM
Sep 20 2019 07:24 AM - edited Sep 23 2019 05:23 PM
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
Sep 21 2019 06:59 AM - edited Sep 21 2019 08:41 AM
@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.
Sep 21 2019 07:32 AM - edited Sep 22 2019 06:33 AM
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.
Sep 21 2019 08:39 AM - edited Sep 21 2019 08:40 AM
@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.
Sep 22 2019 06:32 AM
Hi Sergei,
I've tried your formula it works well.
But yeah it is a bit bulky, haha, sorry to say that, I mean, because we have too many tags here & the number is still growing so it would be a pain to use that.
IF(<current sheet>="Sheet5", [Book2.xlsx]Sheet5!A1 )
I believe if I can replace that "Sheet5" following [Book2.xlsx] with cell(), i.e. [book2]Cell() in which cell() to pick up the tag name, then it will help me get what I want in book2 under same tag, and there's no need to change the formula manually.
But in fact it is an external link, so I'm not sure if we make it like that.
So far I've tried to put cell() and [book2] together over and over and over - a big fail.
What do you think? Is that because that I use connectors in a wrong way, like misplaced & ' " so excel can't read it, if so can you please give me some ideas. Or we just cannot embed cell() in an external link?
Thanks for your time.
Sep 22 2019 07:02 AM
@Deleted
You may combine them with INDIRECT and that returns you back to initial problem.
Sep 22 2019 07:17 AM
@Deleted Bulky ! That's the word you use ! I have been wondering about this whole situation and I am please with your mans advice He will help you ! But Bulky ! THAT IS AN UNDERSTATEMENT ! Good luck in your endeavors ! Richarrut
Sep 22 2019 07:28 AM
Sep 22 2019 07:31 AM
Sep 22 2019 07:44 AM
@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 !
Sep 22 2019 09:32 AM