Free Microsoft Excel 2013 Quick Reference

Add rows based on criteria Results

I created this function, it works great, if it is in the worksheet that has my data in it. I exported it as an Excel Add-in. I want to be able to use the function with mutiple worksheets. When I enter the function into a cell, i have no arguments to pass to it. There for it returns a 0. The function is supposed to delete rows based on a criteria entered into another column.

Here is the complete function code:

	VB:
	
 ExecuteReassign() 
     
     'General Function Declaration Section
    Dim rRangeA As Range 
    Dim rRangeB As Range 
    Dim rCell As Range 
    Dim varPlayerHost As Variant 
    Dim varHostLicense As Variant 
    Dim varQuestion As Variant 
    Dim varAnswer As Variant 
    Dim varInstructions As Variant 
    Dim varConsent As Variant 
     
     'Directions for Use of this Function
    varInstructions = _ 
    "Please read these directions carefully." + _ 
    vbCrLf + _ 
    vbCrLf + _ 
    "You must copy the employee licenses to this spreadsheet." + _ 
    vbCrLf + _ 
    "They will need to be in a column by themselve." + _ 
    "If you fail to complete this step the program will erase" + vbCrLf + _ 
    " all data in this worksheet." 
     
     'Get the users consent to continue program execution.
    varConsent = MsgBox(varInstructions, 4148, "License Agreement & Instructions") 
    If varConsent = 7 Then 
        Goto Bye 
    Else 
    End If 
     
     'Get data for the locations of the gaming license numbers needed for the comparison
    varPlayerHost = InputBox("Please enter a single letter for the" + vbCrLf + _ 
    "Column that the Player Host License" + vbCrLf + _ 
    "numbers are in.", "Player Host Number Location", "H") 
    varHostLicense = InputBox("Now enter the column letter for the copied employee" + vbCrLf + _ 
    "license numbers", "Employee License Number Location", "U") 
     
     'Set the ranges for the data to be compared
    Set rRangeA = Range([varPlayerHost,1], Range(varPlayerHost, 65536).End(xlUp)) 
    Set rRangeB = Range([varHostLicense,1], Range(varHostLicense, 65536).End(xlUp)) 
     
     'The actual comparison and deletion of record that match the license numbers copied.
     'When this function finishes you will be left with a spreadsheet that only has patron
     'information left for the patrons that have an invalid host number.
    For Each rCell In rRangeA 
        If WorksheetFunction.CountIf(rRangeB, rCell) > 0 Then 
            rCell.EntireRow.Delete 
        End If 
    Next rCell 
     
     
Bye: 
     'This is just an exit redirect for those who don't agree to my terms
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Again I am asking how to get the function to perform properly without having to import it to the worksheet every time? I need this process to be as dummy proof as possible.

I am using Excel 2003. I created an application using GUI that asks for input and then goes to a worksheet. All the information is kept on one worksheet. I have created several reports based on the criteria in certain columns. That copy and paste only pertinent information onto a new sheet. These reports work however I can not make the reports work using multiple criteria such as if column a = "Accepted" and column b = "Pass". I believe I am having issues with my loops. Here is an example of a report that works currently but with only one criteria.


	VB:
	
 
Private Sub CommandButton1_Click() 
    Application.ScreenUpdating = False 
    Sheet10.Visible = xlSheetVisible 
     
     
    Dim temp As String 
     
    Sheet10.Activate 
    Dim ocell As Range 
    Dim bnone As Boolean 
    bnone = True 
    Dim current_row As Integer 
     
     
     
    For Each ocell In ActiveSheet.Columns(1).Cells 
        If IsEmpty(ocell) Then 
            ocell.Select 
             'MsgBox ("found at " & oCell.Row)
            bnone = False 
            Exit For 
        End If 
    Next 
    current_row = ocell.Row 
     
     
    Dim blah As String 
    Dim blah1 As String 
     
    Sheet3.Activate 
     
     
    For Each ocell In ActiveSheet.Columns("DD").Cells 
        If Trim(ocell.Text)  "" Then 
             
             
            blah = "CS" & Trim(Str(ocell.Row)) 
            blah1 = "G" & Trim(Str(current_row)) 
             
            Sheet3.Activate 
            temp = ("Onboard") 
            Sheet10.Activate 
            Sheets("Daily Snapshot").Range(blah1).Value = temp 
             
             
             
            Sheet10.Visible = xlSheetVisible 
             
            blah = "A" & Trim(Str(ocell.Row)) 
            blah1 = "A" & Trim(Str(current_row)) 
            Sheet3.Activate 
            temp = Range(blah).Value 
            Sheet10.Activate 
            Sheets("Daily Snapshot").Range(blah1).Value = temp 
             
             
             
            blah = "B" & Trim(Str(ocell.Row)) 
            blah1 = "B" & Trim(Str(current_row)) 
            Sheet3.Activate 
            temp = Range(blah).Value 
            Sheet10.Activate 
            Sheets("Daily Snapshot").Range(blah1).Value = temp 
             
             
             
            blah = "I" & Trim(Str(ocell.Row)) 
            blah1 = "C" & Trim(Str(current_row)) 
            Sheet3.Activate 
            temp = Range(blah).Value 
            Sheet10.Activate 
            Sheets("Daily Snapshot").Range(blah1).Value = temp 
             
             
            blah = "J" & Trim(Str(ocell.Row)) 
            blah1 = "D" & Trim(Str(current_row)) 
            Sheet3.Activate 
            temp = Range(blah).Value 
            Sheet10.Activate 
            Sheets("Daily Snapshot").Range(blah1).Value = temp 
             
             
            blah = "K" & Trim(Str(ocell.Row)) 
            blah1 = "E" & Trim(Str(current_row)) 
            Sheet3.Activate 
            temp = Range(blah).Value 
            Sheet10.Activate 
            Sheets("Daily Snapshot").Range(blah1).Value = temp 
             
             
            blah = "BY" & Trim(Str(ocell.Row)) 
            blah1 = "F" & Trim(Str(current_row)) 
            Sheet3.Activate 
            temp = Range(blah).Value 
            Sheet10.Activate 
            Sheets("Daily Snapshot").Range(blah1).Value = temp 
             
            current_row = current_row + 1 
             
             
        End If 
         
        If ocell.Row = 2000 Then 
            bnone = False 
             
            Exit For 
        End If 
         
    Next 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
(in this report everyone who has a blank in column DD is printed on the worksheet Daily Snapshot(report). Certain information is carried over onto this sheet as well as the status Onboard.)Sheet 10 is the report worksheet and sheet 3 is the main worksheet titled "application".

I have tried putting another for loop around this for loop to add a criteria such as
For Each ocell In ActiveSheet.Columns("A").Cells
If Trim(ocell.Text) = "Accepted" Then...

but it does not work.

Any help is much appreciated!! Thanks!!

Hi:

I am trying to use DSUM to add up values from an excel spreadsheet (several thousand rows in depth) based on a criteria. the criteria is different for each cell that I am trying to lookup. Do I have to write a criteria statement for every single cell? (See example)

Hello everyone:

I have the following query set up and it works well so far:

SELECT Count([B&B No Negatives and Processing Time].[Processing Time]) AS [CountOfProcessing Time], Min([B&B No Negatives and Processing Time].[Processing Time]) AS [MinOfProcessing Time], Max([B&B No Negatives and Processing Time].[Processing Time]) AS [MaxOfProcessing Time], Avg([B&B No Negatives and Processing Time].[Processing Time]) AS [AvgOfProcessing Time]
FROM [B&B No Negatives and Processing Time]

I have 2 other fields ([Pay] and [Total Check Amount] in my query [B&B No Negatives and Processing Time] that I would like to add to the criteria for this new query, such that when the query is finding the Count, Min, Max and Avg of the Processing time it should ignore instances when a row has Pay AND the Total Check Amount is less than 250000 only. Therefore, it should find the count, min, max and avg based on the remainder.

Please please help, this is urgent.

thanks you so much

sample.xlsxHi there!

I am working with a colleague who doesn't have access to Excel 2007, so I can't use Sumif's to solve this, and I can't add a subtotal line (long story) to the data, which has me stumped on how, then, I can solve this problem. With the data below, I want a sum of lines 1-6 if the date at the top of the first table is the same as the date at the bottom section's month end date. (In other words, in the lower part of the data, under 9/30/11, it should equal $20k and in the part where it says 10/31/11 it should also equal $20k.

I've tried doing a sum if, but it will only sum the first row (line 1). I've also tried to do sumproduct or some sort of index/match combo, but can't quite get there. Any ideas? (Also, if it helps for indexing or something like that, I could change the "Line 1, Line 2, etc" to all just say "Line" (I think this would help if i could do SumifS, but alas I can't).

I REALLY appreciate any help!!!

Thank you, thank you!

(ps - I tried to attach a sample file, too - maybe that worked?)

9/30/2011 10/31/2011 11/30/2011 Line 1 $ 10,000.00 $ 15,000.00 Line 2 $ 80,000.00 Line 3 $ 20,000.00 Line 4 $ 10,000.00 Line 5 Line 6 Week 9/9/2011 9/16/2011 9/23/2011 9/30/2011 10/7/2011 10/14/2011 10/21/2011 10/28/2011 Month 9/30/2011 9/30/2011 9/30/2011 9/30/2011 10/31/2011 10/31/2011 10/31/2011 10/31/2011 End of Month? Yes Yes End of Month - date 9/30/2011 10/31/2011 Sum of lines 1 -6 $ - $ - $ - $ - $ - $ - $ - $ 10,000.00

I am looking at column 9 ("I") and 16 ("P"). I went to add +1 to the value in column P in case I have either "OGBL", "OGBM" or "OGBL" in the corresponding row in column P. Otherwise I want to keep everything intact. Please note that some values in column I are "#N/A". I wrote the following loop for this, however, it gives me error. Any suggestions regarding this would be appreciated:


	VB:
	
 
Sub hey() 
     
    Dim LastRow As Integer 
    With Workbooks(ReportBook).Sheets(ReportSheet) 
        LastRow = .Range("g65536").End(xlUp).Row 
         
        On Error Resume Next 
         
        For n = 2 To LastRow 
             
            ticker = Workbooks(ReportBook).Sheets(ReportSheet).Cells(n, 9) 
             
             
            Select Case ticker 
            Case ticker = "OGBS" 
                .Cells(n, 16).Value = .Cells(n, 16).Value + 1 
            Case ticker = "OGBM" 
                .Cells(n, 16).Value = .Cells(n, 16).Value + 1 
            Case ticker = "OGBL" 
                .Cells(n, 16).Value = .Cells(n, 16).Value + 1 
            End Select 
             '
        Next n 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


First, I hope the title of the thread is properly descriptive.

Okay, here is the problem I'm trying to solve.

I have a large worksheet that lists employees and the hours they work each day in a pay period.

For Example:

Column A holds a date and Column E holds the number of hours worked for a paytype. So i want to create some function that sums the numbers in column E, but only those where column A holds a date.

Then i want to return that number to a cell, say C20. I think I've managed to tell the macro which rows i want to return values from, but I can't figure out how to say "add these numbers together and put the total in C20."

I'll check this frequently if clarification is needed.

Oh, and help with this would be pretty much priceless to me at this point, but I am willing to pay a reasonable amount for help with this. (I don't make a lot of money, so I can't say oh, I'll pay 80 bucks an hour - but you know, maybe a flat 50 or something.)

Hi,

I have a piece of code that put a check in all checkbox
in column B from row 5 to row 50 but in column C, I have data
from row 5 to 38. I want the macro to stop at row 38 in column B.
When the cell in column C is empty stop putting checks in column B.

How can I make this macro Check all checkbox down column B and
stop when column C is empty..

How do I add a loop to stop when the cell in column C is empty?


	VB:
	
 CommandButton1_Click() 
     
    Dim CB As Variant 
    For Each CB In ActiveSheet.CheckBoxes 
        CB.Value = False 
    Next 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
.....A.......B............C...............
....1S......X............Ms..............
....1M......X...........Mr. &. Mrs....
....1S......X............Ms..............
....1S......X............Ms..............

....1S......X............Ms..............
....1S......X............Ms..............
....1S......X............Ms..............
....1S......X............Ms..............
....1S......X............ ..............
....1S......X............ ..............

I have a folder which has 200 files. I have extracted data from these files based on autofilter criteria. But there are many duplicate records extracted for the criteria. I need only unique records . Below are the codes. Where to I add the criteria for search records:

	VB:
	
 ExampleSearch() 
     'Note: This example use the function LastRow
    Dim basebook As Workbook 
    Dim mybook As Workbook 
    Dim rng As Range 
    Dim rnum As Long 
    Dim mnum As Range 
    Dim FNames As String 
    Dim MyPath As String 
    Dim SaveDriveDir As String 
     
    SaveDriveDir = CurDir 
    MyPath = "C:Data" '

I am not sure my title is accurate so I will explain what I am trying to do.

I would like to total the numbers from each row which has "Monthly Totals" in column A. There are currently only two of these but there will be more added over the year. Rather then have to edit the formula to include the new cell I want added to the total, is there a way to create a formula which will automatically scan Column A for "Monthly Total" then add the cell from that row to the running total at the top?

Basically, If any cell in Column A has "Monthly Total" add the cell from column B (from the same row) to the running total in cell B1. This way I could add as many "Monthly Totals" as needed and I wouldn't have to edit the formula in cell B1.

Any help would be much appreciated.

-J

I'm creating a large table with call center data which will have data added daily. In the macro that moves some of the data where it needs to be, I need to include an error check based on two criteria to prevent adding data that already exists.

Two criteria must be used because separately they are not unique. They are: CallType and Date.

I can get so far as to set variables named: CallTypeV and DateV to the values being added (data is copied from the call center switch and pasted to the spreadsheet). What I can't figure is how to search for those two variables displayed on the same row in the existing table. In other words, I want to exit the macro (which I know how to do) if the macro finds DateV in column A and CallTypeV in column G - both in the same row.

I think I can concatenate the two values in an extra column in the previous table and search for an exact match that way, but I prefer to not add an extra column if possible. As it is, I'm already anticipating 17 columns and over 30,000 rows.

Any suggestions?

(repost from: http://www.ozgrid.com/forum/showthread.php?t=43149
I reposted because originally there were errors and to make the question easier to understand. Moderator: Please let me know if this is a violation of terms; feel free to delete the previous post).

Hey there. I posted a counter/ looping question a couple days ago. Despite a couple of very well-crafted (and very prompt) responses, none of them work! Maybe we have a programmer's challenge shaping up here! I copy/ pasted my original post below. The title of it is "Newbie Looping Question."

If it matters, the symbols being looped/ checked are imported from a financial data site. I do little formatting on them. Could it be a formatting issue? A couple of " " I'm not seeing, etc?

Here's the original:

Hey all. I have a looping dilemma. I update daily stock information to a table. Column A is the symbol. There are repeats of the symbol, which is what I'm looking for to start running functions/ alerts on them based on criteria etc. Anyway. I'm trying to loop through to look for new instances of symbol values, or, values in column A. What I've tried to do so far experimentally is to get a symbol value to be counted from an input box, then find a matching value in the range. It's below, not working:

Sub NewSymbolAdd()

'This is the macro that will loop through the symbols column of the master list to see if the new data downloads are new/
'need to be set up to download daily pricing data or ignore if that sheet already exists.

Dim symb As String
Dim finalrow As Long
Dim count As Long
Dim countsymb As Long

'Find the range size.

Worksheets("Watch List Main").Activate
finalrow = Cells(Rows.count, 1).End(xlUp).Row

'Loop the range counting the number of times the symb value is encountered.

symb = InputBox("Please enter a symbol to count:")

countsymb = 0

For count = 8 To finalrow
If Cells(count, 1).Value = symb Then
countsymb = countsymb + 1
End If
Next count

MsgBox countsymb

'If the count =0 then add a worksheet with the symbol's name

End Sub

Any ideas?

Thanks in advance... go Phillies! :P

Hey all. I have a looping dilemma. I update daily stock information to a table. Column A is the symbol. There are repeats of the symbol, which is what I'm looking for to start running functions/ alerts on them based on criteria etc. Anyway. I'm trying to loop through to look for new instances of symbol values, or, values in column A. What I've tried to do so far experimentally is to get a symbol value to be counted from an input box, then find a matching value in the range. It's below, not working:

Sub NewSymbolAdd()

'This is the macro that will loop through the symbols column of the master list to see if the new data downloads are new/
'need to be set up to download daily pricing data or ignore if that sheet already exists.

Dim symb As String
Dim finalrow As Long
Dim count As Long
Dim countsymb As Long

'Find the range size.

Worksheets("Watch List Main").Activate
finalrow = Cells(Rows.count, 1).End(xlUp).Row

'Loop the range counting the number of times the symb value is encountered.

symb = InputBox("Please enter a symbol to count:")

countsymb = 0

For count = 8 To finalrow
If Cells(count, 1).Value = symb Then
countsymb = countsymb + 1
End If
Next count

MsgBox countsymb

'If the count =0 then add a worksheet with the symbol's name

End Sub

Any ideas?

One of you helped me with the following Macro. It takes a Worksheet (Combined) and distributes it's rows to newly created spreadsheets based on the data in column (B).

As you can see, it also performs some basic fomula calcs on each new sheet.

Sub DistributeRowsMA()
Dim wsAll As Worksheet
Dim wsCrit As Worksheet
Dim wsNew As Worksheet
Dim LastRow As Long
Dim LastRowCrit As Long
Dim i As Long

Set wsAll = Worksheets("Combined") ' change All to the name of the worksheet the existing data is on

LastRow = wsAll.Range("A" & Rows.Count).End(xlUp).Row

Set wsCrit = Worksheets.Add

' column B has the criteria eg project ref
wsAll.Range("B1:B" & LastRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wsCrit.Range("A1"), Unique:=True

LastRowCrit = wsCrit.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRowCrit

Set wsNew = Worksheets.Add
wsNew.Name = wsCrit.Range("A2")
wsAll.Rows("1:" & LastRow).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=wsCrit.Range("A1:A2"), _
CopyToRange:=wsNew.Range("A1"), Unique:=False
wsCrit.Rows(2).Delete

With wsNew
.Range("L2").Resize(.UsedRange.Rows.Count - 1).FormulaR1C1 = "=SUM(RC[-5]:RC[-1])"
.Range("N2").Resize(.UsedRange.Rows.Count - 1).FormulaR1C1 = "=(RC[-2])-(RC[-8])"
End With

Next i

Application.DisplayAlerts = False
wsCrit.Delete
Application.DisplayAlerts = True

End Sub

Here is a partial from the worksheet that is distributed.....

Combined

ABCD1TerrIDMACustIDCust Name2SACABLA 334508ABUELITA ROSAS MEX. REST. +++3SACABLA 042218VAN'S PIG STAND #1 ++4SACABLA 223289THE GOLDEN RULE HOME, INC. ++5SACABLA 417998RAINBOW INN OF OKLAHOMA ++6SACABLA 328583KICKAPOO CASINO +++7SACABLA 449769KICKAPOO COMMUNITY CHILD CAR++8SACABLA 472563MCLOUD CAFE ++9SACABLA 163949SUNRISE REST. ++

Now I would like the macro to look at another sheet to verify the existance of customers. The new code will ONLY distribute the rows, if the customer can be found on the Worksheet (Cust Type).

Here is an example from (Cust Type)
Sheet1

AB1Cust #Customer Name210496Adair Central Kitchen310538Advance Food Company ++411106Anadarko Public Schools +511726Integris Bass Bapt Ctr +++611866Jim'S Rest ++711973Old Germany Rest +812153Dunn'S Food Center912468Blue Moon ++1012534Bob Davis Fish Mkt ++1112682Boulevard Cafeteria +1213110Chi Omega ++1313300Brothers Rest +

Any help would be appreciated....

Dale

I am using the following code to distribute rows on a master sheet to spawn new sheets based on the values on the sheet. I've added a column (N) that totals 5 previous columns of data. I need the new sheets to spawn with the (SUM) formula intact....No idea.

Code:
Sub DistributeRows()
Dim wsAll As Worksheet
Dim wsCrit As Worksheet
Dim wsNew As Worksheet
Dim LastRow As Long
Dim LastRowCrit As Long
Dim i As Long
    
    Set wsAll = Worksheets("Combined") ' change All to the name of the worksheet the existing data is on
    
    LastRow = wsAll.Range("A" & Rows.Count).End(xlUp).Row
    
    Set wsCrit = Worksheets.Add
    
    ' column B has the criteria eg project ref
    wsAll.Range("C1:C" & LastRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wsCrit.Range("A1"), Unique:=True
    
    LastRowCrit = wsCrit.Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LastRowCrit
    
        Set wsNew = Worksheets.Add
        wsNew.Name = wsCrit.Range("A2")
        wsAll.Rows("1:" & LastRow).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=wsCrit.Range("A1:A2"), _
         CopyToRange:=wsNew.Range("A1"), Unique:=False
        wsCrit.Rows(2).Delete
        
    Next i
    
    Application.DisplayAlerts = False
    wsCrit.Delete
    Application.DisplayAlerts = True
    
End Sub


Hi, i've been struggling finding the right code to make a query and Filter Data based on a ranged cells from a different sheet. Someone has gave me this code to work on but sadly, it did not work.

Dim vArr As Variant, rng As Range, strCrit As String
With Sheets("Criteria") 'amend as appropriate
Set rng = .Range("A1:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With
vArr = Application.WorksheetFunction.Transpose(rng.Value) 'populate your array
strCrit = "'" & Join(vArr, "','") & "'" 'create string of ID numbers surrounded by single quotes and separated by commas
'strCrit = "'070001,'080001'" this is the criteria data

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"ODBC;DSN=MS Access Database;DBQ=D:DATA.accdb;DefaultDir=D:;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
, Destination:=Range("Materials!$A$1")).QueryTable
.CommandText = Array( _
"SELECT Materials.PART_ID, Materials.PIECE_NO, Materials.RESOURCE_ID, Materials.SUBORD_PART_ID, Materials.DESCRIPTION, Materials.QTY_PER, Materials.USAGE_UM, Materials.UNIT_MAT_COST, Materials.EST_MAT_" _
, _
"COST" & Chr(13) & "" & Chr(10) & "FROM `D:DATA.accdb`.Materials Materials" & Chr(13) & "" & Chr(10) & "WHERE (Materials.PART_ID IN(" & strCrit & ")" _
)

This portion: "WHERE (Materials.PART_ID IN(" & strCrit & ")" does not seem to work because ms query will only allow, correct me if i am wrong, string like this: "WHERE (Materials.PART_ID='070001') OR (Materials.PART_ID='080001')"

Please help me on how to make this work.

I have two workbooks. I use excel 2003

I need to update the master workbook with data from the invoices workbook using the following criteria.

In the master workbook column C has unique job numbers.
In the invoice workbook column F has unique job numbers.
Both worksheets are the first worksheet in both workbooks.

If the job number in the invoice workbook is already in the master workbook do nothing.

If the job number is in the master workbook but not the invoice workbook then delete the entire row from the master workbook.

If the job number is not in the master workbook add it to the master workbook.

To complicate matters the master workbook is shared. It is my understanding that shared workbooks can't contain the macros needed ? It would be preferable that the master sheet had them but if not ... oh well.

Any advice very much appreciated !

Hi

I would like to sum the contents of column C based on criteria in cols A & B.

e.g. If for any row from row 2 until row 1000 Col A = Red and Col B = Blue add up the numbers in Col C.

Can you help?

Thanks

Ben

I am trying to past value a number from one worksheet to another but I need it done in a particular column that matches a value on the other worksheet. See below:

******** ******************** ************************************************************************>Microsoft Excel - Company Cash Flow Templete.xls___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutC7C9=
BCDE7Period Ending7 8 9 Total Revenue 143.00 Entry Calc Sheet
[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.

******** ******************** ************************************************************************>Microsoft Excel - Company Cash Flow Templete.xls___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutD6E6F6G6H6I6J6K6L6M6N6O6D7D8E8F8G8H8I8J8K8L8M8N8O8B11=
BCDEFGHIJKLMNO6 1 2 3 4 5 6 7 8 9 10 11 12 7 2006 8 1200622006320064200652006620067200682006920061020061120061220069 1 2 3 4 5 6 7 8 9 10 11 12 10For Period Ending: January February March April May June July August September October November December 11July 2006 12 13Inflows 14Revenues 143 Cash Flow
[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.

I need to copy the revenue number on the first example to the appropriate cell in the second example. I am using a date code, in this example it is 7. The date code determines where the revenue value should be pasted; it will always be in row 14. The column will be the only part that needs to be matched up with the period ending code on the first page. I have tried to record a macro and then add a match formula, a vlookup, and an index offset. None of them worked, as a matter of fact they always came up with a compiling error. I have been using Bill Jelen's VBA book but it does not have anything even remotly related and my VBA skills are really basic.
Any help or guidence would be greatly appreciated.
Thanks

Firstly.. hi and thank you for reading this post.

Elements in this equation..
Source Sheet containing a list of equipment and values related to it.
Destination sheet that i want to pull data into and add the data pulled from source.
Destination sheet has a selection cell(X) that has a validation list containing the complete discriptions colomb a of equipment discriptions in source sheet.
Destination range data, this is the area that data will be pulled in from source sheet to destination sheet based on criteria selected in cell X. This will contain Equipment sourced from in colom a and values sourced from colomb E

Below the selection cell (X) i want to pull in the discriptions and values into the (Destination Range data) based on what item I selected in the validation list. Cell (X)

So ..as i said I have a source sheet containing discriptions and values of different costs witin the business. Discriptions in colomb a and Values in E
There are many costs with the same discpn but have different cost values.
Like egines , wheels, fuel, but each one has a different cost value to it

then i want change a cell(X) in destination sheet that cotain the equipment list, I want it to look for all values that has that discription and return all elements value that is = to that discrip tion along with its value in colom E. as an extraction. And place it in destination range in the destination sheet.

So i figure it will look something like this...
If Cell X = Engines then do a vlookup for Engines in source sheet and return in destination cheet and pull in the value associated with it in theat row...

the result can either be a one row liner that returns Engines with a sum of all the engines or a multi row formula that reurns each one of the vlookups as a single value. you will then have multiple rows with all engines there are 5 rows ir there was only 5 engines.

So the furmula will be in the destination data range and i should then just copy the formula down to make sure that it can facilitate a large return of equipment ...

If anyone can help me with this i will REEEEEAAAALy appreciate it... I am almost tempted to pay for this help.. .

Thanks
CDF..
I will crown you EXCEL GURU GOD if anyone can solve this.


No luck finding an answer? You could always try Google.