Excel 2007 Question
I want to bring data into an excel 2007 worksheet that is stored in a sql
database table.
The idea is to parameterize the "where" clause in the sql select statement
limiting the result set.
There are lots of examples in the literature with regard to bringing in data
stored in cubes --- but not so much from data in sql table.
The basic issue is I want bring in descriptive information about say a
contract (things like description, signing date, etc). Cubes which most
(all) of the literature deals with are dealing with aggregations of amounts
or "measures" and not so much about descriptions.
As always all suggestions are welcome.
Mark Tag: Default File Location for Code Tag: 142062
Macro
I'm trying to do up a line chart based on a table on 'Project Summary Report'
sheet. I recorded a macro and Excel generated a series of codes as follows:
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Project Summary Report").Range( _
"A1:C4"), PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(1).XValues = _
"='Project Summary Report'!R2C1:R4C1"
ActiveChart.SeriesCollection(2).XValues = _
"='Project Summary Report'!R2C1:R4C1"
ActiveChart.Location Where:=xlLocationAsObject, Name:= _
"Project Summary Report"
The values on the active sheet are actually generated from a query in Access
2003 and are automated to be displayed on an Excel spreadsheet. In other
words, the query result set can get bigger.
An example: 1st row indicates the column names "A", "B", "C" while 1st
column indicates the rows "1", "2" and "3"
----A-------B--------C-----
1---Week--Target--Actual
2---1-------3---------3-----
3---2-------3---------4-----
4---3-------4---------5-----
My question is does the range that is generated from
ActiveChart.SetSourceData Source:=Sheets("Project Summary Report").Range( _
"A1:C4"), PlotBy:=xlColumns
vary accordingly?
Do I have to declare a variable say, 'rowCount' as
worksheet1.UsedRange.Row.Count so that a variable range can be defined?
Same applies for
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(1).XValues = _
"='Project Summary Report'!R2C1:R4C1"
ActiveChart.SeriesCollection(2).XValues = _
"='Project Summary Report'!R2C1:R4C1"
Do I have to declare a variable for the range of SeriesCollection(1).XValues?
Thanks in advance. Tag: Default File Location for Code Tag: 142060
Cannot save Excel files to certain folders !!
Has anyone else observed the anomaly of NOT being able to save a file to a
folder because of the folder name syntax?
In our company, we adopted a convention, many years ago, of using square
brackets, such as "[" and "]", when naming certain hard disk folders. For
example, "[ System ]" or "[ Dump ]" or "[ Shared ]" or "[ General ]" or "[
Library ]".
This convention allowed us to give certain folders visual precedence by
displaying them at the top of the directory listing when viewed in Windows
Explorer and in application 'Open' dialogs. The square brackets also set
the name visually apart. This convention also helped remove folders that
would have otherwise been alphabetically interspersed amongst other
unrelated folders. As such, the remaining folders, of a similar relation,
were also kept together.
This convention is perfectly legal within Windows. The naming of folders,
using the square brackets, does not generate any error messages or syntax
corrections. However, it does create a problem when trying to save new
Excel worksheets to such a folder! A new worksheet CANNOT be saved to any
folder with a pathname that includes square brackets.
Another irony is that you can open an existing worksheet form such a folder
and save it, after making changes, but you cannot save a new or 'Save As'
worksheet in any such location. I suspect this relates somehow to the Excel
convention of referencing cells in another worksheet by enclosing the
worksheet name in square brackets.
Either way, it has been a serious problem for us because this hard disk
folder naming convention is so widespread in our small company. FIRST, it
is extremely frustrating. SECOND, Excel should allow access to any file
saved in a folder created following proper Windows Vista naming conventions.
THIRD, we certainly consider this a BUG, or at best, a very serious
oversight by Microsoft. FOURTH, it should be an embarrassment that a user
cannot save a Microsoft application file to a Microsoft operating system
folder, especially when they have followed proper naming conventions.
FIFTH, we fear this could also happen if the users use other special
characters as part of their naming convention.
Has anyone noticed this problem and what is the chance of Microsoft
correcting this issue? Tag: Default File Location for Code Tag: 142059
Older worksheet size
I have several older worksheets. Each one has a corresponding macro
(xlm) sheet. They are set up so that when a worksheet is opened, the
macro sheet opens in the background (hidden).
I want each worksheet to be the same size that exists when I save the
file. Then, later when the file is opened, it goes from the size I
gave it to a full screen window.
The macro sheet has an Auto.Start command. It reads:
=ECHO(FALSE)
=Custom() ( This is a special set of commands for the worksheet )
=CALCULATION(1,FALSE,,,TRUE,FALSE,FALSE)
=RETURN()
What is making the file grow in size? AND how do I change it?
Roger Tag: Default File Location for Code Tag: 142058
Excel and Powerpoint?
Hi,
We use Office 2003 standard on Windows XP computers. All the computers are
P4 2GHz machines with 1GB RAM. I have one user who has a problem with
running Excel and Power Point at the same time. She can never demonstrate
to the problem to me but says that they simply shutdown. No error messages
or any indication why, they just go away and she looses her work. I've
rebuilt the computer, from scratch, twice now and the problem persists.
Any ideas what could cause this and why only one user has the problem?
Thanks in advance,
Linn
P.S. Oh, all patches have been applied and Office is on sp3. Tag: Default File Location for Code Tag: 142049
Merge cells
I have a set of tables where I am putting descriptions before the tables
that I would like to wrap. But the wrapping seems to be only for a specific
cell.
I would like my comments to span multiple cells and act as one cell, then
wrap in that one new cell.
I can do one row of text where the text spans multiple cells as long as
there is nothing in the cells to the right. But I can't wrap a paragraph
that way.
In html, I would just do a Colspan= 5 to have the 1st span 5 cells as one.
Can you do this type of thing in Excell 2007?
I can use a label, but was curious if you can do it the other way.
Thanks,
Tom Tag: Default File Location for Code Tag: 142048
Closing Excel 2007
It seems to me that the behaviour differs depending upon the existance of files in the XLSTART folder: if that folder is empty, Excel closes correctly; if you have a PERSONAL.XLSB or similar file in the XLSTART folder then Excel doesn't close with the last workbook - presumably because the hidden workbook is the last workbook and that book isn't closed yet? Tag: Default File Location for Code Tag: 142041
formatting
Hello, does anyone know how I can look at a cell without seeing a special
character? I need to determine a length by subtracting one cell from another
but the numbers in the cell are engineering stationing in the format
xxx+xx.xx (300+45.10). I need to use this data to get the number 30045.10 to
then subtract from another cell of the same type. Tag: Default File Location for Code Tag: 142038
Color Coding by Condition
Excel 2000
Is there a way to color-code a row based on a condition of one cell in
that row, but not using Visual Basic Editor? Conditional formatting
would be perfect except I am only allowed 3 conditions. I have a
column containing several different numbers and I am trying to color
code groups of numbers which would require more than 3 conditions.
There are approximately 100 or so different numbers and a total of 8
colors (conditions). I'm not an expert at all in Excel and if Visual
Basic Editor is needed then I would need some step by step guidance
with it. I appreciate any help! Thanks
CV Tag: Default File Location for Code Tag: 142029
Can this be done?
If I have the following cell values:
B6 contains A
C6 contains 5
D6 contains .35
I need to set cell H6 to:
A,DCS,5A,0V,<.35V,0S,1S
where A is based upon the contents of B6
5 is based upon the contents of C6 and
.35 is based upon the contents of D6.
If I change the values of B6, C6 or D6, the contents of H6 reflect those
changes accordingly.
For example if B6=B, C6=7.5, D6=.3 then H6 becomes
B,DCS,7.5A,0V,<.3V,0S,1S
Can this be done in xcel and if so how?
Thanks
-Mike Tag: Default File Location for Code Tag: 142027
Insert Code with Worksheet
Private Sub Worksheet_Change(ByVal Target As Range)
' Do Something
End Sub
I have the above code in Sheet1 of my Workbook. When I Insert a new
Worksheet, I want the code to be copied to the new Worksheet as well,
OR have the original code work in all Worksheets.
Can someone explain how? Tag: Default File Location for Code Tag: 142025
CountIf with VBA
Hi all,
I am a newbie in excel and having a problem with
countif function.
Currently in the actual worksheet I was unable to
set a CountIf formula with multiple criteria.
Can we set it with excel VBA? Or just like using
Dlookup function to find excatly match row contents.
Please advise.
Rgds,
Shiro Tag: Default File Location for Code Tag: 142020
New problem opening Excel from Windows file folder
This problem just started. When I double click an Excel file from a file
folder I get an error window that pops up that says can't find file. It
keeps saying that several times and then it finally opens the file.
Apparently if the folder name has any spaces in it Excel attempts to look
for a file from that link and each space in the folder name it stops to
check that until it makes its way through the entire folder name. For
example my folder address looks like this: Y:\SAP\SOX\2008 IT
Testing\excel.xls. So excel is evaluating that folder name and each space it
thinks is the end of the address. The error boxes appear and I just keep
hitting OK and eventually the file opens. It never used to do this. Did I
click something inadvertantly? Has anyone ever experienced this?
Thanks Tag: Default File Location for Code Tag: 142018
Charting
I have a created a trend . However for a couple of year data points
the values are blank. Since there is formula embedded in those cells I
really dont want to touch it. So when I graph over it it would show as
0 . In graphs we would be seeing all Zig Zag lines. If I want the data
points to maintain the flow from where the last data point was
available and not let the line drop to zero is that possible to excel
graphs ? Or should I put another condition in the cell to pick the
previous cell incase its blank or 0 or no value ? Any way out ?
Appreciate all your help in advance.
thanks
SD Tag: Default File Location for Code Tag: 142014
Pivot Changes when refreshed with the same data
Hello ,
I am just not able to find out why my data is changing.
I had created a Pivot from a dataset. The numbers matched. I saved
it. When I opened the pivot after a couple of days , I added a
conditonal FLAG column in the dataset so that I could bring that
condition to the Pivot. I only refresh the Pivot to get the new
variable name added, however most of the data changes, I do not why.
Numbers are intact, and fearing I would lose the effort I do not save
it. I close it and open the original file. When I double click on any
cell of column I get around 80 rows. When I refresh it and double
click on the same cell on the pivot I get 160 rows. Now how is this
possible ? is it because I added a column in the data set ? Say
Columns A to G are in the data set. I saw column E blank so I added my
conditional flag in there so that I do not have to again change the
range. Is it because of this ? Now how do I get it back or is there a
work around this ? Do I need to start all over again :(((
Appreciate any help.
thanks
SD Tag: Default File Location for Code Tag: 142013
Difficulties with named range
I have a single cell named BRHCTS in several excel files. It
indicates the bottom-right-hand-corner of the range I am interested
in. The upper right hand corner is always the cell A10. Therefore,
the range I want to copy from each of my files (into a consolidated
worksheet) is "A10:BRHCTS".
Can anyone advise me how I can do that without opening the source
files?
Thanks. Tag: Default File Location for Code Tag: 142012
Calculate average of a range, but exclude errors
Hello!
In my workbook I have several worksheets. In cell B2 of every worksheet,
there is an average from the data in that worksheet. As this is a
template, some data is not entered yet, and some B2 cells show error
(div by 0). Now, I wanted to calculate the average from B2 cells of all
worksheets, but exclude all cells with errors.
My formula
=Average(Sheet1:Sheet12!B2)
How do I modify this to work?
Thakks
Peter Tag: Default File Location for Code Tag: 142008
How to 'format' cells to be 'absolute' references ?
How to 'format' cells to be 'absolute' references ?
Trying to setup cells with date formulas (for project management) with
formula cell to show date from sum of reference date cell plus number of days
cell, and make formula cell an absolute value so can sort cells by that cell
column (per discussion at ...
http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?query=absolute+reference&dg=microsoft.public.excel&cat=en_US_e064c9ec-14bf-4ef7-ba07-6a1970559f64&lang=en&cr=US&pt=&catlist=&dglist=&ptlist=&exp=&sloc=en-us
...ie, cell-3 = $cell-1(date) + $cell-2(number of days)
How to get reference cells to be absolute values by formating, or set with
shortcut key, or set with main menu pick ?
--
akm
Thanks again for your help.
----
Ah, the miracle of computers!... someone also said "To error is human, but
to really screw up it takes a computer!" Tag: Default File Location for Code Tag: 142003
formula for total days, excluding holidays but including weekends
I need a formula to calculate total days (working days) that excludes
holidays, but includes weekends. The networkdays formula doesn't work since
we work on weekends and have variable days off. I wanted to put the select
days off( some weekdays and some weekends in a holiday range), and have them
subtracted from the start date, end date range if they fall in the range. Tag: Default File Location for Code Tag: 142000
Excel 2007 compatibility mode + Vista offline files = broken?
Problem: (the short-short-short version):
Environment: Win2k3 SP2 file-server, Vista SP1 clients, AD domain with
roaming profiles. Office 2007 SP1 is installed. Offline files is enabled on
a share by group policy.
Steps to reproduce: A pre-existing excel 2003 file is present on a share
that is available offline. A user WITH A ROAMING PROFILE is logged into a
vista client (yes, the roaming profile seems to make a difference)
1) Right click on the file, view properties, hit the offline files tab. The
file is online.
2) Now open the file in excel 2007. Observe that Excel opens the
file in compatibility mode. Right click on the file again, check status,
and
the file is OFFLINE.
3) Make a change and close the file (saving the change.) Check file status.
Still offline. Wait a half-hour. Check again. STILL OFFLINE (not even
background syncing!)
There are two problems as I seem them:
1) Another user can open an already open file WITHOUT A WARNING that the
file is in use. Excel 2003 used to warn that the file could be opened
read-only. Compatibility mode SHOULD be emulating this behavior (or it
isn't really compatible!)
2) Even if problem #1 isn't resolved, it is unreasonable to expect end-users
to manually sync after opening and closing a file.
Bottom line, upgrading to Office 2007 killed a feature we rely on in our
environment. Office 2003 didn't have this problem. Excel 2007 with native
.xlsx doesn't have this problem. It seems to be a bug in Excel 2007's
compatibility mode. Thoughts?
-Cliff Tag: Default File Location for Code Tag: 141999
Aggregating Data in Fifteen Unopened Spreadsheets into Single
I have a series of 15 spreadsheets, all having the same column
structure. They all have the same filename barring first two letters
which refer to a persons initials. The 15 files are stored in the
same directory. In that directory I'd like to create a sixteenth file
which consolidates much of the data in the 15 files. I want to use
that consolidated data in a pivot table. I know that the top-left-
hand-corner of the range I want within each of the 15 spreadsheet
files is the Cell A10. I cannot be so precise with the bottom right
hand corner (BRHC) as the number of rows is variable in each of the
fifteen sheets. I know that the BRHC is in column S (if that
helps). Additionally, within each of the 15 files there is around 25
rows of data below the BRHC which I do not want to insert into the
consolidated file.
Can anyone give me pointers as to the easiest way that I can create
the sixteenth file, without having any blank rows in that consolidated
file.
Ideally without the hassle of having to open the 15 files.
Thanks. Tag: Default File Location for Code Tag: 141998
Value in list wont show same amount of numbers.
I have a list of values. For some reason, I cant get one of the values
to display the same amount of numbers as the others in the list. I
have tried manually changing the cell format, as well as performing
the paste special - operation, add procedure.
The actual number is:530911.0010 (with the zero on the end)
I am performing a vlookup on the list so I need them all uniform.
Any clues?
530720.0000
530810.0000
530911.001
530919.0090
530921.4090
540233.6000
540331.0040 Tag: Default File Location for Code Tag: 141994
Weird result when printing from macro on one system
Maybe someone can help shed some light on this. In Excel 02 (SP3) we
have a macro that runs that highlights the selected fields (based on
some radial buttons that the user elects) and then it executes:
ActiveWindow.SelectedSheets.PrintOut Copies:=2
On 12 of 13 machines it executes perfectly, you get two identical
sheets printed out of your default printer.
The problem, 1 machine which appears to be setup identical to all the
others (including the printer and the print driver) does print the
*second* copy correctly. Regardless of the printer the job is sent to
(including acrobat writer) the first copy always comes out okay, but
the second copy the radial buttons and highlighting get screwed up and
you get a somewhat unreadable copy as sheet two.
To me it just doesn't make any sense as to why this would happen. But
maybe someone here knows just the right tweak to get me back on track.
Thank you in advance.
Justin Tag: Default File Location for Code Tag: 141993
XL office 2007 file to large
I recently created a *relatively large file in excel 2007 and when I tried to
save it as a 97-2003 workbook I too was notified that data had been lost. The
difference being from what I have read here so far is that the alert message
I received informed me that anything after column IV (the original document
contains data up to and including column JO) was not saved and this was
evidently true. Is there anything I can do to save the entire file as a
single 97-2003 workbook without losing anything? Thank you. Tag: Default File Location for Code Tag: 141987
Formula Error or Data Error?
I have the following spreadsheet which I attempting to calculate the Prior
Years Inventory Level in actual Retail $
I have a database query that retrieves the following Information:(
DeptDate( week end date(Saturday), OH Retail$, % Chg Prior Year), DeptNum
DeptName, CatName
(Dept = Abbreviation for Department)
DeptDate = (the date(Saturday) week ending (i.e. 5/10/08_
OH Retail$ = The Retail $ of Inventory for that department
OH %Chg = % chg from Prior Year (Don't have access to Original Data for a
certain Period of Time)
Deptnum = Department #
DeptName = Department Desc
CatName = Category Name (where different sets of deptnums are grouped)
The problem I am having is it doesn't seem to cross check the math.
I have a pivot table to display the OH$ , I then have 2 cols on the same
work sheet that calculates the prior years OH$
but when I cross check it doesn't seem to be right.
Col B (Calculated) has the following formula =G3/(1-(C3)*-1)
Col C = Contains the OH %CHG for each Dept/Cat for that week
COL D (Begins Pivot Table) = Contain Category Name
COL E = DeptNum
COL F = DeptName
COL G (Being Data) = Date on Column headers, and value of Inventory Level
The Problem occurs which I cross check the math.
COL A Contains =SUM(B3:B13)
If I summarize(COL A) the value in the range for each category (All Deptnum
in Category A for example it doesn't match)
am I doing something wrong Mathematically or is the data possibly bad? See
below for data sample
The issue is $104,007 with a (21.9)% chg = $133,182 not $126,785 the sum of
the individual changes
I have confirmed thet query data matches the database data
Thanks for any Help
A B C D
E F G
LY OnHand Retail$ DeptDate
5/3/08 Diff Category Dept# DeptDesc 5/3/2008
(row 3) $50,071 -40.90% Consumables 110 Food $29,592
$909 71.90% 111 DSD $1,563
$4,509 -45.40% 114 Speciality Food $2,462
$0 890.00% 115 Frozen Food
$43,248 -18.90% 120 HBC $35,074
$0 121 Control Sub
$5,110 -8.30% 130 House Keeping $4,686
$7,561 -14.90% 140 Household Chemicals $6,434
$4,811 -14.30% 150 Paper $4,123
$10,081 31.60% 160 Pet $13,266
$6,883 -1.10% 170 Home Organization $6,807
$133,182 $126,785 -21.90% Consumables Total $104,007 Tag: Default File Location for Code Tag: 141980
Currency Settings
I have created a report using VBA and Excel OLAP cube. Some of the
metrics on the report are currency numbers and are to be reported in
USD so I have formatted them accordingly using format ( e.g. "$
#,##0.00"). Some of the users in Germany who use decimal (.) as
separator between numbers are not able to see the format correctly. I
hope they use comma in place of decimal and decimal in place of
commas
as we normally do. Also other users in Europe see Pound or Euro sign
instead of dollar. My report is being used in many geographies and I
have to use $ which every body should be able to see.
Any suggestions? Tag: Default File Location for Code Tag: 141978
Copy formula without changing references [Excel 2007]
Hello!
In my Workbook I have many cells with data validation settings as follows:
Allowed data: List
Source: =INDIRECT(L9)
Now, I`d like to change the source formula to the following:
=IF(M9="P";INDIRECT(L9);R11:R21)
When I check the option "Make changes in every cell with the same
settings", Excel does it, but it also adjusts the cell references"
Is there a way to automatically change the formula in every cell with
the same settings, but leaving the formula untouched?
The formulas to be changed are not next to each other, they are located
throughout the Workbook, in several worksheets, in hundreds of cells, so
the copy/paste option wouldn`t make me happy.
I tried "Find and Replace", but Excel doesn`t find the data to be
changed. I use polish version of the software, so it might be, that some
translations I made, are not accurate.
Thanks in advance
Peter Tag: Default File Location for Code Tag: 141974
Data Validation Lookup List from Another File
Hi,
Without duplicating the cell contents in the current spreadsheet (call
it Template), is there anyway to have a dropdown list of items
(starting in cell T38 and going down to T45) from another file
([Price_List.xls]License_Only!C2 to C whatever row which will change).
Then, when the item is selected from the list in T38, lookup the cell
to the left in row D of Price_List.xls and put it in S38.
The reason I don't want to duplicate the cell contents in the current
spreadsheet is because the file will be way too large to deal with.
I hope that explains it well enough
Thanks for any help you can offer.
Michele Tag: Default File Location for Code Tag: 141972
xlsm macros
Macros in XL 2007 xlsm spreadsheets that have passwords do not run. Msg says
to re-open and enable the macros at the security prompt however the prompt
does not have an "enable" option.
Searched MS help etc and found a reference indicating that this occurs if a
virus s/w pkg does not scan encrypted macros.
i use norton 360. Checked with Symantec and they indicate nothing to
set/unset in N360 - no answer.
problem exists whether N360 is enabled or disabled - makes no difference.
If I remove the password the macros work.
I'm thinking my only option is to run without the password but perhaps put a
digital signature on the spreadsheet.
any other ideas?
--
regards
...barglen Tag: Default File Location for Code Tag: 141967
underline in row not repeating
It seems as if my bottom of the cell outline is not repeating on subsequent
pages. I have a few header rows on a sheet. The bottom row has a bottom
line. I'm talking about cell outlines, not underlining of words in the cell.
Anyway, I've told the print option to repeat the first 3 rows on following
pages but the bottom outline does not seem to repeat. How can I fix this?
Thanks,
Keith Tag: Default File Location for Code Tag: 141960
zoom preview
Is there any way to zoom more than just 2 states (in and out) in an Excel
2003 preview? In so many programs there's a % option but not in Excel. Very
sad indeed and very frustrating. I'm wondering if there's some hidden
setting that I don't know about. Tag: Default File Location for Code Tag: 141959
using RANK
I know i can rank a column of numbers from largerst to smallest.
Can I use rank for smallest to largest? Any help appreciated.
Skip Tag: Default File Location for Code Tag: 141958
convert PDF file to excel 2007 file
Anyone know how to convert a PDF file to an Excel 2007 file? Need help.
Thanks Tag: Default File Location for Code Tag: 141950
Setting up questions & answers
Any pointers would be most appreciated. I'm wanting to create exams online
and have been told Excel will do this. For instance 20 questions with each
question having to choose the correct answer from a number of possible
answers. I want whoever to go through trhe 20 questions and only know how
many they got right by submitting at the end. They won't know which they
got right, only the number they got right. Any advice where to start with
Excel would be brilliant.
Best,
Paul B. Tag: Default File Location for Code Tag: 141946
Excel newbie needs help from math wiz (poker related)
I want to create a Excel workbook that will help in specific poker
situations.
Short version: I want to be able to calculate how often my opponent will
need to fold for an all-in raise to show a positive expectation.
Explanation:
In poker you can win a hand by having the best hand at the end OR getting
your opponent to fold.
A common situation comes up where you have a "drawing hand" and face a bet
by your opponent. In this situation you know you are currently behind in the
hand, but have a chance to win if you catch some of your "outs" (cards that
will give you the winning hand).
Example:
You have one of these drawing hands and it will become the winning hand
about 1/3 of the time if you stay in until the end.
There is $150 in the pot and your opponent bets another $100.
If you were to re-raise all in it would be for a total of $300 ($200 in
addition to his $100).
x% of the time my opponent will fold in this spot
I want to be able to solve for x (the amount my opponent will need to fold)
to show me the break even point of this re-raise.
When I re-raise all in he is only left with two options so we need to
account for both. He can call or he can fold:
100% of the time he folds I will win $250
2/3 of the time he calls I miss my draw and lose $300 (the amount I
re-raised all in with)
1/3 of the time I will hit my draw and win $450 ($150 in the original
pot+his $100 bet+his $200 call of my all in re-raise)
So, how can I create an excel workbook/calculation that will calculate the %
my opponent will need to fold for my all in re-raise to break even?
Thanks in advance for any help with this. Tag: Default File Location for Code Tag: 141945
HLOOKUP-ish?
I'm stumped. I know you can't do an HLOOKUP if you're not starting at
the top of the range. I have a worksheet (called "Data") that
contains 3500 rows of data. There are multiple "departments" within
this data. I want to be able to look up a specific "department" and
return the result in the row beneath it (on the worksheet "Results"),
even if the department isn't the first row. Here's an example:
From the "Data" worksheet:
1. 2008149Bob
2. 2008149Donna
3. 2008149Annalisa
4. 2008149Lindy
5. 2008153Total
6. 2008153Joe
7. 2008153Marty
8. 2008153Larry
9. 2008153Anne
10. 2008153Owen
11. 2008153William
12. 2008153Amy
13. 2008153Jim
14. 2008153Nina
15. 2008153Thad
16. 2008153Richard
17. 2008153Erica
On a different worksheet ("Results", I want to look up Department
153's Total (row 5) and return the results from the row beneath it (in
this case, "2008153Joe"). Because the departments aren't always in the
same range (sometimes Dept 153 will be in row 5, sometimes in row 116,
etc), I can't give the range a 'set' number.
Is this possible? Tag: Default File Location for Code Tag: 141934
Personal Macro Workbook Must Stay Open For Recording
I had the same problem. I solved the problem by opening Excel and save as a blank workbook to the following Directory:
C:\Program Files\Microsoft Office\OfficeXX\XLSTART
-where XX = Office version (probably 10,11, or 12)
Your directory might change if you did a custom install.
Name the file Personal.xls
Cheers Tag: Default File Location for Code Tag: 141929
Average of every 18th cell in a column
Hello!
My spreadsheet looks like this:
Column D
value1
value2
value3
...
Average1 of 1,2,3... (cell D25)
value4
value5
value6
...
Average2 of 4,5,6... (cell D43)...
Every 18th row, starting from D25 there is a "partial" average. Now, I
want to calculate the total average from them, but...
It is a template, so there actually is a value in cell D25 and D43
(25+18), but in the following cells, there only is the formula for
average calculation of future entries. In cells D61 and further, there
is an error message (dividing by 0).
What would be the formula to calculate an average from every 18th cell,
starting with D25, ending by, say, D2000, and considering only the cells
with an actual value?
Thanks in advance!
Peter Tag: Default File Location for Code Tag: 141928
Data Validation
I want to use data validation on a cell where the user MUST enter any
four digits, leading zeros are permitted.
Can someone explain how I do this? Tag: Default File Location for Code Tag: 141923
strip characters from a "number" like string
Hello
Could someone advise as to how to strip characters from a formula
resulting string which could look like the following? ( some of the
result would actually be numbers while others are number/character
combo's - see below for examples)
0002
0124
0125-A
1566A-A
Thank you for any assistance you can offer. ! Tag: Default File Location for Code Tag: 141920
Sum Unique Values in a Pivot Table
Hello
I have a spreadsheet with several records for each machine's name as
illustrated below:
Server Name / Database Instance Name / CPUs / Concurrent Access
______________________________________________________________
Larry / Larry1 / 8 / 5
Larry / Larry2 / 8 / 50
Larry / Larry3 / 8 / 10
Larry / Larry4 / 8 / 1
Amy / Amy1 / 2 / 10
Amy / Amy2 / 2 / 5
Amy / Amy3 / 2 / 35
Larry / Larry1 / 16 / 500
Derron / Derron / 6 / 25
I want to have pivot tables with "Server Name" in a ROW and two DATA fields:
- Data field 1 : a sum of the total "Concurrent Access" for all database
instances on the Server (i.e. 5+50+10= 65 for Larry)
- Data field 2 : a sum of "CPUs" per unique occurrences of Server Name (i.e.
8 for Larry and not 8+8+8)
Is there some way to do that in a pivot table?
Thanks Tag: Default File Location for Code Tag: 141914
Custom locking of columns and panes [Excel 2007]
Hello!
Is it possible to lock columns and panes so that they NEVER move?
Normally, locked panes remain visible when scrolling up and down, while
locked columns remain visible when scrolling right and left. I`d like to
have the first 8 columns and the first 5 panes visible simultaneously
even when I go to, say, cell VX12345.
And a second question:
Is it possible to lock a number of randomly selected columns? For
example the 1st, 5th and 8th column.
Greetings
Peter Tag: Default File Location for Code Tag: 141913
Formula Error - Assign number to unique values.
I have a list of codes in F$19:F$1500.
I was attempting to use the following code to assign a number to each
unique value in the list and repeat the previously assigned number if
it is duplicated.
This works for the first 15-20 numbers, but returns an error (#n/a)
afterwards as if the code stopped working.
I have tried EVERYTHING... Same result each time.
=IF(COUNTIF($F$18:F19,F19)>1,VLOOKUP(F19,F$18:G18,2,FALSE),MAX($G
$18:G18)+1)
Any clues? Tag: Default File Location for Code Tag: 141902
Filter on non-blanks in a range (multiple columns)
I have a spreadsheet that has offers on parts from multiple
clients. The spreadsheet is 12,000 lines long. I am looking to
select on a range (of offers) and the highest offer gets put into a
BEST OFFER column on the same row.
Example
Part Number BestOffer Offer#1 Offer#2 Offer#3 Offer#4 Offer#5
xxxxxx-001 $13.00 $1.00 $5.25 $10.00 $3.50 $13.00
xxxxxx-002 $5.00 $1.25 $3.00 $5.00 $4.75 $8.00
The spreadsheet (12,000 lines long) has to remain in the same,
original format as it came in BEFORE I received these offers.
What is the best bet to address this? Tag: Default File Location for Code Tag: 141900
Cannot include "[" or "]" in filename path???
When I try to save an Excel spreadsheet to My
Document/[Activities]/2008-05-05 I recieve an error that some characters are
not allowed. [ and ] are both valid characters for a file name.
What can I do to get Excel to accept these characters at the SaveAs dialog
box? Tag: Default File Location for Code Tag: 141897
Separate at first number
Have a string of codes similar to below.
Any way to separate (LEFT, or RIGHT) all text to the right of the
first number in the cell?
(Some codes have more/less characters before and after zero, so using
right or left by itself may not work.)
apples12havocx
cherries84toughx
plums45easyxx Tag: Default File Location for Code Tag: 141892
find duplicates and add some data
I've got two Excel sheets containing data about employees. Sheet A
is
a complete list, and Sheet B is a shorter list with only employees
who
are in a certain job category. I need to compare the two lists--both
have Employee ID numbers as part of the data set. Where Excel finds
a
matching ID #, I would like to append the job title that appears in
Sheet B into a blank column on Sheet A.
I'm thinking this could be done with some combo of an IF statement
and
VLOOKUP, but I haven't gotten any further than that. Any help would
be greatly appreciated! Tag: Default File Location for Code Tag: 141891
Not reading pasted values?
I have had this problem a few times before and I haven't been able to
find the answer...
I had a list of formulas and results in the column next to it (B).
I copied the values into another list (C) and now am referring another
set of formulas (d) to point at the pasted results. The new formula's
dont work (#N/A - error) unless I go down to each pasted value and
click in that cell and hit enter.
It's like saying that the cell values aren't real until I validate
them by hitting enter in those cells? Tag: Default File Location for Code Tag: 141888
When I export code, they currently default to Program Files>Microsoft
Office>Office10. Is there a way to specify a new default directory
for my code?