Closed - No Resolution- Lookup Formula

Apr 16, 2009

I have a two worksheet file.

On one worksheet I have every day of the year (work day) and corresponding data for many topics for that day in the column.

On the other worksheet I want the user to type in a date and have the data for that day from the first worksheet display in a specific cell (based on the date entered at the top of the sheet) on the second worksheet.

I think it is some type of lookup formula but am not sure.

Enter todays date on one worksheet and have data display based on that date from a second worksheet. The data on the second worksheet would be manually updated every day.

View 10 Replies


Interpolating Data At 5-minute Resolution Down To 1-minute Resolution

Nov 27, 2013

Entering a formula which will allow me to expand my data set by interpolating between sets of x,y values. My data set is in 5-minute resolution and I would like to interpolate so that I can output a new set of x,y values in 1-minute resolution. Here is my example of what I have and what I would like my end result to be, and also I have attached a sample of my data set in the below excel file:

Original data set
Date TimeTemperature oC
6/12/13 13:30 18.28
6/12/13 13:35 17.9
6/12/13 13:40 17.9

Desired data set
Date TimeTemperature oC
6/12/13 13:30 y
6/12/13 13:31 y
6/12/13 13:32 y
6/12/13 13:33 y
6/12/13 13:34 y
6/12/13 13:35 y
6/12/13 13:36 y
6/12/13 13:37 y
6/12/13 13:38 y
6/12/13 13:39 y
6/12/13 13:40y


View 2 Replies View Related

Lookup Value From Closed Workbook.

Feb 24, 2009

I have a workbook called Book1 that was created from another macro. I am attempting to do a lookup using columns B2 and C2 and look for the same values in columns G6 and J6 from a workbook called Marine Moves..If a match is found copy the value from Column H insert that value into Book1 Column A, if possible I would like the Marine Moves workbook to remain closed during this process.

View 9 Replies View Related

Lookup In Closed Workbook Without Opening

Jan 22, 2010

I want to lookup data in a closed workbook, that is located on my desktop, without having to open that workbook. I have been trying code as follows, but having problems with the syntax.

View 12 Replies View Related

Lookup Range In Closed Workbook

Jan 26, 2010

I'm using the following sub (thx Benvolio 2001) to do a vlookup (of A1 in Sheet2) and drop the resulting value ("found") in b1 of sheet2. Here's my question: I'm having trouble modifying the rng to use a closed workbook in another folder i:other folderotherfile.xls. The "Inventory" tab is in otherfile.xls
How can i do this? use the vlookup in a closed workbook somewhere else?

Sub Example_of_Vlookup()
Dim lookFor As Range
Dim rng As Range
Dim found As Variant
Set lookFor = Sheets("Sheet2").Range("A1")
Set rng = Sheets("Inventory").range("A4:D500")
On Error Resume Next
found = Application.Vlookup(lookFor.Value, rng, 4, 0)
Range("b1").value = found
End Sub

View 3 Replies View Related

Lookup Data In Several Closed Workbooks

Jun 2, 2006

I would like to create a sheet that looks in a certain folder with several .xls files and pulls data from certain cells ( not necessarily next to each other) automatically with out opening each workbook in the target folder.

Each workbook is laid out the same, based on a template, and emailed to me each day (I'll actually get 3 or 4 a day, one from each plant) So the number of files will grow every day. (I've read about a way to automatically save the file based and the date, hour, and minute, and email the file when a certain condition is met, and plan to use this technique in the template.) So the file names should be consistent.

As an example, I would like cell A1 to contain the date from the oldest file and increment row by row as the files come in, automatically. Then the cells B1 on across would contain data from certain cells within the .xls file with that date and time.

Every time that workbook is opened it would update to include data from any new files in the specified folder.

I've done some basic vlookup formulas to look at one particular workbook but not several.

View 9 Replies View Related

Lookup In Closed Workbook Error

Dec 5, 2006

Column D = Dates to be used in each of the matches.

Cell C2 = data-validation cell, for choosing the specific Named Range that contains the data to be returned from the closed wb.

VBA code automatically opens the closed data file and then closes it after returning the data to the main file.

find the error in the cell formula (or named range formula) thatís preventing this from working correctly? (see atttached files)

View 9 Replies View Related

Lookup Referencing Closed File Returns Value Error

Aug 13, 2008

I have a file created in Excel 2003 that uses a vlookup to reference another file, also created in Excel 2003. The function returns VALUE when the referenced file is closed. I get the VALUE error whether or not I update links upon opening the file. If I open the source file, the function calculates properly.

Here is the formula when the referenced file is open:

=VLOOKUP(A38,'[June Scorecard confirmation.xls]VPSC Summary'!$C$10:$F$22,4,FALSE)

The full & correct path appears in the formula when the referenced file is closed.

Columns C, D & E in the referenced range contain text, column F contains a Sum. This file is linked to another file, also using lookups. The linked area in this file works without problem, but it is a precedent to the calculation in column F.

Both these files reside on a network in subdirectories of the same logical drive. I am using a laptop that briefly had Excel 2007 installed on it, but was wiped clean before it was issued to me.

View 3 Replies View Related

Data Elimination- Resolution

Jul 17, 2009

I produce a report each month that requires manual manipulation to get to the end result. Because I have yet to find a macro or formula that can assist me in reducing the data for what I am looking for

Emp Name
Project Code
PRoject Name
LevelRevenueLbr RevenueLbr CostExp CostProfitVarianceRoy, Marcus L.
CM-89.530.000.000.00-89.53-89.53Roy, Marcus L.
PM293.600.000.000.00293.600.00Roy, Marcus L.

View 9 Replies View Related

Reducing Resolution Of Worksheet Data

Jan 16, 2008

I have a piece data acquisition hardware which stores data every 5 minutes into an excel sheet, I need to reduce the resolution of the data to every 30 minutes, currently I am doing this by deleting five rows leaving one and repeating the process, there is however 9285 rows
I am assuming there is a macro which can do this far quicker than me.

View 9 Replies View Related

Auto-Adjust Workbook To Screen Resolution

Nov 6, 2012

I am using code to auto adjust screen resolution when opening a workbook so that my workbook will span that computer screen of anyone opening it.

How to taylor it specifically for each worksheet as they differ in size in terms of what cells are used??


Private Sub Workbook_Open()
Dim Vis_Rnge As Range
Dim Data_Rnge As Range
Dim X_Fact As Double
Dim Y_Fact As Double

ActiveWindow.Zoom = 100

[Code] ..........

View 1 Replies View Related

Set Window Size Based On Screen Resolution

Mar 5, 2008

I have an Excel based application that needs to run with a screen resolution of 1024*768 in order to display correctly. Is it possible to use VBA to detect the users screen resolution. Then: If the screen resolution is not set to 1024*768 then resize Excel's main window to run at that width and height?

View 2 Replies View Related

Auto-Adjust Screen Resolution When Opening A File

Sep 18, 2007

I have made an excel spreadsheet. The area with data exactly fills my screen.

When I send it to other colleagues, it perfectly fills their screen too. But for some colleagues the data area is either too big for their screen or takes up a small proportion of their screen based on their screen resolution. I would like the area with data to perfectly fill the screen of all people who will access the file.

I am thinking that I would do an automatic event so that I can change the settings upon opening the file. I have Excel VBA programming for Dummies which explains about automatic events but I have no idea what sort of sub procedure to use.

View 2 Replies View Related

Copy Range From Closed File & Paste To Closed Workbook

Apr 26, 2008

Is there code that will take certain data from one Excel sheet to another named file in a different place on the network? Example Copy cell aa47 from "Recent Faxes.xls" that sits in "correspondence" folder. Then paste into cell B25 "Current Documentation.xls" in the "Sales Contacts" folder

View 2 Replies View Related

Formula Contains A Link To Closed Workbook?

Jan 5, 2014

How do I get rid of the link??? My formula seems like it is not working because it has a link which I cannot find anywhere.


Screen shot 2014-01-05 at 2.38.08 AM.jpg

View 6 Replies View Related

#Value! Formula Error When Source Closed

Dec 30, 2007

I'm compiling statistics from a number of separate workbooks. When I (or more to the point - one of my staff) open the Stats Workbook the formulas show #VALUE unless the other Workbooks are opened. I want the Stats to calculate without the user having to open the other workbooks as most users won't know which ones to open.

I could generate VBA code when the workbook opens to also open the other ones but wondered if there was another solution.

View 9 Replies View Related

Offset Formula Referencing A Closed Workbook

Nov 8, 2006

I am trying to create a one-page spreadsheet which links info from other various spreadsheets. There is one spreadsheet I am trying to link to which has information listed where Column A is the product name, Column B is the UPC and Column C is the sales rank. I created an offset formula that pulls the top 5 rated items by sales rank (Column C) into my new spreadsheet, but whenever the source workbook is closed, I get error messages. Is there a way around this so the workbook I am pulling my information from does not have to be open.

View 2 Replies View Related

Formula Shows An Error Once Source Is Closed

Nov 30, 2007

I am linking two worksheets together. Formulas work fine except when I close the source sheet, it turnes into #REF error. I saw in one of the posts that you can not use INDIRECT formula on closed workbooks (INDIRECT is a part of the formula in my sheet) and downloaded the changingExternal. zip file as directed. Although, after staring at it for a while, I realized that I had no idea as to what was going on in there.

View 3 Replies View Related

Excel 2007 :: Using Formula For Detecting Blank Cell In Closed Workbook

Jan 27, 2012

Anyone know of a way to detect if a cell is blank in a closed workbook?

Here's what I'm trying to do:

=IF(ISBLANK(closed workbook cell reference),NA(),closed workbook cell reference)

also tried:

=IF(closed workbook cell reference="",NA(),closed workbook cell reference)

Neither one worked. I'm using Excel 2007.

View 8 Replies View Related

Suppress Open File Prompt When Copying Over Formula Referencing Closed Workbooks?

Dec 13, 2013

I have an Excel file that contains formulas that reference external workbooks on a shared network. Each month, I copy the column of formulas over to the next month's column. Then, to update the file path, I highlight the new column and do a Find-and-Replace, swapping the previous month's name for the current month. This practice works fine. The only thing is ... the "Open File" promptbox appears for each formula where I updated the file path (i.e. and this can be hundreds instances!). I wind-up having to choose the file from the exact same file path. It is an unnecessary, and annoying extra step to take.

View 2 Replies View Related

Lookup Formula With Two Lookup Values

Nov 12, 2008

I have 3 Sheets named Paid, Rejected, and Reprocessed.

On the Paid and Rejected sheets I have 2 fields Customer # (Column A), and Amount (Column Q). (The customer # field has many duplicates but the amounts are never duplicates)

On the Reprocessed sheet I have all the rejected items (all fields) and also a field named Reprocessed. I need to use a formula that will check the Paid sheet for any items that have the same Customer # and Amount and return the amount

There are 8,216 rejected items and 45,047 paid items. Some items were originally rejected have been reprocessed and show under paid.

Any thoughts on which formula I should use?

View 10 Replies View Related

Lookup Formula: Find The Longitude And Latitude Data From My "lookup" Sheet

Jan 28, 2009

In my workbook I have multiple sheets but I'm attaching a very simple workbook to demonstrate what I'm trying to accomplish. In my "Lookup" tab/sheet. I want to have known Latitude and Longitude data that will exist in columns A&B. Columns C & D will have address numbers and Street Name. I would like my lookup formula to find the longitude and latitude data from my "lookup" sheet, when the matching address information is typed in, in my 2009 sheet. I have to keep the street numerics and street name separate on this worksheet as well. I believe I'll need two separate lookup formulas as I need these formulas to start in cell G4 & H4 in my "GeoCoding1" sheet. Is it possible to have four columns of data to be viewed in a lookup formula? I tried this formula in cell G4 (GeoCoding1 sheet)

View 3 Replies View Related

Using IF LOOKUP SUM And AND In One Formula?

Jan 30, 2014

Why this below formula is giving me a #N/A result when entering 'Motorcycle', '4x4' or 'SLR' into cell H2?


If there is an error?

View 12 Replies View Related

Lookup Formula

Jun 20, 2007

does anyone know if there is an easy formula to have a cell (h18) copy another cell (J2) if the value in a cell (h17) is greater than the last value entered in the row (17), and if it is not greater than the last value, or if there is no other previous value, to copy cell (K2)

View 14 Replies View Related

IF Formula? IF Then? Should It Be Lookup?

Jan 12, 2009

I am using the following formula to look up data from a seperate sheet and cannot get it to work. all three numbers are on the seperate sheet ... some show correct and others show not there when they are,

99993728569820 you found it!
99993728569820 no
99993767688837 you found it!

{=IF(A3=Sheet1!A:A,"you found it!","no")}

View 2 Replies View Related

Vba On Lookup Formula

Jun 18, 2007

Is it possible to have a macro on vlookup formula? Tried the below code but the lookup result is not what it should be. I know this could easily be done a keying in the lookup formula without using code. But it would be faster if it could be done by using a macro.

Dim c As Range
For Each c In Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
c.Offset(0, 1).Formula = Application.WorksheetFunction.VLookup(Cells(1, 1), Sheets("Master").Range("A1:B10"), 2)
Next c

View 6 Replies View Related

Formula To Lookup And Interpolate

Jun 8, 2007

I got a table of two columns (A1:B6). One column (A1:A6) got temperature values and the other column (B1:B6) got enthalpy values as a function of temperature values as shown in the attached file. All values in the table are empirical values and I am trying to use them to obtain the enthaly (B11:B21) for my own temperatures (A11:A21).

I can use VLookup formula to do so but it won't be accurate because the enthalpy values are very sensitive to temperature changes. I need to use table A1:B6 and interpolate for temperature values that are located in between the integer temperature valuse. e.g. if the temperature=1.5 (between 1 and 2 in the table), how can I obtain the enthalpy value=15 (between 10 and 20) using the table.

View 9 Replies View Related

How To Arrange Lookup Formula

May 13, 2014

How to arrange a lookup formula.


In this example, I am attempting to look up one value based on two criteria. The month must match, and the location must match. In doing so, the value "1" should be returned in the example.

View 3 Replies View Related

How To Lookup A Value And Return A Formula

Apr 6, 2009

I have rows with text and numbers. In order to ensure that the numbers are accurate, I have a "QC formula" that calculates a check using all of the numbers from 1 row. The challenge is that the "QC formula" needs to vary depending on a text value within the row.

How can I lookup up the text value and then return the correct active formula for that row? I have too many differet text values to do a nested If statement. see simplified example below.

Condition ABCFormula' Needed based on Condition

View 13 Replies View Related

Lookup Counting/Sum Formula

Oct 6, 2009

1 2
1 3
1 2
2 3
3 2
1 2
2 1

Im trying to work out a formula that will look down Column A for the number 1 and then will count the number of times the number 2 appears on the same row as number 1 in Column B. The answer I should be getting is 3.

View 3 Replies View Related

Copyrights 2005-15, All rights reserved