Table relationships and lookups
Hi guys, I may be a little over my head, I've had some experience in
creating simple access db's. however this one will be extremely
complicated as far as I can tell. Some backround info - i've got an
excel spreadsheet currently that i would like to convert to Access.
The spreadsheet does multiple lookups and calucations.
This is for a Soccer club that i run to maintain roster information,
dollars, scheduling and stats. I'm currently working on the
scheduling pience. Here's what I have so far.
Club - Lists the teams in the club, home field name and ...SQL query / Formula for Reorder Point?
Is it possible to issue an SQL command(s) that would:
1. Set the Restock Level for all items to be equal to the current on-hand
quantity for each item.
2. Set the Reorder point for all items to be equal to 1/3 of that items
I'm currently doing this manually, or using a formula in excel when items
are initially imported into the DB, but there are still several thousand that
need to be retro-fitted like this...
Thanx in advance!
UPDATE Item SET RestockLevel=(Quantity - QuantityCommitted)
UPDATE Item SET ReorderPoint=ROUND(RestockLevel/3,0)
Ne...Invoice lookup by paid check number
I frequently have vendors call me asking for information about what invoices
were paid by a check number. Is ther an easy way to look this information up?
You could go to Inquiry>Purchasing>Transactions by Document, put your check
number in the 'from' and 'to' fields. Once the document is displayed in the
scrolling window zoom back on the 'Unapplied Amount' field. Viola! the
documents paid by the selected payment are listed. Unfortunately there isn't
a print icon on this inquiry window, but I think it's the information you
how can a hundreds of numbers in a worksheet be adjusted
by the same percentage
You can use Paste Special to do this. For example, to increase every number
by 15%, enter 1.15 in some empty cell and copy that cell. Then select the
cells that you wish to change. Go to the Edit menu, choose Paste Special,
and there choose the Multiply option.
Microsoft MVP - Excel
"Ray G" <email@example.com> wrote in message
> how can a hundreds of numbers in a w...Simple formula
Hi, I'm a new to Excel so please excuse my ignorance in advance.
I have data in Column C that I need to multiply with Column D. I know
(for example) I can get a result by placing the formula: =sum(C3*D3)
in cell E3.
But, I have very long columns of data and I don't want to type in
=sum(C4*D4)in cell E4, =sum(C5*D5) in cell E5 etc.
My question is; can I use a wildcard for the column number and then
just paste this formula in each E cell?
On Fri, 22 Jul 2005 14:02:51 +0100, PigPOg <firstname.lastname@example.org>
>Hi, I'm a new to Excel so please excuse my ignoranc...Handling #REF returned from GETPIVOTDATA
A GETPIVOTDATA function returns #REF, but when I change that formula to
=ISREF(GETPIVOTDATA... I get FALSE. I would have expected TRUE. Why the
This is part of the development of a formula to return a zero when
GETPIVOTDATA returns #REF.
will return true if you pass it an address:
(since A1 is an address)
I don't recall ever seeing this used on a worksheet. I bet that this may have
been useful in the older XLM style macros (just a guess).
Laurence Lombard wrote:
> A GETPIVOTDATA fu...Lookup
Q102 Q202 Q302 Q402 Q103 Q203
How can I lookup the Q103 in the row above and then have
it pull the number to the right one cell (Q203)? thanks
If I understand correctly
where q103 is in a1
and q102-q203 is in a2-f2
>Q102 Q202 Q302 Q402 Q103 Q203
>How can I lookup the Q103 in the row above and then have
>it pull the number to the right one cell (Q203)? thanks
> Q102 Q202 Q302 Q402 Q...Help with LOOKUP function
This function is in a workbook with 2 sheets. It _almost_ works
perfectly. These "C" columns in two different sheets
'2005-2006'!C:C,'2004-2005'!C:C, contain names of people. The D column
in one of the sheets - '2004-2005'!D:D - contains a date associated
with the person's name from the C column of 2004-2005 sheet.
This formula is in the "D" column of Sheet 2005-2006.
The concept is for the formula to lookup the value (person's name) in
column C of 2005-2006 a...current user returns admin
I would like to log in one of the databases the username of the person who is
making changes to a record in a database, but I noticed that access 2207
doesn't have user level security anymore and the currentuser method just
returns admin. Is there a way to do this?
On Jan 3, 10:16 am, icccapital <icccapi...@discussions.microsoft.com>
> I would like to log in one of the databases the username of the person who is
> making changes to a record in a database, but I noticed that access 2207
> doesn't have user level security anymore and the currentuser method ju...A Lookup function does not work
Hi, This is my first posting.
I am using Exel 2000. I have 2 separate spreadsheets that have some
similar columns but not all of the data in the similar columns is the
What I want to do is take column A in spreadsheet#1 and find this same
value in Column B in Spreadsheet#2 and then insert into column 3 in
spreadsheet #1 a value from a different column in spreasheet #2 that
corresponds to the row in which the value was looked up in Column B in
What I am doing is comparing 2 different inventory files that have
stock codes in columns and quantities in another column, but n...Rookie Incremental formula help
I have a simple sheet with totals from (b2)*(d2). Total = [Formula]
When I drag the incremental formula down across 3 sheets and hiding th
zero values, printing still prints 3 blank pages.
Is there a way to fill in the next incrementing formula [(b2)*(d2)
when the calulation is made? I want to keep from having 300 growin
hidden formulas, I'd rather try to have them incrimentaly auto fill s
my print outs wont have 20 extra blank pages.
RubberSoul's Profile: http://www.excelforum.com/me...Formula partial result displayed in cell
My formula =D3*0.0275+850 displays the '850' in the cell without yet having a
value in D3. I know how to avoid having zero values displayed, but how do I
tell Excel I don't want a partial result displayed.
Try this formula
daddylonglegs's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3048
View this thread: http://www.excelforum.com/showthread.php?threadid=50141
...return cell reference in a table based upon given lookup criteria
Is there a way to return the cell reference, or column/row coordinates,
of a cell within an array or table by providing lookup criteria?
Perhaps something like this:
For a table of value in A1:E10
F1: (the value to find)
Note: Commit that array formula by holding down the [Ctrl][Shift] keys and
That formula returns the address of the 1st cell containing the value in F1,
or #VALUE! if there is no match.
Am I on the right track here?
"Travis" ...Advanced Lookups
Is there any way to make an advanced lookup the default lookup? so you don't
have to always choose that option when doing a lookup?
Thanks for any help.
Advanced lookups ARE the default unless you've done something to make it now
so. There isn't any way to "choose" the option when doing a lookup that I
know of unless you have some type of customization (easy to do) that would
give the user an option.
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tracey D" <...Cell Formula reference to cell Based On third Cell Content #2
I want to build a formula in one cell that calls a second based on the
numeric value in a third.
Specifically, Say the frst cell is F10. I want it to look up the number in
F1, and then
if F1=1, F10=E9*something
if F1=2, F10=D9*something
if F1=3, F10=C9* something
I notice that you use "etc" which means there are more.
Exactly how many more? That number will determine the best
way to approach this.
>I want to build a formula in one cell that calls a second
based on the
>numeric value in a third.
>Specifically, Say...Excel IF Formula
I have a spreadsheet with 4 cells that 1 of the 4 will have a calculated
value in it. I want to take the numeric value in the cell that has a value
and place it into another cell for information. Following is the string I
have written but I am doing something wrong.
=IF(E25>1, E25,IF(E27>1, E27, If(E29>1, E29,IF(E31>1, E31)))),"ERROR"
Any help would be greatly appreciated.
This is your "corrected" formula:
"GeneI" wro...Calling employee lookup from button through VBA code
Can anyone show me how to call an existing GP employee lookup from a button
of a modified form through VBA code.
Thanks in advance.
If I'm understanding the question - you need to add the lookup button to
your project and make sure your project provides that it runs on the modified
> Dear All,
> Can anyone show me how to call an existing GP employee lookup from a button
> of a modified form through VBA code.
> Thanks in advance.
As per...Vendor Lookup
One doing the vendor lookup - one user sees the 'show details' information
upon lookup; other user sees the vendor list and needs to clik on the show
details - how do you get the show details window to be the default option you
Check for full stops/periods/dots on the window title bar before or after the
window name. It is possible to use VBA or modifier to open the details
David Musgrave [MSFT]
Escalation Engineer - Microsoft Dynamics GP
Microsoft Dynamics Support - Asia Pacific
Microsoft Dynamics (formerly Microsoft Business Solutions)
http://www...An array formula to sum row max
I have a data range of 1000 rows and 20 columns. I need a sum of the
maximum number of each rows. I usually add another column to calculate
row max. But is it possible to eliminate this intermediate step and
just use a single cell array formula to return the sum of row maxes?
ucamms's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19151
View this thread: http://www.excelforum.com/showthread.php?threadid=382107
Assuming that A1:T1000 contains your data, try...
=SUMPRO...Formulas or not ??
Good morning guys,
I woant a formula which when referring to another cell
verifies whether this cell contains a numerical value or
otherwise a normal numerical number
Is this possible ?
Thanks in dvance,
> I woant a formula which when referring to another cell
> verifies whether this cell contains a numerical value or
> otherwise a normal numerical number
=IF(ISNUMBER(A1),"A1 contains a number","A1 Does not contain a number")
Jan Karel Pieterse
I'm not sure whether I'm interpre...Copying Formulas In Pivot Table
Working in a pivot table where I have formulas pulling from other
fields within the pivot table, can I copy the formula to range of
cells without having to enter the formula in each cell?
I keep getting an error that I cannot copy/paste or drag the formula
across the table.
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.
Is there anyway you can st...Filter message by reverse domain lookup
I am kinda new to Exchange and was wondering if there was a function
built into Exchange 2000 that would "look" at the source domain of the
email message and then perform a "reverse" DNS lookup to verify that
the domain actually exists and if it doesn't then have Exchange
discard the email without sending an NDR? Is this possible strait out
of the box? It seems like I remember iMail gateway having this
feature. I am trying to filter out some of the spam that fills up my
Thanks in advance.
email@example.com (Jeremy Steger) wrote...Can you change the default lookup from Account to Contact?
Is it possible to set the lookup for a customer field to default on
"contacts" rather then "accounts?" We do more business with contacts so it
is a pain to have to change this all the time.
there is no supported way to do this in the current release
Microsoft CRM MVP
"Martin Flaherty" <firstname.lastname@example.org> wrote in message
> Is it possible to set the lookup for a customer field to default on
> "contac...Simply Formula
Is there a simple formula approach to trimming numbers?
Our full Grant Number in one system appears as
Example: 1 F32 AI540542003A1 and in the other system it
Also, can you explain the formula below?
=IF(H30<>"CA",TRIM(F30)&" "&TRIM(G30)&" "&TRIM(H30)&RIGHT
&" "&TRIM(G30)&" "&TRIM(H30)&RIGHT(LEFT(TRIM(B30),14),6)
Thanks Again for all your Help!!!
For trimming numbers there are a number...