2nd to last value in column
My question has to do with the 2nd to last cell with data in a column. To
get the last data, I am using "=LOOKUP(10^100,A2:D2)" but how can I get the
data from the cell right above that?
For instance, my data looks like this (multiple tabs for different entities):
# of Accounts 1/1/10 45
$ of Accounts 1/1/10 6300
# of Accounts 1/2/10 23
$ of Accounts 1/2/10 1550
So, I want my totals page to show the last 2 entries (# and $).
TIA, any help would be wonderful,
Try the below to get the data from the cell right above that?
Can anyone tell me how to create my data values to show
the total of the data points instead of each individual
> Can anyone tell me how to create my data values to show
> the total of the data points instead of each individual
> data point?
Are you attempting to label each point of the chart with a total? If so,
you can create a helper cell (or cells) with the total that you want
displayed on the chart. Then use the XY chart labeler from www.appspro.com
to label your data points with the cells you just created.
dvt at psu dot edu
I will look into ...Validate for Null value for multiple column
I have requirement to validate for Null value which compares Column A & B
entry in excel sheet. I have tried by setting IgnoreBlank = False, but no
success. How do I validate for Null value in either Column A 0r B.
...Finding value in the last cell in column A
I have this expanding Excel-database, and on all occasions I want to
use the latest (max rowindex) value of the datainput in column A.
I'm certain this is a-piece-of-cake, but what kind of formula is to be
=LOOKUP(99^99,A:A) will show the last number in column A
=LOOKUP(2,1/(A1:A65535<>""),A1:A65535) will show numbers or text.
Hope this helps
> I have this expanding Excel-database, and on all occasions I want to
> use the latest (max rowindex) value of the datainput in column A.
> I...Annoying & unrecognised value
Hoping someone may recognise this.
I regularly import data in to excel for use with various look up /
count / if functions. Quite often I find that where I have pasted in
values that they are not recognised until I go to the specific cell and
hard return the value. I have tried cell format; general, number, text
ect but makes no difference. I normally use paste special, values only
so as not import any format or formula. Can be a real pain on large
data sets........... mouse point, enter, next cell, mouse point, enter,
next cell, ......
Ay ideas ?
------------------------...Filtering Combo #2 from a value in Combo #1
I have two Combo fields:
cmb1 (which has a choice of PROD / DEV)
cmb2 - I want to list dates associated either PROD or DEV.
I have this SQL for cmb1:
strSQL = "SELECT DISTINCT tbl01_FullCompare.ShutType,
tbl01_FullCompare.ImpDate FROM tbl01_FullCompare WHERE
tbl01_FullCompare.ShutType = "" & Me![cmbShutType] & "" ORDER BY
Me![cmbImpDate].RowSource = strSQL
However when I i select the cmbImpDate drop down box, there are no records
listed... can anyone advise how to get this working?
Any assistanc...Is it possible to use 'grid' as a value in vb to process XL data?
I was processing the Excel data that has 'grid' as a value, When I used the
VB macro to process that particular row was missing. When I renamed this to
'grid1' the result appears with 'grid1'.
Is there any reason for this?
...Validation based on the value in another cell
How can I base the validation in one cell on the content of another
If value is cell C9 = "Level 1"
I want the validation in cell A3 to force the user to enter a value
equal to or greater than "8000"
If value is cell C9 = "Level 2"
I want the validation in cell A3 to force the user to enter a value
equal to or greater than "4000"
What if C9 is neither "Level 1" nor "Level 2" ?
Microsoft Excel MVP
"supersub15" <firstname.lastname@example.org> wrote in message
news:4d...Data Validation #VALUE
Even though I have data validation to accept only whole numbers, if a user
accidentally enters text, other cells that contain formulas based on this
cell change to #VALUE. The text is not allowed to be entered in the cell,
but the formulas in the other cells apparently read that text.
Don't the #value's only momentarily show up. Then you get the warning message
from the Data|Validation check.
Then as soon as you hit Retry or Cancel, the #values turn back to the previous
I wouldn't worry about it, but you could change your formulas...
=if(istext(a1),"",your...Error retrieving remote system values
i am trying to retrieve remote system attributes using WMI but the
problem is i'm not getting return values. please find the code snippet
below. please let me kno what went wrong..
IEnumWbemClassObject* pEnumerator = NULL;
hres = pSvc->ExecQuery(bstr_t("WQL"),
bstr_t("Select * from Win32_OperatingSystem"),
WBEM_FLAG_FORWARD_ONLY | WBEM_FLAG_RETURN_IMMEDIATELY,
ULONG uReturn = 0;
HRESULT hr = pEnumerator->Next(WBEM_INFINITE, 1,
...copmare cotents of two columns and return dissimilar values
I want to compare the contents of column A with Coulmn B and to get
I will be thankful for your kind information.
In cell C1 enter
and copy down
"Shaukat Hussain" wrote:
> Hi All,
> I want to compare the contents of column A with Coulmn B and to get
> dissimilar values.
> I will be thankful for your kind information.
...Count if date is before value?
I have a roster of employees and there is a column for the date of thei
last annual training. I am trying to have a total on the bottom of th
column that counts how many people are delinquent (havent had the clas
in over a year) in their training.
Looks like this:
Name Class Date
John Sexual Harassment 4/15/2003
Harry Sexual Harrasment 3/17/2003
Don Sexual Harrasment 4/1/2004
2 would be the total if the date was set to 4/15/2004
I have tried COUNTIF(C2:C107">4/15/2004")...Look & return values, in cells with numbers & words
I have a problem in a table that has words and numbers:
ex.-> Puxador 3584.251 Jkf
I want that it reads only the 3 last numbers and returns to onother cell the designation:
ex.-> Puxador 3584.251 Jkf &( Designation i read and recognize as 251)
How can i do this?
Thanks in advance
If the three numbers you are wanting are always preceeded by the only full
stop (period) in the cell you could use:
to return them. It really depends on if your cells are uniform in content.
"Micos3" <Micos3@discussions.microsoft.co...How do I change default attribute values from with in SQL Metabase
I need to change a bunch of bit defaults from yes to no and was hoping to do
it from within Enterprise Manager quickly instead of going in and out of
every field withing CRM. Anyone know the table I need to look at where this
is stored? I looked at the attribute table in the metabase which seems to
have a column called default value but this does not store the default as I
looked at default settings of two bit fields I have one set to yes and one
set to no and in the attribute table there is no difference that I can see.
Updating the tables on the back end is not supp...Display only duplicate values and delete UNIQUE Items #2
I have large excel file - which has duplicate items in, I would like
to compare col A and Col B and if any of the items in apear in that
combination anywhere else in the colums then i want either a helper
col to show its is a duplicate or to highlight it in a different
colour. Ideally as as this will be done by may people ( who will not
follow instructions on how to do this ) i would like for them to just
open up a file and then press on a button ....
A | B| Help Col
1 | 5 | Duplicate
2 | 6 |
3 | 7 |
4 | 9 |
1 | 5 | Duplicate
1 | 5 | Duplicate
I can suggest a way to...Excel drop-down values versus labels
I can't figure out how to duplicate Web-based Select boxes, as drop-down
lists in Excel.
I can create the drop-down list using Data | Validation | Settings,
selecting from Allow the opion List (Ignore Blank and In-Cell dropdown
checked), and then enter into Source: Standard, Large, Extra Large (each
item separated by commas).
But I can't figure out how to calculate which item is selected without
duplicating the data, It seems inefficient to duplicate "Standard, Large,
Extra Large" elsewhere, and do a lookup.
1. Is there not a function which will return the Nth it...Enter Different values in Project Server and get in Reporting
I want to know actually I will create a report in reporting tool.
I want to enter different values which may be numeric and text and may be
select from drop down list which i will enter in my project at project
and when the report will be create in reporting tool.
the values which i entered in my project may be in project information
dialog box and other way will be display at report.
my first question is
How I can enter different values which for my project in project professional.
and these values will be at published database or in reporting database or I
...Changing the values in a column
I have several thousand rows in my spreadsheet.
My question is that I want to look at the value of one column and based on
its value, change the value of another column.
I would I do this?
From what to what?
"Dirk_Bob" <DirkBob@discussions.microsoft.com> wrote in message
> I have several thousand rows in my spreadsheet.
> My question is that I want to look at the value of one column and based on
> its value, change the value of another column.
> I would I do this?
&g...macro to hide columns with zero values?
I'm relatively new to VBA and need to create a macro to hide columns with
zero values. The workbook has 36 sheets, each sheet has columns B:CR and 202
rows. Row 202 is the sum of the respective column.
I'd like to run the macro in a separate workbook so I can use it for other
similar workbook situations.
Any suggestions would be greatly appreciated!
I believe the code below will work for you. It will examine all used cells
on the row on a selected sheet where a cell is selected and hide all columns
with an empty cell or a zero value cell on that row. In your example, ...Importing MS Office Excel Comma Separated Values Files into a MS Access 2003 database
I have been tasked with Importing MS Office Excel Comma Separated
Values Files into a MS Access 2003 database. These files were exported
from a DB2 database.I have also been given the DDL code. I have
Googled a solution to this task and have tried creating these tables
using the DDL code in a query within a blank MS Access database. Is
there a more expedient way to achieve this? I noticed, for example,
that a field in DB2 has a data type of "decimal" while the same field
would have the data type of "number".
What follows is the DDL:
-------...How do I change FALSE values to 0?
How do change values of TRUE or FALSE to 0?
what is the formula you are using?
> How do change values of TRUE or FALSE to 0?
> what is the formula you are using?
> "Redleg40" wrote:
> > How do change values of TRUE or FALSE to 0?
Put a 0 in for the value_if_false argument. Standard IF syntax
=IF(condition,value_if_true,value_if_false), so for your first formula
----------------------------------------...comparing values from one workbook with another workbook
I have 2 workbooks. workbook1 contains a list of employees, emp# and date of
hire. Workbook 2 has a differnt list of employees. I need to compare the
emp# in book1 against all emp# in book2. If the emp# from book1 matches the
emp# in book2, I need to copy the date of hire from book1 into the
appropriate cell in book2. I must compare the entire contents of book1
I assume employee # is in colum A and the date fo hire in column B so in
book 2 enter
copy formula down, change range to fit your n...Please Help!! Values Not Updating from linked source
I have a problem with a couple of spreadsheets.
When I open the master sheet I get the prompt to update or not. If I choose
either the values do not get updated. If I open the other spreadsheet that
my formula is pulling the data from the values update.
...Data Range with more than value
I am trying to assign codes to one of my worksheets based on pricing. The
problem that I am running into is that the data that I am taking my
information has a range of pricing Example below
PRICE POINT/RANGE PP
$0.01 TO $1 PP0100
I need to have a formula that looks at "PRICE POINT/RANGE" and compare it to
a column that has pricing (i.e .95). This formula/function should give me
the "PP" code.
Break the range up into two columns (Data - Text to columns). Assuming you
don't have gaps/overlaps in your price range (why would you?) your formula
...How to change the colour of a cell(s) based on cell value
I am stuck on this rather simple problem. Could not find any hel
I want to change the colour of a cell to green if the vallue is = Tru
and to red if it is False.
I have tried to use the Conditional Formating but without joy.
Artful Dodger's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1656
View this thread: http://www.excelforum.com/showthread.php?threadid=32059
select the cells
choose format / conditional formatting