Lookup from a range on separate sheets
I am trying to look up values from the same fixed range (c50:E200) in 45
sheets and extract the values onto one sheet (same workbook) 3 columns at a
time (ie first sheet B-D, second sheet E-G etc). I can't work out how to put
the sheet name (or a reference to the sheet number) in a formula in the
destination sheet - is it possible or do I have to use a macro? I can write
the code for a macro, but as it is statistical process control limits which
are added to each month, it means rerunning the macro every time
Any help much appreciated!
In the sheet/cells that ...Using a formula to fill a field
I am ery new to Access and was wondering, is there anyway to make a field
fill automaticaly with a formula? Like in excel for example if you have a
fromula on a cell and the arguments change the cell changes.
I have a table with a field of invoice amounts and then a table with account
info, i want to know if there is a way for the account table (ie: Balance
field) to update automaticaly when you add an invoice with that accountID.
Access is a relational database. An Access table may look like a
spreadsheet, but it is really just a "bucket o' data". No, you can't ad...Lookup/Find help
Windows XP Professional
Hypothetical, but hopefully you'll get the gist of it:
I have two worksheets. On worksheet #1, I have two columns. First
column is a list of entire workgroup by name and 2nd column is the hours
worked. On the second worksheet I simply have an list of names that
is a subgroup of those on the first page. These indicate a target group.
Now, on the first worksheet, I want to add a third column for summing
only the target workgroup. Basically, I need a function...Hidden fields in new emails
When I begin to type a new message, the text doesn't appear (the words are
hidden) until spell check/sending/or printing. When I select the icon for
the font color of text (black) it begins to show my typing as I'm composing
the message, but the other words (anything I typed in the 'hidden field')
only shows up in the spell check portion and remains hidden. Why are my
messages starting with this hidden field and how do I get rid of it? I'm
sure I must have pressed some command inadvertently as it was not always
working like this. (only been using about a week so s...Can I set up a calendar lookup for date selection
Is it possible to lookup a calendar for date selection on a cell. I cannot
find any reference to it in the help.
try this url
"Tangoxray" <Tangoxray@discussions.microsoft.com> wrote in message
> Is it possible to lookup a calendar for date selection on a cell. I
> find any reference to it in the help.
...Auto Filling Fields in Current Record from Previous Record
I have looked through posts related to Auto-Filling a field from a
previous record but am still a little unsure of how to do things.
What I would like to do is auto fill a field in the current record
with one from a different field in a previous record. What I have is
a form that records "engine hours" and "tonnes moved" for a piece of
equipment. The operator will record the staring hours and tonnes (at
the start of a day) and the hours and tonnes at the end of the day.
So in the database, I would like the "Starting Hours" and "Starting
Tonnes"...Sorting Issue (Mixde Fields)
I have an issue where I need to sort a worksheet based on a field (Bin
Location)that is sorting as seen below...
How can I get Excel to sort it the following way...
Please help.... I need to get this figured out today.
I appreciate any responses.
One way: add a column showing the length of the column you want to sort, then
sort using the new column then the "real" column.
"Jonathan G." wrote:
> I have an issue where I need to sort a worksheet based on a field (Bin
> Location)that is s...changing lookup columns
Anybody know a way to add a column to the LookUp screen for Accounts? When I
am in a contact record and want to choose a parent account, I would like more
columns to distinguish between the different accounts that come up. This
isn't the Advanced Find or Associated View for Accounts so I can't see where
to add the columns. I've even checked the QueryBase table and don't see any
one that looks like it applies. Thanks.
There is no supported way to do this in v.1x.
Check this folder if you feel curious:
"A...Modified Reports Fonts
We are running Great Plains 7.5 on Terminal Server 2000 and on client PCs
running Windows XP Pro. From a Terminal Server install, user A has been
printing a modified report (cash requirments) for a couple years with a
readable font. Recently we installed GP on her local machine. Now when she
prints, the font is somewhat readable but noticably different then the
terminal server GP. She is using the same printer and print drivers from
both locations. User B prints the same modified report from terminal server
and from a local install and prints to the same printer as user A, but has ...Outlook 2003 cannot see other email addresses in the to field
Outlook 2003 cannot see other email addresses in the to
field - when someone emails me and a group of other
people, I only see my email address. I cannot reply to all
and send the other people an email. Is this a bug?
No, they probably used the BCC field or a mail merge instead so only your
address shows up.
Roady [MVP] www.sparnaaij.net
Microsoft Office and Microsoft Office related News
Also Outlook FAQ, How To's, Downloads and more...
Tips of the month:
-Save Embedded Pictures in Their Original Format
-Create an Office XP CD slipstreamed with Service Pack 3
"Jenni&...How do I create drop down form fields in Excel?
Is it possible to create dropdown lists in Excel? If so, how do I do it?
You can use Data Validation to create the dropdown lists. There are
instructions in Excel's Help, and here:
> Is it possible to create dropdown lists in Excel? If so, how do I do it?
Excel FAQ, Tips & Book List
...Adding company name to the "From" field
Hopefully this is an easy problem. We are running Exchange 2003 on Windows
Server 2003 in a mixed domain. (Win2k and Win2003 servers)
Lets say I am working for a company named "XYZ". The owner of the company
wants the "from" line in the email client to say "Bill Smith, XYZ Company".
Unfortunately I can not find a way to do this. Any help is appreciated.
Just use ADModify to rewrite the Display name for every user in your
company, or for users of that company (if companys are sharing and AD)
ADModify can be found here:
http://www.gotdotnet.co...edit suggest names when completing To, cc and bcc fields
Apparently I don't have an Address Book linked to Outlook
2000. No matter, because I seem to be ok with the suggest
names feature, which I guess is not Address Book-linked,
but linked to a memory bank of names and corresponding
addresses typed in during the past.
However, here is my question:
How do you edit this Suggest Names list? In particular,
A. Suppose you want to stop Outlook from suggesting a
particular name because it is not current anymore. Worse,
you might inadvertantly click on it and send msg to
somebody you don't want to receive it. Is there a way to
delet...Help...Need to modify data within a column in a .csv file
I have been given a .csv file of several thousand rows, and I need to
change column A of each. The brief cutout below shows column A of the
first two rows. (I need to skip the first row, which is the headers.)
Kinda clumsy to have such a wide column, but the application this file
is to be fed after I change the contents requires all the data
separated by double-quotes to be in the one column.
09/12/0509/25/05"1417386"101"900000005"222-22-2222"09/30/05"...Date range in months from month and year fields
In our database we track consultants work dates in four seperate fields,
starting month, starting year, ending month, ending year.
Is there anyway to calculate the range of months they have worked with this
So if someone started March 2004 and ended July 2007 is there a formula to
calculate the 40 months they worked?
DateDiff("m",[starting month] & "/" & "01" & "/" & [starting year],[ending
month] & "/" & "01" & "/" & [ending year])
> In our da...value from field not accepted within onlineform (3.0 -> 4.0)
We just tried to update our running CRM 3.0 to 4.0
there where nearly no problems! (1 report was not upgradeable)
We where very happy.
but right now a real problem was discovered:
the fields: longitude and latidude from the contact form are no more accepted.
Enter a value beetween ....
But the values do perfectly match within the range
When I delete the values I can save the form.
When there is any value in the fields it causes the error: Enter a value ...
Best regards for any help
...how do i semi-lock a group of cells to allow lookups formula...
how do i semi-lock a group of cells to allow lookups formulas without
allowing user changes
Do you mean you want to be able to reference these cells in lookup formulas
contained in other cells?
If so, leaving these cells locked (Format | Cells | Protection), and then
Protecting the Sheet (from teh Tools Menu) should be enough.
You may want to make sure you have Select Locked and Select Unlocked Cells
Checked, if a list of Protection Options pops up (Excel 2002 and later).
> how do i semi-lock a group of cells to allow lookups formulas without
> allo...Lookup #5
I am trying to label my bank info that I import into Excel. I have a column with the charge amount and a column with the recipient. I think a lookup table would work for this, but I need to have it check for wildcards. For example, the grocery chain is numbered here, so one might be GROCER 5454 and one may be GROCER 8724, so I need the function to look for GROCER*, right?
Also, if it does not fit any category, how can I get it to use "Other"? Do I make that one just "*"?
Mine is not working very well. Maybe my syntax is wrong.
...Related Fields in sub activities view
In the standard My Activities view I am able to display information from the
Account (Regarding) i.e. industry, city etc. When switching to Phone Call or
Appointment, the information is not displayed even though the facility is
there in admin to add these fields - is there a fix for this?
...Vendor Lookup Field with Modifier
I would like to modify the ap entry form and create 1 or 2mores field.
This additional field will be used to track related party vendor number/name
For example, the invoice is being paid to Vendor A. But because our invoice
for Vendor A may be related to Vendor B, I would like to enter this info in
the system. Currently, we just enter the related vendor ID in the note field.
But due to the large number of related party transactions, the users are
entering incorrect vendor numbers or they are not properly formatted , in the
note field. This makes it difficult to analyze related party...Parse field in query
I have a table called MasterData with a field called Originator.
The field data looks like this...
CN=Kathy T. Buon/OU=Miami/OU=BU/O=WC/C=US
Terry J Leif
I need it to look like this...
Kathy T. Buon
Terry J Leif
How can this be parsed??
What a mess that you're stuck with! Below will look for the first / and
return the string before it in a query. If there isn't a / it returns the
entire string. Of course this doesn't help with the CN= in CN=Kathy T. Buon.
Parsed: IIf(InStr([Originator],"/")...Parse Name Field into First Name Last Name
I have a field called [Patient Name] that I want to parse out into a First
Name Field and Last Name Field. I am using the Charindex() to find the comma
that seperates the First and Last Name, I am then passing the Charindex value
to the Left Function to grab the Last Name. Below is what the code looks like:
LEFT([Patient Name],Charindex(',', [Patient Name])-1) AS LAST_NAME
I am getting the below error when using the above code
Invalid length parameter passed to the LEFT or SUBSTRING function.
Warning: Null value is eliminated by an aggregate or other SET operation.
...Return only the numbers contained in a field
I would like some help please building a query that will return the
numbers only contained in a field:
Table "PurchaseOrder" has a "Memo" field that usually has the
following data: "Sales Order 7540:" or "Sales Order 34:".
How can I create a query that will show me all the Sales Orders and
their linked Purchase Orders (PurchaseOrder.RefNumber) where the
SalesOrder.RefNumber equals only the numbers in PurchaseOrder.Memo
Thanks in advance,
Welllll. You can but it might not be pretty, accurate, or fast. Actually I'd
expect ugly...Need help getting query to reference a field in a subform
I am trying to run a query using a specific field in a subform as the
criteria. The query is displaying one combo box from the Form, as well
as several fields from its own subform.
My Form name is: FormGrantManagement
My Subform name is: TableMOA subform
My Subform's subform is: TableCostReports
The field in the subform I want to reference is: MOAID
I have found other help topics that lead me to think that a criteria
along the lines of [Forms]![FormGrantManagement]![TableMOA subform]!
[Form]![MOAID] is what I need to use, and I have tried multiple
variations of this but witho...Table Lookup formula where 2 known values are inside array
I am using Excel.
Please help, I am stuck on a non-typical table lookup
I regularly use =lookup to get values where the relationship is 1:1
e.g. in a cell, I put a value, I use =lookup for that value's meaning
I need to do this, but the lookup is a table, not a 1:1 column match
Row 1 is a header, one of my search values
Column 1 is a value I want to find
the table (array) contains the other known value
I need do do a enter values in 2 cells, and have a lookup return a value
based on the value it looks up.
-- | 1 | 2 | 3 | 4
w | a | d | h | i
x | b | e | i | ...