Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Look up based on column and row headings Results

Hello,

I am trying to write a "look up" that will return the cell value where the
row and column headings in the range match the row and column headings in
the source range.

So if I had the source range:

apples oranges pears
Jan 500 600 700
Feb 400 300 200
Mar

And formula range:

Jan Feb Mar
apples (formula here)
oranges
pears

Where it says "formula here", I want it to return 500.

Thanks in advance for any help on this.

--
AD108

Hi Guys,

Have attached an excel example of my query.

How do i look up based on both a row header and a column header to return a value that corresponds to those two from another sheet?

Thanks!

Regards
Lloyd

Hi All,

I need some help in determining how to go about getting information populated in a cell. In one column I have various codes for Instrument Types. In the next two columns I have a S&P rating and a Moody's Rating. I'm trying to populate a percentage amount in another column based on what is reported on in the first three columns.

The Criteria I have been given in layman's terms is as such:
If column A is either "ABS", "CMP","CMV","CRF","CRP", or "CRZ",
AND the corresponding cell in Column B has "AAA*","Aaa*","Aa*","A*","BBB*","Baa*" where the asterisk denotes wildcards,
AND the corresponding cell in Column C also has "AAA*","Aaa*","Aa*","A*","BBB*","Baa*",

Then the value in Column R is good and should not be altered,
Otherwise the value in Column R should be .15 .
This is somewhat related to my previous post below, but I was given additional criteria to work with and I can't get my head around it. http://www.ozgrid.com/forum/showthread.php?t=93124

Any help would be very appreciated.
Thanks!

JL

FYI....Here is the code I've been playing around with for the past couple of hours. Looks logical to me, but what do I know. I must be doing something horribly wrong as it's not coming up with my expected results.


	VB:
	
 MyTry() 
    Dim c As Range 
    For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row) 
        If c.Value = "ABS" Or c.Value = "CMP" Or c.Value = "CMV" Or c.Value = "CRF" Or c.Value = "CRP" Or c.Value = "CRZ" _ 
        And Left(c.Offset(0, 1), 1) = "A" Or Left(c.Offset(0, 1), 3) = "BBB" Or Left(c.Offset(0, 1), 3) = "Baa" _ 
        And Left(c.Offset(0, 2), 1) = "A" Or Left(c.Offset(0, 2), 3) = "BBB" Or Left(c.Offset(0, 2), 3) = "Baa" Then 
             'Do Nothing
Else: 
            c.Offset(0, 7) = 0.15 
        End If 
    Next c 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks again for taking the time to look at this.

JL

Hi,

I am trying to write a simple VBA code that loops through all the sheets in a workbook (There are nine) and then loops through each column in the workbook.

In the first row of each work sheet is a list of names
In the second row of each work sheet is a list of date.
These are both headings.

Then I have a list of dates in the first column and data for each of the headings in the other columns.

What I then want to do is, for each column, look up the date in the second row, return the row position of that date in the first column.

Then I want to clear the range, in each column , from the row returned to row 3. Each column has a different date.

This is the code so far:


	VB:
	
[FONT=Arial][SIZE=2][B]Sub  RemoveBF_2()[/B][/SIZE][/FONT] 
 
[B][FONT=Arial][SIZE=2]Dim  LastCol As Integer 
Dim StartDateRow As Integer 
Dim StartDate As  String[/SIZE][/FONT][/B] 
 
[FONT=Arial][SIZE=2][B]LastCol =  ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column[/B][/SIZE][/FONT]

 
 
[B][FONT=Arial][SIZE=2][COLOR=#0000ff] 'Loop through all  columns
[/COLOR]For c = 1 To LastCol 
 
StartDate =  Cells("R2" & "C" & c).Select 
 
For r = 3 To  5456[/SIZE][/FONT][/B] 
     
    [B][FONT=Arial][SIZE=2]    If Range("R" & r &  "C1").Value = StartDate Then 
        StartDateRow = r 
        End  If[/SIZE][/FONT][/B] 
         
        [B][FONT=Arial][SIZE=2]    Next  r[/SIZE][/FONT][/B] 
         
        [B][FONT=Arial][SIZE=2]Next c[/SIZE][/FONT][/B] 
         
        [B][FONT=Arial][SIZE=2]End Sub[/SIZE][/FONT][/B] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
But I can't go any further since this is giving me a Run-time error '13' Type Mismatch error.

Can anyone help?

Thanks

Assuming I have a table such as:

APPLE BEANS CELERY DOUGH
Will 5 10 15 2

Joe 1 3 7 11

Ben 9 6 4 8

What formula can I use to return a result like Celery by looking up Ben and 4 for example.

In other words, how do I lookup the heading of the column based on the the first column and a data point on the same row.

Thnaks

I'm wanting to use Excel to create a visual representation of what people I have working on what projects and for how long.

Note: The underscores below are there only to help me show a visual of the spacing and not intended to show up in the spreadsheet. I had posted this in another help forum and got a tip, but it didn't quite give me what I was looking for. So I'm going to recap here what I posted, what the help I received was, and hopefully a better explanation of what I'm looking for.

Name | Project | Start | End Date | cels I want to fill (1 week per "x" - header row 2)

John | Proj A | 1/1/06 | 2/28/06 _|__xxxxxxxx
John | Proj B | 1/15/06 | 2/28/06 |____xxxxxx
John | Proj C | 2/1/06 | 3/28/06 _|______xxxxxxxx
John | Proj D | 4/1/06 | 5/31/06 _|__________xxxxxxxx
Sally | Proj A | 1/1/06 | 2/28/06 _|__xxxxxxxx
Sally | Proj B | 1/15/06 | 2/28/06 |____xxxxxx
Sally | Proj C | 2/1/06 | 3/28/06 _|____xxxxxxxx
Sally | Proj D | 4/1/06 | 6/1/06 __|__________xxxxxxxx

So I hope the above formats well enough to give a rough illustration. I'd like to input a equations to the cels on the right that result in an "x" in each cel based on whether or not it falls between the start and end dates.

If I change the start or end date I'd like it to auto-fill the x's accordingly, so the result is a visual representation of usage of employees on given projects.

A slightly better illustration is below. I have the headers for the columns in month/weeks. Again the _____underscoring is for spacing here, but in the case of the _ _ _ _ below, each of those "_" represents a unique cel.

MONTHS___Jan-06|Feb-06|_Mar-06_|Apr-06| (header row1)
Weeks_____1 2 3 4 1 2 3 4 1 2 3 4 5 1 2 3 4 (header row 2)
cels______ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ (worksheet space)
cels______ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ (worksheet space)
cels______ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ (worksheet space)

Each "_" cel needs to know what month and week it is representing and determine whether or not it falls in between the start and end date.

Below is what someone posted in answer to my question on another forum:

----------------------
Assuming...
Name is Col A
Project is Col B
Start is Col C
End Date is Col D

In Col E...
="__"&REPT("_",WEEKNUM(C2)-1)&REPT("x",WEEKNUM(D2)-WEEKNUM(C2))

Format Font to 'Courier'.

Note: The Weeknum() function comes with the Analysis ToolPak which is
installed but not activated when Excel is installed. If "#NAME?" appears
when entering the formula above, the Analysis ToolPak needs to be activated.
Tools>Addins...
Put a checkmark in the Analysis ToolPak box and select OK.
-----------------------

What this gentelman gave me was a good idea, and kind of came close to what I was looking for. In fact, it displayed x's in much the same way as I show above in the first diagram. But it didn't quite do the trick in terms of formatting. However much I futzed with column width and font sizes, I could not get my x's to line up with my column headings for each week.

If you can imagine a grid just to the right of the start and end date, and each column in the grid represents a particular week. Each cel in the column, as I imagined it would work, should know what week in what month it is supposed to represent. I guess the first thing I need to do is figure out how to format those date fields (in the header row 2) to represent weeks? Ideally, I want to be able to see w1, w2, w3, w4, for a 4 week month, and w1-5 for a 5 week month, with x's below in the columns. But behind the w1 in each header cel, I need the spreadsheet to know that w1 is actually a date-specific week.

[I feel like I'm talking in circles here - I hope I'm making sense]

So I'm seeing the empty cels adjacent to the end dates as needing individual functions in each cel that somehow ref the week # from above. Essentially I want the funtion to do this: In cel such and such, If the week# from the header row 2 above the cel falls between start date and end date, then text = "x" in that cel. And I'd like that function in every blank cel.

I really appreciate whatever help you can give. And if it doesnt work, Ill look for an alternative.

Thanks!

Rick

I am new at this so bear with me as I try to explain what I am trying to do.

I have a workbook with approximately 20 worksheets of inventoried items. All have expiration dates for each item in column E. Instead of having to look through each worksheet every day, I want to add another worksheet called "Expired Items" and have anything that expires show up automatically on this one page with the approriate item under the appropriate heading (which I have made into a hyperlink so when the date appears I simply hit the link and go straight to the correct sheet.

sheet 1 is my "EXPIRED ITEMS" sheet.

sheet 2 is labeled "foods"
sheet 3 is labeled "Drinks"
sheet 4 is labeled "cans"

Sheet 1 cell A1 has a heading of "Foods"
A2
A3
A4
Sheet 1 cell A5 has a heading of "Drinks"
A6
A7
A8
Sheet 1 cell A9 has a heading of "Cans"
A10
A11
A12

There are 3 cells between these three different headings representing 3 different worksheets. I have 20 - 60 items in each worksheet that have the potential of expiring. Is there a way of inserting a row automatically under the correct heading with text saying "ORDER CODE XXXXXX" when an item expires, and still leaving the 3 blank rows to the next heading ? Right now I can am accomplishing this with the formula
=IF(FOODS!E15

Hi,

I am trying to work out a formula based on two column headings (Culvert width and culvert height). I have attached my spreadsheet for reference. I want to be able to enter the width and height in the yellow cells (G24 & G25) and return the corresponding value for a particular item (row) in another cell.
i.e.
Given a culvert width of 600, and height of 300, cell F27 should give "0.125"

Culvert Quantity Calculator.xls

Thanks for any help, I have been trying to work this one out for quite some time!!!

This is probably a simple question for excel gurus, but I am a molecular biologist, and don't set up these formulas every day. I have a table full of genotypes that I have indicated as 0, 1, 2 or 3 in columns. Column heads are gene names; row heads are subjects (patient numbers). I want excel to look in row 1 in column A and see if there is a 1 and also in column B in the same row to see if there is a 1, and if both are 1, count that patient in a tally. I want to be able to get a count of the number of patients with 1's in both columns. If someone can tell me what functions I use to set up this tally, I think I can set up more elaborate formulas. At the end, I want excel to look at several columns and tally the number of patients that have 1's in several columns.

I have a chart that displays daily bond sales for two subsidiaries.
Company X and Company Y... Must be retarded because I cannot make the HTML maker work... but here is a small sample
HTML Code:
OSB Sales 2008   Company X
       2007   2006   Difference       1-Jun   Variable    $           225062     $             111,100            day 1  
Step Up    $           280,200     $           465,000                Fixed    $           776,000     $           343,900   
            Total To Date    $          1,521,000     $             920,000     $             601,000    

(don't mind HTML tags because they probably look foolish, sorry)

I have 30 days worth of these 4 row consolidations per sheet, for 6 years. Company Y is adjacent to company X data, using the same row titles (variable, step up, fixed) but they are only used on the far left hand side (next to company X).

Possibly better illustration of the row setup:
RowB RowC
2 Variable $xxxxx
3 Step Up $xxxxx
4 Fixed $xxxxx
5 Total to Date $XXxxx
________________
6 Variable $xxxxx
7 Step Up $xxxxx
8 Fixed $xxxxx
9 Total to Date $XXxxx

and so on for 30 rows. I want to create a subtotal column for Variable, for example.

Is there a formula that can replace the manual labour of typing =SUM(a1,b1,c1,d1,e1,f1,g1) etc..

Thanks in advance for any help that might be offered. (if clarity is required please stress that fact)

Rob

This is a follow-up to my first post. It is more difficult so I thought I could post a new thread. I hope this is not a nuisance.

To reiterate my task:

I am doing some cleanup of marketing contacts for the company I'm working for. I have a list of about 6,000 company contacts. They are under the column headings Company, Name, Last Name, Title, Address Line 1, Address Line 2, State, Country, Zip Code, E-Mail Address.

I have been given the following task. This refers to the "Title" column. Verbatim:

"Then I need you to cut out all people who are NOT the following and place into a separate sheet

CEO,CSO,CMO,COO all execs or advisors
Any title with the words Clinical, Regulatory, Medical
Alliance, outsourcing
Project Managers,

Basically we do not need individuals in business development, Scientists, Investor Relations, Manufacturing, Licensing, Health Economics, Intellectual Property, Legal Affairs."

So basically I am looking at the titles column, eliminating those who do not fit under these qualities from Workbook A and then pasting them in a Workbook B. But not just the titles cell, the entire row with the other information about the individual/company.

Are there any shortcuts to help reduce the time required for this task? I appreciate all help provided.

Hi all,

I am hoping for help with two date formula queries I have.

I have built a spreadsheet which consultants at my work (a recruitment agency) list their successful job placements on. We record the placements monthly, and each month I simply copy and paste the previous month's 'template' (a 'block' of 60 rows) down into the area immediately below. The 'template' then has all the right formulae in the right place, one of which is a formula to auto increase the month by one. So if the previous month was March 2009, the next pasted 'block/template' will automatically be entitled April 2009. The placements are then summed up automatically per consultant per month with further formulae in the template that make calculations based on the data in the month's 'block'.

Some recent changes to the info we want to see per consultant per month leads me into my 1st question:
Instead of just listing March 2009, I now need (I think, unless Excel can be a bit clever) the date to be essentially the range of the whole month, ie 01/03/2009-31/03/2009 (I am in the UK), or for months with fewer days to cover the relevant period ie 01/02/2009-28/02/2009. Is there a way to do this? And let me explain why I think I need it in my 2nd question (the resolution to question 2 may mean my question 1 is not the best way of going about things).

I have a formula that totals the placement fees per consultant by using a sumproduct. The formula (in cell AN154) basically adds up a fee if the consultant's initials by the fee match the consultant's initials where I want the calculation to take place (cell A135). I now need to have a further if criteria, and the formula is getting a bit complicated, so I would appreciate some advice. The formula is as follows:

=SUMPRODUCT((I136:I189=AD154)*(K136:K189))

where the consultant's initials for the calculation are in cell AD154 and column I is where a consultant puts their initials in the data section. Column K is the placement fee.
I now need to modify the formula so that it checks the date an invoice will be raised for a placement for a certain month (ie I need to know the total value of invoices that will be raised in February for a specified consultant - placements are listed when they are made but we only invoice on the start date of a candidate and due to the nature of jobs a placement could be confirmed one month but the candidate may not start till the next month or even the month after).

So this is why I asked the first question. My modified calculation formula needs to check if the placement will be invoiced in the month range of the 'block' which will be copied & pasted down for the next month and so on. So it needs to sum any fee which matches the relevant initials, if the invoice date is with the date range of the month block that this formula is in. (eg if we are looking at the block for March 2009, for placements made by the consultant with initials MG, the calculaton needs to check the entire spreadsheet for fees associated with MG that will be invoiced in March 2009).

Column S is where consultants list the date for their placement to be invoiced. Column I is where they list their initials. Column K is where they list the fee value. My modified formula will have to look at the total columns rather than just the 60 rows in each 'block' because, as mentioned earlier, placements made one month can be invoiced in later months. Can someone help me with the modification of the formula? And also how to create the self calculating month formula which heads up each 'block' (I currently use

=DATE(YEAR(A67), MONTH(A67)+1, DAY(A67)) where A67 is the previous month in the previous 'block' in the format 01/03/2009.

If it can't be done in one cell, I am happy to use my existing formula on two separate cells as long as the calculation formula can check a date range between a date in one cell and a date in another cell - and as long as the different number of days in each month can be handled by excel somehow? Ie having 01/01/2009 in cell A1 and 31/01/2009 in cell B1, using my above formula would produce 01/02/2009 in cell A2 but 03/03/2009 in B2 - I need it to be 28/02/2009.

sorry for the length of my post, it's hard to explain concisely!

In fact, I have just realised I can upload my file. Cell A135 is where I would like to enter the date range by formula.

Cell AN154 is where I would like to modify my formula to check if the invoice date (ie check the whole of column S) is within the month of cell A135.

That way I can simply copy the block for a new month and the whole spreadsheet is self calculating, apart from cell A67 which is where I need to enter the starting point for the dates manually.

Can anyone tell me what formula I need to put in AN154 and how to do the auto month increase problem? Thanks in advance.

Using: Excel 2000, on Windows 98

Good morning! I'm hoping that someone on here can possibly help me. I have basic Excel knowledge, but this is out of my skill set.

My boss has given me a project to work on, in which I have a workbook with two worksheets that I am dealing with. The worksheets are labelled PRICE and SUMMARY.

On the PRICE sheet, I have several columns. Column A lists the product name, Columns E list the per page charge for the *red* program, and Column G lists the per page charge for the *blue* program. Those are the columns we will be dealing with. All specific information on this page starts on Row 5, with the headings, etc on Rows 1 - 4.

On the SUMMARY sheet, we have Column A which lists the product name, Column E which lists the cost per page for *red* and Column G which lists the cost per page for *blue*. As with the PRICE sheet, all specific information on this page starts on Row 5, with the headings, etc on Rows 1 - 4.

What I need to be able to do, is when one of our sales guys enters a product name in Column A on the SUMMARY worksheet (I have this set up with Data Validation, so the name will be the same format on both the PRICE and SUMMARY worksheets), have columns E & G automatically fill in with the corresponding information (columns E & G) from the PRICE worksheet, without the sales guy having to go and look up the information on the other sheet.

I'm not very experienced with setting up formulas (but I'm trying to learn!), so any help and guidance you could offer would be greatly appreciated.

Samantha

Dear All,

Would appreciate if someone could help me with this. I have a worksheet as attached. I would like to lookup a value based on the dates (which are in rows) and currencies (which are in columns). However, in the same column, I could have different currencies.

An example is

Value Date AUD JPY NZD
11/08/2011 18,000 19,000 17,500
11/09/2011
11/10/2011

Value Date HKD SGD EUR
11/08/2011 143,270 120,000 150,000
11/09/2011
11/10/2011

So I would like to lookup 11/08/2011 and return the values for the different currencies in a different sheet in the following format

Value Date AUD JPY NZD HKD SGD EUR
11/08/2011 18,000 19,000 17,500 143,270 120,000 150,000

Additionally, this is a culmulative report, i.e. the number of rows will keep increasing.

Will be extremely grateful if someone could help me with this.

Thank you!

Best Regards,
Vincent

Hello,

I have a spreadsheet of course where I list a bunch of products and their meta data, cost etc.

I then have columns at the end that represent packages and in those columns are select products with the quantity of that product that will be in the package. There are multiple of these "package" columns and they keep getting added on the end. The columns I want to sum up have heading "Qty" standing for the quantity of the product in that row to be included in the package.

I'm looking for a function/formula that will sum the contents (within a range of columns thats expandable as Packages get added) of all the individual rows in my sheet that have the text "Qty" in a specific row which is used as a header row.

The end result would be a column that has the total quantity of parts necessary to make all of the packages.

I can't just sum the individual rows because there is other data in there that would mess up the results.

Any suggestions would be greatly appreciated. If you need more info please let me know.

I've attached the file, it has a lot of columns but many are grouped and collapsed. If you can't scroll horizontally zoom out to confirm the scrolling region is visible.

Thanks!

Hi,

Having trouble putting together the correct formula. Any and all help will be greatly appreciated. My set up reads similar to this. A1 is a drop down box with different words. B1 is a drop down box with different amounts. C1 is a manual input cell where numbers will be entered. D1 is where I will be entering my formula. To the right is a table where values will have to be looked up and related back to cell D1. So the table looks like this: Top Culumn (merged Cells) A1 word X4, Second column heading underneath C1 ranges X 4 repeated under every A1 word. Under the 4 repeated C1 ranges is different amounts for the look up. The the left, the rows are the number value looked up from drop down B1. I hope that was clear. So basically, the user will be selecting a word from drop down box A1 and B2, they are not dependent on one or the other except for the table obviously--the drop downs are independent, and then the user will input a number into C1. Based on this cell D1 will pull a number from the aformentioned table.

Thank you guys so much!

Im afraid Im completely over my head on this. Maybe someone can land a hand. I will try and explain as best as I can.

I have two worksheets, One called destination and another called source. Using column E as the key in the source worksheet, I am trying to write a procedure that will loop thru each record in the source and search the destination worksheet column B for a match.

For each record in the loop, If a match is found:
Copy source cell A to destination cell H
Copy source cell C to destination cell I
Copy source cell E to destination cell F

For each record in the loop, where no match is found:

Create a row
Copy source cell A to destination cell H
Copy source cell C to destination cell I
Copy source cell E to destination cell F

The labels are not important as they are dynamic and change often on the source file. ( there are many source files) However the cell location always remain the same.

Clear as mud!

I have scoured the net looking for a solution, purchased numerous books and ended up more confused than ever only now I have blood shot eyes.

Any help or direction would be greatly appreciated.

Les

Morning/afternoon/evening.

I have been looking around the forums for a solution for this with no avail. I think it will have something to do with Large and case formulas, but cant put 2 and 2 together.

2 Spreadsheets. One is the master, another is a xls dump from an application.

What i need is the following ( going over it in my head to try and give a good example)
----------------------------------------------------
Dump Sheet:
Check for Username (column b)
Find the newest date for that Unique username.(column F)
Store that info.

Repeat for all of sheet.

Master Sheet:
Then copy the date for the username to the Master sheet (Cell H & I) as long as the username is the same as both sheets.

----------------------------------------------------

Heres another tricky bit : If (dump sheet) Columns I,J,K are equal to 0 then copy the same newest date to both Cell H & I (on the master sheet). If there is any other value greater then 0 then only copy the date to the first Cell (H).

your help on this would be appreciated.

Thanks o smarter then me peoples.

Update: I have started a simple code for this but would like to see how i can automate it from getting the newest date from each user and displaying this on a report.

heres what i got so far ( it sorts it at least)

	VB:
	
Sheets.Add 
With ActiveSheet.QueryTables.Add(Connection:= _ 
    "TEXT;C:Documents and SettingspiepercMy DocumentsReady Access ProjectReady Access Details for AAXEDUCATION-EDNET - 03 Mar
2.25.csv" _ 
    , Destination:=Range("A1")) 
    .Name = "Ready Access Details for AAXEDUCATION-EDNET - 03 Mar 2.25" 
    .FieldNames = True 
    .RowNumbers = False 
    .FillAdjacentFormulas = False 
    .PreserveFormatting = True 
    .RefreshOnFileOpen = False 
    .RefreshStyle = xlInsertDeleteCells 
    .SavePassword = False 
    .SaveData = True 
    .AdjustColumnWidth = True 
    .RefreshPeriod = 0 
    .TextFilePromptOnRefresh = False 
    .TextFilePlatform = 850 
    .TextFileStartRow = 1 
    .TextFileParseType = xlDelimited 
    .TextFileTextQualifier = xlTextQualifierDoubleQuote 
    .TextFileConsecutiveDelimiter = False 
    .TextFileTabDelimiter = False 
    .TextFileSemicolonDelimiter = False 
    .TextFileCommaDelimiter = True 
    .TextFileSpaceDelimiter = False 
    .TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 4, 2, 2, 2, 2, 2, 2, 2, 2) 
    .TextFileTrailingMinusNumbers = True 
    .Refresh BackgroundQuery:=False 
End With 
Rows("1:2").Select 
Range("A2").Activate 
Selection.Delete Shift:=xlUp 
Rows("1:1").Select 
Selection.Font.Bold = True 
Selection.Font.Underline = xlUnderlineStyleSingle 
With Selection 
    .HorizontalAlignment = xlCenter 
    .VerticalAlignment = xlBottom 
    .WrapText = False 
    .Orientation = 0 
    .AddIndent = False 
    .IndentLevel = 0 
    .ShrinkToFit = False 
    .ReadingOrder = xlContext 
    .MergeCells = False 
End With 
Range("C8").Select 
Range("A1:N23").Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range( _ 
"F2"), Order2:=xlAscending, Key3:=Range("G2"), Order3:=xlAscending, _ 
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ 
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ 
DataOption3:=xlSortTextAsNumbers 
Cells.Select 
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("F2") _ 
, Order2:=xlAscending, Key3:=Range("G2"), Order3:=xlAscending, Header:= _ 
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _ 
xlSortTextAsNumbers 
Range("B21").Select 
ActiveWindow.SmallScroll Down:=-9 
Range("E5").Select 
Selection.NumberFormat = "@" 
ActiveCell.FormulaR1C1 = "yes" 
Range("E5").Select 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Auto Merged Post Until 24 Hrs Passes;

im actually getting what i want out of this.
Just doing it the ol basic way of recording macros and getting the code.

When this is done ill post it up as it is rather interesting what i want.

learning all about offsets and activecells.

I've got a difficult task to do, and I'm not sure exactly how to go about it. I've looked all over and can't find an answer. Maybe one of the gurus around here can help.

I've got a workbook that has a list that users add rows of information. It's easiest to enter this information in a long list as we go, but in the end it needs to be separated into groups. Each group consists of all of one type in each pour, so all of TYPE "U" in POUR1 needs it's own group, all of TYPE "M" in POUR1 needs its own group, TYPE "U" in POUR2 needs its own group, etc. Essentially, any rows that have matching entries in Columns "G" and "H" should be copied to their own sheet, and as data is entered into the master list, that information is copied to the appropriate sheet.

The info I've got to sort is:

Types (column "H"), possible entries are "U", "M", "B", "T".

Pour Number (Column "G"), starting from 1 and working up from there.

I've included a screenshot to help. Let me know if I need to clarify anything.

Can you have excel create sheets as needed, deleting sheets if they are no longer needed?

I've set up a named range that is linked to an Autocad drawing, but without some system in place, I have to cut each row from the master list and paste it into the appropriate sheet, which is time consuming and opens up the possibility for errors and duplicate information. If excel can sort this out for me, it would really speed things up and help eliminate the possibility for errors.

Is this possible, or is this a pipe dream? I can write formulas and whatnot, but this is above my head.

Thanks for any help/guidance/direction/advice anyone can provide!

I need help,

I have a simple but important idea, and I am at a standstill. I have a table that I am using autofilter to sort the columns (First worksheet). Column A in all the worksheets are names of countries. The first worksheet then enables countries to be prioritized by the autofilter drop down selection. I would like to link rows in the following worksheets (Column A being linked back) to that initial sort, so that the rows of info are then prioritzed based on the selection in the initial sheet. Hard to explain, so I am attaching the worksheet (a demo). I know you can link back cells easily, I know how to do that. By I need the entire row to move relative to Column A in the initial sheet.

Guys, Thanks a bunch.

If you can think of another way to do what I want pls tell me. The prob is I am using follow on worksheets that have 1000's of rows of data. So I want an easy way to prioritize based on my initial selections.

Just a heads up, on the initil sheet, 0 means doesnt contribute (no) to the objective, 1 means contributes (Yes). It will make sense when you look at it.

R
Manny


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