Using cell reference with logical operator in DGET expression
I am using DGET to search an array in the worksheet to find a particular
percent to use in a calculation elsewhere in the spreadsheet. The row members
of the array contain a series of from and to values that I use to identify
which row has the percent I am looking for. I have defined the array as a
range and I can use the logical operatirs with numeric values in the range
criteria and everything works fine. For example, I can use <500 in the
appropriate cell in range criteria to find the percent to use when the value
for that column in the array is less than 500. All working f...reference cell above even if row deleted
I would like to be able to reference a cell imediately above the
current cell even if the row above that cell had just been deleted.
contents of A5: =A4+$B$1
then delete row 4 and rather than have contents of A4:#REF!+$B$1
have it A4:A3+$B$1
Is there some way to do this?
Thanks for any tips.
You can use the OFFSET function: =OFFSET(A5,-1,0)+$B$1
John Keith wrote:
> I would like to be able to reference a cell imediately above the
> current cell even if the row above that cell had just been deleted.
> For instance:
> conte...Getting a subform control to requery
Main Form : F_BU
When I click on one of the records in subform1 (field BillCat) the OnClick
event uses the value in the field as a criteria in a query that is used in
the combox (cbxCat3) in Subform 2. Here is the code I was trying to use:
It was working for a while but now not when I click on the "BillCat" field in
Subform 1 I get: Method: Form" of Object ' _Subform' Failed.
Message posted via AccessMonster.com
I am trying to reference a cell in a formula and I would like to say anything
greater then cell J2 but when I input this it searches for text.
A B C D
1 00001 100 40248 40248
2 12001 150 40237
3 15001 200 40237
4 00001 150 40290
5 00001 50 40350
I would like the total to return 200 because Cell C5 and C4 are larger than
Cell D1 in respect to the Sku number I would like to sum. But when I put in
>J2 it enters “>J2” and it won’t return a value other then 0. (If I put in
just D2 then it returns a valu...We are getting stat=Deferred: Connection reset errors .....
Does anyone know how to fix this issue? I am getting a lot of emails BUT an
Exchange admin from another company that sends SMTP email to is saying that
he sees a lot of stat=Deferred: Connection reset error messages in his log
very very weird. Has anyone seen this issue? He can telnet to the server
fine etc... plus I am getting email from his company but it is deferring aa
What version of Exchange and SP level
Exchange 5.5 SP 4 is our IMS.
"Oliver Moazzezi" wrote:
> What version of Exchange and SP level
>...can I get "step by step" on color seperating for commercial printi
This is kind of an extension on my previous message but I feel it would help
a lot more. Can someone give me a quick step by step on making a document
with text and photo into a press quality color seperated piece using PMS
colors and process black? Or maybe point me to a site that can give step by
step on that stuff?
I have created a form using a table. I want to lock or
block cells that should not be changed.
How do you do this?
...Letter Writing Assistant selection based on Smart List Lookup
We had a great opportunity to use the letter writing assistance to send out a
notice to any vendor used within the last 6 months. Using the smartlist
lookup up we can see those vendors we have issued a check to based on Last
Check date, but the Letter Writing Assistant only allows you to select
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the ...Where can we get xml scheme for tuning setting of WM2003/WM5.0 PDA
We are working upon setting up a lot of WM2003/WM5.0 empowered mobile
devices according with company standards. As far as I know these settings are
tuned through xml file. Then this xml file has to be deployed
deployed/executed onto mobile device. My question is where xml scheme for
tuning such setting is published so we may assign necessary properties
Thank you in advance.
...Cell Protection #8
I am using the UserInterfaceOnly to protect my worksheet. I have it placed
in the workbook object. Unfortunately, after the workbook is open the user
can Tools>>Protection>>Unprotect Sheet. Is there a way to password out this
I would like the user to have access to only the standard toolbar, is there
a method to stop them from enabling various toolbars, menus options and
manipulating the sheet, etc.
Wouldn't it be simpler to use a password that the user doesn't know?
And don't forget to protect your project.
In th...Sorting in select query. manually
I have a select query and I would like to sort on the field month. but I
want to be able to specify the order myself (not descending nor ascending).
how can I do this?
You create a table with the data you which you would see in the Month field
and the sort order that you want. You then join these two tables in the query
and sort on it.
Something like this:
and so on.
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycl...Excel & Word auto selection from menus
Ok, I'm a keyboarder, not a mouser. I use quick commands
like the letter code or arrows on menus to speed along
without the grab, aquire, click business.
Excel & Word seem to be periodically (not always)
automatically selecting menu items based on prior
selections if I use arrow selectors. How do I turn this
off? I get no help from "Help".
Please reply to my e-mail address:
email@example.com. Thanks! Would love to get rid
of this nuisance & keep speeding along.
...How to get Global Notification of browser request...
I am trying to get notification of any browser request... (not only that
of the local server.. so filters will not be the right way to solve problem)
So, whenever any request like (http://www.yahoo.com/) is made in IE, I
want my DLL routine or my EXE should be called.. So, how to do that, any
suggestion will be highly appreciated...
the program you need is called Browser Helper Objects
Command what is yours
Conquer what is not
Microsoft Most Valuable Professional [VC++]
h...How do you move the curser around in a cell
What do mean by "cursor" and what do you want to do? HTH Otto
"krisf" <firstname.lastname@example.org> wrote in message
...Clause order in SQL select query
Are there any rules that govern clause order? Does the
Obviously SELECT must be first but does WHERE have to
precede ORDER BY?
This question arises because I have a situation where
the VBA code would be easier if the WHERE could be the
final clause. That way I could have an unchanging
SELECT, FROM, ORDER BY string and just tag the WHERE; on
the tail. My thinking then turned to whether there is
a mandated order for other clauses too.
Is there an article somewhere to explain it?
remove nothing for v...select query of parent and child in one row
I have a parent table and a child table
one parent has between 1 and 6 children in the child table I wont a
query to have the parent and the child in one "row"
how do I create that?
thanks for helping me
Your table structure is not correct for a relational database but this will
get you there.
This has three but you get the idea and expand on it. Place the table in
the design grid as many times as you have field to combine.
SELECT MultipleFieldData.x, MultipleFieldData.a & IIf(MultipleFieldData_1.b
Is Null,Null," " & MultipleFieldData_1.b) & IIf(MultipleF...Emails getting stuck in Outbox
I am sending a number of emails (around 600) using the vb.net code below.
The problem is that the emails end up in the OL2007 Outbox with a clock icon
and do not go out. If I open one of the email items and click Send then that
item goes out but this technique is too cumbersome for 600 or so emails.
What is the problem and how can I fix it?
Dim objOutlook As Object = CreateObject("Outlook.Application")
Dim NS As Object = objOutlook.GetNamespace("MAPI")
Dim objOutlookMsg As Object
Dim BodyText As String
...Flag a value in a group...
Hi all, tia for any insight.
I have the following query that gives some totals for qty's in a releases
table, pulling and grouping information (part number, rev and desc) from a
details table. Table heirarchy is (one to manys) tblOrders ->
tblOrderDetails -> tblOrderReleases
Min(tblOrderReleases.fldDueDate) AS cfldFirstDue,
LEFT JOIN (tblOrderDetails
LE...mass definitions of cell names
Ok, I have a problem. I have to name a large number of cells in
different sheets. I'm hoping that there is a way in a macro, or some
other way to define these in an easier way other than one by one. Here
is an example:
1 2 3 4
1001 --------- --------- --------- ---------
1002 --------- --------- --------- ---------
1209 --------- --------- --------- ---------
1210 --------- --------- --------- ---------
This would be the column and row headers for the cells. Below is how
the...Moving a line of data when information is entered in a specific cell.
I am trying to create a spreadsheet to keep track of problems that we
encounter at our volunteer organization.
It is pretty simple.
I am having one problem. One of my fields is "completion information."
What I need is when completion information is entered into the field, I
want that line of information to move to an "archived sheet".
In effect, so that I have a sheet with only active problems and when
the completion information is entered it moves to the archived sheet.
Anyone have any suggestions?
For some reason when a client is sending UUENCODE type messages to my
Exchange Server the Clients can not ready the Email or the Attachments. All
Attachments are arriving with the same size and when opened display as a X.
Has anybody seen this problem before
...How Do I specify where XML data gets imported into Excel
I have XML data that I would like to import up as fllows in a spreasheet:
Cell A2: Job Number
Cell A3: Customer Number
A4 through A29 should recive the <AMOUNT> data
B4 through B29 should receive the <QUANTITY> data
Sometimes all the fields will be filled, sometimes not. Any help would be
You should see therre: microsoft.public.xml
...Set a default value to Drop Down Box
Is it possible to set a 'default' value to a drop down box, so that n
matter what value was selected when it was saved, when the workboo
opens it changes to the default value?
I have a written a macro to do this for all the input cells that ar
not drop downs, but cannot figure out a way to change values in dro
Message posted from http://www.ExcelForum.com
What's the default value? The first on the list?
Dim wks As Worksheet
Dim myDD As DropDown
For Each wks In ThisWorkbook.Worksheets
...Calculate per minute cost of cell phone usage
I have a spreadsheet that I=92m trying to use to calculate the cost per
minute of cellular calls. I have 3 plans (in rows 3, 4, and 5) that
each provide a monthly cost (column a), and the number of minutes
included in that cost (column b). There is also a cost per minute if
you go over that allowance (cell E2). I have a field (cell C7) that
use to enter the average number of minutes used. I have a drop down
list (cell C8) that allows you to select the plan you have. I have a
formula that is currently written as:
C7 =3D average number of ...Value pack?
I'm looking for the Office X value pack with the german/french dictionaries
and so on, I seem to have deleted mine by mistake. Could someone maybe mail
it to me?
In article <BB81381D.email@example.com>,
"? Nicholas.Prisi ?" <firstname.lastname@example.org> wrote:
> Hi all,
> I'm looking for the Office X value pack with the german/french dictionaries
> and so on, I seem to have deleted mine by mistake. Could someone maybe mail
> it to me?
The Value Pack is a folder on your install CD.
Yeah, unfortunatly I lost my cd whil...