Free Microsoft Excel 2013 Quick Reference

Extract info from one sheet to another Results

Hi guys - hope you can help me here. I'm moderately proficient at Excel but this has stumped me so far...

I've just been posted to a new team in work who deal with sections of the accounts, namely method of receipts of payments. The main spreadsheet is organised as follows:

- two seperate strands of the business each have their own sheet
- the two sheets are designed in the same format
- across the top we have column headers relating to time periods (more to follow), and then the different streams of business. The time periods consist of seperate columns for financial year, month, and quarter
- for each month of the year, a 'template' is copied and pasted into the spreadsheet from another worksheet, containing all the entry items, eg Direct Debit, BACS etc. These are pasted in the first blank row available. Then the data from invoices etc is entered.
- there is a unit charge and total cost at the end of the row

Now this copy and paste has evolved over the 3 years this sheet has been in operation. It was originally 6 rows big, at one point was as large as 12, is currently 9. The variations come from changes in the way payments are handled.

Anyway, this system works and so they've all resolved to keep it as the inputters are heavily resistant to change

They've asked me to develop a sort of reporting spreadsheet that will allow them to track YTD performance against budget, previous years etc. Previously, the way they did this was to use filters to get down to the info they needed, and then create a new spreadsheet from that - there are hundreds of reports on the shared drive created in this way.

What I've set up so far is a series of tabs each relating to a financial year (Apr-Mar). Now I want to extract all this info from the 2 worksheets. I've set up a model template for each year, which contains all the items that exist, for every month.

Now obviously I could manually go through and extract this info, but I want to build a system robust enough to handle what happens in the future also, i.e. future months' entries. The problem is, I don't know where these entries will be as the size of the pasted element changes.

So far, I've been playing around with array entering the formula using SUM, IF and AND. I had some success in 'single row' entry, but once I try to get it working for an array, I hit problems.

Let me try to explain what I have tried to do.

Column A contains the names of the individual transactions. Columns B-D contain the data Month, Qtr and Financial Year. E-J contain the seperate accounts. Rows 11-82 contain all the transaction types (12 sets of identical data).

In my new sheet, in E11, I tried the following:

=Sum(If ( And ( A11:A82 = a11:a82, B11:B82 = b11:b82, C11:C82 = c11:c82, D11:D82 = d11:d82), E11:E82,0))

This is array entered into e11:e82. (For ease of demonstration, I've used capital letters to refer to cells in the original data entry sheet, and small letters for my own consolidation sheet)

I thought this would have the following effect - it would check whether the values in the data entry sheet match those in the consolidation sheet for each row in columns A:D. If it did, then it would post to that row in column E, whatever is in that row in the original sheet. So for example, if the row in the original sheet was for BACS entries, from April, of Quarter 1, of FY 05-06, then it would copy the value across. If not, it would enter 0.

However this comes up with an error (non-specific error).

So could anybody please suggest an alternative method of doing this.

I realise my explanation might be hard to follow so happy to provide the sheet if required...

Thanks in advance! :-)

Below is some code I've been working on.
I've underlined the line on which I'm stuck.

Public Function P_refix(ByVal Vnum As String)
P_refix = ""
For J = 1 To Len(Vnum)
    If Asc(Mid(Vnum, J, 1)) >= 65 Or Asc(Mid(Vnum, J, 1)) = 48 Then
        P_refix = P_refix & Mid(Vnum, J, 1)
    Else
        Exit For
    End If
Next J
End Function
Sub PVRounded()

Dim wk1 As Worksheet, Wk4 As Worksheet
Dim C_ell As Range
Dim I As Integer, Num1

Set wk1 = Sheets("Vouchers_Input")
Set Wk4 = Sheets("Vouchers_Rounded")

'Clear content of previous operation
Wk4.Select
Range("F9:iv65000").ClearContents

'test
Dim E As Range, F As Integer, G As Range, H As Integer
wk1.Select
Set E = Range("F10", Cells(10, 6).End(xlToRight))
Set G = Range("F10", Cells(65000, 6).End(xlUp))
F = WorksheetFunction.CountA(E) - 1
H = WorksheetFunction.CountA(G) - 1

'extracts repeated voucher numbers
wk1.Select
Range("F10").Select
I = 1
For Each C_ell In Range("G11", Cells(65000, 7).End(xlUp))
    Num1 = Val(Right(C_ell, 3))
        If Num1 = 0 Then
            If Num1 = 0 Then
                Wk4.Range("F9").Offset(I, 0) = C_ell.Offset(0, -1)
            Else
            End If
            If Wk4.Range("f9").Offset(I, 0) > 1 Then
Wk4.Range("f9").Offset(I, 0).Offset(0, 1).Resize(1, F) = C_ell
            Else
            End If
        I = I + 1
        End If
    last_cell = C_ell
Next C_ell
'extracts heading for voucher numbers
Wk4.Range("f9") = wk1.Range("f10")
'identifies no repeating voucher numbers
    If Wk4.Range("F10") < 1 Then
        Wk4.Range("f9").FormulaR1C1 = "There Are No Rounded " & wk1.Range("g10") &
"s"
    End If
    Wk4.Select
End Sub
Right now, I've got this macro copying a cell, based on a condition, from one cell, into wk4.
However. I don't just want that cell, but all of the info in that cell's row.
Right now, I can paste into the desired cells, but I can't seem to copy the content of the desired multiple cells into those new cells. (I have "C_ell" on that side of the equation right now just as a placeholder).
Argh, this is rather difficult to describe in words.
I want to paste into the cells the content of C_ell.resize(0, F).
The problem is that every time I try to tell the equation to = multiple cells, it sends an error message, or runs the macro without pasting anything in the desired cells.

Can someone please help?

Range("B7").Select
ActiveCell.FormulaR1C1 = "=[454679845456.xls]Promoforma!R6C5"

This is my current formula in VB where I am trying to create a formula in one file linking to another. In the above, the fomula is extracting info from the 454679845456.xls file into a summary book. Is there a way that I can have the "454679845456.xls" portion of the formula as a variable? Possibly pulling from whatever other file is open at the time as it won't always be called 454679845456.xls.

I hope this makes sense.

At the moment i have a macro set-up where one file is 'saved as' based on a number inputed in one box. in the above, they have have inputted the number 454679845456. the next step i am trying to achieve is to have this number and the data on this new sheet exported to a summary page. at the moment my formula will keep pulling from the 454679845456.xls file as i don't know how to have this as a variable; pulling in the data from the current open book might be an option but if they more than one file open then this may not work.

appreciate any assistance you can give.

Hi,

Any help/advice would be greatly appreciated. I am fairly new to Excel, and only have experience of basic formatting and formulas.

I have two existing spreadsheets that seem to me to be incredibly inefficient and hard to update. I would like to overhaul them!

I have attached a sample of each. It seems to me that all the data can/should exist in one spreadsheet. I currently have to manually transfer data from one to the other in order to keep them updated.

Spreadsheet 1 currently contains the following information:

Customer name, customer type, weekly sales, ytd sales.

Spreadsheet 2 has been designed to compare and monitor the sales of customers within groups. (i.e a restaurant chain).

--------

If I add a column in spreadsheet 1 with the name of the group then surely I can create a filter that displays just the groups sales on another page? I still need to be able to compare this years sales to previous years (as seen in spreadsheet 2)

--------

I also need to be able to easily add new customers.

--------

So:

I need to setup/design a new spreadsheet with the following information:

Customer name, customer type, customer group code, weekly sales for entire year.

The data will be added on a weekly basis, which will include the addition of new customers. Obviously there will be many columns (52 for each weeks sales, plus extras for monthly and yearly totals, plus customer info).

From the data I will want to extract and display the following information in another sheet/s:

Sales data by customer type, sales data by group (i.e restaurants owned by same company), top 10 customers, top 10 customers by type. Plus an easy to read comparison to previous years sales.

Any advice greatly appreciated!

I hope I have explained it well enough...

Problem: Extract data from 12 separate workbooks and consolidate it new workbook.

Info: I have 36 workbooks 12 x 3 years, Jan, Feb, Mar ect... each workbook has 31 tabs representing days of the month and are labeled 1,2,3, ... 31, there is a list of names in column A, and column D has total gallons for that day same row. This is a running totals SS, it totals number of Gallons produced for each barrel for that day. But it only totals the gallons for all of the barrels for that day, the person who built this never put in formulas to total the gallons for each individual barrel for the month. To complicate things the list of names changes some names have been discontinued and new names added so the list varies in range one day it is line 5 - line 83, two days later it is line 5 - line 87, they are not in alphabetical order. I offered two years ago to rebuild these SS so that we had total gallons for each individual barrel for the month and the year; I was told we don't need that info waste of time. So today upper management calls down needs total gallons for ea barrel for past three years da!

What do I want to do: I want to open new Workbook and extract the needed data to it.

Obviously I can go to each month and build a formula the returns total for a certain barrel for that month and then copy that formula down the page but the problem with that was that the barrel names never stay in same order from month to month as too many people have messed with these sheets, what this causes is that my formula might pull data for another barrel so the data would not be correct. I thought about just copying all the names from each day for three years and comparing them, to find all the names that have been used. Then make copy of each workbook so that I am not working with originals, then I would have to sort each day for three years so that all the sheets were the same order but then I just got lost.... I am not sure how to approach this, I can use VLOOKUP type functions and logical functions and I can nest, I know how to paste in script but I can't write it.... does anyone have an idea of how I should go about doing this.... this is not even my job, they just come to me when they get in trouble and have no idea what to do.... they have started compiling the data manually but it will take over month to do this and of course management wants it ASAP. I want to do this just because I want to learn, any ideas as to best way to get what is needed? By the way when I seek help like this I never claim the work is mine just that I know where to get help, maybe they will listen to me next time.

I have attached an example workbook

Thanks
Ken

I have a sheet exported from ACT that we need to get information from into
another Agency management system.
The sheet has columns that include customer name, contact info, and notes.
The number of notes (rows) is different with each customer. What I would
like to do is extract notes for a specific customer, and create a new
workbook with it.
I believe there might be a way if I subtotal on each change in company, but
I can't find it. Any help is GREATLY appreciated.
See example below -
Before -
Date |Company |Notes
01/01/2004| #1 |Steve is a great guy
01/10/2005| #1 |He Really is
01/25/2006| #2 |Any help or advice
01/30/2006| #2 |would be great
02/25/2006| #2 |Thanks!

So from that I would like to be able to automatically create 2 seperate
workbooks, one named #1 with the first 2 rows (Not including header), and one
named #2 with the last 3.
Thanks in advance -
-Shane

Good afternoon

I have a sheet that is formatted for the benefit of the planning team where i work, unfortunately the format only is of use to them and does not seem very easy to extract my relevant data.

I believe the best way to do this is to utilse some form of VLookup which will sort the information into another table set out for my requirements, However I am at a loss as to where to start.
Basically I need to extract the information so it shows me the quality code and the total number of times each quality is to be made 1) by day and 2) as a total for the week.

Ultimately I am trying to get 4 lists into 1 requirement list by day and by week
The reason I need this data is so i can then utilise it as a reference list for working out total requirement based on qualities made per day, therefore i would have to set the sorted list in such a way that i can use it alongside 2 other lists.

I really am not explaining this well at all sorry

I have attached 2 spreadsheets the one titled planning is the list that i am requiring help sorting the other one shows all the list I will be trying to use to generate my total requirement needs. Each list will be manually imported into excel each week for now.

In the planning list the info i need is in column C (for sunday) =quality code And Column b equals number of times that quality is to be made, its columns f & e for monday, columns I & H for tuesday etc etc

I just want to be able to download the 3 lists and from that a fourth list is created that tells me how many times each day a certain type of scrap is required.

Anyway 1 step at a time

All help as ever is greatly appreciated however i have a feeeling there will be more questions to be answered before anyone can help me with this.

Thank you in anticipation

2nd spreadsheet to big to upload at the minute sorry

Damion

Hi All,

I have excel userform which extracts data from a sql server database on basis of the parameters selected by users on the userform.

I have a Combobox on my userform .i.e. Combobox6 now this combobox gets populated with product codes Now I don't want to give access of all products to all users so what I am trying to do is I have sql table in my sql database .i.e AuthorizedUserList which contains 3 columns .i.e one is XPUserID, second is Name of the User and third is Product now I want to incorporate a line of code in my below commandbutton event which will check the product to which user has the access and only then it will allow him to extract the data for the same otherwise it will show a message that he doesn't have access to the Product which he has selected in Combobox6. Now what my macro will do is that it will get the windows xp user id of the user and on the basis of that it will get the product which is updated against same xpuser id in my "AuthorizedUserlist" table and then accordingly it allow user to extract the data. I have tried to write something from my end in the below commandbutton event but it doesn't work, I have highlighted the same in Red. Please help...

Private Sub CommandButton5_Click()

'Selection String for Sub Product UBR Code
Dim selection As String
Dim lItem As Long
For lItem = 0 To ListBox4.ListCount - 1
If ListBox4.Selected(lItem) = True Then
selection = selection & "'" & Replace(Left(ListBox4.List(lItem), 6), "'", "''") &
"',"
End If
Next
selection = Mid(selection, 1, Len(selection) - 1)

'Selection String For Country
Dim selection1 As String
Dim lItem1 As Long
For lItem1 = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(lItem1) = True Then
selection1 = selection1 & "'" & Replace(ListBox1.List(lItem1), "'", "''") &
"',"
End If
Next
selection1 = Mid(selection1, 1, Len(selection1) - 1)


Dim selection2 As String
Dim lItem2 As Long
For lItem2 = 0 To ListBox2.ListCount - 1
If ListBox2.Selected(lItem2) = True Then
selection2 = selection2 & "'" & Replace(Left(ListBox2.List(lItem2), 11), "'", "''")
& "',"
End If
Next
selection2 = Mid(selection2, 1, Len(selection2) - 1)
  
    ' Setup connection string
    Dim connStr As String
    Dim myservername As String
    Dim mydatabase As String
    Dim myuserid As String
    Dim mypasswd As String

myservername = ThisWorkbook.Sheets(1).Cells(1, 3).Value
mydatabase = ThisWorkbook.Sheets(1).Cells(1, 5).Value
myuserid = ThisWorkbook.Sheets(1).Cells(1, 1).Value
mypasswd = ThisWorkbook.Sheets(1).Cells(1, 2).Value
    connStr = "Provider=SQLOLEDB.1;DRIVER=SQL Native Client;Password=" & mypasswd & ";Persist Security
Info=false;User ID=" & myuserid & ";Initial Catalog=" & mydatabase & ";Data Source="
& myservername & ";"
Dim startdate As String
Dim enddate As String
Dim startdate1 As String
Dim enddate1 As String

startdate = Format(DTPicker1.Value, "MM/dd/yyyy")
enddate = Format(DTPicker3.Value, "MM/dd/yyyy")
startdate1 = Format(DTPicker4.Value, "MM/dd/yyyy")
enddate1 = Format(DTPicker5.Value, "MM/dd/yyyy")

   
    ' Setup the connection to the database
    Dim connection As ADODB.connection
    Set connection = New ADODB.connection
    connection.ConnectionString = connStr
    ' Open the connection
    connection.Open

    ' Open recordset.
    Set cmd1 = New ADODB.Command
   
    cmd1.ActiveConnection = connection
   
    Dim sSQL As String
    sSQL = "SELECT DISTINCT Product FROM Data_SAP.dbo.AuthorizedUserList WHERE AuthorizedUserList.XPUserID = '"
& Environ("Username") & "' AND AuthorizedUserList.Product = '" & Left(ComboBox6.Value, 6)
& "';"
    Debug.Print sSQL
    If sSQL <> Left(ComboBox6.Value, 6) Then
    Msgbox "You don't have access to selected product"
    Else
    Workbooks.Add
    If CheckBox5.Value = True And CheckBox6.Value = True And CheckBox7.Value = True Then
    cmd1.CommandText = "SELECT mydata.*, CRM.Country, CCM.[Sub Product UBR Code], CEM.FSI_LINE3_code FROM
Data_SAP.dbo.mydata mydata INNER JOIN Data_SAP.dbo.[Country_Region Mapping] CRM  ON (mydata.[Company Code] = CRM.[Company
Code])INNER JOIN Data_SAP.dbo.[Cost Center mapping] CCM  ON (mydata.[Cost Center] = CCM.[Cost Center])INNER JOIN
Data_SAP.dbo.[Cost Element Mapping] CEM  ON (mydata.[Unique Indentifier 1] = CEM.CE_SR_NO)WHERE CRM.Country IN (" &
selection1 & ") AND CCM.[Sub Product UBR Code] IN (" & selection & ") AND CEM.FSI_LINE3_code IN
(" & selection2 & ")AND mydata.year = '" & ComboBox4.Value & "' AND mydata.period =
'" & ComboBox3.Value & "'AND mydata.[Document Type]= '" & Left(ComboBox11.Value, 2) & "'
AND mydata.[Posting Date] between '" & startdate & "' AND '" & enddate & "'"
    ElseIf CheckBox5.Value = False Or CheckBox6.Value = False Or CheckBox7.Value = False Then
    cmd1.CommandText = "SELECT mydata.*, CRM.Country, CCM.[Sub Product UBR Code], CEM.FSI_LINE3_code FROM
Data_SAP.dbo.mydata mydata INNER JOIN Data_SAP.dbo.[Country_Region Mapping] CRM  ON (mydata.[Company Code] = CRM.[Company
Code])INNER JOIN Data_SAP.dbo.[Cost Center mapping] CCM  ON (mydata.[Cost Center] = CCM.[Cost Center])INNER JOIN
Data_SAP.dbo.[Cost Element Mapping] CEM  ON (mydata.[Unique Indentifier 1] = CEM.CE_SR_NO)WHERE CRM.Country IN (" &
selection1 & ") AND CCM.[Sub Product UBR Code] IN (" & selection & ") AND CEM.FSI_LINE3_code IN
(" & selection2 & ")AND mydata.year = '" & ComboBox4.Value & "' AND mydata.period between
'" & ComboBox2.Value & "' AND '" & ComboBox3.Value & "'"
    End If
    Debug.Print cmd1.CommandText
    Set Results = cmd1.Execute()

If Results.EOF Then
        ' Recordset is empty
        MsgBox "No Records Found"
        Debug.Print cmd1.CommandText
    Else

   

    ' Clear the data from the active worksheet
    Cells.Select
    Cells.ClearContents

    While Not Results.EOF

        ' Add column headers to the sheet
        headers = Results.Fields.Count
        For iCol = 1 To headers
           Cells(1, iCol).Value = Results.Fields(iCol - 1).Name
        Next
Dim MaxRows As Long
Dim ws As Worksheet
Set ws = ActiveSheet
MaxRows = ws.Rows.Count - 1
        ' Copy the resultset to the active worksheet
        'Cells(2, 1).CopyFromRecordset Results, 65536
        ws.Cells(2, 1).CopyFromRecordset Results, MaxRows
        'add another sheet if we're not at the end of the recordset
        If Not Results.EOF Then Set ws = ws.Parent.Worksheets.Add(After:=ws)
       
    Wend

End If
    ' Stop running the macro
MsgBox "Data Extraction Successfully Completed"
 
    Unload Me
End Sub
Thanks a lot for your help in advance.

Hi guys & gals,

On one sheet I have a list of shops (1 column) & the products they sell (other column):

grocer | fruit
grocer | vegetables
record store | CD
record store | records

On another sheet I "constructed" the ground plan of the mall, and I put a list-box from which the customer can choose the product. By doing this, the name of the stores (some products are sold in different shops) should change colour.

So far I have the initial list, and through some VB I can extract the correct info to a different table.

The problem now is: I need a formula to use in the conditional formatting to change the colour of the "selected" stores...

Is this clear enough? Thanks for any feedback!

Hi there,

I'm having an issue with a complex set of data and hope someone can help me.

I am working with a financials spreadsheet, which has three rows per project/work area (for OPEX, CAPEX and total spend) and three columns per month per project (budget, forecast, actuals). The sheet needs to cover the whole financial year, so there are 12 months - each divided into three. The sheet is quite complex due to the size and data import methods used, but I need to create a chart showing budget/forecast/actuals over time, per project (i.e. filter to show one project at a time).

I've attached a sample sheet, which shows 4 projects (in reality there are around 60). I realise that the spreadsheet might have been better in another format altogther but am loathe to go down that path due to the size and the way the data is pulled from our financial extracts each month.

Another complicating factor; the data needs to be showed cumulatively, i.e. August figures should actually be July + August.

Is anyone able to offer advice on how to produce a line chart which shows this info clearly?

Thanks so much,
Rosa

Hello All,
Used the forums several times for answers, but first post.

I am unable to figure this one out.

I have a multi sheet workbook. I am sorry I can't post due to info contained.

What I am attempting to do is in sheet 1 - I want to place a formula that will look to sheet 2 using a vlookup and a set array. It will look at column 5 in sheet 2 only if the lookup value is present AND the column 5 has a value.

If either the lookup value is NOT present, or even if it is.... that there is NO value in column 5 I want it to look at another "Sheet 3" to pull the value. The value on sheet 3 comes from a system extract and will always be present, but the value (whether lookup or Column 5) is an adjustment form that may or may not be done in any given month.

I seem to have found the formul but can't seem to finish it properly.

=IF(VLOOKUP($A13,'SHEET2'!$A$1:$H$33,5,FALSE),VLOOKUP($A13,'SHEET2'!$A$1:$H$33,5,FALSE),VLOOKUP(A13, SHEET3!A:AA,13,FALSE))

my problems are as follows:

When I leave the adjustment form empty of both the lookup value and the column 5 value I get an "N/A"

When I add the adjustment lookup value without the column 5 value, the formula produces the SHEET 3 value which is what I want it to do

However when I add the column 5 value of ZERO (which is all it would ever be if present) it still produces the SHEET 3 extract result EXCEPT FOR

When the adjustment value in column 5 is greater than ZERO

Essentially put I am trying to take the raw data as the monthly number unless I add an adjustment that is meant to force the number to ZERO (users is the column 13 in Sheet 3 and FORCE ZERO USERS is what the column 5 is)

Can anyone suggest either a better way or help me fix this I have wasted way to much time on this.

Thank you so much.

Hi,

I've two sets of data I need to compare - which I could work out how to do if they weren't formatted so strangely!

The data concerns product specifications and is copied from another program so is pasted into Excel and appears all in one column, eg:

FORD

Fiesta

Blue
Red
Yellow

FIAT

Uno

Green
Orange
Blue

Punto

Red
Yellow
Green

So, the Manufacturer is in Bold, the Model is a Hyperlink and the Values are in standard Ariel 10pt. Both lists arrive like this, all info in column A, on different sheets. But one list might have more Models on than the other (Hyperlinks) and values listed under that model.

It's the additional models I want to identify, and preferably what values are listed under them. (if the model is missing, the values are too).

I've tried various methods to reformat the data via macro so I can run a simple comparison but its proving really tricky. I think the best way would be to compare the hyperlinks, but I can't find a good way to a) extract and compare them on their own and b) then retrieve the values that go with them.

I can usually work out VBA problems with a few hints or suggestions so any help would be much appreciated.

Many thanks,

Alright, once again, I am having an issue with syntax, and can't quite get this right.

Basically I am wanting a listing of unique units that don't have a corresponding value in a table on another sheet. I have everything else being done, but can't get the unique list going through a VB Script...here is a little (or a Lot) of info for background....

I have a worksheet that gets populated by "units"/Codes (whatever) that are entered by a user in column C. When they are actually entering the values the worksheet does nothing significant. I have an "update" button at the top of the sheet that when clicked it starts a macro that fills in several other columns on the sheet according to what is entered in columns B & C (B contains quantity, C contains unit). One of the equations that is entered into a column is a lookup/match formula that returns the cost for the particular unit that is listed in column C. However, there are times when new units are entered into this column, but haven't been entered into the master listing. So obviously the lookup formula will return an error. I am trying to get a listing of all the UNIQUE units/codes to list in another column. I have been trying to do this with the countif statement but can only seem to get it to work using explicit cell references. But I can't use explicit cell references with the manner in which I am using it.

For Example...given the following sheet setup (not exactly like mine, but gets the point across):

.........A.................B...................C...................D................E............... ........F
........No...........Qty.................Unit.............Cost.............Total................Miss ing
.........1..............1.....................A1............
.........2..............1.....................AA1..........
.........3..............2.....................A2............
.........3..............5.....................AA1..........

If AA1 and A2 are missing costs and return an error in column D when I push the update button, I would like to have the following result

.........A.................B...................C...................D................E............... ........F
........No...........Qty.................Unit.............Cost.............Total................Miss ing
.........1..............1.....................A1................$5.00..........$5.00................ AA1
.........2..............1.....................AA1...............N/A.............$0.00................A2
.........3..............2.....................A2.................N/A.............$0.00................

If a unit is missing the cost I am only wanting it to be listed only 1 time. I figured that a countif statement would be more efficient than a for/if combo for the entire listing (the listing can get extremely lengthy) Also, there is also information above this listing (in other words "No." does not reside in cell A1, actually right now it is in A11)

I have a range defined so that the first cell of the range is C11 where my first unit is listed. I am then using a for loop to populate the remaining columns where there is value in Column C. This all works fine. I just can't get my unique values extracted from the listing in column C to put in my Missing column.

Here is my code...I have removed some of the big nasty equations that I am entering into some of the cells...they are irrelevent. The line that is highlighted in Red is the one that I have not been able to figure out. I have written so many different versions of it that I finally confused myself and gave up. Included is a hardcoded version just to show you what I am wanting to accomplish. I know this is quite a bit of info, but I figured the more the better. Thanks in advance for any help.

PS-Please go gentle on my sloppy & inefficient use of code below...I use what I can to get things accomplished....

Private Sub UpdatePriceButton_click()
On Error GoTo GetOut

Dim i As Integer
Dim j As Integer
Dim unit As Range
Dim CalcMode As Long

Application.EnableEvents = False

'disable autocalculation on sheet
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
End With

With ActiveSheet
rowtotal = .Range("h3").Value
lastrow = .Range("h2").Value
dif = .Range("h4").Value
Set unit = .Range("c11")
With unit
For i = 1 To rowtotal
If Not (unit.Offset(i - 1, 0).Value = "") Then 'if there is a value in column C
If .Offset(i - 1, -1).Value = "" Then 'If the quantity is blank
.Offset(i - 1, -1) = 1 'set it to be 1
End If '.offset(i-1,-1).Value = ""
.Offset(i - 1, dif) = "=indirect(""c[-8]"",0)"
.Offset(i - 1, dif - 1) = "=indirect(""c[-8]"",0)"
If Left(unit.Offset(i - 1, 0), 1) = Worksheets("Remove Price").Cells(2, 4) Or Left(unit.Offset(i - 1, 0), 1) = "i" Then 'Determine type of unit from prefix
'Big nasty eq goes here for column D
Else 'if unit doesn't have the I then it is assumed to be a install unit
If Left(unit.Offset(i - 1, 0), 1) = Worksheets("Install Price").Cells(2, 4) Or Left(unit.Offset(i - 1, 0), 1) = "n" Then 'install unit is preceeded with "N"
'Big nasty eq goes here for column D
Else 'If unit doesn't have an I or an N, then assumed to be an install unit
'Big Nasty Eq goes here for Column D
End If
End If
.Offset(i - 1, 2) = "=if(iserror(indirect(""c[-1]"",0)),0,indirect(""c[-1]"",0))" 'Column E
Else
.Offset(i - 1, -1) = ""
For j = 1 To 11
.Offset(i - 1, j) = ""
Next j
End If '(Not (unit.offset(i-1,0).Value = "")
Next i

'Turn auto calculate back on
Application.Calculation = xlCalculationAutomatic

Dim count1 As Integer
Dim count2 As Integer
count1 = 1
For i = 1 To rowtotal
If Not (unit.Offset(i - 1, 0).Value = "") Then 'If unit is not blank
If Not (.Offset(i - 1, 3) > 0) Then 'if the total cost is 0
'.Offset(i - 1, 20) = .Offset(i - 1, 0) 'list the unit in col W
'check to see if it is a unique unit
row1 = unit.Offset(i - 1, 0).Row
uniq1 = Evaluate("=CountIf(c11:c13, c13)")
'the above line is the one that I can not get to work properly with dynamic
'values. As I have it above it works. But that doesn't help me much.
'As the above statement is it would be used in row 13.
'Below is one of my attempts that didn't work.
' uniq1 = Application.CountIf(Worksheets("Budget").Range( _
indirect("C11", "C" & unit.Offset(i - 1, 0).Row - 1)), indirect("c[-1]"))
If uniq1 = 1 Then
'copy the value in C to the next available slot using count1 as a pointer
End If 'if it is a unique value that doesn't have a cost
End If 'if the total cost is 0
End If 'Not(unit.offset(i-1,0).value = "") then
Next i

End With
End With

'clear message to click update button
Set rng = ActiveSheet.Range("d6")
rng.ClearContents
Set rng = ActiveSheet.Range("l6")
rng.ClearContents

Application.EnableEvents = True
Exit Sub
GetOut:
Beep
Application.EnableEvents = True
Application.Calculation = CalcMode
MsgBox "error" & Err.Number & " " & Err.Description
End Sub

Greetings,

I've just started to work with Excel macros and my knowledge is extremely limited. I figured I'd come to the experts to learn a thing or two. Now, let me try to describe the problem at hand.

Problem 1: Auto Hide/Show Rows
Description: I have a part number in row 4, then nine blank rows underneath it, followed by another part number and another nine blank rows, rinse and repeat many times. I need these nine blank rows underneath because one of the column sections (AF:AJ) "In-Transit" may require anywhere from 1-10 rows to fill in the data. This changes daily and I copy/paste the data from a report on a program called SAP.

However, a majority of the time I only need 1-3 of those rows per part number. So I'll have the remainder of the ten blank rows just sitting there. I need to discover a way to Auto Hide rows that do not contain any data. Here is the twist however, I also need a way for Excel to know to UnHide those rows when my report grows larger.
Example: On Monday, my report has three rows of data. I copy/paste into Excel and by way of Macro the remaining 7 rows auto hide. On Tuesday, my report contains 8 rows of data. I copy it and attempt to paste it over the previous days data (which is currently only showing 3 rows). Excel recognizes I have additional data on the clipboard beyond the three rows and it UnHides 5 more rows and permits the full paste of data into the now showing 8 rows.

This may be beyond its capabilities. If so, I'll probably need to create a "Info Dump" sheet and paste the report in its entirety over there and have the summary sheet extract the data into it. However, I will still need to figure out an Auto Show/Hide Macro.

Problem 2: Pasting, ignoring locked cells.
Description: Because of these extra rows needed for my "In-Transit" column section, I have a lot of blank cells under the rest of the information that requires only the first row (inventory quantities being an example). The reason this is a problem functionally is because my SAP report for this information is presented, copied, and pasted in a sorted, one on top of the other manner. So the report displays every part number I have on my sheet and it's quantity one after the other. When I copy and past that information, it naturally pastes into the blank rows beneath it, when I want it to ignore those rows/cells and instead paste into the next part numbers row, so on and so forth. I have all those cells locked currently, but Excel will not permit you to paste while a cell is locked nor is there any practical way I can have those cells ignored during the paste.
Example: Row 4 is birds, followed by 9 blank rows. Row 13 is cats, followed by 9 blank rows. Row 22 is dogs, followed by 9 blank rows. My report shows the inventory of birds, cats, and dogs as 1, 2 , & 3 respectively. I copy the quantities (vertical column), and paste it in the appropriate inventory column starting with birds (lets say column D). It enters the data into Row 4, 5, and 6 in column D. I want it to enter the data into row 4, 13, and 22. Please be advised that this is y current problem. Presuming I find a method to auto hide/show rows as stated above, there may be any number of blank columns between the birds, cats, and dogs. It would change daily.

Hopefully I have been as clear as possible. If you have additional questions or concerns, I'm all ears and eyes. I appreciate any assistance you can provide me and am very grateful for it in advance.

Regards,
~Trez

Looking for a Formula - Help?? In sheet1, I have list of people information in one cell, but in different rows: A1 - ID#, name, country, gender (all in one cell). Another person's information in A2 cell, etc... In Sheet2, from A1 to A100, I have their ID#. What I have to do is - if the ID# in Sheet 2 matches the ID# in Sheet1, extract their information from Sheet1 and add in a new cell their information in Sheet2 next to the ID#. For example:
Sheet1
A1: 56-9870-9889 - John Deery - Argentina - Male
A2: 79-8455-9303 - Sue Manning - United States - Female

Sheet 2
A1: 79-8455-9303
Find this ID in Sheet 1, extract their information to B1 of Sheet 2 (don't need to separate their information in different cell; all info should come in one cell.

Answer should come like this:
B1: Sue Manning - United States - Female

Please help me find a formula.

Thank you.

Hi,
I am having difficulty getting a formula to extract a value from two different ranges. My data can only be pulled this way. Here is hat I am trying to do. I have named two ranges. Week (Column A to D) and Tech (column G to J) Each Column represents a week out of 4 weeks. In another sheet I have one value (Week number- column B). What I want the formula to lookup if A is "On-Call" and then lookup up B and search every name in my 'Tech" range that matches the week number from range "Week". It is difficult to explain. I have attached a spreadsheet. The formula result I want is in column F. (I hightlighted in sheet Data the cells I want the results from.) Let me know if you need more info.
Thank you


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