Formula is too long.

[xlStar]

Hello, my VBA is working fine, it is creating a report base on same reports from several files - these files are varies in row numbers.

The formula got too long ...

=+'S:\fincon\(3)OneWorldAccy\Budget 08-09\test\[BUDG0809-RES-DCX.xls]D Report'!$B$30+'S:\fincon\(3)OneWorldAccy\Budget 08-09\test\[BUDG0809-RES-RISK.xls]D Report'!$B$36+'S:\fincon\(3)OneWorldAccy\Budget 08-09\test\[BUDG0809-RES-LEGAL&DS.xls]D Report'!$B$24+'S:\fincon\(3)OneWorldAccy\Budget 08-09\test\[BUDG0809-RES-IT.xls]D Report'!$B$20+'S:\fincon\(3)OneWorldAccy\Budget 08-09\test\[BUDG0809-RES-CFO.xls]D Report'!$B$48+'S:\fincon\(3)OneWorldAccy\Budget 08-09\test\[BUDG0809-RES-CORP.xls]D Report'!$B$20+'S:\fincon\(3)OneWorldAccy\Budget 08-09\test\[BUDG0809-RES-HR.xls]D Report'!$B$18+'S:\fincon\(3)OneWorldAccy\Budget 08-09\test\[BUDG0809-RES-C&F.xls]D Report'!$B$32+'S:\fincon\(3)OneWorldAccy\Budget 08-09\test\[BUDG0809-RES-E&R(60).xls]D Report'!$B$18+'S:\fincon\(3)OneWorldAccy\Budget 08-09\test\[BUDG0809-RES-CORPITEMS.xls]Corporate'!$B$167+'S:\fincon\(3)OneWorldAccy\Budget 08-09\test\[BUDG0809-RES-CORPITEMS.xls]Corporate'!$B$50


Is there a way to place "'S:\fincon\(3)OneWorldAccy\Budget 08-09\test\" elsewhere on the main report say A1 and get the formula to pick up that cell follows by the workbook/worksheet name? i.e...


="="&A1&"[BUDG0809-ADS-ASHH.xls]D Report'!$B$34"

This doesn't work, is there a way of doing something similar?

 

[SkipVought]

Hi,

Check out the INDIRECT function.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[xlbo]

some useful info here: thread68-1277173

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[xlStar]

Hi.

I've been told off for not applying Stars to other users who responded to my threads, however I am not applying any stars for this thread.

Please be aware that INDIRECT.EXT is NOT useful as I hoped it would be.

I have downloaded the software to obtain the AddIns to use the INDIRECT.EXT function and amended my VBA codes accordingly. The macro takes 40 seconds for each cell to update the INDIRECT.EXT formula with new linked cell code.

When the macro finished, on the sheets that contains INDIRECT.EXT formula - everytime you click on a cell it goes through updating formula which takes a mintue to complete and i could switch the calculation to manual as I need to do work on another sheet.

I thought I point this out to other users that INDIRECT.EXT isn't the prefect answer. What I have done instead is to create a new sheet that contains linked to other workbooks by listing them row by row; then on my original sheet the formula would sum the listed amount on that new sheet. For this method the macro runs incredibly faster than INDIRECT.EXT functions ones and theres no need for calculation update.

 

[xlbo]

Well - you asked how to reference text as a range. You were given the answer. If you had asked "How can I lay out my spreadsheet to enable me to extract data from closed workbooks" you may have got a different answer.

I'm not fishing for stars (I have plenty) but your tone is one of disappointment so I thought I would point out why you got the responses that you did...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AnotherHiggins]

Hi, xlStar.

I don't want you to be bitter about Tek-Tips.

Folks don't get "told off" for not awarding stars to everyone who replies. That isn't the intent of the stars.

They are meant to be awarded to posts that correctly/successfully answer your question or that you found helpful.

This does more than just say "thanks" to the responder, it also lets future visitors - visitors who may be looking for an answer to the same problem that you are asking about - see that a thread contains useful information.

That having been said, I'd like to point out that INDIRECT.[!]EXT[/!] does not equal INDIRECT.

Skip's suggestion of the Indirect function satisfied the criteria set forth in your Original Post. Geoff's link went further, suggesting other options including INDIRECT.EXT.

As Geoff pointed out, you never said anything about needing to link to closed workbooks.

Please see the links in either my or Geoff's signature lines for tips on how to ask a question in such a way as to get the answer you are looking for.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Markus4]

xlStar:

I am just a looker-on in this forum because my knowledge is not as great as that of the others. All of them try to help because they know how it feels to be stuck with a problem, how minor it may be.

Please be aware that INDIRECT.EXT is NOT useful as I hoped it would be.

This, INMHO, is not how you should be answering to anybody who makes an effort to help you with your personal problem.
As I read "is NOT useful as I hoped" and as I am German I had to look up the translation for what I was feeling. The term seeming appropriate to me was "holier-than-thou".
I have comprehended this forum as a place where people with problems can ask for help. There is no entitlement to a solution just by asking a question in this forum. Just imagine what would happen if a scrupulous merchant would be sending bills to those who sought and received advice.
M.