define named range in Excel 2003 INDIRECTLY referencing another workbook


define named range in Excel 2003 INDIRECTLY referencing another workbook



I am trying to define a named range in a particular workbook in Excel 2003. Specifically, I want to assign the name "Name" to a range (list of data) on, let's say, Sheet 4 of workbook "Hours". This list of data is itself an output of formulas referencing another workbook ("Task Break-Down"). For example, cell A2 of Sheet 4 ("Hours" workbook) ='[Task Break-Down.xls]Sheet2'!A2&"".



When I create/define the named range as described above, it will show up in the list of named ranges when clicking ctrl F3. However, when I reference the named range in a formula (using "Name"), it doesn't work. Also, when I click the little drop down arrow next to the name box in the top left corner to display named ranges, "Name" does not show up. Any idea why?



I am trying to use the range "Name" (again, consisting of outputs of formulas referencing another workbook) as a reference within a formula that specifies the "source" in data validation.



The range "Name" is defined as follows:


=OFFSET(Sheet4!$B$1,1,0,SUMPRODUCT((Sheet4!$B:$B<>""+0)-1,1)



It is meant to be dynamic range (thus OFFSET...).



The data validation source is as follows:


=OFFSET(OFFSET(Name,0,1,1,1),Match(E2,Name,0)-1,0,COUNTIF(Name,E3))



Interestingly, the reference "Name" and data validation drop down (as defined above) work perfectly when opening in excel 2007+ in compatibility mode (.xls).



any help would be greatly appreciated.



Thanks!





Does this happen when the filename has no space in it?
– pnuts
Jun 27 at 17:50





yes, it does. I think it has to do with the SUMPRODUCT part of the formula defining the range "Name" (particularly the special characters <>"", most likely). I'm saying this because I tried replacing the SUMPRODUCT part with COUNTA(Shet4!$B:$B)-1 and it works. However, the problem with that is that the range will include cells that contain formulas in them who's output result in nothing (cells look empty...similar to =""), which is not what I want. I want to exclude empty-looking cells from the named range.
– Jac
Jun 27 at 18:36





Is there another way to specify that the range should include only cells actually showing a value, besides the sumproduct function with special characters above?
– Jac
Jun 27 at 18:37





again, it's interesting that the whole thing (with SUMPRODUCT) works great in Excel 2007 and up.
– Jac
Jun 27 at 18:38









By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Comments

Post a Comment

Popular posts from this blog

paramiko-expect timeout is happening after executing the command

paramiko-expect timeout is happening after executing the command Can someone help me why timeout is happening after executing the command .I am trying to SSH to a machine and execute a command.I want to store the result of the executed command . I have referred paramiko-timeout but i didn't get my expected result. import traceback try: import paramiko except ImportError: raise Exception("Please install paramiko , pip install paramiko") import pexpect from paramiko_expect import SSHClientInteraction def main(): HOSTNAME = "minion5.net" USERNAME = "root" PASSWORD = "help@432" PROMPT = "root@*:~$s+" try: client = paramiko.SSHClient() client.load_system_host_keys() client.set_missing_host_key_policy(paramiko.AutoAddPolicy()) client.connect(hostname=HOSTNAME, username=USERNAME, password=PASSWORD) with SSHClientInteraction(client, timeout=20, display=True) as interact: ...
Read more

Export result set on Dbeaver to CSV

Export result set on Dbeaver to CSV Normally I use Dbeaver for windows and always export my result set like this: run my query --> select the result --> export the result set --> select export to clipboard --> done This step by step puts my result set in my clipboard and I can paste it wherever I want to work with it. The problem is that now I am using dbeaver for mac and this guide is not working. I can go on until the moment that I select my result set like in the image below: exporting data set But once I go further in the process, in the last step I get: no query Note that in "source" it was suppose to show the query that originated the result set, but instead it says just "select. As a result it does't select my result or anything (besides being "successful"). Normally my query would show up there automatically and I couldn't find any option that corrects this problem in the menus. Please help me! Thanks. Aft...
Read more

Opening a url is failing in Swift

Opening a url is failing in Swift breakpointing inside the callback and logging success shows false. I added itms-apps to my plist: LSApplicationQueriesSchemes and put this in my app delegate: func application(_ app: UIApplication, open url: URL, options: [UIApplicationOpenURLOptionsKey : Any] = [:]) -> Bool { return true } func moreTapped() { let url = NSURL(string: "itms-apps://itunes.com/developer/quantum-productions/id979315877") if #available(iOS 10.0, *) { UIApplication.shared.open(url! as URL, options: [:], completionHandler: {(success: Bool) in }) } else { UIApplication.shared.openURL(url! as URL) } } Are you testing on a real device? – rmaddy Jun 21 at 23:57 I try on a device, get "Cannot Connect to App Store". Device is online ...
Read more