Free Microsoft Excel 2013 Quick Reference

can have data display in cells based on what in drop down list?

Say i have sheets Monday - Sunday, and on ever sheet just displays time spent on certain task. Also i have at top of every sheet Three cells for Name, date, and drop down list saying week 1-4. question i want to know is there a way i can have all the data on each sheet connected to weeks 1-4.

say its week 1 and the employee fills out all the tasks he did and how long it took. then week 2 comes and switch week 1 to week 2 in drop-down list, is there a way to make all the data from week 1 only there when week 1 is displayed in say C2. so i can just pick from drop down list and i open file from july 2006 and select week 1 and will show what they did that week, then select week 4 and show what they did that week in exact same cells?

I been playing with Vlookup but not sure if this does exactly what i want or if so how to use it right.

Post your answer or comment

comments powered by Disqus
I have an issue I can't seem to solve no matter how many searches I do online or on these forums. Perhaps I'm not putting in the correct search terms, but here's my problem ...

I have a "memo of meeting" excel document used to capture notes during client meetings. In this document there's a cell for "purpose", "agenda", and "notes". The 'consultation type' cell is a drop-down populated by values set in Sheet2. The 'agenda' & 'notes" cells are then populated with information (also stored in Sheet2) based on what drop-down option was chosen in the 'purpose' cell. I'm currently making this happen with basic =IF() statements; however, the problem is that by doing it this way, the =IF() statement must reside in the actual cell which will end up showing the data.

This is undesirable as there may be additional notes, agenda items, etc. that I'll need to add during the meeting and as it's currently setup, I can only have either the pre-populated info OR the ad-hoc notes - not both. It seems a simple fix. Maybe put the =IF() statement in a cell off to the side but have it "assign" the value to 'agenda'/'notes' but that doesn't seem to work.

What am I misssing? Am I going about this the wrong way? Please help.


I have an Excel 2007 worksheet that contains a drop-down list with different options. Based on the users selection of one of the options, I want to restrict their ability to enter text in certain columns in that row. For example, if on row 3, the drop-down list in A3 contains A, B, C, D and E, when a user selects A, I want to prevent them from entering any data into G3, J3 and M3 but allow them to fill out everything else in the row. If on row 4, the user selects B, I want to restrict entry into different columns, such as C4, D4 and I4. There will be restrictions on each option. I'd also like to have multiple VBAs or expand the macro to similar restrictions on a different drop-down list in another column. For example, combining the above restrictions with another that is based on the drop-down list in N3 that has options for yes, no or N/A, I want to futher restrict which cells are editable based on the user's selection. The two drop-down fields will be independent of each other and would not be affected by any related restrictions.

I've been researching this issue but don't think I'm using the correct search terms so I haven't been able to find answer.

If you would like an example or have any questions, please let me know.


I am desperately seeking a way to list data based on one linked option selected in a drop down pick list. So for example select an author from a drop down list and excel automatically displays in another list all the novels they have published. I want to see the drop down list selectionb and all the results, save the selected data. I also do notwant the inputter to see all the raw data just that relevant to their selection.



I have searched your very helpful forums, and have found a wealth of information regarding drop-down lists, but I do not see this specific task represented.

I am attempting to populate a row of several cells on a worksheet using a drop-down list, using data from a seperate worksheet in the same workbook. The worksheet containing the data will be hidden (I do not think that matters in this case).

Do I place a VLOOKUP function in the first worksheet cells?

Please see attached sample of what I am trying to accomplish.


As a follow-up question, as time goes by, the data in the source worksheet will be appended with new items (additional rows of data). As each new item is added, will I need to edit the formulas, or is there a way for Excel to dynamically add the new data? This might be stretching my wish list a bit but I thought I'd ask.

Thank you.


Is there a way to take a cell range from workbook A and link it to Workbook B based on a drop down list?

say i have Drop down list in Woorkbook B with January 2005 - September 2008 in it. Is there a way so when i have June 2007 selected it will link range C33:C37 from workbook B to cell range C33:C37 on workbook A Displaying Info from wookbook A to Workbook B. Then change drop down to workbook to july 2008 and have it display something from even workbook on and so on for all the drop downs?

I'd like to have other cells in the row of the drop down list change
according to what I've chosen in the list. Is a drop down list the
best method for this application?

I have a table of material properties that I would like to read from
based on what material is chosen from the drop down list.

mae1778's Profile:
View this thread:

I'd like to have other cells in the row of the drop down list change according to what I've chosen in the list. Is a drop down list the best method for this application?

I have a table of material properties that I would like to read from based on what material is chosen from the drop down list.


I have a worksheet with a legend sheet and various other sheets. I would like to in sheet 2 create a drop down list in column B related to sheet (Legend) column A1-C1. Then based on the selected value from dropdown list in column B would like to create another dropdown in column C from the legend sheet A2-A4 or B2-B3 OR C2-C5

Legend sheet


How can I conditionally format a cell based on the value in a different cell.

I have two columns of information. None of the information in the cells I am
trying to format is numeric. I am trying to track names to loans that are
with the company. If they wrote outside, I need to keep track of which other
company it was written with.

The First column currently has a conditional format to highlight the cell if
the value is = "Home". The other values that are entered are variations of
"Outside". I have to keep the variations as they are, in order to track which
loans went with which other company.

The second column has the borrowers name. I need this column to highlight
the cells if the loan was written outside.

So if the cell is highlighted in column A, it should not be highlighted in
the column B.
And Vice versa.

Creating macro that inserts data into a cell based on certain critera in spreadsheet

let's say that I have a range of data in column A and based on that data I am inserting certain values in column b. For example, if cell a1, contains phrases like "£25" and/or "music" I will put "concert" in cell b1. Additionally, if cell a1 doesn't contain those phrases but contains other ones such as "£10" and/or "book", I will put "study" in cell b1. I basically want a way of automating this in a macro, please assume that I have little or no experience of creating macros.

Many thanks in advance

Hi there,

I'm am trying to restrict the length of data that can be entered into a cell based on a drop down in that row. I have achieved this, but what I need to be able to do is use data validation to restrict the cell length to one of two values - so for example the length can only be 6 OR 9 characters long.

I cant see how to do this. At present the best I can do is rectrict to between 6 and 9 - but this would allow 7 and 8 which is not allowed.

Any advice would be greatly appriciated.

Many thanks,

Hello all,

I am currently working on a workbook that has two different sheets.

The first sheet is a "UI", and the second contains all the data.

I have populated some of the cells in the first sheet with drop-down menus based on categories of data on the second sheet. The final step of my project is to populate the cells next to the drop-downs on the first sheet with data in the cells next to the lists on the second sheet, based on what is selected in the drop-down.


One of my drop-downs is tied to a list of items on the second sheet. Each item on the second sheet has statistical data in cells in the same row. When a particular item is selected on the first sheet's drop-down, I want the cells in the row next to that drop-down populated with the data in the cells in that item's row on the data sheet.

How do I do this? I've been looking for a while and haven't found an answer yet.

Thanks in advance!

have a file that has two sheets in sheet two are list of manufacturers in
A1-A25, manufacturer brand code in B1-B25, and brand code discount in C1-C25.
in sheet one in cell E1 i have created a data validation where there is a
drop down menu that has the list of manufacturers from A1 -A25 from sheet
two. how do i set it up so from the drop down list if i select the
manufacturer that in A1 on sheet two that the information in sheet 2 in cells
B1 and C1 will be put into cells F1 and G1 in sheet 1

Im trying to find out if it's possible to populate a range based on a drop-down list choice you make.

Heres a small sample of what im working with for visual aid.

Spread Sheet Sample

As you can see plans and titles, drop down selection, and 6 plans I intend to refrence one at a time through the drop-down menu (I expect that these will end up on another sheet and hidden). Basically what I am trying to avoid is having to assign every single cell a VLOOKUP function and simply fill in the entire column entitled Aetna Plans instead.

I have looked and tried different things to create what I need but have been unsuccessful (Due to my lack of knowledge in excel).
I need to create a workbook for users with even less excel knowledge than me (Now I am in trouble! ) They will be running excel 2007 or older.

Here's the idea.
I have a workbook with two sheets. Data and Listas. (This is for a spanish speaking audience)
The Listas sheet contains three lists. Column B list options will NOT change or have any more options added. List Name = Ingreso_Egreso. List Options: Ingreso, Egreso. This one I am good with.

This is where my problem begins:
The other two lists, Column D and Column F Will have items added, removed and modified. Therefore I need to have these lists be dynamic. By searching I have made the following list formulas:
for both. I don't really understand it but I think it creates a list based on only the cells that have values from D2 or F2 to the last value. Column D list is named "Ingreso" and Column F list is named "Egreso" (Which matches the options from the previous list)
OK, Now I need the options on the data sheet to be based on the lists.
On the data sheet, for column D I have data validation to allow a list and on the source I have =Ingreso_Egreso to allow only items from the "Ingreso_Egreso" List, so only possible values are: Ingreso or Egreso (This matches the names of my other two dynamic lists) and works...
However, on column F is where I need to see the options based on the input of column D so if column D = Ingreso, then give me the drop down options from the ingreso list and vice-versa. The formula I have on Column F is: Data Validation, Allow List and on source I have =INDIRECT(D2) and I get an error....
"Source evaluates to error" If I click Yes to continue I get no drop down options..
File is attached...

Can someone please help me!

Any advice and help is greatly appreciated..

Hello all - I need help using a conditional format based on a drop-down cell value.
I have a spreadsheet for scheduling - in this example I am using 2 columns, A1 shows a name, B1 has a drop-down list to select an 'absent code' (eg. 'V" for vacation). How can I use conditional formatting to change the color of A1 based on the value of the drop-down in B1? Any ideas would be very helpful....thanks


My brain is hurting!

I'm looking for a way to automatically populate information in cells based on a drop down selection using data validation.


If i choose 3 from the list on column F, it should automatically populate 'used' in column E and 'truck' in column G on the same row.

Thanks for any advice

Creating Seperate Sheets based on certain Drop-Downs in the Main Sheet using Macro..!

Dear Forum,

I need to create 4 Seperate Sheets everyday based on the Data, in the same workbook Once I receive the data from the TeleCalling team to send to the Logistics and the Sales Team for further follow-ups.

The Data is differentiated into 4 Major Categories i.e
1. Not Interested
2. Interested
3. Site-Visit
4. Presentation.

I need to create 4 Worksheets for these four Categories such as CC Not Interested, CC Interested, CC Site-Visit and CC Presentation.

Now this data would be received on a Daily basis from the Call-Center or the Tele-Calling Team and this data will be further Actioned by cascading the information to the appropriate departments.

Now, the "Not Interested" Data needs to maintained seperately for any new promotional campaigns..

The "Interested" Data is of the Customers who have shown interest but not actually willing to make a Site-Visit or ask for a Presentation.
So, this would go to the Sales Team to be Followed-Up..differently.

The "Site-Visit" Data would go to the Logistics team to arrange a vehicle /transport for the site-visit.

The "Presentation" Data would go to the Sales Team who would approach the Clients to visit the company premises or go to their office/residence for a presentation.

So, each data needs to be treated seperately and therefore needs to be actioned differently with different people and teams and sales and customer service strategies involved..

Now, since this is a mundane activity it will be difficult to actually make Seperate sheets on a daily basis..

I thik using VBA MAcro it will be easier, so can someone please help me on the same.

There's more to it each days data needs to be created seperately or better would be to append to the earlier days data from the bottom..

The Call Center would be giving Historic Data which is ideally 1 or more than a day old and therefore this data would keep on increasing in the CC Main Data and I need to create 4 Sheets with the aforesaid categories and then the next time the new data goes in this 4 same Sheets from the bottom.

Please suggest if there is a better option..

Warm Regards

I am creating inventory and p.o. forms but am having trouble with filling in cells based on previous drop down selections. For example if I select in cell A:1 a certain manufacturer's name I want cell B:1 to recognize that and only offer the certain products that particular manufacturer offers.
Thanks in advance,

Hi! I'm working on making a spreadsheet that allows me to select an item type from a dropdown list (through validation) and then would copy in a range of values from another worksheet based on my dropdown selection.

The set-up: I am primarily concerned with two sheets in my workbook, BiS and Weights. I have a database of items and their associated values listed in Weights. I have already managed to get dropdown lists of my specific item types using named lists and have put that in the BiS worksheet.

What I would like to do is have the values in the Weights worksheet copied over in the same order and number of cells based on what item I select from the dropdown list.


Seems I'm going in circles. Trying to make a chart that's dynamic based on the selection of a drop down list (highlighted in yellow). The chart should show 12 months worth of data based on the selection from the drop down list.

Below are the list of defined names for Chart.

Range =OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-1,0,-MIN(12,COUNTA(Sheet1!$A:$A)-1),1)
North =OFFSET(Range,0,1)
South =OFFSET(Range,0,2)
West =OFFSET(Range,0,3)

Can't seem to work out how to reference the drop down list conditional to the 12 months.

Any help is appreciated. Have attached excel sheet.


I have numerous lists which contain code and price of items.

I would like to create drop down selections which will populate the following field based on the selection made.

In my attached example i have three columns that need to be populated.
I have managed to figure out how to display the correct options in the 'List' & 'Code' columns but can't figure out how to get the correct 'price' to display based on the selected item from the 'code' drop down list.

I hope this makes sense and someone can help me?


I have a sheet that returns different performance results for 21 categories,
based on a drop down list of 3 choices. Depending on the item selected from
the list (A, B, or C ), different values populate the 21 categories, and a
"total" is shown in a single cell, based on the respective choice.

Currently, I have to MANUALLY select from the drop-down list to see all
three different totals.

I would also like a "quick reference" to know all three results,
immediately, for a quick comparison.

How can I create a separate group of 3 cells, that will AUTOMATICALLY assume
that each choice in the drop-down list was made, and let me instantly see the

I thought of using IF, but that would still mean I would need to manually
pull the list box down and select one of the three choices.



Hoping someone can help, i'm a bit inexperienced with macros.

Basically what i want to do is have a user select an option via a drop down list. I need a macros that will read the selected option then pull data from a corresponding worksheet. The selected option will be in column A and wherever it appears the macros would automatically pull that whole row i.e. the the corresponding values in column b, c, d, etc. Is this straight forward?

No luck finding an answer? You could always try Google.