Sum With 2 Criteria In 2 Different Area?

Mar 24, 2014

i want to do a sum of values, based on 2 criterias, one is that it has to match the name, and it has to match another criteria say "Y" in this matter.

in one sheet i have

row 1 to 100, column a:

list of names of people

row 1 to 100 column b:
list of "Y" or "N"

in row 200+, column a:
list of names of people

in row 200+, column b:
values

I need to sum the values in column b, if the name in row 1-100 is the same as row 200+, and the criteria is "Y" for that particular person, as indicated in column b row 1-100.

View 2 Replies


ADVERTISEMENT

Print 2 Selected Area (highlighted Area)?

Apr 11, 2013

How can I print 2 selected area (highlighted area). I know there is a trick to do that. Right now I can only select (highlight) one area to print, but would like to know how to print 2 or more areas at same time

View 7 Replies View Related

Programmable Print Area: Macro To Set The Print Area According To The Amount Of Data In A Particular Range Of Cells

Feb 25, 2009

Using Excel 2003 I am trying to write a macro to set the print area according to the amount of data in a particular range of cells. I find I can include this instruction

View 2 Replies View Related

Graph The Following Data In An Area Chart With Line Charts Superimposed On The Area Chart

Nov 1, 2006

I am trying to graph the following data in an area chart with line charts superimposed on the area chart. I have a lot of data (and a lot going on) so I'm trying to figure out the best way to show this in excel from a functional standpoing (i can't get this to work in excel!!) to also an asthetic standpoint (dont want it to look terrible or illegible). This is what I'm trying to chart:

1) Weather data (temperature) by region:
So one region, would be: Northwest
I would like the "area" (so a shaded region) to be the min/max of the temperature data for each month.

2) I would like to show the temperature for each year as a line graph on the chart - so you can see if a year falls in or out of the shaded region.

3) I would like to show a company's sales increases across the same months per year as separate line charts. I may choose to just show the biggest outlier year in the end... or to show 2006 (the latest data).

What I am trying to convery with the chart is that the company's sales is or is not tied to weather deviations. I have attached an excel file with the data. I haven't been able to use the area chart or get a two axis chart to work or get it to look even remotely professional.

View 3 Replies View Related

How To Set Fixed Print Area In Excel So Cells Within Print Area Can Still Be Modified

Feb 12, 2013

I have a workbook with multiple sheets that I need to print.

Is there a way to create a fixed print area within which you can mess around with formatting without extending or shrinking the print area?

I have tried adjusting the margins settings so that they are all the same, but this does nothing to keep a fixed print area.

View 1 Replies View Related

Print Only Used Area

Nov 9, 2006

I have a sheet that will be about 5 pages long if printed. To minimize wasted ink and paper, using VB I would like to be able to have it only print the portion uses (data entered) along with a section at the top and bottom. The section at the top would print on the top of the first page and the section at the bottom would print on the last page.

I know I could record a macro to select an area and print that but it would not do what I am trying to accomplish.

View 14 Replies View Related

Specifying Area Of Worksheet

Jan 30, 2009

1)I am trying to specify the area of a worksheet, for example columns a-z and rows 1-1000 but I am having great difficulty in doing this. I have tried selecting the last row and shift>end>down, Edit >all then Edit>delete but nothing happens. The screen just flickers and all the rows are still there. Even specifying the print area does not do it. Any answers?

2) Any ideas how to sum a datalist by text in a box? I.e if there are 1000 rows selected how amny are filled. If there is a way of selecting 'any text' this would work but there is no constant value/text to use.

BTW I am using Excel 2000

View 6 Replies View Related

Set Print Area VBA?

Jul 18, 2012

I have an excel sheet with lots of rows (it goes up to BAA) I would like to be able to print certain parts with a VBA

for instance if Sales is selected from a drop down (lets say A1) the macro will print

Area : C7:Z500
Scaling: fit to 1 page
Orientation: landscape
Color: Color

or if Sales next year is selected

Area : C7:D7 AA7:AZ500
Scaling: fit to 1 page
Orientation: landscape
Color: Color
etc....

View 9 Replies View Related

Print Area Always Changes -

Oct 2, 2007

I use the code below to print a portion of a sheet. My problem is that the area always changes. The $A$476 should be the first cell in that column that contains "HEA" The "536" in $F$536 is the last row that contains data. There are conditional formats all the way down to row 1,000 but not data

Private Sub CommandButton1_Click()
Dim strCurrentPrinter As String, strNetworkPrinter As String
****strNetworkPrinter = GetFullNetworkPrinterName("Adobe PDF")
****If Len(strNetworkPrinter) > 0 Then ' found the network printer
********strCurrentPrinter = Application.ActivePrinter
********' change to the network printer
********Application.ActivePrinter = strNetworkPrinter
********ActiveSheet.PageSetup.PrintArea = "$A$476:$F$536"
********ActiveSheet.PrintOut** 'print something
********' change back to the previously active printer
********Application.ActivePrinter = strCurrentPrinter
********ActiveSheet.PageSetup.PrintArea = ""
****End If
End Sub

Obviously there is a function also that finds the full network printer name, I didn't think it was important, because my issue is the print area. Everything else works fine. If you need it let me know and I will post it.

View 5 Replies View Related

Finding The Top 3 Of Each Area

May 1, 2008

I have a list of tons of items that have lost orders against them. The items themselves are catagorized in 11 different areas and I'm trying to find the top 3 of each Area. These items are sorted descending by the number of lost orders and will constantly be changing every day. Here is an example of how my workbook is set-up:

A B C D E ....

View 9 Replies View Related

Active Area

Dec 29, 2008

I have the following macro that works fine. But, I would like it to stop when it gets to the bottom of the active area.

Sub Insert_for_SQs()
Do
Selection.EntireRow.Insert
ActiveCell.Offset(6, 0).EntireRow.Activate
Selection.EntireRow.Insert
ActiveCell.Offset(12, 0).EntireRow.Activate
Loop
End Sub

View 9 Replies View Related

Vba To Set Print Area

Feb 18, 2007

Looking for a macro that will set the print area on an accounts spreadsheet to the last non blank row, bearing in mind that all the rows contain formulas, have tried some macros but they assume the cells with formulas in them are non blank

View 4 Replies View Related

Set Print Area Using Activecell

Oct 7, 2012

Goal is to select range from active cell ( where coursos is ) to the top of that row. For some reason

ActiveSheet.PageSetup.PrintArea = Range("xey1":ActiveCell())

Does not work?

View 3 Replies View Related

VBA Code For Set Print Area

Feb 6, 2014

Making a macro that selects active cells (so anything with text in) and sets that as the print area.

Using the default builder I have:

Range("A1:AA44").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWindow.SmallScroll Down:=30
Range(Selection, Selection.End(xlUp)).Select
Range("A1:AA692").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$AH$692"

[Code]...

Can trim this code with something

Range("any containing data").Select
ActiveSheet.PageSetup.PrintArea = "selection"

View 1 Replies View Related

Print Area Keeps Expanding

Mar 29, 2014

copying a document that was made in word into Excel. I set the print area and page size but it seems I can keep adding columns. When I check the print preview it still shows the one page but it appears wider.Will it automatically create a new page if the page size limit is reached?

View 2 Replies View Related

Counting Merged Area?

Jul 21, 2014

I have a sheet with many merged cells.how is it possible to count the merged area and display the unique values in each area?

View 8 Replies View Related

Input Msg To Appear In The Other Area Where I Put The Arrow

Jan 19, 2007

In the attached file and when I click on Name or ID (B5 or B6) the input msg will appear on the same cell using the validation rule.

Actually I need any input msg to appear in the other area where i put the arrow. I tried to move the input msg to that Irea and it worked great each time i click on B5 or B6 but after closing the file and opening it later.. they will go back to the same place.

View 14 Replies View Related

Print Area In 2007..

Jan 22, 2009

Excel 2003 used to have a feature where the user can select and set the print area. I do not see this feature in Excel 2007. So, how dow I set the print area in Excel 2007?

View 2 Replies View Related

Dynamic Print Area

Feb 5, 2009

I can define a name to calculate the desired Print Area for a page. For example, print_area_notes: =Notes!$A$1:INDEX(Notes!$A:$Z,final_Note,5). I can then use that name when defining the Print Area via Page Setup.

The problem is it calculates the cell range immediately and substitutes that in the Print Area field. When print_area_notes changes the actual Print Area does not. Is there some formula I can enter in Print Area that will allow this value to be set dynamically? I have tried setting Print Area to =indirect("print_area_notes") but it doesn't like that. I could, but prefer not to, write a VBA function to set the Print Area but even then how do I set it to be called automatically? I'd prefer to be able to enter a formula and do it without VBA code because you then have the security issues every time the spreadsheet is opened.

View 3 Replies View Related

Copy Certain Excel Area To TXT

Apr 25, 2014

I use this code to copy certain areas out of excel into a txt-file in a way that 3 Excel columns remain 3 columns in notepad. The problem with it is that it gives me a comma as the decimal separator, but I need a point.

[Code] .....

View 10 Replies View Related

How To Set Print Area In Code

Jul 6, 2014

I have 2 codes that set up different "print areas". Every time i run one of the codes the print area doesn't get set unless i run the same code again. So if i run code A which will set the print area with no issues and then right afterwards i run code B then code B's print area won't get set unless i run code B a 2nd time. And if i now i run code A, the same issue happens and the print area to Code A won't get set properly unless i run the code again.

Here is part of the code that discusses the print area:

[Code] .....

View 6 Replies View Related

Setting Print Area By VBA?

Aug 19, 2014

I have looked for a way to set the print area of a worksheet through vb.

I would like it to find the last row of the worksheet and set it to be printed.

Some of the examples take it beyond the last row of data, because the the code that may be in the empty cells.

HTML Code: [URL] .....

View 2 Replies View Related

Dynamically Set Print Area?

Feb 12, 2014

I'm trying to dynamically set the print area for a given worksheet. I use the code below and it works perfectly when it's executed upon opening the tab; however, the worksheet has filters which may lengthen/shorten the required print area.

Is it possible to execute the code below when the user selects print rather than when opening the tab?

[Code] ......

View 4 Replies View Related

Isolate Last 3 Used Area Of The Column

Mar 5, 2014

Finding last empty cell on a column and inserting, copy paste the last non empty column. Now, I do have same problem again but this time I have to copy the last 3 non empty columns and paste it on 3 inserted column. Here is the piece of successful code with my previous problem.

[Code] .....

View 5 Replies View Related

Refer To The Last Row In A Selected Area Using VBA..?

Nov 3, 2008

I'm trying to do is add a signature box (for inventory-taking accountability) directly below the pasted area after I paste it. Now, of course, the copied and pasted area could have 5 rows or it could have 15, and when I copy it from the first sheet and paste it into the second it doesn't preserve the name of the range on the second page, so what I really need to do to add the signature box directly below the last row in the pasted area is specify the location in relation to the last row in the pasted selection, but I don't know how to do that. Using ActiveCell always refers to the top-left-most cell in the selected area. So...

If I have an area selected, how do I refer to the bottom-right-most cell in that selection (in the same way that ActiveCell refers to the top-left-most cell)? Or at least the bottom row? Or, even better, the row below the bottom row?

View 2 Replies View Related

Specify Area To Delete Using Userform

Apr 3, 2009

I have very little experience with Visual Basic and seem to be having trouble with what I think should be a fairly simple matter.

I want to add a control to a userform that allows the user to select a range of entries to be deleted.

The user can’t directly modify the spreadsheet as it is protected. The only way for them to change it is via the userform.
The way I want it to work is when the control is selected a form appears in which the first and last rows of information to be deleted are specified.

I don’t want to delete the whole row, just the first 7 columns of the specified row.

Here is the Code that I have so far:

View 8 Replies View Related

Variable Print Area

Nov 4, 2009

I'm using Excel 2003.

I have a spreadsheet used for costing jobs giving a detailed breakdown of parts and time. Sometimes the customer requests a copy so I'm creating a macro for my boss that prints the spreadsheet, less the hidden columns that I don't want the customer to see. This I can manage.

My question is this: Is there a way within the macro to set the print area to change to where the last cell is automatically?

Each spreadsheet is a different length dependent upon the work required and setting a larger print area to compensate would use excess paper and ink.

View 10 Replies View Related

Scroll Area Set But Won't Stay Put?

Oct 29, 2007

I set the scroll area on a worksheet and saved the file. The next time I open the worksheet the scroll area is no longer set.

View 7 Replies View Related

Calculating Area Of A Shape

May 17, 2012

Is there an automatic / easy way to get the area of a shape in excel (using vba or otherwise)?

View 1 Replies View Related

Stacked Area Chart In VBA

Oct 28, 2013

Code:
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlAreaStacked

I created a simple stacked area type in vba. Is it possible and how to have this stacked area to get values from combobox and textbox?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved