Help on Macro or Formula

Hi,
i hope someone can help me. i need to create a formula that sits in a cell 
and looks for data. ( obvioiusly ). however, the formula needs to be in place 
even though the file from ehere the data comes from might not be there yet. ( 
i have to create a book that when a new file is created, the links are 
already in place ). i think it could work with an IF type formula for ( if 
B2="",""). here is my information.

Cell description:
A2 = Job no.
B2 = Client Name
D2 = Actual Spend on project
Register!D2 = Job Description
Register!H2 = Quoted Amount

my path is S:\Clients\ & Client Name & \ & Job Number & Job Description & 
Job Number & " Quotes & Orders.xls
if H2 cell has no value then active cell shows no value. 
( this cell will indicate the file created and filled in )

Can a formula be written to take the above details and create a path to a 
proposed file? 

Thanks in anticipation,

Nigel
0
Nigel (119)
6/13/2005 9:05:02 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
267 Views

Similar Articles

[PageSpeed] 35

Try this formula:-

=IF(B2="","",HYPERLINK("S:\Clients\"&B2&"\"&A2&Register!D2&Sheet1!A2&"Quotes
& Orders.xls"))

One word of warning, not really a good idea to uses "&" in filenames
(Quotes and
Orders.xls would be better) unless I have misunderstood?

The above will only create a link to the file, it won't create the
file.

The link above will be created once the client name is entered, you may
want to adapt it so that the Job No, Client name and Job Description all
in place i.e
IF(OR(A2="",B2="",Register!D2=""),"",HYPERLINK("S:\Clients\"&B2&"\"&A2&Register!D2&Sheet1!A2&"Quotes
& Orders.xls"))

Good Luck

Gary


-- 
Gary Brown
------------------------------------------------------------------------
Gary Brown's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=17084
View this thread: http://www.excelforum.com/showthread.php?threadid=378487

0
6/13/2005 12:22:37 PM
It looks like you want to build that path and filename and use that in the
formula.

I think you'd want to use =indirect() to return your values, but the bad news is
that =indirect() won't work with closed workbooks.

Harlan Grove wrote a UDF called that will retrieve the value from a closed
workbook.

If the reference doesn't exist, it shows an error, but you could use:

=if(iserror(pull(...)),"",pull(...))

You can find Harlan's Pull UDF at:
http://groups.google.co.uk/groups?selm=ASAuc.4700%24H4.12%40www.newsranger.com

But sometimes Google will create errors when copying|pasting.  You can find the
same function at Harlan's FTP site:
ftp://members.aol.com/hrlngrv/

You can find his pull.zip there (and shouldn't have any trouble with google!)

Nigel wrote:
> 
> Hi,
> i hope someone can help me. i need to create a formula that sits in a cell
> and looks for data. ( obvioiusly ). however, the formula needs to be in place
> even though the file from ehere the data comes from might not be there yet. (
> i have to create a book that when a new file is created, the links are
> already in place ). i think it could work with an IF type formula for ( if
> B2="",""). here is my information.
> 
> Cell description:
> A2 = Job no.
> B2 = Client Name
> D2 = Actual Spend on project
> Register!D2 = Job Description
> Register!H2 = Quoted Amount
> 
> my path is S:\Clients\ & Client Name & \ & Job Number & Job Description &
> Job Number & " Quotes & Orders.xls
> if H2 cell has no value then active cell shows no value.
> ( this cell will indicate the file created and filled in )
> 
> Can a formula be written to take the above details and create a path to a
> proposed file?
> 
> Thanks in anticipation,
> 
> Nigel

-- 

Dave Peterson
0
ec357201 (5290)
6/13/2005 12:27:16 PM
Reply:

Similar Artilces:

Solving a formula
I have the following formula that I need Excel to solve for different values of P6 ( I have different values of P6 (P7, P8, P9 (reference cells)). I need the value of x for the different values of P6. Is this possible? It seems like it should be but I can't find out how to make Excel do this. Please help! Thanks. 70000 = P6*((((x/80)*(1/5280))*(2/3))+(((x/40)*(1/5280))*(1/3))) If I am not mistaken, x equals: = 22176000000 / P6 HTH -- Dana DeLouis Win XP & Office 2003 "Anth" <Anth@discussions.microsoft.com> wrote in message news:6196C6C5-FC82-42E2-B6A1-B2...

concatenate help
Hello, I am having issues with the CONCATENATE function. I am entering it correctly, and it is equaling the correct values. However, when I try to drag the cell down, it is just copying the same cell over and over as opposed to putting the correct value in the cell. Am I being clear? Please help. I have a list due tomorrow that will take a whole lot more time to finish if I can't get this function to work. Thanks ------------------------------------------------ Message posted from the Excel Tip Forum at http://www.ExcelTip.com/forum/ -- View and post usenet messages directly from htt...

SMS 2003 report help
I have no idea how to do this, but hopefully someone out there can help. Running SMS 2003 and I have the two reports below that I would like to combine with a change. I would like to be able to see the username and time of day usage for each user hit, not just the last usage along with everything in that first report. I would also like to just pull all the data for all games and all dates instead of having to specify. We aren't that big that I need to cut this list down. Any ideas? Thanks a bunch for any help! Mark "Users that have run a specific metered s...

Help
Hi I have produced an Excel workbook for gathering exam results and giving a score to them, counting the number of passes over a certain level etc. Each sheet is one student and I have used a little macro to name the sheet according to the name entered at the top of the sheet. My summary sheet requires certain formulae to analyse results from each worksheet. The problem is, I can easily enter the row of formulae for one student - but there could be up to 50 in a workbook! The fill action does not increment through the sheet names either when they are re-named or when they have original na...

Need Help w/ Weighted Averaging
I now want to utilize Weighted Averaging. Please find some example data below: &&&&&&&&&&&&&&&&&&& 1 0.067 2 0.133 3 0.200 4 0.267 5 0.333 ----------- 15 1.000 &&&&&&&&&&&&&&&&&&& The result of A6 = Sum of A1:A5 (or 15) The results of B1 to B5 are: B1 = A1/A6 (or 0.067); B2 = A2/A6 (or 0.133); B3 = A3/A6 (or 0.200), etc. The result of B6 = Sum of B1:B6 (or 1.000) Now, here's what I'm trying to achieve: - Alt...

How to assign a hot key to an existing macro in word 2007?
I saw how to attach a key to a macro when starting to record a new macro. But how do I attach it after one is created and working? Word Menu (or Button)>Word Options>Customize>Keyboard shortcuts "Customize" and choose the "Macros" category nixonian wrote: > I saw how to attach a key to a macro when starting to record a new > macro. But how do I attach it after one is created and working? I finally found the command "Customize Keyboard" using "Search Commands" menu item. I still have not found it in the standard ribbon...

Need Help with Report
I have a main from and subform. Main form looks up a doctor and the subform lists the doctor's patients. On the main form there are command buttons to print specific report for these patients. However, when I click on report the report comes up with only 4 out of the 5 paitients. It does not pick up the first patient. Everything else works fine except that one. This is my code for the report. I do have requery in my main form. Even after I requery, it will no show all 5 patients. I would appreciate any help anyone can give me with this. Thank you. Private Sub ADOLVC_Click...

FOrmula to work out pricing rounding up and down
I have a formula in cell that reads "=3DIF(C7>0,"FOC",-C7*1.175)" it turns a negative into a Positive and a Positive FIgure into answer of "FOC" Sometimes it will display a result of =A32 or =A35.50 or whatever the figure when the original answer is a negative. With me so far!! The result which is displayed as a number i need it to round up t the nearest 9.99. For example if c7 is =A34.00 display answer of 9.99 if c7 is =A311.00 display answer of 19.99 if c7 is =A328.43 display answer of 29.99 So no matter what the answer is it rounds up to the 9.99 answer...

Newbie needs help with table
I have created a database with two tables: Product Orders The product table contains the following fields: ProdID Descr OnHand the orders table contains: CustID ProdID QTYOrdered Cost When I created the Orders table I wanted to use the ProdID on the Product table as a lookup field. When I created the lookup type I selected from the Product Table the ProdID and Descr. When I try to do data entry the system does not properly input the data into the orders ProdID table. What I want is to display the ID AND the Description but I only want the ID placed in the or...

Dumb question... how to create a Enterprise Formula Custom Field
Guys, I'm writing because I've tried and search but can't get it ... From PWA I created a Project Custom Field "MyField" of type Text with the formula "Hello World", I tried with " and '. When I open a project the field returns #ERROR. I published to see if magic happens, but nothing. I did the same with type number and formula 10, same result. So In the end, I can't get any formula to work. What am I doing wrong ... I googled with no success. Perhaps try opening the project and hitting the F9 button to refresh calculations? Does ...

Help me with formula
I have a basic graph 1,2,3,4,5.....going down in and a,b,c,d,e,f,c going across. EX. A B C D E F G ---------------------------------------------- 1 500 25 75 800 (200) 2 3 4 5 6 7 My question is how can I make an equation that Goes like this : Column A+B+D-E=G I showed an example above for on row but i wouuld wnat this to work for any row no matter what data I answer. I want this ewuation to work for any data entered for the whole sheet. I hope you understand and can help anyone. -- thatsall ------...

formula = ADD whatever is not highlighted.
Hi. I have a workbook which I use as my monthly budget. I have tried other software made for that, but this is really what I like. As transactions go through, I "highlight" the cell, using a variety of colors. I would like 1 cell (presumably, one that would calculate, what is left to go through) to add only the cells that are not highlighted, in a certain range. Any and all help is greatly appreciated. These should help. Sub addnoncolor() mysum = 0 For Each c In [m1:m5] If c.Interior.ColorIndex = 2 Then mysum = mysum + 1 Next MsgBox mysum End Sub Sub whatcolor() For Eac...

Christmas lights macro #3
Thanks for that. I'm a complete XL dummy. Can you please explain in 2 sentences how get that into the spreadsheet and run it -- pmolse ----------------------------------------------------------------------- pmolsen's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1511 View this thread: http://www.excelforum.com/showthread.php?threadid=26744 Hi pmolsen, See David McRitchies notes at: http://www.mvps.org/dmcritchie/excel/getstarted.htm --- Regards, Norman "pmolsen" <pmolsen.1dsg3z@excelforum-nospam.com> wrote in message news:pmolsen....

How to use this DeleteEMFs() Macro
This macro was on the MicroSoft website as a work around for the .emf storage problem http://support.microsoft.com/default.aspx?scid=kb;en-us;299372 What exactly does this macro do and how should I use it? I want to remove all the .emf files from a particular Excel File. B/c the workbook creates hundreds or thousands of temp .emf files everytime it opens and it takes forever to open.. Thank you very much for any help! george Private Sub Workbook_Open() Call DeleteEMFs End Sub Private Sub DeleteEMFs() Dim fso As Variant Set fso = CreateObject("Scripting.FileSystemObject"...

Help identifying virus
I get an email. Almost instantly another email "arrives" with the same subject but containing the following text: --- begin quote Hello How are you doing recently? I would like to introduce you a very good company which i knew. Their website is www.ebakm.com They can offer you all kinds of electronical products which you need,like Laptops ,GPS ,TV LCD,Cell Phones,PS3,MP3/4,Watch etc........ Please take some time to have a check ,there must be something you 'd like to purchase . Hope you have a good mood in shopping from their company ! Best Regar...

macros, selfcert, portability
(Excel 2002, SP3) I have just written some VBA pgms, for the first time. In the course of doing this, I had to sign them. (I don't know if this is always true, or if I just took a wrong turn somewhere.) So, I did the self-cert thing. After doing that, I could run the macros with macro security set high. I was able to run the same WS on another PC with Excel 2002, after I "added the certificate" to the "trusted persons root". I can't say I had a clear picture of what I was doing, but it seemed to work. When I try to run the same WS on a PC with Excel 2003, ...

Help Pleeeeeeeeeeeeeeeeease :-(
hello im new, my names Gary an im from coventry in england :-) im doing A(s)-Level ICT and currently working on a spreadsheet desig for a business including order forms customer database etc. the classi a-level ict project basically. I have a catalogue page with the products and the cutomer chooses th size of the product and how many they want etc. i want to create button that the uer clicks that then inserrts this data they hav chosen into the next available space on the order form worksheet, ho would i do this? i probably sound really stupid :confused: but i would really help me if you c...

I need help to customize a specific PowerPoint template
For PowerPoint template called "Beam", how do I change the first bullet on the master slide to be yellow instead of red and still keep the shade effect, and bring the bullet that is blue with the shade effect up to be the second level bullet? ...

Vb macro stops when I try to create more than 89 charts
I created an Excel2002 VB macro that manipulates some data in different ways then it creates charts of the certain parts of data that I extracted. Every time when the macro is trying to create the 90th chart the macro stops (the VB Editor still says that the macro is running but nothing happen). When I press the pause or the stop button the Excel closes without any warning. Is there a limitation on how many charts I can create, or is something to do with my computer memory (and if that is the problem any suggestions on how to re-write the procedures to manage the memory better?) I have...

Command Button
I would appreciate any help with this. I have a command button on my main form that when you click on it it will take you to the main switchboard and the reports page. This is the what I have now. My Switchboard has two pages. Page one has forms and button to go to the second page which lists reports. This only takes me to the main switchboard. If I type [SwitchboardID] is says there is no such field. I want the command button to take me to the second page of the switchboard and open reports. This is the info in the switchboard table for Reports. SwitchboardID = 2; ItemNumber =...

problem with formula #2
I have one column with increasing standard time (hr:min:sec). I put a formula in the first cell of the first row of a second column to convert the same cell of the first column to a value of decimel based upon minutes. The result is correct in the first cell of the first line (row) where the the formula was placed in the second column, but when I copy the formula throughout the rows of the second column, the results shown are always the answer for the first line - even though when I klick on the cell the formula in the lower rows/cells shows it referring to the correct later time ...

Lotus 123 print, formula, format, ect
How can I get Excel to emulate Lotus 123 commands such as: print range, + - (formula) range, move information and calulatons. ...

adding lines of code to a macro to delete rows
Hello: As the last step of a macro that I have written, I need all rows at the end of this spreadsheet to be deleted. (The rows at the end of the spreadsheet have bogus data, and I need to get rid of all of those rows and leave just rows of "real" data--long story short.) Specifically, the row after the last row of "real" data is a cell in column A that simply contains the word "Grand". How do I put in code at the end that says "delete all rows at the end of this spreadsheet beginning with this last row that contains just the word 'G...

Does Excel support mixed formatting of a string in a formula cell, yet?
Excel is one of the most spectacular apps ever created. But one feature I really need is mixed formatting of a string whose cell is a formula. By mixed formatting I mean, you know, making selected (as opposed to all) character(s) of the string bold, italic, a different color, sub- or superscripted, etc. In Excel 2002 you cannot do that in a formula cell (right?). In edit mode, the user would, say, press F9 to display the calc'ed string, selectively format it as usual, then cancel edit. Excel would save the formatting as a separate mask. What could be easier? The user would ...

Having trouble with help in Publisher 2000
I've noticed within the last month that when I click on a link within a help page, in Publisher 2000, that almost invariably I get a scripting error message. For example: Internet Explorer script error Line: 1 Char: 1 Error: Object doesn't support this property or method Code: 0 URL: mk:@MSITStore:C:\Program%20Files\Microsoft% 20Office\Office\1033 \pub6.chm::/pub9html/pbmnuCONTNT3016.htm Do you want to continue running scripts on this page? | Yes || No | The only links that seem to work are in the introductory help page, the first page to come up when I select "Microsoft...