# Offset Real Time Data Based On Validation List

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.

## 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.

## OFFSET / Data Validation - Dropdown List Not Working

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.

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

[Code]..

Table with offset function:

A B C D

Chi
Account
NAM
Contact name

[Code]..

All client, NAM and account lists are dynamic !

## Coloring Real Time Data

Feb 9, 2007

I have an API, which inputs real-time stock data into an excel book. Does anyone know how to make the Last Price cell change coloer (Greem=up, Red=donw) depending on whether the price moves up or down, relative to the last price?

ie, over the course of a minute a stock may price at 35.00 => 35.01 (cell turns green) => 35.00 (cell goes red) => 34.95 (cell stays red) => 34.97 (cell goes green).

VBA, conditional formatting, or any other means of doing this is perfectly acceptable.

## Excel Live 60 Min Data Real Time

Jun 8, 2014

I need a forex excel spreadsheet that shows me the last 60 min of usd/jpy in real-time. It has to update every 60 minutes automatically.

## Real Time Data Collection Brainbuster

Dec 14, 2009

Objective: Collect data into the table below on a weekly basis coming from a real time data feed.

Can this be done formulas in the table?

The current time is running in cell D1.
The current date is in cell C2.

When the hour closes for the matching date, I would like rows to show the last value for that hour.

Example: ROW 3 to show the value of the currency pairs at the close of 7:00 hour for 12/13/09, etc.

My real time data is on another sheet. For reference call it: Main!G25:Main!G38 (14 currency pairs of data running, listed vertically)

ABCDEFGHIJKLMNOPQR1Week of:12/13/091:22:00AUDUSDNZDJPYGBPCHFEURUSDCHFJPYEURCHFUSDJPYUSDCHFEURGBPNZDUSDGBPUSDEURJPYAUDJPYGBPJPY2Today:12/13/2009Open Price0.911864.521.67941.462886.11.512489.051.03390.90030.72461.6242130.2681.2144.65312/13/200908:00 GMT7:004SUNDAY12/14/200909:00 GMT8:00512/14/200910:00 GMT9:00612/14/200911:00 GMT10:00FILLIN TABLEHERE712/14/200912:00 GMT11:008MONDAY12/14/200913:00 GMT12:00912/14/200914:00 GMT13:001012/14/200915:00 GMT14:001112/14/200916:00 GMT15:001212/14/200917:00 GMT16:00

## Reset Data Validation List All The Time When Opening File?

Jun 11, 2014

When opening the attached file, for some reason, I have to again select the cells from column A in tab "Transactions" and go to Data Validation and select again LIST - only then the data validation from list resumes working. When I close the file after saving and open it again, the data validation does not work - I have to again go to Data Validation and again reselect LIST (making sure, of course, that I do not mess up Source) to have Data Validation list run again.

what is wrong with list validation when opening the file.xlsx‎

## Moving Average Of Real Time Data At Timed Intervals?

May 19, 2008

Currently i have a spreadsheet with realtime data feeds from Bloomberg (or reuters). What i would like to do is:

a. Fill a vector/column of data values every minute until we have 30 observations i.e. from 9.00am till 9.30pm

b. This will then allow me to calculate a moving average of the last 30 (1 minute) observations.

c. At 9.31am, the 9.00am value drops out of the column and is replaced by the observation at 9.31am. This results in a constantly updating column of the last 30 minute observations and will allow me to have a realtime moving average.

## Data Validation List Based Off Cell Value

Apr 25, 2014

I'm trying to have a Data Validation list issue where I want to populate a second list based upon the value of the first list.

In this instance I have a Department in the drop down and Employee Name in the second drop down and I want people to only see Employee names if they're allocated to a particular Department. I've been able to do the list using the INDIRECT function, however that only works when there's only a small sample of both names & depts, however I'm looking at populating this document with around 3000 people and over 250 departments, so I would like to be able to use Column J on the Source Tab to populate the details on the Manager Entry tab.

I have attached a sample spreadsheet if I've not explained very clearly.

TIMESHEET sample.xlsx

## Populating A Data Validation List Based On...

Apr 18, 2009

I want to populate a Data Validation based on values in another cell on another worksheet--but, I want to populate it with the values stored in the cell right next to the cell. http://i32.photobucket.com/albums/d3...n/untitled.jpg. would be an example....

What I want my Data Validation List to do is to look at column B and wherever it sees a certain color--for example, red, I want it to put the corresponding value in column A in the list.

## Hide Row Based On Data Validation List

Sep 26, 2007

What I would like to do is hide rows 27:30 if the data validation list is equal to "Select Product...". The list is in B27:B30. I've search the forum but couldn't get the codes to work...

## Updating Cells Based On Data Validation List

May 14, 2009

What I am trying to do would appear to be very simple. I am trying to build a quotation form for our sales guys to use. I would like to use a drop down list with the list of products in, which I have created using a list in a second sheet and then using a data validation drop down list.

What I would like to achieve is the sales person selects the product (of a list of only 14) from the list and excel to automatically fill in the two columns to the right with the model no. and also the price.

Would it be possible to use an IF statement as there are only 14 products to choose from? Is there a better way of doing this? I have created the lists of model numbers and prices in the second sheet alongside the product name which the drop down list sources from.

## Data Validation List Based On A Table That Can Change?

Jan 11, 2013

I am building an Excel file that will be used to track information and at the core of it all is a list of people from different offices where the number of people per office can change and/or a person leaves the company and is replaced by another. I'll simply:

Column A Column B
Office Employee
Hamilton Emp 1
Hamilton Emp 2
Hamilton Emp 3
Toronto Emp 4
Toronto Emp 5
Toronto Emp 6
Toronto Emp 7
Toronto Emp 8
Waterloo Emp 9
Waterloo Emp 10

This will be all on Worksheet 'Info'. I have a Worksheet for each Office and named them accordingly. On each worksheet I want to use Data Validation on a column, we will call it 'ChosenOne', set it as 'List' and have the Source pull all the employee names that belong to that office and use them as a selection

ex: Hamilton Worksheet, 'ChosenOne' would show Emp 1, Emp 2, and Emp 3 in the list.

If Emp 3 changed offices to Waterloo 6 months from now I would like to change A4 from Hamilton to Waterloo and the formula would not have to be changed and the next time someone selects 'ChosenOne' it would only show Emp 1 and Emp 2.

Of course this means on the Waterloo Worksheet, 'ChosenOne' would show Emp 3, Emp 9, Emp 10 now.

So basically I am trying to not specify a specific named range for each office and am hoping there is a way to poll information from a Table (or any other tool that can simplify this).

I would be ok with something like:

Column A Column B
Office Employee
Hamilton Emp 1, Emp2, Emp 3
Toronto Emp 4, Emp 5, Emp 6, Emp 7, Emp 8
Waterloo Emp 9, Emp 10

and just move Emp 3 from B2 to B4 but I don't know if a list can be created from multiple items in a single cell seperated by a , or ; or :.

## Hyperlink Based On Data Validation List Choices

Feb 6, 2008

I want to select a Hyperlinked file.These files are named using Data in ColumnA,B & C. The realtionship between filename and data in columns is; A-B-C.ext. After selecting data A from the dropdown list,i should be able to select corresponding data B and then again another dropdown list for corresponding data C.This should show the desired already hyperlinked file. I want to select the hyperlinked file name by simply selecting all the three data independently.

## Insert Row Based On Data Validation List Choice

Aug 10, 2008

I'm working on a simple worksheet, it is a tracking sheet for programs taught. What I'm trying to find is a code to insert a row based on a Data Validation List.

Exampl of data:

Column A is for " Name" then futher down Column K is "Program Taught" and Column L is " Date Taught".

Column K is where the Data Validation List is located with a in cell drop down.

What I would like to do is when a user selects a program from Column K a row is inserted with all of the formulas and formats from the previous row, and upon insert would like it to copy Column A into the new row.

This would be so I can keep a historical of programs and dates taught.

And to really spice things up I want to lock Columns K & L of the previous row after the new row is inserted.

## Macro - Hide Rows Based On Data Validation List?

Dec 30, 2011

I have a data validation cell that has 5 items to be selected, lets say A-E.

I would like a macro that checks and does the following:

A or B selected, hide row 25-34, rows 45- 53

C or D selected, hide row 24-44

E selected, hide rows 34-53

In each case, the macro should check and unhide all rows between 25-53 before doing the above

## Adding A Time Offset Based On Time Of Day

Oct 1, 2008

I am replicating a Matlab program which calculates tide levels at different times of day. I need to replicate it in excel to speed up data analysis and I am nearly there.

what happens is I need to apply a time offset to the time of high tide at port a based on the time of day, so if it is:

between 00:00 and 06:00 the high tide at port b is 81.6 minutes after the peak at port a
between 06:00 and 12:00 the high tide at port b is 74.56 minutes after the peak at port a
between 12:00 and 18:00 the high tide at port b is 81.75 minutes after the peak at port a
between 06:00 and 12:00 the high tide at port b is 79minutes after the peak at port a

I tried this formula, where CO2 has the time/date of the high tide at port a:
=IF(CO2<0.75,IF(CO2<0.5,IF(CO2<0.25,CL2+(81.6/(24*60)),CL2+(74.56/24*60)),CL2+(81.75/(24*60))),CL2+(79/(24*60)))

The problem is the high tide on 07/01/2005 07:45 is read as 38359.32 rather than 0.32 - is there any easy way to tell excel I'm only interested in the time not the date? I have this spreadsheet setup now to do all the other bits required and i is just the timing that is a problem.

## Cross Reference (Vlookup) Based On Data Validation List Dropdown?

Jun 19, 2014

Attached is example of what I'm trying to do. I want to use the drop-down on the "Master Tab" and have the corresponding price by promo_month appear in the yellow cells.

Currently its on July, but I would like to switch that to August etc and have new prices populate in the yellow cells. Prices come from the "table" tab.

Example 06.19.14.xlsx‎

## Updating Cells Based On Selection From Drop Down List Data Validation

Dec 13, 2009

I'm using Excel 2007. I would like to seek some advise on how i may update cells after selecting a month from a drop down list.

i have created a simplified version of what i intend to have. One the 1st tab 'Cash Budget 2009', i have filled up a table with numbers, sorted by months. On the 2nd tab 'Dec09', i would like to create an expense table, to be able to be selected by month. So i have created a drop down list based on the months that are created in the 1st tab.

Upon selection of the month from the drop down list, i would like the cells to display (fetch) the data from the 1st worksheet. It would also be helpful if the name of the 2nd tab can be updated to read as the month that is being selected.

## VBA To Fill In Three Cells Based On Data Validation Dropdown List Selection

Dec 26, 2012

I've created seven names in my data validation source list on Sheet 3 in the following range of cells, A2-A7. My data drop down validation list is located on Sheet 1, A2 in the same workbook. I would like to trigger a macro based on the name I select in this drop validation drop down list that will fill out an address, telephone number, and email address in the following cells next to my data validation drop down list on Sheet 1: B2, B3, and B4. I want it to be designed so that users will select a name and have that person's corresponding address, telephone number, and email address fill right next to their name on the same sheet.

## Data Validation With OFFSET

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.

## Offset Formula In Data Validation

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.

## Excel 2011 :: Using OFFSET And COUNTIF Inside Data Validation?

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

## Validation List Based On Named Range Not Restricted To List

Dec 15, 2007

I am attempting to use a named range as my Validation data source but have two questions:

1) It seems when you use a named range as a source the user can enter in any data they want in that cell. I really only want the user restricted to the list I give them. If I enter the list manually in the source box then it works but I really want to use a named range

2) My range is fixed to 10 cells and the user can enter in as many variables as they want (up to ten). When a user clicks on one of the cells that the validation is set to read the range the user has to always scroll to get to the top. When selecting the cell Ecel always defaults to the bottom choice in the list. I want to minimize the VBA because Mac users will be using this and VBA seems to be touchy with macs.

## Data Validation Used To Create Separate Data Validation List

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

Feb 25, 2013

I have 300 records in one table (A:I) and would like to create another table where column I is equal to a specific value. I understand that this is possible with an advanced filter, but I would like the second table to populate in real-time instead of needing to reapply the filter each time.

I have been trying to do this with conditional vlookups, but cannot get it to give me all the unique values. Everything comes back to circular references, which I still haven't been able to get to work.

Reasoning: I have created a fantasy baseball "cheat sheet", ranking the top 300 players. Column I is a dropdown menu for the team that drafts each player. As I update this throughout the draft, I would like my "team" to auto-populate in another table as I draft players and select my name in column I.

## Creating Validation List From Table Column Based On Data In Another Column

Dec 11, 2013

If I have a table as noted below with the following assumptions:

- this table will likely grow
- the 'Include' column data will change based on external criteria/formulas, so the 'Include' column will not be sorted.
- Macros aren't an option as this sheet needs to be macro free.

A
B
C
1
Item
Calories
Include

[Code]...

How do I build a formula that I can place in a data validation drop down to only include 'Item's that have Yes indicated in the 'Include' column?

I've been researching this and found answers if the 'Include' column was sorted via offset, but I haven't found any to sift through when unsorted. I feel like there is a simple answer to this that I am missing. Here is the sheet --> ExampleSheet.xlsx

## Dropdown With Value Equal To Combobox (real Time)

May 20, 2014

I wonder if it is possible in real time, to select a name from the combobox (Userform) still open Userfomr pass this value to a Dropdown in the tab?

## Real Time Countdown Timer Function With Ticker

Aug 23, 2008

I'm playing a game that requires me to keep track of money that is increased by a variable amount (pre-calculated and in a cell) and in a fixed time interval of 51 minutes weather I play it or not so I want to be able to keep track of the progress of the money gain outside of the game.

So, I need to know the macro coding for a real time counter that will increase the total money amount in one cell based on the variable income (declared in another cell) in the 51 minute intervals.

now... I also need excel to keep track of the value increase even if excel is closed (by the difference in time from when excel was closed last).

## How To Implement Real-time Stock Price(s) Into Excel File

Aug 1, 2014

Currently I am using MS office 2000 premium. I would like to implement real-time stock prices (i.e. ticker MSFT) from the Nasdaq website (NASDAQ Stock Market - Stock Quotes - Stock Exchange News - NASDAQ.com) into a single cell, so that this cell shows the current stock price only.

By a click on the hyperlink symbol in the excel sheet I entered the following information... Microsoft Corporation (MSFT) Real-Time Stock Quote - NASDAQ.com and clicked the OK button.

Instead of receiving the real-time stock price information directly into the single cell, a new window opens with the Nasdaq website and all kinds of information that is not needed.