Can I pass a string in a cell to construct a filename .... 2

[Hasit]

...if the cell is selected?

What I mean is that I have in column A a different set of names. In column B, you can put an X in the corresponding cell. What I want to do is pass the name in column A, against which there is an X, to a file name, so that certain cells within that file are presented.

e.g.

COLUMN A COLUMN B
Fred
Brian
Dave X
Jim

Cell C1 has an IF function that has as part of its formula a file name, which needs to be preceded with the name in column A, which has an X next to it (in this example, its "Dave&quot, so the filename would be complete, and the function could return the results from filename "Davefile.xls".

Anyone have any thoughts...?

 

[ETID]

in cell c3

=if(upper(b3)="X","a3&"file.xls","&quot

 

[ETID]

oops...
had an extra quote in there


=if(upper(b3)="X",a3&"file.xls","&quot

 

[DaleWatson123321]

Hasit,

The problem I see with ETID's formula, is that it refers ONLY to the row on which "Dave" is located - whereas you need a formula which can apply to the ENTIRE list of names.

There are usually various options, but here's one which will work - providing that there will be only ONE "X" entered at any one time.

=DGET(data,1,CRIT)&"file.xls"

The above "database" formula will return whichever name has an "X" in Column B.

It requires that you set up your list ("name column" and "X" column) as a small database, i.e.

1) Enter "Field Names" at the top of each column.

2) Assign a Range Name to the database - I have used the name "data".

3) Set up a "criteria" range (referred to in the above formula as "CRIT&quot. To do this, copy the field name you use for column "B" to a separate cell, and enter an "X" in the cell below it.

4) Then assign a Range Name to the those two cells (I have used the name "CRIT&quot.

Once you have taken the above 4 steps, enter the above formula, and it will work.

I hope this helps. Please advise.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Hasit]

ETID: thank you for the suggestion. It has worked.

Dale: Good point on the slight limitation with ETID's suggestion. Excuse my ignorance, as I would prefer to use your suggestion, as I want to make the spreadsheet is stupid-proof as possible, as I see your recommendation as achieving that. Small problem is I have no idea how to set up the "small database", or assign field names etc. I have checked help in Excel, but its pretty poor.

Can you step me through the process with where I can find the options to assign field names, range names etc...

Thanks!

 

[DaleWatson123321]

Hasit,

I believe most Excel users will agree with your comment that Excel's help function is "pretty poor" in many respects.

One VERY obvious example, is where Excel's "Macro Record" feature ALWAYS records the "RESULT" of what is recorded - INSTEAD of recording the actual "VBA CODE". This is EXTREMELY DUMB on Microsoft's part. I can't expect anyone wanting the "result", but I DO EXPECT that EVERYONE would appreciate getting a "recording" of the "ACTUAL CODE".

If Microsoft wants a GOOD example, they should have a MUCH closer look at Lotus 123 Release 5.0.

Anyway, back to your request...

To assign a Range Name, it simply involves the following steps:

1) Highlight the cell or range-of-cells to which you want to assign a name.

2) Use Control - F3 - i.e. hold down the "Control" key and hit the "F3" key.

3) Type your arbitrary choice for a name.

4) Hit the "Enter" key.

It's THAT simple. But, you should keep in mind that it is IMPORTANT NOT to create range names which "conflict" with cell addresses - i.e. don't use a name like "A10" ...instead use "A_10". You should also avoid creating names with "numbers" - i.e. don't use "1" - instead use "_1".

Also, with creating range names for use with database functions, avoid using the name "database" and "criteria", as these names can cause problems. Instead use names like "data" or "crit". Obviously, if you have more than one criteria, you would create a name which would be associated with the type of data which the criteria is intended to capture ...example "crit_name" for criteria which is being used to identify records for a particular name or names.

Answer to your other request ...regarding your inexperience with setting up databases within Excel.

First, creating and using database functions in Excel is (again) NOT properly and thoroughly explained in Excel's HELP. Because of this, I've decided that trying to explain a "complete" picture "verbally" is NOT as good as allowing you to SEE the ACTUAL file I created to resolve your SPECIFIC problem.

Subsequent to your last posting, I've added several notes and comments to the file - to give you a better understanding of how database functions work.

A final note... There is a very close "link" between the same "criteria" used for database formulas, and the criteria required for "extracting" data from a database to another range or to another sheet. This extraction process is EXTREMELY useful and is often a PREFERRED option to "filtering-in-place".

As I said, the file I am offering deals with your SPECIFIC problem, and there is much more to learn about database functionality, but this file will likely be very useful in getting you started.

Please advise as to how you make out.

For the file, just email me, and I'll attach it to the return email.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Hasit]

Dale, thank you very much for the information. I will try it out early tomorrow and let you know how it goes.

 

[DaleWatson123321]

Hasit,

As I haven't received an email from you, is it possible you overlooked my reference to offering to email you the file.

This file should make it MUCH easier for you to "tackle the learning curve" associated with database functions.

...yours for the asking ... but of course I need you to email me, so I can return the file to you.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Hasit]

Dale,

Just to let you know, I finally got round to trying out your suggestion, and it worked a treat. One further question. Once I have been able to create a file name from the specific choice someone has made, can I then use that file name to reference a specific cell in the file name I have created?

For example, someone chooses "Dave", which contructs "Davefile.xls". If I then want to reference cell A1 in a sheet called DaveAccounts, then can I create a construct which returns the contents of:

'[Davefile.xls]DaveAccounts'!$A$1

into a cell in another file?

Thanks!

 

[DaleWatson123321]

Update...

I have emailed Hasit separately, but I thought I should provide this update, in order that other Tek-Tips users can potentially benefit from this information.

The formula I sent Hasit is as follows:

=INDIRECT("["&filename&"]"&"DaveAccounts!test&quot

...where "filename" refers to the cell containing the "DGET" formula which pulls out from a database a name marked with an "X" (in this case a file called "Davefile.xls&quot

...where "DaveAccounts" refers to a sheet name in the filename called "Davefile.xls".

...where "test" is a range name in the "DaveAccounts" sheet.

I email Hasit 3 files - one which includes the example of how the "DGET" formula works and the "link formula" referred to above, the second file simply contains the data from which the "link formula" refers, and the third file is a "Workspace" file which loads both the "xls" files.

If anyone else would like a copy of these files, simply email me, and I'll return the files by return email.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DaleWatson123321]

Update to File...

The use of the "INDIRECT" function ONLY works in the above situation providing that the file to which it refers is loaded in memory.

As an alternative solution, to allow for "pulling in data" from file(s) WITHOUT having to load them into memory, I sent "Hasit" an updated version of the file, which NOW includes the following code.

These routines enter either a "link formula" or a value into the current cell, based on the following variables which are currently set up as range-named-cells within the file, but could be set up as variables within the VBA code, depending on which is preferable.


Dim filename, pathname, rangename As String
Dim filetoget, pathtoget, rangetoget, pathfile As String


Sub Create_Formula()
'enters a “link formula” into the current cell

filetoget = Worksheets("Sheet1&quot.Range("filename&quot
pathtoget = Worksheets("Sheet1&quot.Range("pathname&quot
rangetoget = Worksheets("Sheet1&quot.Range("name_in_file&quot

pathfile = "='" & pathtoget & filetoget & "'!" & rangetoget

ActiveCell.Formula = pathfile

End Sub


Sub Enter_Value()
'enters a “value” into the current cell

filetoget = Worksheets("Sheet1&quot.Range("filename&quot
pathtoget = Worksheets("Sheet1&quot.Range("pathname&quot
rangetoget = Worksheets("Sheet1&quot.Range("name_in_file&quot

pathfile = "='" & pathtoget & filetoget & "'!" & rangetoget

ActiveCell.Value = pathfile

Selection.Copy
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False

End Sub

===========
END OF CODE
===========

Again, if anyone else would like a copy of the file, don't hesitate to ask.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Hasit]

An update for all, Dale's suggestions have worked and I believe that if anyone has a problem as I have described, the solution Dale has offered is an elegant one that works around some of the inherent issues with Excel (i.e. that of not being able to update cell contents from another file without that file being open, if you are using the INDIRECT function).