how to activate the function/formula in subsequent cell
suppose,
Column-B, is filled with formulas or links to next multiple worksheets.
I need the value arrived by activating the links or formulas typed in the same row.
eg,
in the workbook XYZ.xls,
B1 contains, [abc.xls]sheet1!B1
B2 contains, sum(B3:b9)
now i need the value in A1, by activating the function in B1.
Thank u in advance.
Im not sure I understand what you are asking? if B1 contains, [abc.xls]sheet1!B1, then B1 with give you the answer to that anyway, it does not need to be "activated" (unless you have calcs set to manual, then just press F9)
and if you want the same answer in A1 as in B1, then in A1 just type =B1
if this is not what you meant, i would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"
Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
## additional facts, might be helpful,
1. we cant use vlookup, as the data source contains multiple cells with same value.
2. the data source will be frequently deleted and replaced with new updated data.
3. data source are from multiple sheets/workbooks
4. indirect function alone could be used, but this alone is very time consuming, so seeking for an alternative way
hope this will clarify my requirement.
thanks in advance.
Not sure whether this is what you are looking for
In F3 cell
Formula:
Please Login or Register to view this content.
To ignore 0 result when the source cell is empty
In F3 cell
Formula:Please Login or Register to view this content.
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
@Sixthsense, this is not sufficient to solve. i think u didnt get my requirement. pls refer previous thread.
thank u.
@jbshew,
Sorry still I am unable to understand your requirement
the rihgt formula at Sheet1!A3 must be
IDIRECT(B3) not INDIRECT("B3")
etc
so we know that the supposed solution is indirect
can you explain whysince you will just use the fill handle on the given solutions...4. indirect function alone could be used, but this alone is very time consuming, so seeking for an alternative way
also maybe like this
=indirect( D1 & E1 & F1)
I think people forget the word "THANK YOU!!!!" Do you still know it???
There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "
Regards,
Vladimir
@vlady,
this worked. but only PARTIALLY !
=indirect(D1&E1&F1) is working if the reference is within the workbook (eg, D1 is Sheet1)
but if reference is made to the next workbook, its not able to get the reference data. (eg, D1 is [Xyz.xls] )
thank u
Another quick question whether this workbook [Xyz.xls] will kept open or it will be in closed mode? Since Indirect won't work with the closed workbooks
@sixthsense,
this is a big problem now.... is there any other function which is able to work in similar way but also in an closed workbooks??
The answer is "No". None of the builtin worksheet function will do the work of Indirect with closed workbook.
Or
You just try to keep the sheets within the same workbook instead of keeping it in separate workbooks.
@sixthsense,
this is a big problem now.... is there any other function which is able to work in similar way but also in an closed workbooks??
The answer is Yes.
You download morefunc addin by way of a google search you will get the addin.
Indirect.Ext one of the function of Morefunc addin which will work with closed workbooks also.
Indirect.Ext (Morefunc Addin).JPG
this will defiantly help... thank u @sinthsense .
could you pls suggest me the download link for morefunc addin.... as i was nt able to find appropriate source...
thank u so much..
I googled...indirect.ext download...and came up with a number os options to download it. perhaps you're search criteria was inadequate?
Thank u so much for the replies frens
finally found it
but there is some problem while using it...
I am attaching the workbook for reference, to show how i have tried to use it...
hope this will be helpful finding the problem.. could anyone pls suggest what is the problem and a solution??
I have used the Indirect.ext in the 'main' and another is for reference..
Thanks in advance frens...
I wont be able to test what you have, because i am behind a company firewall that will not allow loading add-on's like that, sorry
The way you referred the root path is something incorrect. Just press = in main.xlsx and refer A1 cell of Support workbook and close the support workbook and see the main.xlsx formula to know how the root path is referred.
Check the attached screenshot to know how to use Indirect.Ext.
INDIRECT.EXT - Function Help.JPG
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks