In col b10 I have a drop down list box with numbers
e,g, 10
12
14
16
18
Now I am seeking a formula that when say 12 is selected in B!0 a predetermined number is displayed in C10 I have attached an example
If a user selects certain options from a drop down list created by cell verification (from list), is it possible to display an input box and have the resulting input populate on another worksheet in the workbook? I have an attendance template I'm working with and if a user selects OT (Over Time) then a input box is displayed prompting for how many hours. The overtime is then tracked on another worksheet.
View 1 Replies View RelatedI know how to create drop down list but I have a problem to Return different value then is displayed in Dropdown List. For example: On the workbook I have two wokssheets: one with empty cell and secound with list created for dropdown list.
On the secound woksheet I have created: column A namebox "Country" - cointains full name of countries (Great Britain, Australia ...) and column B namebox "CountryCode" - contains country codes only (GBR, AUS ...) When I click on dorp down list on first worksheet, I would like to my drop down list display me list with columnA "Country" but when I choose one of that it would returns me cell from columnB "Country Code".
I have a list of questions that need to be filtered by the user and i was looking for an effective way to do it.
Column A is the question number
Column B is the Question Category
Column C is the actual question.
The user wants to use a dropdown box and select the question category. the results they will get are the question number and the question. there may be several questions within a category so all questions need to be displayed for the category that is selected.
(I havnt explored the use of a list as it gives the user the chance to display all the questions and i am trying to keep the viewing space that this list takes up to an absolute minimum). Also i have mentioned a dropdown box as these are very familiar to the user and so they could use it without the risk of user error. I have attached a list of the questions.
I am looking to find the best method of pulling data from a Lookup Table I would like to set up a drop down with all the relevant Steel Sections we make it work and have it access the information and display just that information a separate face page. The information I have set up on the lookup and have named ranges fro them all But how do I get the drop down to trigger the process.
View 3 Replies View RelatedI have a project I'm working on that requires me to display information from various sheets on a dashboard. I have created a data validation dropdown list with part numbers. When one of these part numbers is selected from the drop down list I need the rest of the fields to populate with the appropriate information from various sheets in the workbook. How do I tell the dashboard to display this information per part number?
see the attached workbook.
Mini-Project Wally Mart (B212-10).xls
This spreadsheet will be tracking sales and purchases. I want to be able to have any purchases formatted as an expenditure (negative sign or brackets) to show it is money out. Column C "Action" has a drop down with either Buy (infrequent) or Sell. I want to be able to have column I "Unit Price" be a negative number if the corresponding cell in the same row in Column C = "Buy". I do not have these prices formatted as currency as their are 2 different currency used and I don't know how to make the correct currency format appear automatically, though I do have a drop down validation column for which currency the transaction was in, so maybe that is possible. I know this is probably simple, but I don't spend that much time using Excel. I have some sample data in their for the moment.
View 2 Replies View RelatedI have the catalog #s of some products in column A, and I have the brand in column B, Product name in Column C, and the model # in Column D. I want to create a formula that if I select the catalog # it should autopopulate the brand in column B, product name in column C and the model # in column D!!
And I have the brand/product & model # in a different sheet!!
I wanted to create a multiple drop down lists (using data validation) in column B (50 in all, every 3rd line) whereby, multiple, comma deliminated, results would display in each of the cells - for use elsewhere in the spreadsheet.
I found some code (as follows) which worked perfectly for me
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
[Code].....
As I mentioned above, I don't really understand the code and all I know is that the line about halfway down "If Target.Column = 2 Then" is defining which column (B) this will work in.
The problem I am having is that I need to protect the worksheet and the moment I "protect" the worksheet, the functionality of displaying multiple values goes away and the drop-down list reverts to only displaying one of the available values.
I'm trying to copy data from one sheet to another sheet automatically based on a number selected in a drop down control.
I have used OFFSET to pull some information successfully from a list. This is easy for me to do with single entries and a list and has worked well. I want to do similar but with a dataset not just single cells.
But, I can't seem to make OFFSET work to show a set of data easily.
Perhaps I will need VB code to do so? Or there is another control I'm not aware of? I've done very little with VB.
I'll attach a sample file to try to show better what I'm trying to do.
So i'm receiving a web query on a sheet that is taken from a table off the web. When this data gets extracted into the sheet, it populates as "$xxxM" in order to show that it is a certain million dollars. The problem is, I need to total all these numbers to display at the bottom of my report. So essentially, I am trying to configure web query to display as "number" as opposed to "$numberM" so it is easier to total.
View 1 Replies View RelatedI'm trying to display a value both numerical and also in english (e.g display both 200 and two hundred, 259 and two hundred and fifty-nine and so on.)
View 3 Replies View RelatedI have column h with address number.
I have column k with street name.
In column iv i have an x an number or it is empty.
What I want to do is press a button and it starts at iv10 read that cell if empty then read iv11 etc.
When it reads x in the cell then read next witch will be a number.
Example:
iv10
iv11
iv12
iv20 x
iv21 21
So I would get columns a - ae and rows 10 - 21 displayed and wait till i press button again then when i press button it starts
Example
iv22
iv23
iv89 x
iv90 68
then it would display a-ae 22 - 90
I would like to display a number as weeks and days. For example the number 8 needs to be displayed as "1w 1d" or the number 14 as "2w 0d"
I am able to use a formula such as =INT((C3)/7)&"w "&(((C3)/7)-INT(((C3)/7)))*7&"d" but I wish to preserve the formatting of a number so that I can add 2 or more numbers together.
I am hoping that there is a way of doing what I want using the Custom Category in the Format Cells dialogue box.
I maintain a class register in Excel to monitor student attendance. The first row shows the date of the class in the form dd-mm.
I need to identify all dates which fall on a Monday and thought that if I custom formatted a new row as "dddd" and enter the formula =DAY(cell ref) into the cells of this new row it would achieve this- I could easily spot the Mondays for the period under review.
What I'm finding, however, is that the formula seems to incorrectly state that 16th September 2008 is a Monday whereas it's actually a Tuesday- utterly bizarre!
I can get a fix simply by modifying the =DAY() formula by adding 1 to my formula [ie =DAY(A1)+1] but am wondering is this a "so called known issue" with Excel or has anyone else come across it? I have never previously come across this and consider myself to be an above average competency level user of the application.
I'm trying to create an Excel sheet to display MAC addresses in the following format -
00:00:00:00:00:00
However. the characters can be either letters or numbers. I've modified the "hh:mm:ss" template to accomodate the format I need, but it will not recognize letters for the obvious reason. Is there a wildcard character that I could use in this format or has anyone created a format that would meet my needs? If I can pull this off, it could mean a promotion.
I'm making a Excel 2013 spreadsheet that has formula in a column that auto enters a number 1-40 when something is entered to the left of that cell. There are 300 rows in the spreadsheet. I would like to make a drop down list in a column cell to the right that would delete that number in that cell from the drop down list. For example cell C1 has 39, that 39 then is deleted from the drop down list. C2 has 22 in it, click on the drop down list cell and it shows 1-40 less 39 and 22.
View 11 Replies View RelatedI use hex2dec frequently and input with a hex 'E' such as 05e8 is misinterpreted as exponential notation. I can use =hex2dec("05e8") to get the correct value, but when the hex number is in another cell, I get the exponent assumption/ conversion (to 5 x 10e8), or a # NUM error if it is in quotes. This seems like a blatant problem but I've found no mention of it in searches or FAQs.
View 3 Replies View RelatedI am working on a spreadsheet that calculates sales results weekly / monthly / quarterly / and year to date. I need to be able to get a picture to automatically populate in a specific fields based on the sales results for that week / month / quarter / year. There are 3 pictures indicating: sales below budget (sad face), above budget but below goal (black dot), and above goal (gold star). I have tried using non-macro means, but I can't seem to get anything to work for use with multiple cells and I do not wish to use conventional conditional formatting. I do not know nearly enough about macros or VBA to even attempt this type of thing.
View 2 Replies View RelatedI'm looking for a way to insert / display images in a worksheet depending on the value in a cell. The following link is able to do what I need, but it's very limited - i.e. all images must be present in the worksheet first and only the one require is visible:
McGimpsey & Associates : Excel : Display picture based on cell value
I'd prefer to store the images in a normal folder in windows (for a large library of images) and have Excel retrieve the image somehow.
My Problem is I have a workbook with multiple sheets with a possibility of a number between 2 and 999 occurring. I am looking for a formula that can display a table on the "total" worksheet for every ID number that has been entered and the number of times the ID number is displayed.
View 7 Replies View RelatedI have a very large database, filled with every job we're working on and all the details about it - duties, employees, vehicles, material-costs, etc.
I am currently working on a 1-page "Summary Sheet", that would be used to wade through all of this info and only give me the information I am looking for on one job. I would like to be able to provide drop-down menus on this "Summary Sheet" so that management can pick the information they'd like to see, and not be bombarded by everything else.
Ideally, what I am trying to do is to have a space on top where somebody could enter the name of the job, and then when they pick an item from the drop-down list, it would pull that info from the database via an "Index/Match". When somebody enters a job-name and picks "Joints Tested" from the drop-down, it will only display how many joints were tested on that job.
I already have the drop-downs created, and have other Index/Match formulas in other spreadsheets (they trip me up a bit, but I can generally fiddle with them until they work correctly), but can't figure out how to make it work this way. My database has headings above each column, and I'm writing those same headings next the different options in the drop-down list, but I can't figure it out.
An integer in stored in Sheet1!A1. I want a formula in Sheet1!A2 to display data of sheetx!A3 where x is the number chosen in Sheet1!A1.
e.g. Sheet1!A1 store 3. Then Sheet1!A2 will display Sheet3!A3.
e.g. Sheet1!A1 store 7. Then Sheet1!A2 will display Sheet7!A3.
I have a database with a list of transactions with multiple fields, including:
Customer ID
Store ID
Item ID
Date
Price
I am wanting to add a calculated field that displays the first (or MIN) Date from the date column, looking at all of the orders where the customer ID, store ID, item ID and price are the same.
In the attached file I have shown what I currently have, as well as a solution using a helper column, and formulas I would use if I wasn't using powerpivot.
I am trying to create a summary Calendar that would display all the names of the people who are off on a given day. I have a grid where I display each day of the month and a person in the group has their own column. If they are out of the office for any reason their name appears in that row. In the big Summary Calendar cell for each day of the month I want to look across all the people columns for that day and then display the names of the people who are not going to be in the office. Can I do this by a formula or will using VBA work best.
View 6 Replies View RelatedI would like to automate a sheet to report statistics of projects based on a single cell that is the year that a user manually inserts. I've got a separate worksheet with all dates manually set up for next 10 years and would like it to be read by formulae in a reporting worksheet based on the year that user enters.
So if I specify year 2014/15 (financial year), I would like the cell C1 under "Reporting" sheet to display 1/07/14 predetermined in "Dates" B3, if I specify 2015/16, the cell C1 will read 1/07/15 from "Dates" B6 and so on.
I could do it with "IF" function but there will be too many nested IF functions in a lot of cells (hundreds if not thousands). This may make my file size very large.
Attached is a simplified example of what I mean.
I want to automate the Display labels in my Charts using VBA Coding…
I would need to use this small code in Charts with different Scales as some of the Scales in my Data are in a very large Range close to Trillions so its not possible to have these as the Primary-Y-Axis Scale and therefore I would need to use the in-built Display label option which Excel provides..
However, I always prefer to have the scale in the Range of Thousands so the Max I can have in the Scale can be 10,000 so if there is any Number more than that or the Scale is going above than I would prefer that this gets auto-calculated to Multiply with the remainder and show appropriate Scaling.
For Ex:In this example My largest Number is 4500000000 which is 100 M..So I would want the scale to show 4500 and the Display label as X Millions..
So The Display Label would be different for different Ranges but the Max Scale preffered would be 10,000.
I have a company with upto 5 products, and I have created a dropdown box where you select your product.
When I select product 1 i would need info on product 1 , when i select product 2 I need info on product 1 and 2 and continue. so for product 5 -i need info on product 1-5.
I tried to add all info on excel and tried to delete it one by one by deleting rows in excel for products but it does not work.
I'm sure this can be done but I don't think I have the formula correct. What I am trying to do is add each day's total while displaying a positive number if the total exceeds 6000.
View 5 Replies View RelatedI have a drop down menu using data validation and the values are B,C,D
I want to keep the letter in a cell, but make each one become number, so B=5, C=10, D=20
I have another dropo down menu that contains 6,10,16
What I require is that once the 2 drop down menus have 'values selected' I need to multiply the 2 cells together
so for example
If B and 6 was selected, I require the 3rd cell to be 5*6 so what is the formula?