Too Many IF Statements Nesting Error (Excel Formula Loop w/o VBA)
Hello Excel Problem Gurus,
First of all, let me thank you in advance. I find it exemplary that
you all can devote time to helping others who are having issues with
their work. Hopefully one day I can be at a mentor level, and help
Hope you can help! I have an issue where I don't know how to write the
formula that I need without going over on the nesting. The current
formula that I have is as follows:
Data",IF(V7="Yes",&qu...Tracking shared records
I need to create a report that shows which records are shared and with whom.
Can someone guide me in the right direction? I can't seem to find it anywhere
online. thank you
Sharing is recorded in the PrincipalObjectAccess table in the SQL database.
Advanced find does not allow you to access this so you will have to create a
report on the directly on the database.
Patrick Verbeeten (MCPD)
Extended Entity and Plug-in browser:
> I need to create a report that shows whi...Duplicate Rows
I have an extract from a student information system in Excel that
looks like this.
Student Class Grade Quarter
John Chemistry 70 1
John Chemistry 80 2
John Math 95 1
John Math 100 2
Alice Chemistry 67 1
Alice Chemistry 47 2
Alice Math 88 1
Alice Math 85 2
What I would like is this:
John 70 80 95 100
Alice 67 47 88 85
However, since there are hundreds of students, this would be an
extreme pain to do by hand. Is there any built-in formula or function
in Excel that can do this?
What is it that you actually want to do? (The best approach depends on what your desired end r...Add rows automatically? Accordion
Is there a way to automatically add/show rows that have data?
I have a data entry sheet. Then I have a report.
The report pulls data from the entry sheet.
If there is no data for a specific line/row item, is there a way to
automatically hide or not show the row(s) with no data?
can I have more than one autofilter on a sheet?
> Use the filter function
> Select the data and click on...
> This should make an arrow appear at the top of the data (in the header row).
> click the arror and select "Nonblanks"....Duplicate record in RM tables
We experienced an issue in Apply Sales Document that may have caused a
duplicate record somewhere. We found this when running Paid Sales
Transaction Removal and received this message:
Violation of PRIMARY KEY contraint PKRM3101. Cannot insert duplicate key in
I ran the RM duplicate tool found in the automated help area of this website
and found the following:
--- Begin copy here ----
Duplicates between RM Open and RM History
Document #: 07-003021-17 Customer #: 079100 RMDTYPAL #: 7
--- End copy ---
It looks like the duplicte tool also logs the qu...How to track ActiveControl.Name when switching records in form with multiple subforms
I need to have a global variable always contain the name of the
current form field. This bit of code is attached to the GotFocus
event of all fields and the Enter event of all subforms:
gxCurrentField = Me.ActiveControl.Name
However it doesn't work properly when changing records in a subform.
My parent form contains two subforms in a many-to-many relationship.
The above variable usually ends up containing the name of the first
field in the second subform when switching records in the first
How to correctly code this? Or is there some native variable I'm not
aware of? I...Format for credit card numbers
I've tried a custom format for entering credit card numbers (four groups
of four digits, separated by dashes: xxxx-xxxx-xxxx-xxxx.
I have tried to use ####-####-####-#### and 0000-0000-0000-0000 but
each of these causes the last digit to change to zero. So if I enter
5415779800902512 I get 5415-7798-0090-2510.
Anybody already solved this problem?
Schmacker's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28041
View this thread: http://www.excelforum.com/showthread.php?th...limit the number of outgoing messages
who can I limit the number of concurrent outgoing messages?
for example: if I have a queue of 1000 messages, how can I force
exchange to send only 50 messages at a time?
...creating a spredsheet and log the info into another spredsheet
I have a excel sheet that is printed out and a cashier manual enters
information, invoice number, invoice amount, cash amount, check
amount, amex amount.... I would like to have the cashier input this
information on her PC and print a copy to go along with the deposit
and at the same time log the information into a google excel document
I created. Any ideas how this can be done?
...Coloring a row
I have a spreadsheet and I want to have cells colored from column A to K if
cell h is not blank. So if h3 has a date in it I want A3:K3 to be say light
blue. This is for Office 2003. I can do it with conditional formating in
2007, but my work place doesn't have 2007. I did use column L and put an if
statement to give a true or false in the cell depending on if the cell in
col. h was empty or not. Any ideas how to get this to work?
This sort of thing will work in 2003 conditional formating. In Cell
A3 go to Format - conditional formattting. Formula is
Paste...Numbering in Book Files
I am creating a tech manual where each chapter is its own file. I have
created an outline numbered list and saved the list in a template that I then
use to create the new chapters. However, I am finding that when I go into a
new chapter that the outline numbering does not pick up on the chapter
(Heading 1) number. For example:
Chapter 1: Heading 1 style
1.1: Heading 2 style
1.1.1: Heading 3 style, etc.
But when I start my next chapter, when I update the chapter number to start
at 2, I get this:
Chapter 2: Heading 1 style
1.1: Heading 2 style
1.1.1: Heading 3 style, etc.
...Removing text from cells leaving numbers (help with function)
I need a function that will remove all text from a cell and just leav
numbers. Formatting cells to number does not work.
For example if I have:
(Sired] Tennessee 37013 (herein
I just want 37013 left.
Anybody know a function to resolve this
Message posted from http://www.ExcelForum.com
The following will strip the text from the active cell and place the number
in the adjcent cell one column to the left. If there are subsequent numbers
in the original string you will get erroneous results. Put the cursor on the
cell to be processed and run the macro.
********************************...Freeze the side column/top row & scroll others
what is the function to set (lock in or freeze) the first column and / or top
of a spreadsheet, so the words and numbers remain in the same place as you
scroll the other columns and rows.
(so you can add more columns..yet keep the main information in the first
In older versions of Excel, it is under Window.
In 2007 version of Excel, it is under View.
You first select a cell, then activate the command. Excel uses the selected
cell's upper left corner to define the freeze point.
Play with it. You can also Unfreeze panes that were fro...Formula to count the number of different values in a range
I'm looking for a formula that will give me the number of different values
in a range.
Example: Column A may have five cells that are "4", five cells that are
"7", five cells that are "9". Of the fifteen cells that contain data, there
are only 3 different values. I'd like to use a formula that will count the
number of different values in column A, in this case the result is "3".
=SUM(IF(A1:A15<>"",1/COUNTIF(A1:A...Add a secondary administration group error c1041721
I have added a new administration group for a sub site in Japan and now I am
getting the following error:
The action could not be completed because the Microsoft Exchange Information
Store service is unavailable.
Operation: Retrieving mount information
ID no: c1041721
Exchange Sytem Manger
I get this error when I try and drill down to the new admin group to the
First Storage Group. I only get this message on machines on my side of the
network. If I remote to the mail server in the new site I am able to drill
all the way down and see the mailboxes. I can also see our main site
informat...I configured news group in outlook 2003 but couldn't see it
I configured news group in outlook 2003 following the Help. I didn't finish
the server configuration and see all news subscribed. However after I closed
the configuration, I couldn't see the Go => News menu. What's worse is that
the Customize Command panel, the "News" command is available in the "Go"
Appreciate if anyone can help!
> I configured news group in outlook 2003 following the Help. I didn't
> the server configuration and see all news subscribed. However after I
&g...How to use different return email addresses
I use outlook 98. I have a mailbox with three aliases. People sending
me emails can use any of the three email aliases and the email will
arrive in the same mailbox. Accessing the mailbox once will retrieve
all messages regardless of which alias was used.
If I reply to these emails though, I want the recipient to think that
the email has come from the alias that they originally used. What
outlook always seems to do is use the email address of the service
that is listed first in Tools->Services->Delivery. Is there any way
Outlook can be set up so when I reply to a given email, my email
...Locating a number for a MS office
I have lost the boklet that accompanies my CD. However, I
am unable to locate a number to contact someone. Any
I think if you ever learned how to contact them directly, they'd put a hit
out on you.
(Seriously, visit the MS website and drill down through the tech support
area until you find the numbers you need. Good luck.)
The problem with resting on your laurels is that eventually you are sitting
on dead branches.
"ziggy" <email@example.com> wrote in message
> I have lost the boklet that...how can you add more rows in excel 2000?
Is there a way to add more rows to excel's already 65536 rows? In other
words can you expand excel to have more rows then it already has.
HTH. Best wishes Harald
"Khody" <Khody@discussions.microsoft.com> skrev i melding
> Is there a way to add more rows to excel's already 65536 rows? In other
> words can you expand excel to have more rows then it already has.
Unfortunately, you can't increase the number of rows. Versions 2002 and 2003
have the same limitation.
"Kh...Difference between Publish and Save As Web Page in Excel 2002
I am trying to find aout the differences between using the Publish button in
Save as Web Page and using Save in there. I have seen that when I use Save,
it saves the Named ranges as bookmarks where Publish saves them as cell
references. Can you tell me why and if there are any other differences I may
need to know
when you publish, the result can be viewed as an excel web
page, whereas if you "save as web page", it just makes an
xml sheet. Save As only allows one sheet in the workbook
whereas publish allows more.
>I am trying t...One table with two date fields returning records in a date range
I have a table that has several columns, two of which are date fields. A
start_date and comp_date for start and completion date.
I need to run a query that will return all the records between two dates
selected by a user. Not hard, piece of cake. The problem I am having is that
my query isn't returning all the records that overlap a given date range.
I select a date range: June 1 to September 1.
My query will return 10 records that start and stop between June 1 and
September 1. This is good and correct, but....
...I have several records that start before June ...Number colours
Is it possible to change to colour of a number to indicate whether it was
positive or negative? IE - blue for positive numbers and red for negative.
Thanks for any help.
Gord Dibben Excel MVP
On Thu, 11 Dec 2003 16:32:12 -0000, "The Stoat" <firstname.lastname@example.org>
>Is it possible to change to colour of a number to indicate whether it was
>positive or negative? IE - blue for positive numbers and red for negative.
>Thanks for any help.
&...public view which displays all the records under a business unit?
we have a requirement where a user should be able to see all the
records created under his business unit by selecting a public view. He
must be able to see all the records created by other users belonging to
his business unit.
If any one has worked on this or faced the similar requirement, please
revert back. Your help would be highly appreciated.
Thanks in advance
...Record Count Help
I have a form to track Dispute Calls that come into our call center.
Each call is an individual record saved to a table.
However, there are instances where I'll need multile records to count as 1
dispute. This way if a client calls 5 times about the same dispute, the query
or report will count it at 1 and not 5.
I thought about making a seperate textbox to manually input a number and
have the query or report search for unique instances of records. But I'm not
sure how to do that either.
Assistance please? Thanks.
You might want to include a field "DisputeID&quo...external and internal port numbers
cx Im seting up file sharing between my desktop (host cmputer) and my laptop
...I'm asked to insert external and internal port numbers. Are those the same
as the ones I have entered in my email setup??
> cx Im seting up file sharing between my desktop (host cmputer) and my
> laptop ..I'm asked to insert external and internal port numbers. Are
> those the same as the ones I have entered in my email setup??
No. Different application. If it's the file sharing built into the operating
system, you'll want to ask in a newsgroup about that OS.