Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










If Statement To Pull Values From Another Spreadsheet / Worksheet


I have two spreadsheets, i suppose one part of the solution may be to copy spreadsheet 2 into a new worksheet in spreadsheet 1, but this is my current situation:

Spreadsheet 1 has "Employee lastname" in column F, "Employee Firstname" in column G. Column A in this sheet needs updating with the data populated in column H for that employee in spreadsheet 2 - the lastname is contained in column B, firstname in column C - so i need to to a match.


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
IF Statement To Pull A Date Range?
I've created an IF statement with multiple formulas, but I can't seem to
figure out if it is possible to pull the information by a specific date
range. I have sales reps that visit customers on specific dates and I need
my tally sheet to only pull dates within that current week. Does anyone know
if this is possible??

This is what I have so far:
=SUM(IF(sales rep!AB2:AB216=I3,IF(sales rep!$AA$2:$AA$216=I4,IF(sales
rep!P2:P216=I7,IF(sales rep!$N$2:$N$216>=DATEVALUE("3/18/2005"),1,0)))))

If you look above I have it to calculate the date value starting at
03-18-05, but I would like it to only pull that date through the next week.


View Replies!   View Related
Pull Specific Data From Spreadsheet
I have a complex data set that has been imported into Excel from a binary data file and I am looking for a way to simply pull out specific fields. I tried to record a Macro that would simply delete the rows between the data rows I want to keep, but it always references itself to those same ranges and I'm not sure how to identify that I want it to sequentially move down the page deleting the same number of rows, and adding the row of information I want to keep as it goes.


View Replies!   View Related
Pull A Value (text) From Another Spreadsheet Corresponding With A Number
I am trying to pull a value (text) from another spreadsheet corresponding with a number. I can't use vlookup because none of them are in a left column. HEre's an example:

there are 2 columns with names and percentages. I am trying to pull a name that corresponds to a certain percentage.

View Replies!   View Related
If Statement With Vlookup Does Not Pull Proper Response
I am building a template for a report. The report resides on the first sheet of the workbook and the subsequent sheets is where the user would paste the appropriate reports so that the formulas on the first sheet can pull the appropriate data. I have all my formulas working fine with one exception.

The data in question needs to be pulled from the sheet "Paste Adcap Report Here" and the column is AA. The common piece of data between the two sheets is in column C of the Adcap sheet and column B of the main report sheet. What I need to do is conduct a vlookup between the report sheet and the Adcap sheet using the account id's and then return either the date value in row AA or the word ongoing which would be those accounts which do not have a preset end date. So far a co-worker had been able to come up with the following:

=If(VLOOKUP(B16, 'Paste Adcap Report Here'!C:AA,25,0) >0,"cap","ongoing")

The code is a bit screwy as she had to leave early. The problem we encountered was the entry in column AA for a non-ending campaign. It is represented by two hypens (--). This is what tied her up. we could not figure out why the formula was returning an #n/a instead of the term "ongoing" when it encountered the (--). Again, if the formula pulls a (--) then the word "ongoing" needs to be displayed and if the formula pulls an actual date value then it needs to display this date.

View Replies!   View Related
IF Statement- Spreadsheet To Calculate Referral Fees Paid To Our Agents
I am trying to build a spreadsheet to calculate referral fees paid to our agents. These are sliding scale fees and I need the formula to consider both the high and low range of the project value. For example we pay a certain percentages for sales in the following ranges:

Up to $5,000,000 (I got this one to work)
$5,000,001 to $7,500,000
$7,500,001 to $10,000,000
$10,000,000+

I assume the best way to do this is with an IF command but I only know how to use it for a single value, not a range. Also, if the statement is not true, then return $0.

View Replies!   View Related
Pull In Address From Another Worksheet
I have two worksheets. The first is a master listing of all stores for a
certain client. They are all assigned a store number. The second sheet is a
list of all invoices, I need to be able to type in the store number and it
pull up the street address, city, state and zip, all in different columns.

View Replies!   View Related
Pull Web Page Into Worksheet
Can you use something like that to pull "All the text" from that URL back to Excel (like in Select All, Copy, PasteSpecial) rather than using SendKeys? The existing URL listed in your Test Sub would be fine to use in the example. If so, could you please modify the Test Sub code to show the code?

View Replies!   View Related
Pull Out Unique Values
I have 36 cells that will have multiple letter values. The cells in the range (MyRange) in question, have formulas in them. I will be transferring this into a cell range that has over 6000 cells, but I'll worry about that when I get this part down. :D I have 07 excel but this is in the older versions.

View Replies!   View Related
Pull Top 3 Highest Values
I have a column of numbers that I would love it if Excel could pull out the top 3 or 4 highest values from the column.

View Replies!   View Related
Pull Values From Many Worksheets
I need to place a value in cell B on sheet 2 of a workbook. The value I need is stored in C10 on all other worksheets in the same workbook. The name of each worksheet is stored in Column A of sheet 2 (Rows 3 through 404). How do I get the value from C10 on each worksheet into the corresponding row on Sheet 2?

I have Excel for Auditors and there is code on page 201 but it is the reverse of what I need. I need to get the value from the worksheets as opposed to moving a value to the corresponding worksheet.

Example:

Sheet 2
Column A Row 1 = Worksheet name
Column B Row 1 = Need Cell C10 value from worksheet name referenced in A

View Replies!   View Related
Pull Values Between Two Periods
I have a text value which consists of two periods ".". I want to pull the value in the middle. eg. From the text value in a cell "oakland.california.usa", I want to pull "california" in another cell. All the values in column are in similar format.

A B
1 oakland.california.usa california

Hope this is not duplication of any pre-existing thread, if it is, I probably don't know the correct keywords to query.

View Replies!   View Related
Pull Data From Two Other Worksheet Per Condition.
The spreadsheet will be have 3 worksheets. The first worksheet will be the mastersheet (Form-01 in the attachment) that will be used as a summary/receipt for the person participating in the auction. The second worksheet will be a list of the people participating in the auction (Names) will all applicable contact info. The third worksheet will be the list of all the items for the auction (Auction Items) with all relevant information per item and a unique number for each item.
The idea is to be able to track information during the auction on the "Auction Items" worksheet, there is a column for the bidder ID of the winning bidder. Then after the auction is over and its time to settle up be able to go to the master sheet (Form-01) and enter the Bidder ID# and have the other fields auto-populate.

View Replies!   View Related
Pull Information From Worksheet To Outlook
I want to setup an excel spreadsheet that will list employees and when exams were completed on them. The exams need to be completed once a year. I want the excel spreadsheet to send out an email to their corresponding supervisors when is within one month or so of an exam needing to be completed so their exam can be scheduled accordingly.

I have searched this forum and read through some of the other posts regarding exporting data from excel to outlook. This seems like what I need to do but I wanted to see if anyone has an advice on what way is better than the other?

Also, if excel is setup to push the data to outlook, does the excel file need to be open? In other words if Jimmy comes within one month of his exam but no one opens the excel file will it send out the email to outlook?

Is it a better option/ or possibility to have Outlook pull/check to see if the condition exists where an employee is within a month of his exam and then let the supervisor know?

View Replies!   View Related
Vlookup: Pull Data From One Worksheet To Another
I am trying to pull data from one worksheet to another. I am using Product ID numbers. The problem I am having is that not every Product ID I am searching has a partner on the second list, so I get an #N/A. In stead of #N/A I just want a "0".

my vlookup looks like this: =IF(VLOOKUP(A1,Sheet2!A5:C500,3)>0,VLOOKUP(A1,Sheet2!A5:C500,3),"0"). If A1 does not find a match on Sheet2, it returns "#N/A" when I want it to return "0".

View Replies!   View Related
V-lookup To Pull Information From One Worksheet To Another
I'm using a v-lookup to pull information from one worksheet to another. I know that each of the cells has a match on the second sheet, but the calcuation is resulting in a Value Not Available error. The only way I've figured out to get past the error is to go into each cell, hit F2 and then enter.

View Replies!   View Related
Pull Info From 1 Worksheet Into Another - Not A One-to-one Relationship
I have one worksheet with 238 rows and another with 163 rows. I want to pull information into the sheet with 238 rows whenever there is a match to a record in the 163 rows. I tried VLookup, but as soon as it gets to a record in the 238 that doesn't exist in the 163 it gives errrors and stops. I've looked at =offset and =offset(match) but I'm not finding anything that deals with the situation of not always being a one-to-one (or even one-to many) relationship. Ultimately I want to pull the Category & Project fields into the records matching on IDNumb in Sheet 1 but Sheet 1 has more records than Sheet2. example......

View Replies!   View Related
Pull Details Of Each Person From Another Worksheet
I have worksheet 1 which calculates a member's pension which is based on his date of joining a scheme, date of leaving, salary and yearly contributions paid into the scheme. Normally I would input these details manually but now I have a large number of members to produce figs for. These members' details will be on worksheet 2 with a separate row for each member's details. I want excel to process each member one by one and enter his details in worksheet 2 on the calculation worksheet 1 and put the results in worksheet 3. Is this possible? And how???

View Replies!   View Related
Add A Worksheet To An Existing Spreadsheet Which Has VBA Behind It, But The Worksheet Is Password Protected
One of my work colleagues need to add a worksheet to an existing Excel Spreadsheet which has VBA behind it, but the worksheet is password protected. The developer who wrote the application has now left, so we have no idea what the password is. Is there anyway of getting round it, like cracking into the spreadsheet to find out the password, or another way?

View Replies!   View Related
How To Pull The Duplicate Values From 2 Columns?
I have this problem I have 4 columns which contain data. The first column is a list which contains random values from the third column. Now what i want to do is to pull the duplicate values from the second list without agitating the data from the 4th column because it is corresponding.In the example i have attached i would like to take the values from column A and find them in column F then extract them together with corresponding value from column G

View Replies!   View Related
Pull Unique Values From 3 Locations
I would like to create a macro which will look at columns A,B and C. Based on A,B and C, I would like the macro to return only the unique combinations. At the same time, I would like columns D and E to perform a sum.

This is the original sheet:

******** ******************** ************************************************************************>Microsoft Excel - Trial19.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutE1E2E3=
ABCDE1A1YES1032B2YES1013C3NO1034D4MAYBE1015D4MAYBE101NJSS*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

This is what the sheet will look like once the macro I would like to create is executed.

******** ******************** ************************************************************************>Microsoft Excel - Trial19.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutE1E2E3=
ABCDE1A1YES1032B2YES1013C3NO1034D4MAYBE202NJSS*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

As you can see, the highlighted areas denotes the changes that were executed through the macro.

View Replies!   View Related
DSUM Function To Pull In Data From Another Worksheet
I've been using the DSUM function to pull in data from another worksheet.

In brief, the problem is that when I Cut & Paste the formula from C7 to C8, I want the Column reference to change but not the Row reference.

If I copy across the Columns then the result is OK – e.g. The Formula will change from…

#=DSUM([HBAP_SHUTDOWNS_2008.xls]NWM_SuccessfulShutdownsByDepartment!$A$4:$E$2924,"Total",'Calculations Page'!A$17:A$18)#

To…

#=DSUM([HBAP_SHUTDOWNS_2008.xls]NWM_SuccessfulShutdownsByDepartment!$A$4:$E$2924,"Total",'Calculations Page'!B$17:B$18)#.........................

View Replies!   View Related
Pull Data From Worksheet Based On ID Number
I have 2 worksheets (Sheet1 and Sheet2). Sheet 1 contains around 3000 records with multiple fields (columns). Column A contains the ID number. Sheet 2 contains some records with different fields except for the ID number which is common.

I need a macro to retrieve information from sheet1 and copy it to sheet 2 based on the ID Number; i.e the macro needs to get the ID number from sheet2, locate it in sheet1 and copy the data from column D in sheet1 for that particular record and paste it in sheet2.

View Replies!   View Related
Place / Pull Html Table Into Worksheet
I need to copy/cut and paste an Html table from a website into and Excel sheet without the data being transposed. See link for the table I need to get into Excel:

http://www.voedingswaardetabel.nl/voedingswaarde/?vw=vv

Then cut and paste it to Excel, you see the data is now vertical. I need the table horizontal as it appears on the website.

View Replies!   View Related
IF Or LOOKUP: Search For Values From One Worksheet And Identify Whether Or Not Those Values Exist In Another Worksheet
I tried both IF and LOOKUP and failed. I'm trying to search for values from one worksheet and identify whether or not those values exist in another worksheet. I attempted the following lookup in field A2:

=LOOKUP(B2,Sheet3!A$2:A$914,Sheet3!C$2:C$914)

B2 (thru B5000 or so) contains values I want to search for; sheet3!A$2:A914 is where I want to look and column C of that same sheet, entered the text "Yes" in an attempt to have the results list "Yes" for hits and N/A for misses. (All fields are text.) I copied the formula all the way down the sheet in column A. The result it is returning is N/A in A2 and Yes in A2 -to the bottom, which is incorrect.

View Replies!   View Related
How Can I Pull Selected Rows Of Information From 1 Worksheet Page To Another
how to reference cells in 1 page from another and understand basic formula's, and would appreciate any input on how or if i can acheive what i'm trying to accomplish.

I have a worksheet with say 100 rows of information, it is a list of materials to go into a construction job. the columns to the right of the description calculate volume/quantity etc and produce a cost for the item. what i want to do is have a summary page that only lists the items that are being used, not all 100 items are in every job and i dont want to have to print the whole list with a bunch of empty values, which will just waste paper and ink. I'd like to enter a value in a cell or setup a check box or something that i could somehow use to then list that row or a part of that row of information on a seperate sheet, for a nice concise, easy to read list of job materials. i would like to do produce 1 sheet with the cost associated with the item, and one without cost.

View Replies!   View Related
Pull Data From A Specific Cell From A Closed Worksheet In VBA
How can I pull data from a specific cell from a closed worksheet in VBA?

Not sure but I think that Workbook("wb1.xls").Worksheet("Sheet1").Range("A2") only works if the worksheet is open.

View Replies!   View Related
Pull 4 Values Separated By Commas In Single Cell
I have a single cell with 4 values in it all separated by commas and i would like to pull out each one separately. The number of characters in each value changes.

View Replies!   View Related
Pull Data From Closed Workbook Based On Cell Values
I want to pull data from closed workbook based on cell values of open workbook of column B and the source file name is on cell J1. Actually I save monthly files and opening balnce of current month should take vakues from previous month file.

Suppose current month is May 2008. Then Column Column D for May month shold take value from column G of April 2008. For simplicity the previous month’s name and thus source file name will be placed on cell J1.

The code should loop from column B of source file and current May 2008 file and should pull values for only those items which are in the current file in the Column B. Thus those products which are deleted or newly added item in the current item should not copied. Though for new item no name will be thre in the source file but for deleted items the item might be there in the source file but the code should ignore those value.

View Replies!   View Related
Lookup: Pull Any Color Choices Associated With A Year From The Data Table On The First Worksheet
I have a data table that contains begin dates, end dates, and colors. The data is input starting with the earliest begin date. On a second worksheet, I have a listing of the years starting with the year of the first begin date and ending with the end of this year. I need to be able to pull any color choices associated with a year from the data table on the first worksheet. Some years may not be specifically listed in the data sheet as the person could have had the same color choice for several years. Sometimes a person could have more than one color choice in a year.

I am at a loss for how to get to the data I need. I created an example spreadsheet to demonstrate the data I am working with, and an example of what I need my results to look like in the end.

View Replies!   View Related
Pull Until . Found: Pull The Number From The Right Until It Hits The Decimal Sign
I have a column of numbers each have a 0. infront of them (example 0.2346
0.5698). I want to pull the number from the right until it hits the decimal sign. So for the two above the result would be 2346 and 5698

View Replies!   View Related
"If" Statement: Work Out On A Spreadsheet How Many Failures I Have Had According To A Traffic Light System
I have a scenario where I am trying to work out on a spreadsheet how many failures I have had according to a traffic light system. If I have 5 or less failures it is considered green, between 6-10 amber and 11 or more red. How can I create a formula under column A that captures which status it is on?

View Replies!   View Related
Two Different Values Using IF Statement
I'm working on a sheet that will help us with lots of data. This spreadsheet has 2 work sheets in it, one is being used as a form, the other data.

I'm having trouble with a formula on a "Form" worksheet that tells me - if a cell on the "Data" worksheet is blank, then leave it blank. If it contains the word "ON", then put an "X" in the cell.

Here's what I am using to try and get this working, not having any luck.
Any ideas?

=IF(OR('Data'!H2="",'Data'!H2=ON),””, X)

View Replies!   View Related
If Statement Referring To Another Worksheet
I am trying to insert an If statement using the

With Sheets("Sheet2").Select
Range("c" & rBegRow).Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=IF(Sheet1!R[-8]C[4]<>0,Sheet1!R[-8]C[4]*-1,Sheet1!R[-8]C[5]*-1)"
Range("C" & rBegRow).Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
End With

The reason is that once the formula is inserted in to the cell in worksheet 2 then I drag it down kind of like a copy paste so it changes automatically the if statement cell reference to worksheet 1 automatically. the trouble I am having is that I do not want to use the R[#]C[#] since when the contents of the information changes the formula does not work properly. If i substitute them for G9 instead of the R[-8]C[4] and H9 R[-8]C[5] but it inputs it in the actual excel worksheet as =IF(Sheet1!'G9<>0,Sheet1!'G9*-1,Sheet1!'H9*-1) therefore creating a value error.

View Replies!   View Related
IF Statement With Multiple Values
I would like to be able to search a group of cells for particular text,
placing the true of false value in another cell, using this formula;

=IF(ISNUMBER(SEARCH("ACP",A2)),"ACP","XXX")

My reason for this is that I have a large amount of data that I need to
break down. I have a column that is listed similar to below;

OR/ACP
OM/ACT
OR/MTS
O/O

The part before the / is 'system code'
The part after the / is 'module code'

I want to be able to have one additional column for system code and one
additional column for module code, without having to manually go through all
the data and make any amendments to it.

So as a result, (without the correct syntax) with 'S' being the system
Column and 'M' being the module column, what I would like to achieve is;

If cell contains "O/" then S1 = Office but if cell contains "OR/" then S1 =
Office Range but if cell contains "OM/" then S1 = Office Medium

I hope that makes sense.

Obviously repeated for the module column;

If cell contains "/ACP" then M1 = ACP but if cell contains "/ACT" then M1 =
ACT but if cell contains "/O" then M1 = O


View Replies!   View Related
VBA-statement To Look For Inexact Values
I need to change the following statement to look for inexact values; i.e. the string of text (ABC) can be found in any position of that cell.

Example 12345ABC = True, 123ABC456 = True, etc.

If (ActiveCell.Value = "ABC") Then

View Replies!   View Related
Set Up An If Statement That Where 'a' And 'b' Are Numerical Values
I want to set up an if statement that does the following, where 'a' and 'b' are numerical values.

If 'a' is not a multiple of 'b' Then
...
Else
...
End If

How can I write this so VBA can understand it?

View Replies!   View Related
CountIf Values With Between Statement
I have a list of 100 values in Column A of the attached spreadsheet Sample.xls. I need to count the number of values between a certain percentage including both positive and negative values. Example: I need to count all values that are between 0 and +5% and 0 to -5%.

View Replies!   View Related
Copy From One Worksheet Into Rows On Second Spreadsheet.
Worksheet (Daily Sales) - daily input, copy to the worksheet (Weekly Sales), Monday through Saturday. The ranges copied are not consecutive. I am using If...then...else.

View Replies!   View Related
Worksheet Update For Holiday Spreadsheet
I need to have all the information in one workbook for all 100 employees and still provide an individual report for each user and an overall report. In the attachment is a "Summary" and "Calendar" sheet these are the original individual files. summary contains personal information and the "calendar" tracks absence and holiday and the totals returned to the Summary sheet. I think what I need is a Master worksheet that has all the information in for each employee pre row and to have a "calendar" input/change/ call worksheet sheet to call an individual detail, this would also update the master worksheet with any changes.

View Replies!   View Related
IF Statement Help With Values In Multiple Cells
I need to see if it is possible to set up an IF statement that can pull information from cells based on the results already available in those cells.

ABCDEFGHIJKLM1Non FeaturedGoodGoodIdentical IMEIsGoodNeeds RLM NumGoodGoodGoodChk DOA BoxGood32Non FeaturedNo ExchangeGoodGoodGoodNeeds RLM NumGoodGoodGoodChk DOA BoxGood23

What I need is a formula in cell M1 that will check each cell B1 thru K1 for the data "Good". If it does not equal "Good", then I need the checks true/false value(s) to show the reason shown in the other cells.

In other words, for row 1, i need a formula where the outcome would be "Identical IMEIs" because D1 is the first box in the set that does not equal "Good". Then if D1 was corrected to "Good", the outcome would then be "Needs RLM Num".

I tried to do it in sections such as {=IF(B7"Good",B7, then the next check )} but the number of statements exceeds the number of IF statements that can be nested. And I really need the formula to fit in one cell.

View Replies!   View Related
MIN Statement But Excluding Negative Values
All i am trying to do is create a function that will provide me with the minimum invoice value between cells H17:H150 which excludes negative values i.e. refunds ...

View Replies!   View Related
If Statement Doesn't Recognize Non-zero Values
I have the following formula with multiple if statements:

IF(N2>0,N2,IF(O2>0,O2,IF(P2>0,P2,IF(Q2>0,Q2,IF(R2>0,R2,IF(S2>0,S2,IF(T2>0,T2)))))))

This formula refers to cells in columns N,O,P,Q,R,S and T. Every cell in columns N,O,P,Q,R,S and T also contains formulas (VLOOKUPs).
My if statement works fine but when it checks cells in column P, for some reason, it doesn’t recognize cells with non-zero values. I can’t figure out why this is happening and how to fix this problem.

View Replies!   View Related
Copying Values Into Text Statement
I am using Excel 2007.

In cells A1 and B1 I have numerical values, e.g. 50 and 12 respectively. Cell C1 calculates the product A1*B1 (= 600). In another cell, say D1, I want to display a statement like “50 x 12 = 600” with the 50, 12, and 600 taken directly from cells A1, B1 and C1, without re-entering them. If I change the 50 to another value, say 70, then I want D1 to read “70 x 12 = 840”

View Replies!   View Related
Accounting For Multiple Possible Values In A IF(AND Statement
IF B1 has a possible value ranging from 1 - 5, and IF the value in E1 is equal or great than 2,5,10,10,15 BUT 2,5,10,10,15 need to match to specific ranges set in B1 1=2, 2=5, ,3=10 ,4=10 ,5=15 THEN IF TRUE "WITHIN" IF FALSE "NEEDS UPDATE"

Got help earlier with this formula: =IF(AND(B1=1,E1<=5),"WITHIN","NEEDS UPDATE") :D Worked Great! This was my attempt at expand that formula:

View Replies!   View Related
If Statement Based On Two Cell Values
If I have any value in cell A1 then the cell should show 1 if true or nothing if false. I have managed this via

View Replies!   View Related
Test 2 Cell Values With IF AND Statement
I want to reference 2 cells to open a msgbox. Currently I use this formula in a cell "=IF(E4="C",IF(A4>30,"OT","")) I am trying to write a macro that will open a msgbox instead. This is what I've tried so far

Private Sub Worksheet_Change(ByVal Target As Range)
If Target(1, 1).Address = "$E$4" Then
If Target(1, 2).Address = "$A$4" Then
If Target(1, 1) = "C" Then
If Target(1, 2) > "30" Then MsgBox("1")
End If
If Target(1, 1).Address = "$E$4" Then
If Target(1, 2).Address = "$A$4" Then
Target(1, 1) = "F" Then
Target(1,2) > "38" Then MsgBox("2")
End If

View Replies!   View Related
If Statement Using Offset Values For Dates
I'm trying to write code so that it uses an offset value depending on criteria. In the attached book I have three coloumns, firstly the date, secondly some letters, an If statement in the third coloumn and another IF statements in the fourth coloumn. I would like a macro that calculates the number of DAYS ONLY, depending on the fourth coloumn. The way it should operate is it should is detect a 1 in the D coloumn and then calculate the difference in days from the adjacent date in coloumn A and subtract from the first time it appears in coloumn A.

For instance in the worksheet, the first 1 in coloumn D is in 'D5'. The date in 'A5' is 04/01/2005. The first time a '1' appears in coloumn C is C2 and its date in 'A2' is 01/01/2005, so 04/01/2005-01/01/2005 is 3 days. Now heres the twist, the next 1 in the D coloumn is in 'D8' and hence the date in A8 is 07/01/2005, now this time the '1' in coloumn C is in 'C7' and hence the new calculation is 07/01/2005-06/01/2005. So hence the 1 in coloumn C is always changing (ie new offset value).

View Replies!   View Related
Archive The VALUES Of The Spreadsheet Every Two Weeks
I have a spread sheet that pulls data from several different locations.

I need to update this spreadsheet once every two weeks.

I need to archive the VALUES of the spreadsheet every two weeks.

I have a recorded macro that only seems to work from Personal.XLS Modules and I need it to work from the Workbook itself so that others can use it from the shared drive that it is posted on.

The workbook sheet names are DO7, DO8, DO9, Features, and Source Explanation, but I only need the first 3 listed to archive to the new folder. The range could be A1:J100 on each sheet if the entire sheet can't be copied.

I know there is a way to utilize temp files and would prefer to use that method instead of the kindergarten level of a macro that I have pasted below.

Sub AutoArchive()
YesNo = MsgBox("This will save the document and save an archive file. Do you wish to proceed?", vbYesNo + vbCritical, "WARNING: This document will self destruct in 10 seconds!")
Select Case YesNo
Case vbYes
'Insert code for "Yes"
ActiveWorkbook.Save
Sheets("DO7").Select
Range("A1:J100").Select.....................


View Replies!   View Related
Checking If Values In One Spreadsheet Exist In Another
I have a 'master' spreadsheet containing over a thousand filenames. I have been given another spreadsheet with just a couple of hundred filenames on it and have been asked to find out if the filenames on this 'small' spreadsheet appear on the larger/master one.

Is there any way (by formula or by macro code) that I can do this easily? What would be great is some way of Excel colouring in the filenames on the 'small' spreadsheet that also exist on the master one.

View Replies!   View Related
Nested IFs Statement That Looks At A Range Of Cells Between 2 Tabs Within The Same Worksheet
I'm trying to create a nested IFs statement that looks at a range of cells between 2 tabs within the same worksheet, determines if there is a value of 1 in ANY of those cells, and if there is, to multiply the value of yet another cell by 1 in a cell on the second tab. So, if 1 is a value in any cell of Tab 1 cells C82 to C86, then in Tab 2 cell B21, multiply 1 by the value in 'Tab 1 Cell H5'.

I've tried this =IF(('LM fundamentals'!C82>=1), ('LM fundamentals'!C83>=1), ('LM fundamentals'!C84>=1), ('LM fundamentals'!C85>=1), ('LM fundamentals'!C86>=1), 'Order Information'!$B$21!=(1* 'LMfundamentals'!$H$5!))

I even tried a vlookup, having given the cell range in Tab 1 a name, etc. But I have little skill with logic statements, apparently.

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved