Hello, I'm using the following Vlookup formula: =IF(ISNA(VLOOKUP(A4,'S:\Operations\Bloomberg\Investment Support\Dummies2Live\2010\May 10\[04052010.xls]Sheet1'!$A$4:$F$600,6,0)),"PRINT",0) This is linked to another spreadsheet - in this case 04052010.xls. Next day I will need to check file 05052010.xls and so on. To make life easier for my colleagues I wanted to add a cell with date where they can input the date and this will change the date in the formula (i.e. from 04052010 to 05052010) How can I link this formula to let's say cell A1 where I want to insert date every day. Thanks Submitted via EggHeadCafe - Software Developer Portal of Choice WPF GridView Sample To Insert, Update, and Delete Records http://www.eggheadcafe.com/tutorials/aspnet/fc9a5bf6-f5bb-4443-a92a-c9a46fd3aeb2/wpf-gridview-sample-to-in.aspx

0 |

5/6/2010 4:43:06 PM

The closest thing to this would be to use the INDIRECT function, however... INDIRECT can't be used with an external workbook reference. *sad face* -- Best Regards, Luke M <iv> wrote in message news:20105612436iveta.siryova@hotmail.co.uk... > Hello, > > I'm using the following Vlookup formula: > =IF(ISNA(VLOOKUP(A4,'S:\Operations\Bloomberg\Investment > Support\Dummies2Live\2010\May > 10\[04052010.xls]Sheet1'!$A$4:$F$600,6,0)),"PRINT",0) > > This is linked to another spreadsheet - in this case 04052010.xls. Next > day I will need to check file 05052010.xls and so on. To make life easier > for my colleagues I wanted to add a cell with date where they can input > the date and this will change the date in the formula (i.e. from 04052010 > to 05052010) How can I link this formula to let's say cell A1 where I want > to insert date every day. > Thanks > > > Submitted via EggHeadCafe - Software Developer Portal of Choice > WPF GridView Sample To Insert, Update, and Delete Records > http://www.eggheadcafe.com/tutorials/aspnet/fc9a5bf6-f5bb-4443-a92a-c9a46fd3aeb2/wpf-gridview-sample-to-in.aspx

0 |

5/6/2010 5:07:45 PM

i want to perform a vlookup using the Now() function to generate the lookup value (A1), the 1st column in the table [col B] array will be all the dates in a year listed consequtive,, and the 2nd column being a value assigned to each day in the 1st column [B]. My formula is vlookup(A1,B1:C367,2). The result I get is "#N/A. What am i doing wrong? Thanks Tonso NOW() returns both the date and the time, so you would be better off using TODAY(), which only returns the date. Another problem might be that your dates in column B are really text values that just look like dates - see what happe...

I use a formula to reference a another workbook cell. The other workbook name can changes after sent to another party and returned ammended. Rather than change the name of the spreadsheet, I would prefer to change the reference in a cell of the first spreadsheet. The formula below activates the lookup if cell c75 is Y. I would like to replace the text [Midwest G1 5000.xls] with a cell containing the name. =IF($C75="Y",SUMIF('[Midwest G1 5000.xls]Summary'!$B:$B,F$4,'[Midwest G1 5000.xls]Summary'!$J:$J),0) I have tried using "&" and direct cell references...

assuming i have this code, is possible to use this vlookup withnthe adta into mdb access?... old scenario: Private Sub TextBox25_Change() Dim CODICE As Integer Select Case Me.TextBox25 Case "" Me.TextBox4 = "" Case 1 To 8 CODICE = Val(Me.TextBox25) Me.TextBox4 = Application.WorksheetFunction.VLookup _ (CODICE, Worksheets("TABELLA").Range("Q2:R9"), 2, False) Case Else Call MULTI_LINE_BOX End Select End Sub new scenario: Inested column Q and R in excel i have created a mdb into: \\my server\myserverdir\USER.MDB and into this mdb have inserte a table U...

I need a formula that finds the closest sum of the list of bills to the specified amount, i.e Bills $ 30.00 $ 20.00 $ 30.00 Amount $ 70.00 In this case formula should highlight or indicate in some way cells with amounts $30.00 and $30.00 and show the total of these cells. -- Roman ------------------------------------------------------------------------ Roman's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24442 View this thread: http://www.excelforum.com/showthread.php?threadid=380407 Try this, Roman: http://www.mrexcel.com/pc09.shtml ******************...

Hi, I need a formula to do this, I think some function may be used A1 : Mar- A2: Mar- How could A3 be: Mar-1/Mar- Thanks Hi LDH! It depends upon whether or not your settings are US or Non-US and whether you are using dates or text. If A1 and A2 are dates and you use non-US date settings: =TEXT(A1,"mmm-yy")&"/"&TEXT(A2,"mmm-yy") Not quite what you asked for: Mar-01/Mar-02 If A1 and A2 are dates and you use US date settings: =TEXT(A1,"mmm-d")&"/"&TEXT(A2,"mmm-d") If A1 and A2 are text: =A1&"/"&A...

I am trying to lookup a cell within a table - but the table to use is found in another table. =VLOOKUP(B3,VLOOKUP(B2,F3:G9,2,2),2) Cell B2 is a dropdown box allowing one of the choices in colum f below. Column G represents which table to use for the initial lookup based on your choice in the drop down dox. column f column g Alt A 30 Yr fixed30 Alt A 15 Yr fixed15 All I get is an error - can someone help ? Thanks, Yosef It sounds like you would need to use INDIRECT within the lookup formula http://tinyurl.com/czxtt that thread shows the way to do it exce...

I have an existing spreadsheet with different formulas in it. Just recently I noticed that the forumlas stopped calculating. It's odd since I can cut a formula out of a cell and paste it back into the same cell and it will work again. What is causing this? It's annoying to have to spend 30 mins updating formulas this way when I have only 2 mintues worth of data to imput. Brodie: Go to Tools, Options, Calculate Tab, and check to see if your spreadsheet is set to calculate automatically. If it is not then you will need to set it so that it is by clicking the appropriate radi...

I have two worksheets, one of which has formulas which reference the other. The problem is, I cannot sort the first worksheet and have the formula values in the second worksheet follow it so the values stay the same. I have tried making them absolute references ($A$1) but it doesn't help. I feel your pain. I've ran into the same issue before. The problem is the use of worksheet functions. The solution is DO NOT USE worksheet functions. Use a VBA subroutine instead. Here is an example: With worksheet functions.... A B ...

Can someone walk me through this please? I a workbook that imports a years worth of data from filemaker to be analyzed and charted in excel. It contains several sheets, but I am concerned with worksheet 1 (daily data) and worksheet 2 (bodyweight). Daily data contains the raw data I pull in from Filemaker. It results in a table with a row for each day of the year. It has 12 columns, but in this instance, I am only interested in 2 of the colums Column F, (Date), and Column R (Bodyweight). There is only one entry per week for body weight. The bodyweight sheet has 3 columns (week #, date, and w...

I'm using the following function which I found on another site. Public Function DMedian(FieldName As String, _ TableName As String, _ Optional Criteria As Variant) As Double On Error GoTo Err_DMedian 'Returns the median of a given field in a given table. 'Returns -1 if no recordset is created Dim db As DAO.Database Dim rs As DAO.Recordset Dim strSQL As String Dim RowCount As Long Dim LowMedian As Double, HighMedian As Double 'Open a recordset on the table. Set db = CurrentDb strSQL = "SELECT " ...

On the worksheet I can insert in a cell =VLOOKUP(C5, Hobokee.xls!AcsLow, 2) and it works perfectly, looks up the value in column 2 of the range named AcsLow in the same workbook. But elswhere I want in a macro to lookup the same table and assign the result to a variable BalAmt. BalAmt = VLOOKUP(AccNum, Hobokee.xls!AcsLow, 2) does not work. It gives a function not defined error on Hobokee. If instead of Hobokee I put Workbooks("Hobokee"), it gives function not defined error for VLOOKUP which it changes to VLookup. I have been overVLOOKUP in the Help file and see nothing wrong. W...

I have a worksheet that has tabs for each month. I want to track the avg use of a material we use for the last five workingdays... So no problem untill I need to go look at the previous month tab for that last few days of the month, to avg with the first few days of the current month. And I want it to skip the cell if it is empty So if I want to do a running five day avg, and skip the cell if it is empty, and look at another sheet, what would that formual look like. My first question may have been a little confusing. so basically the formula looks like this, =IF(K7=""...

"Selected" text is in a light blue color, which is difficult to see against the white background. Is there any way to change this color to a different color which is more easily visible? Thanks. Previously answered. See the thread you started 16/05/10 17:35. --rms www.rmschneider.com On 17/05/10 07:58, Eric74 wrote: > "Selected" text is in a light blue color, which is difficult to see against > the white background. Is there any way to change this color to a different > color which is more easily visible? > > Thanks. In articl...

I am hoping what I want to achieve can be done. I am not a speadsheet expert so hope someone can help me. I have a column of times in 24 hour format. I have cells to the right of that covering monday to sunday. I want to put a time in 24 hour format that looks to the time set in the column of times that says, for example. The time in cell B 9 is 12:01, the time of 12:20 has been entered in H 9. This is greater than B 9, convert the difference to minutes and insert into H 9 on a different sheet. If the time is identical or before the time in B 9 insert a 0 in H 9. If it helps I can emai...

Hi is it possible to replace the nr 4 with a " in this formula? I just want to put a " in the cel Thanks! =IF(D100<>0;(IF(L100=K100;"4";IF(L100<K100;"m";"k")));"") Do you mean this? =IF(D100<>0;(IF(L100=K100;"""";IF(L100<K100;"m";"k")));"") -- HTH RP (remove nothere from the email address if mailing direct) "kyrbi" <kyrbi@datoeternietoe.com> wrote in message news:Kz%Vd.28618$oB5.3036261@phobos.telenet-ops.be... > Hi is it possible to replace ...

Hi! At the end is a complete short program listed. If I do this statement Stack<string>.Enumerator q = numbers.GetEnumerator(); I create a Enumerator that is called q in this example. But enumerator is used when iterating through a collection for example by using the foreach construction. So my first simple question is why is it not possible to write foreach in this way using q as an enumerator ??? This cause the compile that is listed after the foreach. My qualified guess is because this enumerator q doesn't implement the IEnumerable we can't use a foreach he...

I down a list of top 100 stock symbols to Excel and place them in Column A and their rank in Column B (1-100). the next week I download a new list of the top 100 stock symbols and their rank to Column C and D respectively. then I create column E using the function =VLOOKUP(C4,A:B,2,FALSE) Which tels me the rank each stock had last week and if a stock is new this week it puts "#N/A" in the appropriate cell in column E. How do I get the Vlookup function to put "NEW" in column E if the is new to the list and wasn't in the list last week? Ed =if(isn...

Hi all, I have a Lookup formula setup to validate what the user enters against a list of stock codes, returning the item costs. Stock codes are A001, A002 etc, B001, B002, etc, D001, D002 etc If I then enter a stockcode starting with letter C, I get the item cost returned from the last letter B stockcode. How can I get it to return P.O.A. This is what I have at present. =IF(C4="Fittings",(LOOKUP(F4,Fittings!$C$1:$C$5019,Fittings!$D$1:$D$5019)),0)*AL4 C4 - Parent stock item description F4 - Stock code AL4 - Quantity Any help - greatly appreciated. -- PeterG ------------...

This formula works: =SUMPRODUCT(--((E3:E1000=6)+(E3:E1000=6.5))*(M3:M1000="w")) I tried a lot of different ways, but I couldn't find a way to shorten this part: (E3:E1000=6)+(E3:E1000=6.5) I'm sure there is a way to make that more efficient. Thanks You do not really need the double negation. It is used to transform TRUE/FALSE to 1/0 - Excel does this whenever a math operation is performed on a Boolean value. But you are multiplying so the double neg is redundant. either =SUMPRODUCT(((E3:E1000=6)+(E3:E1000=6.5))*(M3:M1000="w")) or =SUMPRODUCT(--((E3:E1000=6)+...

If I have a whole list of items that are, say, 7 characters long and all start with "ab" (example: abcdefg, abdefgh, abzyxwv), is there a way to run an update query (or is there any other way) to change the first two characters "ab" to something else, like "12", so I end up with 12cdefg, 12defgh, 12zyxwv? Thanks in advance for your replies and/or patience. Ken -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200706/1 Dale, I'd agree, but the "meaningless set of characters" I'm wanting to i...

Is there a formula that would allow the following: the entry of 6 variables (a, b, c, , 1, 2, 3) into cells A1 through A6 as either single entries (a..b..c..etc) or multiple entries with a maximum of 3 variables (ab..ac3..etc) that will result in an "X" inserted into cell A12 -- rwfster No, a formula only returns a result to its parent cell. -- __________________________________ HTH Bob "rwfster" <rwfster@discussions.microsoft.com> wrote in message news:DF797521-EBFA-40C3-B584-0A92F8BB49AE@microsoft.com... > Is there a formula that would allow the follo...

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 ------...

Here is exactly what I am trying to do through VB in Excel: Weekly data pull fills colums A:G. Row count is always different. I am modifying the data pull through VB, and I have a VLOOKUP formula in cell H2. What I want VB to do is copy that formula down column H to the last row (with data) each week. I guess I want it to be dynamic so that as rows decrease/increase the formula is only copied down to the final row/record. I know someone out of this smart group will know how to do this! Thanks in advance! Tony (pseudo code) in a macro .... dim lngLastrow as long dim rngTarget...

Would like to post data from one worksheet to another. Could you provide a few more details about what exactly you want to do. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "dr" <drivera@opvista.com> wrote in message news:1f1001c52c08$ad4c33a0$a601280a@phx.gbl... > Would like to post data from one worksheet to another. ...

I am trying to add a combobox that when you select from the dropdown menu, the columns nextdoor automatically pull up corresponding data that is related to the selection from the dropdown list. Does this make sense? What do I do to set this whole thing up? (I don't know code). Thanks. -- Message posted via http://www.officekb.com You shouldn't need code for anything you described here. Set up the combobox with the list fill range, if any. Set the linked cell to the desired cell. Then, use the VLOOKUP command in another cell to look for the linked cell from the combo box to lo...