Lookups and Offsets

I would like to take a previous issue to the next level of complexity.
Here's the scenario

Message posted from http://www.ExcelForum.com

2/2/2004 9:10:30 PM
excel 39879 articles. 2 followers. Follow

1 Replies

Similar Articles

[PageSpeed] 9

Here's a possible solution :-)

           Ken.......................    Microsoft MVP - Excel
                  Sys Spec - Win XP Pro /  XL 00/02/03

It's easier to beg forgiveness than ask permission :-)

"wmjenner >" <<wmjenner.110p7g@excelforum-nospam.com> wrote in message
> I would like to take a previous issue to the next level of complexity.
> Here's the scenario:
> ---
> Message posted from http://www.ExcelForum.com/

Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.576 / Virus Database: 365 - Release Date: 30/01/2004

ken.wright (2489)
2/2/2004 9:19:51 PM

Similar Artilces:

CRM should let me have multiple lookups to the same entity
We defined different type of contacts in the CONTACT entity (patients, physicians, caregivers, sales reps,...). Then defined a customized entity PROGRAM which should have the information of a Patient, a Physician and a Sales rep, for every instance of the customized entity. We would like to have several lookup fields from the PROGRAM entity pointing to the CONTACT entity. ---------------- 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 ...

Vendor ID Lookup in Payables Transaction
Weird happenings...customer is reporting randomly on the vendor id field on payables transaction, they can not enter more than the first 4 characters of the vendor id; or when they do a lookup it defaults to the vendor from the prioor transaction. Any ideas? They are on GP 9.0 Thanks! Is it a modified form?(You can tell if you notice a "." on the window title) Does the customer have smartlist security enabled as well? "Andrea Smiley" wrote: > Weird happenings...customer is reporting randomly on the vendor id field on > payables transaction, they can not ent...

Lookup function for two variables and return another cell?
Hi! I am looking for some help. Seems pretty simple but I can't seem to figure it out. I know I'll need a lookup function and an "AND" function of some sort... Take a look and let me know what you think. row/column A B C D 1 New SSN 1st signed Blue 2 2 New SSN Calc - not signed Green 1 3 Previous SSN 1st signed Red 3 So I need help finding the combination of "New ...

DNS and forward lookup zones
Hello, when i create a dc I have the option to install DNS and I must do it if it is the first DC. In this case the forward lookup zone will be created automatically and if I want I can choose to replicate dns to all other DCs with DNS installed either in the same domain or all forest. Now in the same domain means also on child domains or it means in the exactly same domain only for example only on test.com and not on south.test.com I am trying to understand when I should create a forward lookup zone since when I created the DC the forward lookup zone was configured automatical...

Lookup and copy
Hello, I run a hockey pool and I want easy roster management. What I woul like is to have the 1st sheet to have every single NHL player on on sheet. Then 10 different sheets with for the teams in my hockey pool. What would be great is to have a formula that would look at column for the team and copy the information on Column B and C on th appropriate sheet. Column A Column B Column C Toronto Sakic, Joe Centre Then I would find on sheet Toronto Sakic, Joe and Centre. If he were traded to Vancouver, then I would select Vancouve (Validation>list I created) and then he w...

modifying lookup window
How can I modify the SO and PO document lookup window so that I have different columns rather than the default columns? For example I would like to have the customer name and customer PO number in the Sales entry document lookup window? Can I do this with Modifier or do I have to do it through dexterity? With modifier you should be able to add additional fields onto the lookup, however, the order that you see the records in is controlled by the keys on the table and cannot be changed. Using Dexterity would be difficult as the lookup is an alternate window in the Smartlist dictionary ...

Lookup associated to Account
I added a Contacts lookup field on the Accounts form. When I click it to select a Contact, it shows me every contact from all Accounts. I need it to show me the JUST contacts that belong to the Account that I am currently in. Can this be done? You cannot filter the displayed records in CRM. See http://www.stunnware.com/crm2/topic.aspx?id=CustomLookup for a custom solution. -- Michael http://www.stunnware.com/crm2 ---------------------------------------------------------- "DubSport" <jamie.carmichael@cmgl.ca> schrieb im Newsbeitrag news:1157726656.055061.140880@p79g20...

Data validation lookup
Is there a way to have a drop down list using data validation in file "jobs.xls" sheet "Sheet 1" Cell C4 to select a contact from a list that is in the file "parts.xls" sheet "contact" and fill in the corresponding cells in "Jobs.xls". The list of contacts starts on row 3 and goes to row 48 now but there will be more in the future. If there is another way to do this other than data validation I am open to other ideas. In "Job.xls" Sheet name is "Sheet 1" C4 C5 C6 D6 E6 C7 D7 C8 C9 ...

Purchase Order: Add Lookup by Supplier
When creating a Purchase Order by Supplier, RMS creates a Purchase Order that contains every Item under that Supplier on the Purchase Order and the user is forced to delete all Items that they do not want. You can also create a Filter, but that also adds every Item that fits the criteria. What I have been asked for many times is a way to get a list of Items from a Supplier or list of Items that fit a Filter so that they can select the Items they want to add to the Purchase Order. Much like the Add Item feature, but with a way to narrow down the items that appear. You can use the Find w...

Equipment Lookup in Service Call Management
Allow the user to lookup Equipment for a particular customer in the Equipment Maintenance window in Service Call Management series. The user wants to find the equipment by the customer's name, but this cannot be done in the lookup. So in order to find the customer id, they would have to look it up first. If they could enter the customer id in the window using the customer lookup (can search by customer name). Then once they have the customer id on the window, the equipment number lookup would be restricted to equipment for that customer only. The other option would be to add ...

Repost: Copy and Offset cell reference
Hi Groupies: I posted this a couple of days ago, but I missed the functions group, no reply yet. I was asked if this was possible and I can not think of how, hopefully, somebody can help me. On Sheet 2, I have the following data: cell B2 =sheet1$N$14 cell B3 =sheet1$N$32 On sheet 3, I need to have cell B2 =sheet1$N$15 cell B3 =sheet1$N$33 On sheet 4, I need to have cell B2 =sheet1$N$16 cell B3 =sheet1$N$34 Basically, each sheet is a different month and the references need to be offset each time. This needs to be done a number of times in this and other workbooks. Is there an efficie...

I am using excel 2007 and I am working with a workbook that has 2 sheets. One is called Monthly and the other is called History. Both contain the same fields and columns which includes Account Numbers. The Monthly has a small amount of records for the month and the History has all records for the year 2010. What I would like to do is hide the History tab. Which is no problem. And when the user is looking at the monthly records they can also select a account number on that sheet and a dropdown, combo box, or lookup will pop up and they would be able to then view all the history re...

help with lookup formula
I made an inventory spreadsheet for my job. I would like it to look u the feet and inches off of astrapping chart I put on another sprea sheet and enter the appropriate gallons in column v of the inventor for each tank onright side of inventory sheet. I am pretty sure I nee a v lookup but I don't know how to do it. I am attatching theinventor spreadsheet and the one with the strapping charts. This inventory i for gallons of alcohol. I will enter the # of feet in column r and th inches in column t. In some cases there will be a fraction of an inc involved. let's use pg 103 for an exampl...

Column and Row lookup? Matrics retrieval
I have say a 26 by 26 grid of info and the row 1 is filled with values A-Z and column A is numbered from 1-26. How do I write a function that will search Range A-Z(Row 1) and then search range 1-26 (Col A) and when it finds the two values if will return the value in the cell they intersect? ex. given a 3x3 grid. top row of cells are filled with A, B, C and first calumn is filled with 1,2,3. I want a function that will return the value in (B,2). Thanks. -- jt76 ------------------------------------------------------------------------ jt76's Profile: http://www.excelforum.com/member.p...

vlookup uses only first 8 characters of the lookup value?
Has anyone else noticed this? VLOOKUP seems to use only the first 8 characters of the lookup value, so both lookup values 12345678 and 123456789 will match a table array value of 12345678. Anyone know of a way to change this so it uses all characters of the lookup value? On 29 Jul., 20:16, eggman2001 <sod...@gmail.com> wrote: > Has anyone else noticed this? VLOOKUP seems to use only the first 8 > characters of the lookup value, so both lookup values 12345678 and > 123456789 will match a table array value of 12345678. Anyone know of a > way to change this so it uses all chara...

DNS Reverse lookup #2
Hi, One help, please: - I have a domain test.local (W2k3) and in it a Exchange 2003 Std (machine mail.test.local); - I configured the users send email as @test.com; - My MX is pointed to Exchange Server; - I have a public IP from DSL service; - when I send an email the recipient use Reverse DNS lookup and the result is mail.test.local and not test.com How can I solve that problem? Thanks. Luiz Luiz wrote: > Hi, > > One help, please: > > - I have a domain test.local (W2k3) and in it a Exchange 2003 Std (machine > mail.test.local); > - I configured the users send e...

User Lookup to start with business unit
By default if a user has organization level read access on users when you do a lookup to assign an owner of a record for example it will show all users of all business units. I'm pretty sure I know the answer to this but maybe I missed something. I want to limit the list initially to their own business unit, but if they do a find I want it to look broader based on their access. ...

Lookup value of text box in another
Hi, I am trying to alter the number of fields I need to input on the form. What I am trying to do is, once I input a Person ID field in a text box (txtEmpID), I am trying to get another textbox to look up the name of the person in another text box (txtEmpName) eg txtEmpID - 123 once I have input this then automatically in txtEmpName - Jez appears how can this be done? I have the Emp ID and Names in a table called tblEmp Thanks Jez If you really want to do it that way you could include tblEmp in the form's record source query. When you enter the number (which I assume is stored),...

One click to select a newly create account from a Lookup form
On the Case form, when you look up a Customer you have the option to click New. If New is clicked and a new Account (or Contact) is created, is it possible to link the Account straight back to the form when the Save & Close button is clicked. Rather than having to select the newly created Account from the Lookup up form, the account is selected automatically and the lookup form is closed. This CRM implemenation is for a call centre and they must minimse the amount of clicks to perform certain actions. This is a CRM 4.0 implementation. Is this possible with JScript, or by any other means...

Offset help #2
formula'{=OFFSET(A42,MATCH(A2,LEFT($A$42:$A$658,LEN($A$2)),0) -1,0)}' which works fine, but how do i show the next entry from the list in th cell below -- comotoma ----------------------------------------------------------------------- comotoman's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2729 View this thread: http://www.excelforum.com/showthread.php?threadid=49525 Do you mean =OFFSET(A42,MATCH(A2,LEFT($A$42:$A$658,LEN($A$2)),0),0) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "comotoman" <comotoma...

Random and Lookup and Countif
Greetings All, In Col A I have many different text entries such as Flightline, Avionics, Sheet Metal and text data in B1:AC1 down to 1700. I'm trying to count the amount of times that say, Flightline occurs. Then sample 10% of those instances in A1:A1700 and return the 10% and the associated row (?1:?1700) Say Flightline occurs 17 times in A1:A1700. I want to randomly select 2 of those rows in another worksheet. Can you help? -- Vick Hi! This is fairly easy to do but the explanation would be long and complicated. I put together a sample file that demonstrates how to do this. Le...

Lookup based on list of names
I need to import a file everyday that looks like this, but there are 250 people listed. I want to be able to enter a formula that will look for just my team members based on a list, and either delete the other rows or put the data in a new sheet - whichever is easier. The data looks as follows: Entry Operator Orders Demand Avg Total Promo's Promo % ABRAMS,TANJA 16 850.25 53.14 4 2500% CONNOR,JASON 97 3,978.55 41.02 3 309% COSGROVE,CAROL 260 14,057.88 54.07 54 2077% CRISTINA,TINA 29 1,658.75 57.20 3 1034% CROCKETT,BARBARA 32 936.42 29.26 7 2188% DAVIS,ELIZABETH 50 2,448.10 48.96 7 1400%...

Lookup Cell Interior
I am currently using the vlookup function to find values on another sheet ('Previous Report') associated with data on the current sheet and to transfer any matching results to the first sheet, (working with no problems). How can i extend this so that as well as transfering any matching data the interior color for the cell is also 'copied' over to the current sheet. rangecheck = Range("A8") Range("L8").Select Do Until rangecheck = "" ActiveCell.FormulaR1C1 = _ "=VLOOKUP(RC[-11],'Previous Repo...

countif/offset problem
Greetings, Experts, I'm sorry for not following the earlier advice, i just don't get it. perhaps i'm not explaining well. We're trying to define the formula that will look at a row of data and sum a specific number of cells into a new cell (called the new cell A2). then, cell B2 picks up summing the row for a specified number of cells and puts that sum into B2. Here's the sample data, in a row. Row 1: 10,35,50,7,25,48 Cell A7 has the value "3", which means that we'd like to put the sum of the first 3 numbers in Row1 into cell A2. Cell A8 has the v...

data lookup #2
What is the appropriate function to find data at the intersection of columns and rows? I've used VLOOKUP tables where I renamed cells in the left column, but never in the top row also. Would this work, and how would I write the formula so that the proper data is returned? Thanks. Jane with the item to row match in A1, the item to column match in B1, and a table that is D6:G9 (row names in C6:C9, column headers in D5:G5 then =OFFSET($C$5,MATCH(A1,$C$6:$C$9,0),MATCH(B1,$D$5:$G$5,0)) should look up for you QUOTE=Amazon]What is the appropriate function to find data at the intersect...