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


ADVERTISEMENT

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 View Related

Give A Blank Cell If

Dec 27, 2007

I have a LOG sheet where I am getting all my data from,
and a TIME and DUTY sheet..

COL A Has Dates, a4 to a34, Limited to 31 lines for the dates
and in col F I have # values, (1.2-2.2 etc).
The formula I have in Col F is
=SUMIF(LOG!$A:$A,#REF!,LOG!F:F)

but when i get to the end of the month, say for example the last 2 lines of the time and duty sheet, when a32 is 29/ Feb, the next row is blank and same with next row. (leap year).

How do I get the formula above to ONLY calculate when the cell in COL a on the same row, when there is a DATE value in COL a?

View 9 Replies View Related

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

Vba Give Format To A Cell- Specify A Type For It

Jul 27, 2007

Does anyone have any clue of a VBA function that gets a cell and Changes the type of itīs contense?

I have been using the Cstr function as in the next example but I dont get the result I want

hojaRES.Cells(i, 6) = CStr(hojaRES.Cells(i, 6))
If IsNumeric(hojaRES.Cells(i, 6)) Then
MsgBox "nothin changes :("
Exit For
End If
hojaRES.Cells(i, 6) = "0" & "34" & hojaRES.Cells(i, 6)

My problem is that i want to add 034 prefix to a telephone number, and as it treats the data as numeric; number 0 (before34) is deprecated

View 3 Replies View Related

Give Letter Numerical Value And Use It To Format Another Cell?

Jul 22, 2014

I am a teacher and I want a column to turn a particular colour if a pupil has met their target grade and another if they have not. Our grades work as letters A* being the best then descending as follows A, B, C, D, E, F, G, U.

I have a column with the pupil's target grade (one of the above letters) and another column with their current grade. How do I get excel to understand the ranking of the letters and format the current grade column according to whether they have met their target or not?

View 8 Replies View Related

Put Together An IF Statement Which Will Give Me Certain Output Depending On The Particular Value Within ONE Cell

Mar 5, 2009

I am trying to put together an IF statement within excel which will give me certain output depending on the particular value within ONE cell.

For example:

If cell L5 has a value between 0 and 1.00 output value 0
If cell L5 has a value between 1.01 and 2.00 output value 20
If cell L5 has a value between 2.01 and 3.00 output value 40
If cell L5 has a value between 3.01 and 4.00 output value 60
If cell L5 has a value between 4.01 and 5.00 output value 80
If cell L5 has a value between 5.01 and 6.00 output value 100

View 4 Replies View Related

Give Cell Number After Letter With Function.

Dec 3, 2009

I want the A4 cell contains the calculation of B4 (but the number gained from the funtion row and if the B1 cell contains the number 10 the K(B1)=K10

[A4]=B(row())*K(B1)

View 4 Replies View Related

Insert Row Based On Cell Value :: Give Input Box On Click

Oct 8, 2009

when a cell in column "K" is clicked, an input box gives the user the choice of entering a number between 1 and 12. That value then inserts row(s) based on input value ..

View 6 Replies View Related

Give Cell A Unique ID Based On The Contents Of 2 Other Cells

Aug 11, 2008

I am trying to give a cell a unique ID based on the contents of 2 other cells.

Basically in cell A1 a value is entered between 1 and 13 and in cell A2 another value is entered between 1 and 4 to find the unique ID that matches these I need to look in DF4:FE4 to find the number that matches A1 ( there will be 4 matches) then look in thoses 4 matches in DF5:FE5 to find the match to A2 ( there will only be 1 match ) then return the id from the relvant cell in DF3:FE3.

View 9 Replies View Related

Nested If Statement That Doesn't Give Blank Cell When It Is Evaluated?

Aug 29, 2012

I have a nested if statement that doesn't give a blank cell when it is evaluated. The cell is general formatted but results in a '0' in the cell instead of a completely blank cell.

If statement is:
=IF('Sales info'!B16="",'Sales info'!B6,IF('Sales info'!B6="","",'Sales info'!B16))

Why is this giving me a 0?

View 9 Replies View Related

Excel 2007 :: Why Does Using Cell References In Formula Give Incorrect Value

Sep 30, 2011

Surely it's user error, but it honestly seems like Excel is not calculating 2 numbers correctly. At work, we have office 2010 where it is not calc'ing correctly; at home I have 2007 and it's fine, but anyway, here's my situation:

Cell A1: 0.003529
Cell B1: 0.48

If i put in Cell C1: "=B1/A1" i get a value of 136.000000 But that's not correct, it should be 136.01587... But if in Cell C1 i put "=0.48/.003529" then i get 136.01587...which is correct.

Why does using cell references in my formula give me the incorrect value?

View 2 Replies View Related

Paste Text Paragraph Into Excel And Give Each Individual Word Into Its Own Cell?

Sep 10, 2013

How do I paste a text paragraph into Excel and give each individual word into its own cell?

View 6 Replies View Related

Chart With VBA Based On A Row With Values That Will Color The Offset Cell Interior Red And Also Give It A Value Of 1

Feb 15, 2010

I'm trying to make a simple chart with VBA based on a row with values that will color the offset cell interior red and also give it a value of 1. (look a the example sheet.)

View 4 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

Cell Value Is Dropdown List Based On Other Cell Value Which Is Also Dropdown List?

Aug 4, 2013

fill the column "Level 2 Area" based on the value selected from "Level 1 Area" which is coming from a drop down list. So the "Level 2 Area" will be a drop down list also based on the selected value from the drop down list from "Level 1 Area".

Level 1 Area
Level 2 Area

View 7 Replies View Related

Match Value Of 1 Cell To Multiple Cells And Give Multiple Values

Oct 7, 2006

Sheet 2 is the problem.

I need to match cells e3:h3 whenver the dates change in in cells c5:c9 and then put there result of cells b5:b9 into the corresponding cells of e5:h9.

Basically what I'm saying is that I want to keep a track of all previous pay amounts from each pay period. So when the next period changes the date it also copies the new pay amount to the corresponding date of the previous pay section.

I have included a sample.

View 9 Replies View Related

Performing IF Cell Contains Number THEN Copy That Number ELSE Give 0

Jun 14, 2013

Looking for a way to search an active cell (that has been found using macro) for numerical data and if there is some present paste this in a different location

If there is no numerical data in this this cell then paste a 0 in a different location

An example of the string contained in the cell

Product 7.5% wheat <- i would want 7.5 (or 7.5%) copied to a different cell
or
Product X <- i would want a 0 copied to the cell the 7.5 would be in from the above

I can find the cell to search in, i just do not know the correct way to extract the information I need.

View 9 Replies View Related

Give Cell A Value Based On Length Of Row (variable Row Length)

Jan 9, 2010

My worksheet contains data with the reaction times on a psychological test. Each respondent in the test has 280 rows in my excel sheet.

The 'perfect' length of the row, is from A to M. When an error is made in the test, the length of the row will increase. So the error length can be A to AA.

For me it is important to analyse the error. So I would like to give a perfect row length, the value 1, and an error row length a value 2.

So, in conclusion:

If:
Cell length = A1 - M1? --> Copy A1 B1 C1 (A B Cof that row) to Sheet3, and give D1 in sheet 3 the value 1

Cell length >= A1 - M1? --> Copy A1 B1 C1 (A B C of that row) to Sheet3, and give D1 in sheet 3 the value 2

View 9 Replies View Related

Copy / Paste From Cell In One Sheet To A Cell In Another Dependent On Selection Of Dropdown Menu

May 31, 2012

I have a workbook with two sheets. The idea behind the workbook is an Interview Guide to be used just before an Interview. For now my problem is this.

Sheet 2 "Competencies" is just data. It stores competencies with their associated definitions and questions.

Sheet 1 "Control Page" is the sheet where the questions will eventually go. The user (Interviewer) will input data on the first two pages which will include name of candidate, date of interview etc. but they will also select 5 Competencies from the already existing drop down menus on page two. From there as the selections are made I wish for a code to copy the corresponding definition on sheet 'Competencies" to cells lower down in sheet "Control page".

I don't see a place to upload a file as I have a sample of the sheet ready to go.

View 2 Replies View Related

Changing Value Of Cell By Change Of Value In Another Cell Using Dropdown List

Jun 7, 2013

I have a spreadsheet with numerical data in B1:B11 and textual data in C1:C11. The numerical data is a couple of digits, nothing big, and the textual data is always two letters, in this case AB, AC or AD. I have the options for AB, AC or AD for each of the cells in C1:C11 in a data validation drop down list. What I'm trying to do is make a VBA macro, of some sort, that acts upon when, AB for instance, is changed to AC, and then multiply the adjacent numerical value in the B column by a conversion value:

AB -> AC = 3
AC -> AD = 7
AD -> AB = 4

So for example, if I had cell B2 as 10 and C2 as AB, then changed AB to AC using the drop down menu, the macro would recognise the change, select the adjacent cell, B2, and multiply it by 3 to get 30, and put 30 in B2.

This is what I've got so far, and I'm no VBA expert, so I've probably made mistakes in multiple places. At the moment, the code below is getting stuck on active.celloffset - and I'm really not sure why. I did manage to get it working with specific cells rather than a range, but that only ever worked once and I had to close and re-open excel to get it to work again. Is my code any good, or am I coming at this from the wrong direction?

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Code:
Static ov As Variant
For Each Cell In Range("B1:B10")

[Code].....

View 9 Replies View Related

Dropdown Box Coding To Another Cell

Dec 24, 2012

I have a drop down box with the 10 different shifts we run here at my work. Ie - Daytime (0700-1500), Nightime (1500-2300) etc. I have these on a drop box as stated earlier (Cell 6) - and want to code a selection from within the box, to effect Cell 7 with the approriate hours worked depending on what I selected in Cell 6.

View 2 Replies View Related

Add Dropdown List To A Cell?

Mar 7, 2012

How do I add a drop down list to a cell?

View 3 Replies View Related

Same In-cell Dropdown In Two Locations

May 21, 2012

I made an analysis table that shows a list of prices on a certain company. I have a table on one worksheet and I made a chart (from that table) on another worksheet. I'm using an in-cell drop down list on the table worksheet to change to different companies. Is there a way that I can put the same drop down list on the chart worksheet? Essentially, I want to be able to switch the company on either worksheet and have it change on both worksheets. I'd like to keep the chart and table uniform and running together.

View 6 Replies View Related

Selection From Dropdown Changes Another Cell

Nov 21, 2013

I have a a sheet with a drop down, if the selection of Critical in the drop down (A57) is selected, i need E57 to have a message saying we need to elaborate on the explanation or when critical is selected a popup with NEED TO ELABORATE in the description comes up.

View 1 Replies View Related

Dropdown Lists In A Cell

May 29, 2014

Im trying to create a spreadsheet which has a drop down list in a cell, then when a selection is made a predetermined dollar figure will be entered in the cell next to it.

For Example:

Cell A1 Drop down list I have 20 cities listed, If I select St. Louis, I want the cell next to it to populate with $250.

Every city has a different dollar amount tied to it. So any time I select a city I want the specified dollar amount to populate the cell next to it.

View 4 Replies View Related

Dropdown List For Cell

Oct 18, 2007

I inherited the attached spreadsheet and cannot figure out how to change the dropdown control and the tool tips? If you click in either of the top two yellow cells a form control dropdown appears...I can't seem to get to the properties of the dropdown? Also, when clicking in each yellow cell...a tool tip like pop-up appears. Those I cannot find a way to edit either? I can't find any code for any of this...and they all act the same when I disable macros?

View 2 Replies View Related

Getting Linked Cell From Variable Dropdown

Dec 28, 2012

I'm using the following code to get the linked cell and return the value to a range. Is there anyway I can achieve this WITHOUT selecting the shape to get the linkedcell. I find using 'select' is cause me formatting problems.

VB:

Sub finalizeclrx()
Dim s As Shape, finRNG As Range, pk1 As String, SphFill As Long
SphFill = Worksheets("input").Range("K65536").End(xlUp).Row
Set finRNG = ActiveSheet.Buttons(Application.Caller).TopLeftCell
For Each s In ActiveSheet.Shapes
If Not Intersect(finRNG.Offset(-5, -11).Range("A1:A1"), s.TopLeftCell) Is Nothing Then

[Code]....

View 2 Replies View Related

Copying Result Of Dropdown Box To Another Cell?

May 3, 2014

if you can copy the result of a drop down box to another cell in another tab. Want to use front tab as master sheet and drop selected info down to other tabs .I used Form Controls to create drop down box -

View 8 Replies View Related

Cell Reference From Dropdown List

Aug 25, 2014

What is the formula in excel where the cell in reference return a value from a drop down list?

For example: I have a drop down list of HomeLoan Programs with rates from 3 different channels. If I choose a 30yr FNMA with rates 3.875 = 100.125 ; 4.00 101.250; 4.125 = 102.00

If I choose 20yr FNMA it will return the pricing to the cell next to the program.

I provide a spreadsheet.

30yr FNMA works from the drop down but all other programs does not work. I am not sure if I have to reference and index or match.

View 2 Replies View Related







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