Chart Data Labels
Hello,
How do I make chart data labels appear in thousands, for example, 330
instead of 330,143?
Thanks Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123888
Scroll area lock- again
Hello
I found in older posts the following code to lock the scroll area:
(I have a workbook with only one sheet: Sheet1)
Sub Workbook_Open()
Worksheets(1).ScrollArea = "A1:N50"
End Sub
This works. I save it, but after reopening it- it doesn't work. If I
go than to view code- and click run for this code- the scroll area
gets locked correctly.
My understanding is that workbook_Open should be executed as the
workbook is opened- why does it not run. Does someone have any ideas?
Thank you all! Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123885
Making a Cell Turn Color
Hi,
If I input a number in A1, such as 1, and I have the following numbers
in B1 1,2,3 just like that separated by commas.
When I put that 1 in A1, since it matches a number in B1, can I then
have B1 automatically turn red? (or yellow or whatever)
Thanks Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123879
Auto Save
How do you turn Auto-Save off so it stays off? I go to tools>autosave
and uncheck it but it keeps popping back up.
Thanks Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123878
Validation list in a conditional formula
I would like to include a validation list (drop down choices) in a
conditional formula in Excel.
Something along the lines of
=IF(A4="RAIN",$B$11,IF(A4="TEMPERATURE",$D$11,""))
where $B$11 has a drop down choice of appropriate units for rain based
on a list from a column of cells (e.g. containing inches, cm, mm) and
$D$11 has a drop down choice of appropriate units for temperature
based on a different list from a different column of cells
(e.g.containing Kelvin,Centigrade,Fahrenheit).
But I can't get the validation list with the drop down choices to be
used in the conditional formula.
Also, my actual choices of weather parameters in cell A4 is up to over
50, so I don't think I can have that many embedded IF clauses.
Can someone tell me if there's a way to do this? Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123872
Macro Button Menu
I have created a macro button menu to access 21 of my workbooks.
I did this so my wife could access the workbooks easily.
I would like to lock the view that shows on the screen so it will not scroll
if someone uses the scroll button on the mouse.
Can this be done?
Excel 2002
Thanks Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123869
alert in Excel
I collect cash deposited(by customers and our cashier) into our account
online and post it to a shared workbook where others use the data.
Sometimes, I post cash received from customers(written in cash received list
sent to me) and then paid into the bank.
I need an alert for any repeated figure in the worksheet.
Please, help! Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123859
Troubleshooting Data Import from Excel File
Hello all!
I am trying to determine why my data import into excel 2002 is not
working correctly. I have multiple Excel sheets that I am going to
have feeding into one primary one. One sheet I have imported
correctly; although its formatting got shot to hell in the process, it
is still usable and workable.
However, I run into problems with a second sheet. When I do the data
import from the other excel file, I can see the titles for both
columns and rows, but no numerical data. This happens with another
worksheet from the same workbook.
In the excel workbook that I am trying to import from, I notice that
it is read-only. Is this the root cause of my problem? I cannot
understand why it would be - all I am doing is reading data from that
other workbook, not writing to it.
Can someone please help?
Thank you!
-Kevin Kennedy
B/E Aerospace Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123853
Multiple Text Colors in Same Cell
Hello --
I am wondering if there is a way to format a cell so that multiple
colors can be assigned to different text within the cell.
I am trying to construct a simple in-cell bar chart representing the
distribution of data across different categories, and want to be able
to distinguish across the different categories using colors.
For instance, this would create a two category bar chart, but I have
no way to distinguish between the first category and the second
category:
=REPT(A1,A2)&REPT(A1,B2)
Is there any function (e.g. COLOR() ) that would be able to do the
following:
=COLOR(REPT(A1,A2),SOMECOLOR)&COLOR(REPT(A1,B2),SOMECOLOR)
Maybe there is something in VBA? Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123852
Checking For Numeric Before Computing?
Seems like:
=IF(ISNUMBER(R8C15),IF(R8C15 > 0, R8C15/R1C17,Null),Null)
Should return Null if R8C15 contains "xxx".
But it returns "#NAME?" instead.
Can anybody see what I'm doing wrong?
(I'm pushing that "IF" statement into the cell's .FormulaR1C1 via VBA
code) Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123846
Macro to find, replace.
I extract a list into a .txt file and then open in excel.The list
contents are from a dynamic source, but contain certain text items
that are constant. I want to be able to prompt for the word and then
find the next instance of that word in Row D, then navigate three
cells left and one cell down and then paste a formula. I need to loop
through this until the end of instances of the given word in column D.
Currently I have got as far as follows: -
Range("A1").Select
Cells.Find(What:="directory", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Offset(1, -3).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(R[-1]C[3],"" "",R[-1]C[4],"" "",R[-1]C[5],""
"",R[-1]C[6],"" "",R[-1]C[7],"" "",R[-1]C[8],"" "",R[-1]C[9])"
ActiveCell.Select
Selection.Copy
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(1, -3).Range("A1").Select
ActiveSheet.Paste
Now I need to loop through the rest of the document.
Help gratefully appreciated.
Mark Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123844
Counting number of TRUEs in a column
Following on somewhat from a previous post, I have a column that contains
the text TRUE or FALSE. I need to count the number cells containing TRUE.
Suggestions?
Jeff Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123840
Extend visible cell text over adjacent cell
Apologies if this has been answered previously but I don't even know
what to search for!
If I type a long string into a cell (without wrap checked), the cell
contents will be displayed in full if the cell(s) to the right are
empty. However, if they contain information, the first cell's contents
are visible only up to the border of the two cells. This makes sense
under normal circumstances.
I am looking for a way to enforce this first overlapping behaviour.
The adjacent cell contains important info but does not need to be
seen. In fact, I have made the font white so that it is invisible.
Any ideas?!? Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123839
SeriesCollection.(X)Values: locale settings specific?
Hello!
I am using VBA to make some hard coded charts:
[=2E.]
.SeriesCollection.NewSeries
.SeriesCollection(i).Name =3D "MyName"
.SeriesCollection(i).XValues =3D MyXValues(param1)
.SeriesCollection(i).Values =3D MyYValues(param2)
[=2E.]
where MyXValues and MyYValues are functions returning strings of the
form
"=3D{0,0.1,0.2,0.3,0.4}" or the like.
This works fine on my English Windows & English or German Excel
computer,
but not on my colleagues=B4 German? Windows & Excel..
>From the "Source Data" form of the wrongly displayed charts on their
computers
I can see things like:
"=3D{0.0.1.0.2.0.3.0.4.0.5}" or "=3D{0\0.1\0.2\0.3}
I can only think of different locale (language specific) settings that
make my strings
misinterpreted..
Any experience in this topic? Any idea where the correct description
of this Strings
may be? other separators (which are non-language specific) allowed?
Thanks! Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123835
Macro to automate task...
Hello
I am working with a worksheet in Excel, and one of the fields (columns) in
the worksheet is "TEAM". I have to replicate the spreadsheet for each team,
and at the moment I am creating a copy of the spreadsheet, then using the
auto filter to filter on a team, renaming the worksheet to the name of that
team, and then repeating this process for each of the teams.
Is there a way of automating this task, so that I create a worksheet for
each team using the main worksheet that has all the teams listed?
Hope this makes sense - any assistance would be greatly appreciated.
Thanks in advance
AC Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123828
table problem
I have a table with three columns of data. Column A contains a part
number such as "Part XX", or "Part ZZ". Column B has a date in the
01/01/05 format. Column C has a numeric value. There are about 20,000
rows of data. The dates cover three years. For example:
PartXX 1/1/05 123
Part XX 3/7/05 456
PartZZ 9/10/07 789
PartXX 1/1/06 159
PartXX 1/1/07 234
Eart part only has one entry for each date during the year.
In the fourth column I'll add a column with all 365 days for the year.
The date will be in the 1/1, 1/2, 1/3 format.
I want to create an output which searches for the specific part and
places the numeric value in the row with the matching date. Using the
above data, columns 4 through 7 would look like this:
1/1 123 156 234
And so on for each date. It's also possible that some dates will not
be present for all parts. So it needs to put a zero in the cell.
Any ideas how to do this? Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123826
Border lines too thick !
Excel 2007 only appears to have a limited number of border lines. The
problem is that we are choosing the thinest line in the dialog and it prints
horendously thick for what we need. The printer is capable of printing very
thin 600 DPI hairlines, but Excel prints the lines much thicker with no
apparent option for editing the line widths. We are also finding this to be
the case with text - our text prints very bold when the printer is capable
of much finer text. Does anyone know if we can edit the default line widths
or font boldness and address these problems? Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123823
Excel 2007 Interface
Is there any way to use Excel 2007 with toolbars like all THe previous
versions, or at least Excel 2003.
My productivity is appauling using Excel 2007 as evertything takes so long
to find, if indeed i Can.
PWS Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123809
Filtering out single values (keeping duplicates)
Is there a way to filter out all the values in a column that only appear
once, leaving just items that appear 2+ times? I know there's a way to get
rid of duplicates, but I haven't been able to find a feature that would do
the opposite. Thank you for your insight! Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123807
Cell Formated for MAC address
Hi,
Is there a way to format a cell to display twelve alphanumeric characters to
look like a MAC address?
For example, if the cell contents are: 0003FF411153
I want it to display like this: 00-03-FF-41-11-53
If a MAC address were all numeric's, the following would work::
##"-"##"-"##"-"##"-"##"-"##
But because MAC addresses have characters A-F, this won't work for me.
I tired using: @-@-@-@-@-@ but that didn't work either.
Any thoughts?
Thanks!
---
Bob Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123802
Compressing letters in a cell
I have a seating chart for a group of students. I want to show each first
and last name in a single cell in the following fashion, without changing
the font size or cell length.
Last Name,
First Name
Some names are quite long. Is there a way to compress the letter spacing
(up to a reasonable degree of course) so that the last name stays on top
without separating the last few letters of the first name
Otherwise, here is what is happening
First Nam
e Last Name
Thanks for any help ... I am using office Excel 2007
Jerry Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123801
Matching and VLookups
I have just used some vlookups to find some information on a Unique
ID. Unfortunately each week a few of these ID's are deleted and some
are added- so a vlookup can not find information pertaining to them.
The good news is that the data is sorted in such a way that when a
vlookup returns a NA, b/c it can't find info on the related to the
ID,
that all data above it is what I need.
For instance, after running a Vlookup I left with:
ID's Region location
1 NY NYC
2 NA NA
3 CAL LA
4 NA NA
5 NA NA
*Note: ID's 2, 4 and 5 are new and therefore the Vlookup can't find
anything b/c there is no info on it. However, ID 2's, region and
location is the same as ID 1. Also, ID 4 and 5 region and location
info are the same as 3.
In saying and showing, is there any way to make a formula that would
find any NA returned by the Vlookup and match the region and
locations
associated with the ID to those above it.
Thank you. Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123787
Matching and Vlookups HELP
I have just used some vlookups to find some information on a Unique
ID. Unfortunely each week a few of these ID's are delelted and some
are added- so a vlookup can not find information pretaining to them.
The good news is that the data is sorted in such a way that when a
vlookup returns a NA, b/c it can't find info on the related to the ID,
that all data above it is what i need.
For instance, After running a Vlookup i left with:
ID's Region Location
1 NY NYC *Note: ID's 2,4, and 5 are
new and therefore the
2 NA NA Vlookup can't
find anything b/c there is no info on it.
3 CAL LA However, ID 2's,
region and location is the same as ID 1. Also,
4 NA NA ID 4 and 5
region and location info are the same as 3.
5 NA NA
In saying and showing, is there any way to make a formula that would
find any NA returned by the Vlookup and match the region and locations
associated with the ID to those above it.
Thank you. Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123786
cell won't accept formula
I have spreadsheet that contains a few cells that won't accept
formulas. Numbers and text can be hard-keyed, but that's it. I've
tried to change the format from number to accounting, to general,
etc., no luck. Any thoughts? Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123785
Count Unique with condition
Hello Everyone,
I am looking for a formula to count unique occurances of column (A) where
column (B) equals a certain value. Any help would be appreciated.
I have the formula
=SUMPRODUCT(('data'!A3:A6<>"")/COUNTIF('data'!A3:A6,'data'!A3:A6 &"")),
which gives me a count of the unique values in 'data'!A, now I would like to
break it down over a value in 'data'!B
the result would look like this:
smith, john, blue
smith, john, red
jones, james, yellow
jones, james, green
williams, john, orange
williams, john, teal
Unique john = 2
Unique james = 1
Unique count = 3
Thanks in advance Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123783
Remove lines with +++
Is there anyway to find any line that has a + in it and delete that whole
line?!
I'd also like to do the same for *.
I have a list of about 1300-1500 names and addresses. Some of them have a
few +++ next to the name and some of them have a few *** next to the name.
These were put there by the company to designate things. They need to be
removed from the list, but it's a lost of work doing it one by one. Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123769
Highlight highest value in row
I have a sheet with 10 columns and several hundred rows. How do I (in Excel
2007) highlight the highest value in each row. I know I can apply
conditional formatting to each row individually but this would take far to
long and is impractical. Any help would be greatly appreciated.
Gerry Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123763
Formatting Issue Excel 2007 in VB
You have values 1 to 10 in 10 cells. You then apply 2 tone formatting cells
based on highest and lowest value with the highest value color being grey
and the lowest value white. When doing this the cells will be colored in the
various tones of grey to white based on the values of the cells. How do you
get the equivalent color values in the conidtional format so that you can
use those colors in the normal cell VB formatting. E,g.
Cell1.Interior.Color = 15921906 [White]
Cells2
....
Cells 9
Cell10.Interior.Color = 8355711 [Grey]
Thus want to programatically determine the color numbers for all the shades
using VB
Thanks in advance Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123762
SUMPRODUCT problem
I have a list where columns B - H are totaled in column I, and columns J - P
totaled in column Q. In column R, I want to show the difference between I
and Q, but only if there is at least one entry in cols B - H, AND at least
one entry in cols J - P.
I worked out this, which ought to work but doesn't.
=SUMPRODUCT((SUM(J84:P84="")<7)*(SUM(B84:H84="")<7)*(I84-Q84))
Where am I going wrong? This formula returns the difference between I and Q,
regardless of whether all the entries in B - H and J - P are blank. Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123746
I cant sum the values in cells!!
Hi everbody. I have a problem. There is a worksheet that sent
to me. I want to change the properties of cells or I want to make
operations( like summing cells). However I cant. It cant sum teh
values. And I want change the properties of the cells. If someone help
me , I will be very glad. Thanks a lot. Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123745
Runtime Error in Excel 2007
Runtime Error in Excel 2007
Everytime I open a .xls file I get a message that says Runtime Error
R6025-pure virtual function call and when I click OK the program closes down.
Unable to work on any files. I have installed all the updates available.
Any help is appreciated. Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123744
showing several sheet names at once
hi everybody there,
I have a workbook containing multibple worksheet said 35 sheet. I want
to see all of them at a timem so dont bother myself scrolling across
them.
thanks in advance
afshar Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123741
Dynamic Cell Reference
I have data on a template (Sheet1) and I need to output some data to a
summary table (Sheet2). On Sheet1, every 7th cell (starting with A2)
is the cell I want (i.e. non-contiguous) and I want to populate these
values into Sheet2, starting with B2 and continuing contiguous (i.e.
B3,B4,B5,etc) until all the values from Sheet1 are accounted for. As
another way of saying this, I'd like a dynamic formula to avoid having
to type the same formula over and over for a bunch of cells when only
one value changes with respect to the reference. This wouldn't be a
problem if my values I want to reference were contiguous.
=Sheet1!A2
=Sheet1!A9
=Sheet1!A16
=Sheet1!A23
.
.
.
.=Sheet1!A366
OFFSET and INDIRECT will not do this, or at least I can't see how. I
have tried using arrays, named ranges and INDEX to no avail. I
realize VBA is very powerful and could probably do this no prob, but I
have exactly ZERO VBA experience.
Regards,
Scott Pickles (a.k.a. Namelkcip <-> "Pickle Man" spelled backwards) Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123738
Pressing rhe Print button brings up a "Save As" dialog box.
I'm using Win'XP and Office 2003. Pressing the print button in Excel brings
up a "Saved As" dialog box. Rebooting doesn't change a thing. Following
through on the "Save As" dialog box doesn't lead anywhere. Been using Excel
for months and never saw anything like it. What am I doing wrong? Please
help.
Don J
-------------------------------------------------------------------------------------------------- Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123732
Matching cell colors to a legend?
We want to color groups of cells (an employee shift schedule) according to a
color legend. As such, we want certain cell ranges to match the color of a
cell in a key or legend. When the legend color is changed we want the color
of that group of cells to dynamically change also. This would be somewhat
similar to linking several cell values to a single cell value, except that
the cell colors, versus the cell values, would all change to match changes
in the master cell. Is there a way to link the colors of a group of cells
to the color of a master cell in a legend? Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123731
Cell input control
Hi,
in Excel 2003, is there a way to set a cell, or a column, in a way
that a user can only enter a limited number of character in it?
For instance, I want a user cannot enter more than 10 character into
any cell of column C.
thanks for help Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123723
Excel macro
i that to design a macro in excel file such that when i open the excel file
the macro should read the names of all the folders present in 'my document'
folder and paste those read folder names on workbook sheet.
please help me out. Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123722
Merge two files no single unique identifier
I need to add the columns from one Excel file to another Excel file. The
problem is that file A has 2506 rows and file B has 1825. The "missing"
681 rows in file B are spread out throughout the file, so if I just
paste everything from file B into file A, the rows don't align.
I need a way to merge the two files, adding empty entries to the file B
information so that every row aligns the correct information from both
files.
To make it more difficult, there is no single unique identifier from
each file. Multiple entries in one file can have the same Municipality
ID, and multiple entries can have the same School District ID, but no
entry can have *both* the same Muni and SD ID.
I'm guessing I first need a way to create a unique identifier by
combining information from Muni and SD IDs, then I need to pull
information from file B into file A based on the combined identifier,
but I have almost no idea how to get started on either task.
I am an Excel novice (can you tell?), so please go slow.
Thanks,
Ken Benson Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123718
Refresh and PivotTable
This behavior refers to Excel 2007.
The Refresh button update all PivotTables linked to a connection (that
connects to cube in Analysis Services). Refresh All works on all
connections.
Is it possible to refresh a single PivotTable without refreshing all
other PivotTables using the same connection?
Thank you
Marco Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123716
Cell Formatting
I have a date in cell M3 02/07/2007 (format dd/mm/yyyy) and a date in cell
P3 07/08/2007 (format dd/mm/yyyy).
In E3 I have the formula
=IF(AND(M3<>"",P3<>""),CONCATENATE(">",M3),M3)
the result is >39265. I would like the result to be >02/07/2007. I need this
format for copying to the criteria field of a filter.
Any ideas?
Thanks in advance
Sandy Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123711
Do a two way lookup and get the result in multiple columns
Hi,
I have a worksheet with data on store sales:
StoreLoc Date Sales$
A 10/28/2005 $100
A 11/04/2005 $150
B 10/28/2005 $200
B 11/04/2005 $250
B 11/11/2005 $300
B 11/18/2005 $350
C 11/04/2005 $300
C 11/11/2005 $350
C 11/18/2005 $400
D 11/11/2005 $400
etc.
I wanted to do a two-way lookup on StoreLoc and Date and get the sales
$ in a new worksheet in the following table format:
Store 10/28/05 11/04/05
11/11/05 11/18/05
A $100 $150
B $200 $250
$300 $350
C $300
$350 $400
D
$400
I tried using index-match function, the formula in B2 was:
=index(STORELOC,match($A2,STORELOC,0),match(B$1,DATE,0))
but for some reason that only allows me to do a fill down in ColumnB
but doesn't work for a fill across. Please help.
Thanks,
Anuj. Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123709
titles are bold and I don't know why
Good morning - My title is working quite right. While font size is
correct (18 and 14 pt), the entire title is bold and I have no idea
why. I greatly appreciate it if you could take a look and see if you
see anything out of place. I don't want it bold.
Thanks so much.
Mike
Sub MinPopRelGoal()
Application.ScreenUpdating = False
Dim myList() As Variant
Dim i As Integer
Dim j As Integer
Dim myCount As Integer
Dim chtDeer As Chart
Dim shtData As Worksheet
Dim rngData As Range
Dim myDataSet As Range
Dim strCounty As String
myCount = 1
Set shtData = Worksheets("zPopTrends")
With shtData.Range("A2").CurrentRegion.Columns(1)
.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
ReDim myList(1 To .SpecialCells(xlCellTypeVisible).Count)
'MsgBox .SpecialCells(xlCellTypeVisible).Count
With .SpecialCells(xlCellTypeVisible)
For j = 1 To .Areas.Count
'MsgBox "There are " & .Areas.Count & " Areas"
'MsgBox "This is J " & .Areas(j).Address
For i = 1 To .Areas(j).Cells.Count
myList(myCount) = .Areas(j).Cells(i).Value
'MsgBox "This is CellsI " & .Areas(j).Cells(i).Value
'MsgBox myList(myCount)
myCount = myCount + 1
Next i
Next j
End With
ActiveSheet.ShowAllData
End With
Set myDataSet = shtData.Range("b2").CurrentRegion
'note that there is nothing significant about b2, the address of
the currrent
'region remains the same as long the cell in the range address is
somewhere in
'the first 67 rows and 3 columns
'MsgBox "This is the range address for mydataset " &
shtData.Range("b2").CurrentRegion.Address
For i = LBound(myList) + 1 To UBound(myList)
'MsgBox "Now doing " & myList(i) & " County"
shtData.Range("A2").AutoFilter Field:=1, Criteria1:=myList(i)
'This example filters a list starting in cell A1 on Sheet1 to
display only the
'entries in which field one is equal to the current value in the
array myList.
Set rngData = Intersect(myDataSet,
shtData.Range("c:E").SpecialCells(xlCellTypeVisible))
'MsgBox "This is the range address for rngData " & rngData.Address
strCounty = Trim(shtData.Range("A65536").End(xlUp).Value)
' make a chart
Set chtDeer = Charts.Add
With chtDeer
.ChartType = xlLineMarkers
.SetSourceData Source:=rngData, PlotBy:=xlColumns
.SeriesCollection(1).XValues = "=zPopTrends!R2C2:R18C2"
.Location Where:=xlLocationAsNewSheet
.HasTitle = True
.HasDataTable = True
.DataTable.ShowLegendKey = False
.Pictures.Insert( _
"C:\Documents and Settings\tonkovic\My Documents\Data Files
\Not Deer\OFFICE\WONotebooks2006Logo.jpg").Select
Selection.ShapeRange.IncrementLeft 500
'Selection.ShapeRange.IncrementLeft 5.1
With .ChartTitle
' .Characters.Text = strCounty & " County" & vbCr & " Antlerless Gun
Harvest Trends and Antlerless Opportunity Index (AOI), 1995-present"
.Characters.Text = strCounty & " County" & vbCr & " Minimum Prehunt
Deer Population " & vbCr & "Relative to Goal, 1995-2006"
.Characters(Start:=1, Length:=7 + Len(strCounty)).Font.Size = 18
.Characters(Start:=8 + Len(strCounty), Length:=84).Font.Size = 14
End With
.Axes(xlCategory).HasTitle = True
With .Axes(xlCategory).AxisTitle
.Characters.Text = "Year"
.Font.Name = "Arial"
.Font.Bold = True
.Font.Size = 14
End With
'With .Axes(xlCategory).TickLabels
' .Font.Name = "Arial"
' .Font.Bold = False
' .Font.Size = 12
'End With
.Axes(xlValue).HasTitle = True
With .Axes(xlValue).AxisTitle
.Characters.Text = "Minimum prehunt population (n)"
.Font.Name = "Arial"
.Font.Bold = True
.Font.Size = 13
End With
With .Axes(xlValue).TickLabels
.Font.Name = "Arial"
.Font.Bold = False
.Font.Size = 12
End With
.HasLegend = False
'With .Legend
' Position = xlBottom
' .Border.LineStyle = xlNone
' .Font.Name = "arial"
' .Font.Size = 12
'End With
With .PlotArea
.Interior.ColorIndex = xlNone
With .Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
End With
.Name = strCounty & " County Pop Trends"
End With
AddChtLegend
Next i
shtData.ShowAllData
Application.ScreenUpdating = True
End Sub
Sub AddChtLegend()
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlBottom
ActiveChart.Legend.Select
ActiveChart.ChartArea.Select
ActiveChart.Legend.Select
With Selection.Border
.Weight = xlHairline
.LineStyle = xlNone
End With
Selection.Shadow = False
Selection.Interior.ColorIndex = xlAutomatic
End Sub Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123706
Index and Small
I am using the following formula (and varients of it) to produce a table
showing the top two and bottom two locations (the place names and their
relevant percentages):
=INDEX('Q4'!H5:H36,MATCH(LARGE('Q4'!H5:H36,1),'Q4'!H5:H36,0))
In the main they work fine, however, if I have two values the same then it
will not bring back the second label - it just repeats the same as either the
highest or lowest (whichever it matches).
Is there a varient to the formula to be able to look at identical values??
Thanks. Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123705
Formatting Charts from Pivot Table...
I've created a pivot table/with chart. The default chart was a bar chart,
which I've changed to a pie chart.
The problem is I don't like the default colors so I've changed them to the
way I like them; however, once I save the document they go back to the
default colors.
Is there any way to keep the revised colors, rather than the default colors?
Thanks,
KSL. Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123704
Pivot Table Source Data
I think I remember being able to take a pivot table view of data and
"double-click" on something (like the column headings or the whole
sheet) and a new sheet would be created showing all the background
data used to form the pivot table. If you know how to do this, could
you please describe the steps for doing this? Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123699
Losing leading 0 when saving as .csv
Hello,
I've got Excel 2002. I've got an address file that I need to save as a .csv
file. In the file are ZIP codes that begin with 0. These leading 0's are
being dropped when I save as a .csv file. Here's what I've tried so far to
keep the leading 0's in .csv:
1) In the .xls file, selected the ZIP column (column G) and formated the
cells as Text. Then saved as .xls and then saved as .csv. When I then open
the .csv file, the leading 0's are missing. If I open the .xls file, the
leading 0's are intact.
2) After opening the .csv file created in step 1, I again select the ZIP
column (G) and format the cells from General to Text. I then manually add
the missing 0 to the affected rows and save as .csv. I then close the .csv
and reopen it and the leading 0's are missing. I reselect the column to
check the formatting of the cells. The column has reverted back to General
even though I changed it to Text.
What am I doing wrong?
More importantly, how do I keep the leading 0's in the .csv file?
Thanks.
Mark Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123694
Problems Rank Ordering
I am trying to rank order a list of numbers, so that I can use them in an
Offset functions.
I am aware that I have some duplicate ranks in my data and have therefore
used the following formula to try to take account of these duplicates:
=RANK(U2,SportLocationList)+COUNTIF($U$2:U2,U2)-1
This works absolutely fine on all of my tables except one. On the
problematic table the function works like an ordinary rank and assigns
duplicate ranks (although ironically the ordinary rank formula assigns unique
ranks - which it shouldnt).
I am going to be using these tables as part of an automated report (where
the data will regularly change), so I would like to iron out what the problem
is.
Can anyone suggest why it might not be working on this one occasion? Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123691
Excel 2007 - How to export a sheet to tab delimited text file without doublequotes
Hello all,
I have a little problem to resolve. I have worked on an Excel 2007
sheet and am trying to export it to a tab delimited text file (Save
As...) but when the file is exported, and when I open it with a text
editor such as Notepad then I view a lot of doublequotes added to some
values... Do you have a clue about that ? I'd like to get a text file
that has nothing added to it when exporting it...
Thanks Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123683
Working with multiple worksheets
This is probably very simple but i am struggling with this. I have a
workbook where one sheet will be updated on a monthly basis and i want the
sheet preceding it to capture its outputs. A few things i need it to capture
is to check if Col B has a specific value and if yes check the value of the
cell adjacent to it and count this if it is a Yes or a No.
Please help!
BK Tag: MP3 AVI DIVX MPEG SOFT = www.GEGEREKA.com Tag: 123678
Millions files for everyone. Music, movies, soft and other media.
Check it now!! http://www.gegereka.com