Data Validation List - OFFSET Function
Apr 20, 2012
I have the following formula in a Data Validation List:
=OFFSET(B8,MATCH(J2,
(CHOOSE(B7,D1Array,D2Array,D3array,D4Array,D5Array,D6Array)),0),1,COUNTIF(CHOOSE(B7,D1Array,D2Array, D3array,D4Array,D5Array,D6Array),J2),1)
In B8 it is the formula =VLOOKUP(B7,LookupTable,2,0). and the lookup array is this 1$H$112$J$113$L$114$N$115$P$116$R$11
So the value returned in B8 will be a the start reference for the OFFSET function. This works but it retuns blank values and not the values in the arrays.
View 3 Replies
ADVERTISEMENT
Aug 21, 2012
I have the following function in cell (table 2 column C) where I want to find a contact name from my table TMI if 3 criteria are true (client, account, NAM) For each contact I have 1 true combination of client/account/NAM but for each client/account/Nam I can have several contacts.
{=OFFSET(TMI[[#Headers],[Contact]],MATCH(1,(TMI[Client]=$B$1)*(TMI[NAM]=C3)*(TMI[Account]=B3),0),,COUNTIFS(TMI[Client],$B$1,TMI[Account],B3,TMI[NAM],C3),1)}
The function is working well but shows only the first contact name if I have 3 contacts for the same client/account/NAM. (I pressed Ctrl+Shift+Enter)
When I press F9 on the function, it shows all the contact names! so they are somewhere.
Hence, I have copied and pasted this function into data validation / list in the source but it came up with an erreur.
Table TMI:
A B C DClient
Account
NAM
Contact
Comments
[Code]..
Table with offset function:
A B C D
Chi
Account
NAM
Contact name
Comments
[Code]..
All client, NAM and account lists are dynamic !
View 2 Replies
View Related
May 8, 2007
What I have:
I have 12 months in a year and each number represent a month. I have generated lists of data mainly interest and principle payments from 12 amortization sheets which i have just copied into the attached spreadsheet and will change accordingly based on the loan terms which doesn concern us. For our purposes we wont make distintion between principle and payment as i just need to see how to make what i need work.
What I need:
I need to offset data vertically automatically once i the user selects an option value (Number in this Case) from the validation list located within the Payment control box. Going from left to right you see the raw data but in this example i need the data on the right hand side to be offset down the number of rows which may be found in the control box as a validation list.
IE:
So if i were to use "Loan I" [L6] and select a "2"[J6] from the "Payment Control" then the data on the right hand side [L6[ would reflect a $0 but instead the new value for L7 would be "$24.96" and would continue down for about 374 rows. This will have to be done for Loans I - Loans N [L6:Q6] respectively. This is all i need in order to compile and make use of in my spreadsheets.
Thanks and cannot be a formula as forumlas will be overwritten and cannot be a macro so it must be a module in itself so it an run when the list is changed.
the single digit values in coulmns M6:Q3 are there mainly so you can see the difference between the base and the position at which the data is placed at. its not need but is there to stress and to show the number rows in offset of data.
View 9 Replies
View Related
Jul 5, 2007
find attached the excel sheet. In sheet1 I have named the range I have inserted a Name(Test) for range $A$1:$A$2(range contains only texts) and have a list validation on cell $B$1 using the name. This works perfectly. In sheet2 I have named the range I have inserted a Name(Test1) for range $A$1:$A$2(range contains only numerics) and have a list validation on cell $B$1 using the name. This doesn't work.
View 3 Replies
View Related
Aug 12, 2012
Is it possible to set up a cell to either return a vlookup result or allow a specific data validation list? For example, if precedent cell is blank, then I want to perform a vlookup and return result, but if not, then only allow a choice from an unrelated list.
View 5 Replies
View Related
Jul 23, 2014
I need to compare 2 cell with 1 specification reference.But the function can't give return value as per required.Both 2 input cells using Data Validation List.
Please refer attachment for some examples : matching.xlsx
View 2 Replies
View Related
Mar 28, 2014
I am using a data-validation-list with =INDIRECT(B7) as the source. It works as expected except for after the file has been closed and reopened. When it is reopened, the source is changed to =INDIRECT(#REF!).
I have tried =INDIRECT('Worksheetname'!B7) but same result.
View 9 Replies
View Related
Apr 17, 2012
I am looking to do lookup list using the offset function. Scenario: I have 5 dealer groups, each with there own dealers. Each dealer has a list of customers. Once I select the customer it will bring up a list of their vehicles. I am stuck on selecting the customers and vehicles once the dealer group and dealer are selected.
View 1 Replies
View Related
Jul 8, 2006
I'm trying to use three nested Do..While loops to find data in a worksheet and consolodate it in a table. Individually the loops work, however when I nest them only the innermost loop works. The inner loop is written to exit when the Find value is Empty and the other two exit when firstaddress = latest address so they will only cycle through their range once.
Problem is when nested, the middle loop FindNext is Empty and exits on the first go, and the outer loop FindNext looks for the value of the cell above the first found cell, rather than what it should be looking for, and so creates an infinate loop. I thought this might be a problem with the ranges being redefined in each loop but respecifiing the range at every Find/FindNext hasn't solved it.
View 4 Replies
View Related
Jul 16, 2006
How do I hardcode the Offset function? Right now I am placing the offset formula in Validation from the Data menu toolbar.
Also, I have two columns "Expense" and "Income" used for offset. Expense has 11 selections for the listbox, and Income has only 3. When I choose income from the dropdown in column B, I get way too many blank selections in the dropdown just because It doesnt contain as many items as Expense. How can I re-code adn hardcode the formula so I have only few blanks for my selection.
I have attached a copy "data validation test" to help make things more clear.
View 7 Replies
View Related
Oct 1, 2012
I have a from that work great in PC but if opened in a MAC, some of the functions will not work.
For example, There is one cell with Data validation that based on the value selected in another data validation cell, it should only display all possible options pertaining to value chosen. These are the values: a user select a customer based on customer name. The Data validation will the put the customer number in cell AF3. In another call with Data Validation I have this formula:
"=OFFSET(SHIPTO,MATCH(AF3,SHIPTOColumn,0)-1,2,COUNTIF(SHIPTOColumn,AF3),1)"
SHIPTO is the Name range for the tabs called SHIPTO with "Shipto!$A$1"
SHIPTOColumn is the Name Range for "SHIPTO!$A:$A"
Column A is where the customer numbers are.
This work fine in a PC but not in the MAC using excel 2011
View 1 Replies
View Related
Feb 15, 2014
I have 2 columns First Name & Surname. What I want to do is create a data validation list on the surname which results in the 2nd data validation list only showing the first names which link to one of the surnames.
i.e. If I selected Smith in the 1st validation list then I would only like to see 'Paul' as an option in the 2nd list
First Name
Surname
Paul
Smith
Paul
Jones
Tony
Phillips
View 1 Replies
View Related
Sep 18, 2006
I have a dynamic validation list (see attached) which will not accept the indirect function as a formula in the listbox.
Using the named range on its own works fine, but using the indirect generates an error.
View 3 Replies
View Related
Feb 22, 2007
Is it possible to use the HYPERLINK function inside of a validation list?
Presently I have a dynamically named range on another worksheet that includes a hyperlinked term. When I attempt to create a validation list using this named range the term appears in the list without the hyperlink.
View 7 Replies
View Related
May 31, 2014
I am having some difficulties using a combination of IF and the OFFSET function to display a range of cell values from another column based on a simple condition. The values I need to display at the destination cells should be offset by 8 columns to the right and "X" rows down from the reference column. The value "X" is to be determined via the IF function to check for the row index number.
For example, if Index value "X" = 8, then display the value of B2 in cell I9. IF X = "9", display B2 in cell I10 etc.
I have attached a sample worksheet that provides some examples.
View 3 Replies
View Related
Jun 20, 2008
I can't seem to find a way to make a data validation list automatically show the first item in the list rather than showing blank.
View 10 Replies
View Related
Jun 15, 2007
I'm trying to use data validation to restrict the user to only selecting values in a list which I create. Right now, the list is a named range. I'd like to get rid of the range and just use a named list. I create a name using the following as my list.
Insert > Name > Create
Name: Fruit
Refers to:
banana,apple,orange
When I try to use the name Fruit in my data validation, I get the message "The List Source must be a delimited list, or a reference to single row or column." I thought my name "fruit" was a delimited list.
View 4 Replies
View Related
Oct 28, 2011
I'm looking for a way to get a unique list from a column to a data validation drop down list. Any fancy formula or vba script to create a UDF which. Does this?
View 5 Replies
View Related
Apr 29, 2012
I have a List of Different Fruits in Cells A1 to A5
Apple
Banana
Orange
Strawberry
Cherry
And I use data validation list in 5 different cells from Cells C1 to C5 then in every cell the list will show all the fruits,
But I want that if I select Any Fruit in cell C1 that should not be included in the remaining 4 cells, and the fruits selected in Cells C1 and Cell C2 should not be included in the remaining 3 cells and so on....
I Used the formula
=IF(C1=A1,OFFSET(A2,,,COUNTA($A$2:$A$5),1),0)
But this works fine if I select Apple in the Cell C1, then the List of C2 Shows all Fruits other than Apple, But if in Cell C1 I select any fruit other than Apple it does not work... (Using Excel2007 & Win XP)
View 4 Replies
View Related
May 12, 2004
dear....can i set width in list box data validation (width in cell i set short).thanks
View 9 Replies
View Related
Oct 18, 2006
is there a way to force so that a cell value can be only of what a validation droplist offers, hence users can not enter their own values and have to use the droplist for cell content selection.
View 2 Replies
View Related
Jul 16, 2009
I am using Data Validation on some fields to create a drop down list from a named range! These fields however allow you to enter values that are not in the list.
Is there a way to make the cell have to be an entry from the data validation list?
View 6 Replies
View Related
Jun 9, 2009
i know it is possible for a selection in a drop down box to determine another field using the INDIRECT function in validation
eg - 1st drop down box - Football, Rugby, Cricket
2nd drop down box (if chosen football) - displays list of football teams
2nd drop down box (if chosen rugby) - displays list of rugbyteams
2nd drop down box (if chosen cricket) - displays list of cricket teams
but is it possible for the 1st drop down box to determine what is available in a range of other drop downs?
eg - 1st drop down box - Football, Rugby, Cricket
2nd drop down box (if chosen football) - displays list of football teams
3rd drop down box (if chosen Man Utd) - displays list of Man Utd players
View 7 Replies
View Related
Apr 10, 2014
See attached. I cannot get my indirect function to work in Cell K2 based on cell J2
Data for the Validation is in cells W1 to AC14
I want to do data validation in Column K based on Column J, but cannot get the indirect function to work to past into the data validation tool
I have named all my ranges.
AM Call Log.xlsm
View 4 Replies
View Related
Dec 5, 2009
I have a data validation on a cluster of Cells that has a name.range = "match". On clicking on a Cell in Row 61 belonging to range = "match" i get a Data Validation|Custom formula(Alt+A+V+V) "=J61>=0"
now how can i convert this function of Data Validation to a Vba script.
To be Exact what i need is....
If a value is entered in cell belonging to range.name = "match"
then check the value in "corresponding row" of the column J.
If Value is greater than J
then stop vba script and show message box.
If value is not greater than J then run desird Code.
I hope the following picture might clear things even more of what im looking for..
View 9 Replies
View Related
Sep 26, 2013
I'd like to create a drop down list in data validation from a column of data that contains numerous duplicates.
For example, let's say column A contained hundreds of transactions with either North, South, East and West, how could I create a drop down list in another cell that only had four selection options?
View 5 Replies
View Related
Mar 18, 2009
The idea is to create a liquor order sheet which takes items and prices from a liquor inventory workbook, which is part of the same file. i'm sure that you can do it all fancy with a macro, but unfortunately i'm not super experienced with excel. so i helped myself with what i know a bit -> drop down lists. I created an extra sheet which has 4 ranges on there... the liquor type (liquor, beer, wine), and the 3 sub classes for each type (red/white/blush/sparkling for wine, draft/import for beer and so on and so forth). On the inventory i named the range of all white wines "white", of all red wines "red" ..
On my order sheet i created a drop down list for type and created 2 more via "indirect" .. so when you select "wine" in the first one, the 2nd one will ask you for white/red/blush/sparkling and the last drop down then for the exact item according to the range on the inventory.
Hope what i did so far is understandable ^.^
Now my problem is that i want the unit cost to be copied when an item is selected. So you select "beer" in A1, "import" in A2, "corona" in A3... and the price for corona (which is written in a cell on the inventory sheet) should show up in A4.
I attached a screen of the inventory sheet so that you can see how it is structured.
View 12 Replies
View Related
Sep 9, 2009
Hello, could some one please help me with the following:
I have created a drop-down list for the range : C3:C65000 using Excel 2003 and Windows XP Pro. I did this using Excel's Data Validation Tool. The settings I used are as follows:
Allow: List
Source: =GROUPS
Check Box Ignore Blank is Ticked
Check Box In-cell dropdown is Ticked
GROUPS = GROUPS!$A$2:$A$29
Now all this works well, such that when I click in the cell range : C3:C65000, then a dropdown list appears - showing a list of all my groups.
However, when I click on any of the dropdown lists in this range, the width of the dropdown list is only as wide as the column. Column C has a width of 20.
What I need please is for the dropdown list to be as wide as the list of information showing in the dropdown list. If some one could please explain on how I can achieve this - that would be great.
View 7 Replies
View Related
Aug 29, 2008
I have a workbook that uses data validation from a list.
The same list is used in several of the individual sheets.
Can one use just one list for different sheets, as I'm having to create separate list for each sheet, which when an overall change needs to be made it is easy to miss one.
View 9 Replies
View Related
May 28, 2014
I want to load the actual data exist in the rows by selection value from the data validation list.
E.g I have two worksheet in one excel file. One has a data activities of persons with their name like two columns i have in which one exist the name of person and second exist the activities which they perform.
On the second sheet, i made a data validation list of all the person names
Now my requirement is, when i select a person name from the list, load all the data from the 1st sheet to second sheet. Is this possible without VB code, because I want to share it on the Google sheet with my boss, where VB sheet is not supposed to work.
View 13 Replies
View Related
