I did it, guess so. The External Referencing.

Deleted
Not applicable

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 

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.

@Sergei Baklan 

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.

@Sergei Baklan

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. 

 

 

@Deleted 

You may combine them with INDIRECT and that returns you back to initial problem.

@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

Yeah you're right. So I might, try something else I guess. Cheers
Haha, I'm losing faith in myself but I will try a bit more. Touch wood, touch wood.

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

HA Thanks for pump me mate. At last I made it, the solution is not straightforward, kinda stupid. But, it works. So we'll see how it goes. Hope it'll do the job.