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
210 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:

Creating a group of cells. Need Help Please.
Havn't used excel in a while and I need to create a group of cell corresponding to an input of a min and a max. Here are the details. On one sheet I have a box where you enter th min and a box where you enter the max. In another sheet I want column starting at A2 to output (MIN,A2+1000,A3+1000,....MAX) ho would I do this -- Thundersix ----------------------------------------------------------------------- Thundersixx's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3055 View this thread: http://www.excelforum.com/showthread.php?threadid=50207 Name the...

Macros for worksheet copy & paste?
I have 6 different excel files. How can I combine all 6 of them in one single excel file under individual worksheets (E.g. Worksheet1 for File1, worksheet2 for File2 ....etc)? I do not wish to copy & paste it manually. Can I use a macro to take care of it? If so, where can I find further info on how to go about it? THANKS!! Assuming (1) this is a one-time need, and (2) your workbooks have only one sheet each, this would probably be faster done by hand: 1) With all the files open choose one of the files to be the receiving file. 2) Select the sheet in the subsequent books, one at...

Please help..with a formula. I don't know code.
I have a long list of numbers - values in a file X, and I want to fin and replace those values in a even larger list in a file Z an highlight those values in Z -- Message posted from http://www.ExcelForum.com Hi not really sure what you're trying to achieve. What do you want to replace, etc. You may give an example (plain text - no attachment please) >-----Original Message----- >I have a long list of numbers - values in a file X, and I want to find >and replace those values in a even larger list in a file Z and >highlight those values in Z. > > >--- >Message...

Need a default email account for all users, need help.
I have a tablet PC running WinXP Tablet with Outlook 2003. This tablet will connect to our exchange server via VPN. How can I set it up so that everyone that logs onto their account can access one (the same) email account. The problem is that I dont know at this point all of the users however anyone using the tablet will use one generic email account. So how can I set Outlook to default to this account so that no matter who logs on they will use this account? Thanks! Shane ...

Need help with formula 01-13-10
I am trying to adapt a formula in I2 from another spreadsheet that works well, but won't in mine. I've traced the error, but I would need help to understand the help it gives! My formula is this: =IF(J2="0-Jan-00","To be advised",WORKDAY(J2,1,NWD)). I have a worksheet in the same workbook with a list of non-workdays, and defined the column of dates with the name "NWD". What I expect the formula to do is this: If J2 is Feb. 4, it would give Feb. 5 in cell I2 because Feb. 5 is NOT a non-workday in NWD. But if J2 is Feb. 5, and Feb. 6 and...

DEADLINE... PLEASE HELP! Stacked Bar chart?
I'm not even sure how to ask the question so here's what I have... 2003 2004 2005 Actual/Goal Actual/Goal Actual/Goal Me 1009/1061 591/866 658/897 Comp. A 966/1012 633/811 624/808 Comp. B 699/744 450/593 480/607 Comp. C 957/1005 642/821 665/838 I wanto to show a bar for each competitor, for each year, so there will be 4 bars for each year. Each bar showing Actual performance & Performance Goal...

please help with this query
Ost Ocity Dstate Dcity Carrier Price Rank Diff A B C D X 1200 1 100 A B C D Y 1300 2 100 A B C D Z 1350 3 100 A B C D W 1789 4 100 A1 B1 C1 D1 X1 785 1 A1 B1 C1 D1 Y1 789 2 The rank for every carrier is based on the price . If rank1 carrier is not a pariticular carrier(say if it is not X1 or Y1 or Z1), then i want to calculate the difference be...

Excel 2000 macro security Level
Hi! I am running an ASP.NET application which ideally opens an Exce spreadsheet with a macro included in it. When I try opening spreadsheet without a macro, everything works fine, but with macros, i hangs on the opening command. I tried to go to Excel 2000 and go t Tools->Macros->Security Level->Low but it still hangs when I try t open the macro spreadsheet. All works fine with the spreadsheet no containing macros, so it really is a question of macro security. But put it to low, so I don't understand how it is still not opening!! An help would be greatly appreciated! Thanks and...

Running an Excel Macro from an Access Command Button
Hi , Can anyone help me with this, I am trying to run an excel macro from an access command button. I want the macro to Bold the Headings on a excel spreadsheet. The code runs without any errors but the macro doesnt seem to work, I think I have got the line ExcelApp.ExecuteExcel4Macro "RUN(""PERSONAL.XLS!BoldHead"")" wrong The code: Private Sub cmdRunMac_Click() Dim ExcelApp As Excel.Application Dim ExcelBook As Excel.Workbook Set ExcelApp = Excel.Application ExcelApp.Visible = True Set ExcelBook = ExcelApp.Workbooks.Open("C:\Documents and Settings\...

New Named Range Created Each Time Data Imported into Excel via Macro
I have noticed that each time I import data into an Excel spreadsheet via a macro, a new named range (for the same range) is created. This does not pose a problem, but after a while, I'll have a huge number of named ranges that will never be used. Why does Excel name the range and how can I stop this? I noticed in the recorded macro, there was a line .Name = "drd_5". I commented this out to see what would happen, but it just renamed the range "ExternalData_5". The next one was "ExternalData_6", etc. Thanks, Carroll Rinehart You could refresh with differen...

Getting Cell Value from the Concatenate formula
I built a concatenate formula that returns the following result: =Jul! $D27 I am looking for the cell contents of Sheet: July Column D Row 27. I tried to use offset, but I am stumped. Can I add something to the front of the concatenate to not only build the reference to the cell, but also return the value instead of the =Jul!$D27 ? Thanks John =indirect(yourformulahere) Don't include the equal sign in your formula. And match the name correctly (Jul or July???). Depending on the name of the worksheet, you may need to have a string that looks like: 'Sheet 99'!d27 =indirec...

stop automatically changing formula!
i have a countif function COUNTIF(Locking!I16:I40,"f") when i copy this and paste it to the next cell, the formula automatically change to COUNTIF(Locking!J16:J40,"f") How do I stop it from changing column I to J?!?!?! thanks. Caryn, =COUNTIF(Locking!$I$16:$I$40,"f") or =COUNTIF(Locking!$I16:$I40,"f") HTH, Bernie MS Excel MVP "caryn" <caryn.tan@gmail.com> wrote in message news:d7n4u3$hgi$1@avnika.corp.mot.com... > i have a countif function > COUNTIF(Locking!I16:I40,"f") > when i copy this and paste it to the nex...

vba code to enable macros
Hi, Can we write a vba code to enable macros. In most of the sites it says we cant do that . The only thing we can do is change the macro setting or work around is hide the sheet which has macros. But none of them will work in my case. Can we change an excel security setting to from macro on open so that macros are enabled. No. If we can do that with code, then so could a hacker with bad intentions. Mike F "varsha12" <varsha12@discussions.microsoft.com> wrote in message news:DBEB1614-C956-491B-B351-02DD9F711891@microsoft.com... > Hi, > Can we w...

Hyperlink File Help
I am needing some major help. I have a file with hyperlinks in column F that link to a file on our server. I am needing to test to see if the file exists and if it does, copy the file to a folder in my documents called (CapturedFiles) and if it doesn't format the cell color to red. Can VBA do this and if so how? Any help would be greatly appreciated. Thanks in advance. Fileserver or webserver ? Tim On Nov 23, 7:20=A0am, Aaron <Aa...@discussions.microsoft.com> wrote: > I am needing some major help. =A0I have a file with hyperlinks in column = F that > l...

PivotTable Formulas
I have set up a pivot table. I have been asked to add a column at the end of the pivot table that calculated the %variance of sales from this year versus last year. I tried to create the formula (Calculate Field) after I had grouped my dates for Months & Years. I had to ungroup these to be able to create the formula. I do not see how when I have ungrouped the date to distinguish between 2003 & 2002 in my Pivot formula. Is this possible of am I flogging a dead horse here? Any suggestions would be most help ful Regards GarethG ------------------------------------------------ ~~...

Help please user not showing in 5.5 GAL but is in exchange 2003 GA
Up until today I have been bable to add users fine and their address would appear in both the 5.5 GAL and the exchange 2003 GAL. Is a single site with 2 5.5 servers and 1 exchange 2003 server. When I add a new user now through users and computers and put the mailbox on the new exchange 2003 server the user gets his email addresses and appears in the GAL on the 2003 server but people connected the the old 5.5 servers cannot see it. When I open the 5.5 exchange admin tool again if connected to one of the old 5.5 server I cannot see the person I just created but when connected the the 20...

VLookup #VALUE! error help needed to resolve
The following is the funcation I have: =VLOOKUP(B10,'FA CC Summary Report 1141'!F$9:G$92,2,0) I have all the columns formatted the same; as in the column that the function is using to lookup is text and so is the column for this figure in order to pull back the appropriate answer. I have keyed the data instead of having links. I have replaced the final '0' with TRUE & FALSE then put it back. I have formatted the columns for text and for numbers. But I am getting the #VALUE! error in SOME of the cells NOT all of the cells. I don't know what else to d...

help with a sub
Hi, can anybody tell me why the following code fails at FormatConditions.Add Private Sub CommandButton1_Click() Dim Sh As Worksheet Dim lngLastRow As Long Set Sh = ActiveWorkbook.ActiveSheet lngLastRow = Sh.Cells(Cells.Rows.Count, "A").End(xlUp).Row Range("A4:E" & lngLastRow).Activate Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=(MOD(ROW(),2)=0" Selection.FormatConditions(1).Interior.ColorIndex = 24 End Sub Thanks -- Traa Dy Liooar Jock You have an extra open paren just before MOD: &qu...

cdrom.sys corrupt in Win7
Yesterday Win7 decided to no longer show my 2 LiteOn DVDRW drives. I've tried to re-install/repair the driver (6.1.7600.16385) and everytime I get the same response = my current driver is good. BUT, then when I check with Device Manager, it shows that the drives are not working. Can anyone help me get a new cdrom.sys installed into the system32/drivers folder? Booting up with the Win7 DVD will work. But I can't find the cdrom.sys on the disk. No other repair options are there to get this fixed. Help would certainly be appreciated. I don't want to have to start all o...

Macro on a protected worksheet in a shared workbook.
Hi, I have a macro in protected worksheets that can't run once the Workbook is Shared. I have wrapped the Macro code so as the worksheet is unprotected for the time the Macro runs, and unable the Autofilter. The code is as follow at the moment: Sub Newaction() Sheets("Critical Path").Unprotect ("") ' Newaction Macro ' Macro recorded 17/11/2004 by Clifford ' ' Selection.AutoFilter Field:=1, Criteria1:="=" Sheets("Critical Path").EnableAutoFilter = True Sheets("Critical Path").Protect contents:=True, userInter...

Exchange 2003 SMTP QUIT
= = = = = = = = = = = = = = = = = = = = = = = = = = = PROBLEM: Problem is that OUR SERVER is sending QUIT-, instead of sending MAIL FROM: MY Server open a SMTP connection REMOTE Server says 220 .. MY Server says EHLO to REMOTE Server REMOTE Server says 250 ... MY Server then say QUIT ! (instead of MAIL FROM ....) We have::Exchange 2003 , cu SP1, pe Windows 2003.. Exchange has also IMF (spam filter from Microsoft) and Symantec Mail Security for Exchange 4.5. all PTR is installed and working OK. The SMTP Server is workin OK a while, then it start opening a lot of connections (7-10 /sec) t...

Formula #50
I currently am working on a few spreadsheets containing daily sale readings from 8 different stores. I have created a chart to show the days sales comparing it to last years sales (same day but for 2004) and would like to create either a formula and/or link to the second sheet so that it automatifally imports the info as opposed to me changing the formula every week....now this is done for all days of the week. Please HELP! -- precioustj Can you give an example of the data you have. Are you trying to match two different pieces of data or just one? Thanks. "precioustj" w...

Output to macro to sharepoint
Hi all i need help please, Using Access 2000 and up I have an update macro that is doing update to tables and produces a report that normally gets printed out. Now i need to stop printing reports and distribute them electronically. My idea is to have them in SNapshot format and push them to a SharePoint library. I have tried this several times and get 'The report snapshot was not created because you don't have enough free disk space for temporary work files'. It then goes on to tell me to empty my recycle bin. I have done a disk clean and nothing changed. Is it that i can't sa...

I need help
Here is my problem. A couple of years ago I began playing around with money. But I have come to the conclusion that I really do like the services, but I now have a bunch of acct. info that should have been input in a better manner. I want to know how do I delete all of the old info so that I can start fresh. I deleted that money program once and reloaded it but it retrieved all of the old garbage that I did not want. Your help is greatly appreciated. Sincerly, Matt On Tue, 19 Aug 2003 20:58:14 -0700, "Matt" <mrisher4@hotmail.com> wrote: >Here is my problem. A c...

Help styles
With great difficulty I am now creating documents with some styles.and outline numbering Now when I copy a table from another document into this document I get 1.1.1.1 and similar numbering all over the table. How do I accomplish the copy without the numbering inside the table? afd Try this: Select the table cells and press Ctrl+Shift+N. This (re-)applies the Normal style to text. -- Stefan Blom Microsoft Word MVP "afdmello" <afdmello@hotmail.com> wrote in message news:%23R7DwkCdKHA.4952@TK2MSFTNGP06.phx.gbl... > With great difficulty I a...