Dropdown Box To Give Specific Data Range?

Jun 18, 2014

I have a list of sites and depending which site I select from my drop down box I want to retrieve that data from my full data sheet. How can I go about doing this?

View 3 Replies


ADVERTISEMENT

Give Specific Value To Each Cell Within Range?

Mar 8, 2012

I am currently using the .resize function to give a specific value to each cell within a range.

First of all, is it the most efficient way to do it?

If so, why am I getting error "Application-defined or object-defined error"

with this code :

Code:
ThisWorkbook.Sheets("Merge").Range(Cells(3, 2), Cells(4000, 2)).Resize.Value = "TEST"

View 2 Replies View Related

Dropdown To Give Value To ANY Cell

Oct 24, 2008

Excel 2003

Lets see if I can even explain what I want to do.

will have a date column

Will have a total calorie column (this will be a sum of several column containing individual foods)

On a separate worksheet (food sheet) will be a list of foods in one column and their calories (per serving) In the next column.

On the first sheet I want a drop down that lists all the foods on the "food sheet). selecting from this drop down will use a vlookup (or hlookup - depending) to arrive at a caloric value.

So here is (are) the problems. (and the solutions need to be NON-VBA)

1. How do I tie the drop down selection to a vlookup (macro maybe)

2. How do I use that one drop down to populate any cell on sheet one?

can this be done without coding?

This will be an exercise for an excel class to teach vlookups, linked sheets,
making a dropdown, using a macro, formulas, IF statements............

The class is probably not ready for VBA which is why this needs to be a non-coded solution.

I am open to other solutions (related topic) that can bring together all of the above learning concepts

View 14 Replies View Related

Search Box / Dropdown To Return Specific Data

Nov 15, 2013

I have a workbook with several sheets of data - it's basically names with a series of assessment data. Each name and selection of data is in its own row.

I want to build an additional sheet with a search function/drop down box that wil allow any name to be searched for/selected and return that specific name/all/specific cells in that row. I believe this could be done either using VLOOKUP or a Macro but I am only a beginner with either of these things.

View 7 Replies View Related

Load Only Non-Blanks From Range Into Data Validation Dropdown

Dec 12, 2013

On the Control Panel sheet of the attached book, I need to load only the non blank cells from BQ3:BQ9 into a data validation drop down.

View 8 Replies View Related

Dependent Dropdown Lists Based On Dynamic Data Range?

Apr 19, 2013

I have data that resembles (I have also attached an example workbook as well if that is easier)

Column A.................Column B................Column C..................Column D.............Column E................Column F
Year.......................Quarter...................Month..................Product-Code...........[data A]................[data B]
2013.........................Q1.........................Jan........................SD-21...................13%......................0.05%
2013.........................Q2..........................Apr........................NV-12...................7%.......................6%

etc

I am hoping to have, on another sheet, a series of dependent drop boxes- Year, Quarter, Month, SKU- which after selecting then displays 'dataA' & 'dataB'.

So you could select- List 1: 2013 ----> List 2: Q1 ----> List 3: Jan ----> SD-21.................and then 13% and 0.05% are displayed.

The data will be continuously added to and so needs to be based on a dynamic data range.

I know that a pivot table is the perfect way to do this, however my bosses have requested that I do not use that format so it is easy for all staff to access.

So I think I need to construct some sort of dashboard sheet, I have experimented with OFFSET from other posts, but so far have had no joy.

View 14 Replies View Related

Choose Selective Data From A Range Driven From Dropdown List?

Aug 23, 2013

I have a spreadsheet listing various pieces of computer hardware, software and mobile accessories, etc with costs attached.

I have created a dropdown list of staff profiles eg a sales representative, finance assistant, IT administrator.

What I want to do is get Excel to pull the data through from the data sheet, to a printable summary output form (something you could also cut and paste from). For example, a sale rep would need to be set up with a laptop, laptop case, mobile phone, etc, so when you select Sales rep from droplist, I want that action to pull all the associated data through to the summary form. Similarly, a Finance assistant would require different kit , perhaps a desktop PC , monitor , etc.

Next to my data I have created columns with "Y" for yes in cells containing kit that match the staff role requirements, but I am not sure of the best way to pull this together.

View 3 Replies View Related

How To Give Range To Find The Sum

May 16, 2013

I'm doing a statistics workbook there I need to add untill the given value in the cell

ex: if gave 5 in the cell we want to get the total until only to 5th cells

(M/M/S)FIFS/INF/N)

No of Server
3

l
0.20

n
Coeffient
P(n)

[code]....

We changing the value in N=5

View 7 Replies View Related

Dropdown Lists Data Went Selected Won't Show In Next Dropdown Only Remaining Data

Mar 11, 2013

I am trying to pull data from more than one drop down but don't the same data to show if already use, example as follow:

First drop down Contains:- Pants
Shirts
Hats
Shoes
Dresses

if I chose Hats it should not show up in the second drop down

second drop down Contains:- Pants
Shirts
Shoes
Dresses
Is this done in data validation or combo Box? if so how?

View 2 Replies View Related

Copy Named Range Data To Worksheet Based On Dropdown Or Combobox

Feb 10, 2013

I have a worksheet called "Lookup" with several dynamic named ranges (each is 1 column wide) including facility, department, shift, etc. On a summary worksheet in the same workbook I want users to use a dropdown or combo box (don't care what type) in cell B2 to select a facility and then based on their selection, copy the department named range data and paste it into the summary sheet beginning in cell A5 and paste the shift named range data into the summary sheet beginning in cell B5.

Example: user selects "AR Plant" from the dropdown or combo box and the data from the "AR_Rpt" named range is pasted into cell A5 and the "AR_Shift" named range is pasted into cell B5.

View 9 Replies View Related

Make A Data Validation Dropdown List That Also Enters A Range For Cells

Apr 5, 2009

Make a Data Validation dropdown list that also enters a range for cells, for example:

Kitchen ¦ High ¦ Upto 100 ¦ Easy ¦ Blank ¦ 2 ¦ Daily ¦ >6 hrs ¦ Medium ¦ >1 week
Lounge ¦ Low ¦ Large ¦ Easy ¦ Blank ¦ 0 ¦ Daily ¦ >6 hrs ¦ Medium ¦ >1 month
Bedroom ¦ Low ¦ Large 100 ¦ Easy ¦ Blank ¦ 1-3 ¦ Daily ¦ 1-3 ¦ Low ¦ >1 year
Bathroom ¦ High ¦ Upto 100 ¦ Easy ¦ Blank ¦ 0 ¦ Weekly ¦ 1-6 ¦ Low ¦ <1 year

I can make a dropdown list for:

Kitchen
Lounge
Bedroom
Bathroom

But if I select "Kitchen" I would like it to enter the rest of the cells in that row:

Kitchen ¦ High ¦ Upto 100 ¦ Easy ¦ 2 ¦ Daily ¦ >6 hrs ¦ Medium ¦ >1 week

The total number of rows I use is 10, and the number of items in the dropdown list will be 30.

The data in the cells 2 to 10 does not change from that first set to its data menu name.

View 4 Replies View Related

How To Give Range In Constrain Solver

Sep 3, 2009

FromToTriggerrange26Stop-lossrange25Combination triggerlevelie: 4Combination stop levelie: 5Profit $10

How can I make "Solver" run a check on which combination of trigger and stoploss that gives the highest profit? The range is in two different cells, but I can't get it working. I guess that it's my lack of skills, and not "Solver" that isn't complex enough.

View 9 Replies View Related

Completing Range Of Cells Based On Data On Another Sheet Prompted By Dropdown Selection

Mar 23, 2014

First sheet has a client name and several cells of data along the same line. All of the data on this sheet is kept long term.

The second sheet is completed copied and deleted. In completing the second sheet the client name is selected of a drop down list and pulled from sheet one.

What I want to do and not sure if there is a way is that when I select a client and populate it on sheet two I would like the related data on that clients line from sheet one to populate to same named lines on sheet two.

if this is possible and if so provide a brief description of how it would work

View 3 Replies View Related

Need Lookup Formula To Give Column Range

Jul 26, 2014

I'm trying to write a formula that searches for the name of the column on one sheet, finds that column on another, then results in the column letter. So, ideally, if the first three columns in the first tab were "Team," "Name," and "Total," respectively, I just want a formula to search for "Name" and give me back "B."

I want to do this in case anybody ever moves the "Name" column, for example, from column B to column C -- so then it would search for "Name" again and change the range in the formula to "C" because that's where that data lives now.

Currently, I'm using it for a COUNTIF() function that is a very simple: =countif(indirect("'"&$A2&"'!K:K"),$B2), where A2 is the sheet name. The latter half of $B2 is fine -- but I would love to have a more complicated formula take the place of the range "B:B" in case the "Name" column ever got moved.

View 8 Replies View Related

Locate Value In A *rectangular* Range, Then Give Column & Row

Feb 7, 2008

I need a formula (or formulas) to give me the "coordinates" (Column & Row location) of a certain value in a large rectangular range. Intuitively this should be doable with a smart combination of VLOOKUP, HLOOKUP, INDEX, MATCH, ADDRESS and/or CELL functions (maybe in the form of "array formulas") but I can't seem to find the way...

Here is an example of what I need:

I have a large rectangular range (A10:F500), full of numerical values. Then in cell A1 I have one of the (many) values contained in the range, let's say, 3.14159. I need two formulas that give me the following:

a) A formula in cell B1 that tells me in which COLUMN of the range the value 3.14159 is located
a) A formula in cell C1 that tells me in which ROW of the range the value 3.14159 is located

If the range consisted of a single column (or a single row), this would be an easy INDEX and MATCH combination.... but I'm dealing with a *rectangular* range here...

View 9 Replies View Related

Hiding Runtime Errors: Refreshes A Specific Data Range That Imports Data From A Text File

Nov 25, 2009

I have a macro that first refreshes a specific data range that imports data from a text file as such:

View 2 Replies View Related

Copy Data Into Specific Range?

Jul 4, 2014

I have a worksheet which contains daily results and want to copy the formulas within the Cell F2 but only copy into the cells where the data has recently been added.

EG: Within the attached worksheet i have just entered the data for the 03/06/2014, so i only want the formula to be copied into column F for this range.

Potential it could the vb code could look at Column B and if blank then paste.

View 8 Replies View Related

Find Data In Specific Range

Dec 14, 2006

I have a piece of code used to find a matching date in a column and then unhide that column. Code works because there was only one date on the sheet other than the one's I was trying to find. I now have to store additional dates in the sheet and this is disrupting the search as it stops (as it should upon finding a match). I would like to see if there was a way to modify it so that it will look for the match in specific columns i.e. Specifically the dates are located in AX1:IX1. Here is the code

Columns("AX:IK").EntireColumn.Hidden = True
Dim DateToFind As Date
DateToFind = Range("a1")
Cells.Find(What:=DateToFind, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False).EntireColumn.Select
Columns(ActiveCell.Column).EntireColumn.Hidden = False

View 3 Replies View Related

Updating Range Of Data To Start On Specific Dates?

Mar 6, 2014

I am looking to have the data ranges in tab 'Type' update automatically in 'Output' for a particular start date. So for example if I enter LBO into cell E3 in the 'Output' tab it will input the data range for LBO from tab 'Type' but from a specific start date.

I know I can achieve part of this via LookUp functions but it is the start date that has me stumped.

View 8 Replies View Related

Returning A Certain Result Based On A Specific Data Range

Nov 26, 2009

I have a problem with a calculation i am trying to do. I have to calculate holiday allocation for staff but i my company also offers length of service bonus days. After 5 years you get 1 extra day, after 10 years you get 2, after 15 you get 3 and so on. In my sheet i have a formula based on their start date that tells me their length of service in the following format: 10 years, 2 months.

I have put an IF Statement in for each of the different milestone criteria but the result is not coming back so i know i am missing something. Is there something wrong with the formula itself or is there a better one that will allow me to put the start and end of the range i want it to look at? For example can i tell it to say if between 5 years and 9 years 11 months put 1, if between 10 years and 14 years 11 months put 2 etc in column H.

View 3 Replies View Related

Select Specific Coloured Cells From A Range Of Data In One Go

Feb 3, 2010

How do you select specific coloured cells from a range of data in one go, without having to scroll through the worksheet and pick them out individually?

View 9 Replies View Related

Add Dropdown To Specific Cell Color

May 27, 2014

I have the following code from a previous program I made and need converting it.

[Code] .....

I want it to do the same thing except it will only add a dropdown menu for a Cell that has a background of Yellow.

View 2 Replies View Related

Forumla To Give A Starting Number And Take One Away Each Time A Code Or Codes Are Dislayed In A Cell Range

Mar 22, 2009

In 1 cell i need a forumla to give me a starting number and take one away each time a code or codes are dislayed in a cell range. Something like this

A1 = 23 days or any number days i needed

Cell range B1:C52

every time a selected 1 or 2 letter code appears in the cell range i want A1 to subtract 1 day from the starting number, i would need it to subtract half a day if one code appears ie HD, the codes may be P, OT, HD

View 4 Replies View Related

Verify Data In Specific Range Or Move To Next Open Cell Down And Past Value?

May 9, 2013

I have a command button that will run paste a value in a specified cell but I also need it to check for the next open cell and paste the data if the previous cell in a specifid rage is already filled with data. Here is my code I have below. *jumps with Joy* My Range is E6 to E10

VB:
Private Sub CommandButton1_Click()
Dim f As Double
Dim t As Double

[Code].....

View 1 Replies View Related

Formula To Look At All 9 Data Points And Give The Place Number

Jan 19, 2009

Column P is the percentage of body weight lost...

P
-1.98%
-2.92%
-3.87%
-3.48%
0.00%
-0.10%
0.00%
-3.56%
-3.57%

What I need is a formula to look at all 9 data points and give the place number. Remember that the lowest number is actually in first place.

View 2 Replies View Related

3 Dependent Dropdown Lists / Combined Selection Provides Specific Information

Nov 11, 2013

I'm trying to create a database where when 3 dependent drop down list selections are combined, information is displayed.

I've tried VLOOKUP and INDEX/MATCH but to no avail.

Attached is the spreadsheet and what I am trying to accomplish. On the first sheet ("budget form") is a table named "Event Costs - room hire". I have created three drop down lists that interact based on what you select. Some rooms offer different services and so are dependent on the selection.

However, I want the final box to display the price depending on what is selected in the previous 3 lists. The amounts and a table I have created is in the "Product Database" sheet and the table is labelled, "Room Hire Control C".

View 5 Replies View Related

Indirect: Reflect The Data In The Active Sheet Or Give Me A #VALUE Error

Jan 16, 2009

I am running the formula =VALUE(RIGHT(CELL("filename"),15)) to read the tab name in a workbook with 90 sheets. When a new account is opened, the operator inserts a new sheet from a template (which contains the above formula), and labels the tab to the account number. The formula then 'reads' in the account number and performs look-ups based on it.

The problem is that the sheets are only working when the sheet is active. They all seem to reflect the data in the active sheet or give me a #VALUE error. Where am I going wrong? How do I fix the problem?

View 3 Replies View Related

Specific Cells Populate With Specific Numbers When A Value Within A Range Is Entered

Oct 9, 2009

Here is what i am trying to achieve. If the date 2/20/2010 is located at F53 & the cell next to it at H53 is populated with a number between 1 & 16, then i want the cell at J11 (42 rows further up) to auto populate with the number 1. When this occurs the cells beneath this, from J12 to J52 should also auto populate with the with consecutive numbers from 2 to 42. Would also like to see the cells with numbers 1 to 28, automatically format to orange & the cells containing numbers 29 to 42 automatically format to yellow. I plan to have this condition repeat several times later in the year, at dates that are to be decided. When these dates are decided i want to be able to enter a number from 1 to 16 & next to the date & all of the above automatically occurs.

View 14 Replies View Related

For EACH Cell In Specific Range Meeting Specific Criteria - Call Sub

Jul 1, 2014

Looking to have a macro call a subroutine every time it finds a cell meeting specific criteria.

Code in plain english would look like this:

For EACH cell in range A1:BZ500 meeting the following criteria:

Cell value is a date

AND

Cell's date is at least a week or more in the future

AND

Cell background (Fill) = RGB color code: (R:191 G:191 B:191)

DO the following:

Call repeatingsub

View 11 Replies View Related

Lookup Range Of Cells And Populate Specific Cells Based On Matching Data?

May 23, 2014

I am trying to build a staff roster. The staff rotate over a 4 week cycle. the name of the staff member, and their shift needs to be looked up from the key then matched with the particular week. the name and shift then need to populate specific cells.

I have attached the worksheet so you can see what i am trying to achieve.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved