Display Data Based On Key Codes Entered?
Jul 8, 2009
I am trying to do in the title so here it goes. I want a user to be able (on form 1) to enter a list of codes that refer to specific requirements (e.g. code PB114 may mean the user needs to perform a pressure test) and then excel will automatically display the requirement somewhere below. It is going to be somewhat of a database. In otherwords lets say in cell A1 you type in a code, e.g. PB114. In a range of merged cells or somewhere below that cell I want the information "Perform a pressure test" to appear.
View 5 Replies
ADVERTISEMENT
Mar 31, 2014
Is there any vba codes that can show tab/sheet that is based on the filter selection.
For example:
Cell A1 have a data validation of Apple, orange and Mango
I have 3 tabs named Apple, orange and Mango.
So every time I select Apple on the main in page filter. Apple tab will show. and if orange then orange tab will show.
View 3 Replies
View Related
Feb 27, 2007
I need a macro that displays a msg box when the user hit Delete on the keyboard. "The msg box will display/
"Are you sure you want to delete the selected information? This information might not be recovered.
If you delete it now the undo button in the toolbar could recover the information if activated prior to any other input.”
View 9 Replies
View Related
Oct 11, 2009
I have a lil project, was wondering how to work this. I have several rows of numbers, thing is, they can only contain either a "1" or a "0" (zero). If anything else is entered other than a "1" or "0", I'd like to have a statement below it saying "Error - data must contain a 1 or 0" - or something similar.
I tried using conditional formatting - not working well.
View 2 Replies
View Related
Jul 19, 2014
I own 3 restaurants and I use Excel to track my sales statics. I have to enter certain pieces of data on a daily basis and it can become slightly cumbersome when dealing with the sort of washed out look of Excel spreadsheets. I wanted to write a Windows application that would handle the process for me but my knowledge of programming languages is too limited and I don't really have the time to learn. What I'd like is to have a single page on my workbooks with which I can enter the data in on a field type view, and have it go to the appropriate cell on the "Raw" data sheets in the same workbook. For example:
Let's assume this is the Raw data sheet. I currently enter the data that I need here, and Excel does the rest for me. This gets hard to look at everyday when dealing with hundreds of numbers.
Net Sales
Taxes
Prior Year Sales
Percent Increase/Decrease
[Code].....
Entering the data here would automatically fill in the data on the Raw sheet, and would know which cells to put the data into based on the date, which would of course be gotten from the =Today() formula.
View 4 Replies
View Related
Jan 20, 2014
Basically, I have a Userform and am trying to have a user enter 1 datapoint into a Textbox. Based upon that datapoint (it's 9 digits), I am trying to see if I can therefore reference a linked Access Table to fill in other textboxes with information tied to that record (so if they enter a unqiue ID, I want to automatically return Name of Person, Location, etc.) . I liken it to a vlookup in regular Excel but am just not sure how to go about it in VBA.
View 2 Replies
View Related
Jun 19, 2009
I have this formula in a cell:
=IF(AND($D4=H4,$F4=I4),3,IF(SIGN($D4-$F4)=SIGN(H4-I4),1,0))
but i need to somehow incorporate a formula that only shows this if a cell has a value present.
I know as an example its something like:
=IF(C2="","",C3)
but i dont know how that would go in my existing formula as i dont know how to stack IF statements?? (a very kind person on this forum did the original formula btw)
the cell that i need to have as the 'blank or not' is D4.
View 3 Replies
View Related
Nov 6, 2009
How do I combine these formulas for one cell?
=IF(H16170,"Check your Entry","")
=IF(I16215,"Check your Entry","")
=IF(J16190,"Check your Entry","")
=IF(K16240,"Check your Entry","")
If any of these four cells are entered incorrectly I just want the message displayed. I put this formula in the cell I want the message displayed:
=IF(H16170,"Check your Entry",IF(I16215,"Check your Entry",IF(J16190,"Check your Entry",IF(K16240,"Check your Entry",""))))
That works except "Check your Entry" is diplayed until all four cells are filled. I don't want the message displayed with a zero value.
View 9 Replies
View Related
Sep 8, 2007
I have a userform for entering a sales invoice onto a spreadsheet, it obviously has a textbox each for the net value(textboxnet), gst tax(textboxgst) and total value(textboxtotal). How do I get the total amount textbox (textboxtotal) to automatically sum up as I enter the net (textboxnet) and gst (textboxgst) amounts?
View 4 Replies
View Related
Jun 20, 2006
What i want to do is copy all records from whatever date i enter, onto sheet test. The full excel file has over 80 worksheets for each individual rep, the example i attached has 8 sheets..
View 9 Replies
View Related
Jun 17, 2008
My colleague in accounts has a sheet that lists all the invoices and values and other 'accounts department things' that I know little about.
Anyway, she has to manually input all the invoices she receives onto this excel document but sometime receives duplicate invoices (& inputs them twice be accident).
Is it possible for someone with macro skills to create a macro to run in a worksheet so that if she enters a number in a given column more than once an alert box appears to tell her. I've summarised the 'rules' below*
*Can is be written so that I can edit which column the alert is based on?
[In (for example) column F is a list of invoice numbers - all unique]
If user types a number in column F that matches a number that already exists, show alert box "THERE APPEARS TO BE A DUPLICATE ENTRY IN COLUMN F"
====
Any further clarification, let me know i'll try to post a blank sheet if my boss lets me.
View 9 Replies
View Related
Jun 28, 2014
I have a picture on sheet 2. When I enter the text "htfo" without the quotes into cells a1 on sheet 1 , i would like the picture to be displayed for 5 seconds and then exit the picture and return me to sheet 1.
View 2 Replies
View Related
Aug 17, 2009
We are working on having the spreadsheet display the correct options, depending on what criteria are chosen. There are three choices in the first criteria and two choices in the second criteria. These choices are presented using data validation so they will always be formatted correctly. Worked on conditional formatting to display results but did not make much progress. Started working on "if" statements to display results, looks like this may not be efficient. Spent a lot of time reading, trying to find a solution.
View 3 Replies
View Related
Jan 3, 2014
I am trying to find the revenue generated by different groups of accounts to figure out which group is worth the most, and rank the others.Here is a attached screenshot of what I am working with, cell BK22 is the revenue of all of the accounts coded AADA but i need to find an easier way other than plugging it in for all 3500 cells.
View 6 Replies
View Related
Jul 30, 2008
I got some functions from some of you to spot duplicate bar codes
the first function was this =IF(ISERROR(MATCH(D5;$D6:D$65536;0));"";D5)
the second next to it this =COUNTIF($D:$D;$D5)
and the third: =IF(COUNTIF(D:D;D5)>1;"DUPLICATE";"")
well fine but now I dlike to add the numbers of the 2 cells of those duplicate codes. In some cases I have more than 2 identical bar codes.
Example
4905524490831DPFV900B.CEU0649055244908312DUPLICATE
4905524490831DPFV900B.CEU502DUPLICATE
View 9 Replies
View Related
Jan 21, 2007
I have a tab set up to display information that has been colletect on other tabs. What I would like to do is change this information depending on what month is selected.
For example, if I select December then data would be pulled from other tabs and displayed. If I Select June the information would change and select other data from the same tabs.
View 9 Replies
View Related
Sep 11, 2006
II have a list of football fixtures (see attached sheet - barclays premiership) each with a different status Win,Loss,Draw,Pending. I want to display the next team with a Pending status in a cell at the top of the list (cell T13/14) so the user knows at a glance what the next fixture is. I also want to display the days remaining to that fixture in the cells below this (cell T15/17).
View 2 Replies
View Related
Apr 8, 2008
I wonder if the following is possible in the worksheet_open event. I want to be able to open my worksheet and see via a message box all the important features I need to look at.
Using column AG,
Take the cells that are conditionally formatted to red, take the corresponding cells in column A & B and output this
information in a message box.
View 9 Replies
View Related
May 30, 2012
I'm currently creating a formula to look up the date based on the alphanumeric codes, my problem is if it crosses the year, say 2011 & 2012, the lookup will be distorted because codes for jan. 1 2011 & jan. 1 2012 is the same. Our alpha numeric code is A01= Jan. 1.
to elaborate further:
wanted to get the ageing days between todays date and item manufactured date:
today: May 30 2012
when the administrator enters the manufacturing date code of "A01" I have a look up of the code that equates A01=Jan 1. which will compute the difference of May 30 2012-Jan. 1 2012 and give me the Age date of 150. Now, if I enter H1, I wanted to have it look up at the 2011 not 2012 of Aug. 1; is there a look up formula that looks up the date already past (current date below), not the future date of under the same database?
View 5 Replies
View Related
Aug 10, 2007
Having the Zip Code Tabulation Module from the US Census Bureau, I have the following and much more. But based on the 3 items :
5-digit Zip Code, Longitude, Latitude.
I'm wanting to Select a Zip Code and have (let's say) several Zip Codes plotted (maybe 4 - 10) out from the center point of the map.
either with, or without a connecting straight line .
And possibly a Scalable Miliage indicator |---- 10 Miles ----| which would be based on the distance.
View 9 Replies
View Related
Jul 13, 2006
First it should Insert a Column to the left of A. Then it should go to column B and search for the number 99 and delete all rows after it. In column C I want it to do the following formula =vlookup(b2,descriptions!$b$2:$c$250,2,false) and have it copied all the way down to the end of data.
Next I need labels added to the tops of the rows
Next I need the description columns in C to be copied to column I
Finally, beginning at J2, I would like the formula (e2 * g2)/100 entered to the end of data. Attached is sample data.
View 2 Replies
View Related
May 14, 2014
I want a table to display data based on which month i select from a drop-down list, the data is of course extracted from a different table. For example, in my final table( highlighted in yellow), i want to display the revenues, cost of goods sold..etc of April in this table when i choose April from the drop-down list, the data of the entire year is located in another table that i plan to hide, as we only need to review one month.
View 1 Replies
View Related
Apr 29, 2014
I am trying to create a formula which will allow me to type a word in a cell on the Main sheet, and then pull any row from multiple sheets and display them on the Main sheet. For example, my sheet named Main looks like this:
A B C D E
1 Query:
2 Fruit Store Cost Mode Date
And there's a sheet for each month with the same format and different data:
A B C D E
1 Fruit Store Cost Mode Date
2 Apple Dominicks 1.99 Bagged 3/16/2014
3 Apple Piggly Wiggly 2.19 Ala Carte 3/14/2014
4 Banana Jewel 0.49 Bagged 3/1/2014
5 Banana Dominicks 0.57 Ala Carte 3/16/2014
6 Banana Costco 0.69 Ala Carte 3/16/2014
7 Cantaloupe Jewel 2.99 Ala Carte 3/14/2014
8 Cantaloupe Dominicks 1.99 Ala Carte 3/1/2014
9 Eggplant Jewel 0.99 Ala Carte 3/30/2014
10 Eggplant Dominicks 1.19 Ala Carte 3/30/2014
11 Eggplant Costco 1.29 Ala Carte 3/21/2014
12 Eggplant Safeway 0.79 Ala Carte 3/16/2014
13 Pears Jewel 1.19 Ala Carte 3/14/2014
14 Pears Dominicks 1.59 Bagged 3/1/2014
15 Pears Piggly Wiggly 0.99 Ala Carte 3/30/2014
16 Rhubarb Jewel 0.99 Ala Carte 3/14/2014
17 Rhubarb Piggly Wiggly 0.59 Ala Carte 3/14/2014
18 Rhubarb Costco 0.89 Ala Carte 3/14/2014
19 Tomatoes Jewel 1.99 Ala Carte 3/1/2014
20 Tomatoes Safeway 1.69 Bagged 3/30/2014
21 Tomatoes Dominicks 1.89 Carton 3/27/2014
... and so on. On my main sheet in Cell B1, I want to type a fruit name, such as Banana, and have every row in every sheet with Banana in column A,pulled and displayed on the Main sheet. Firstly, is this even possible with a macro?
View 4 Replies
View Related
Jan 24, 2014
I am using Excel 2003.I need two distinct sums:
I need to calculate out the loan amount of approved loans by week (weekending on Sunday). For example for the week ending December 28th, 2013
$1,234,567,890 in total loans
I also need to have a list of sums of the loan amounts per code types of approved loans. For the week ending December 28th, 2013
$123,456.00 in total loans for Code: LTV/TLTV > Guideline max
$987,654.00 in total loans for Code: FICO > 10 pts below Guideline min
I need to ignore any loan amounts and codes for any loans that have not yet had a approved date entered and also ignore all loan amounts for any loans that are denied. I have tried doing this in many different ways with Pivot Tables and I am not getting the results I need.
View 1 Replies
View Related
Jul 20, 2014
I have trying for 2 days now to write a macro/VBA that looks up IF the stock codes (i.e., in column A and G) match THEN insert the ID_ISIN from column H in the empty ISIN column C.
or should i use Vlookup?
View 3 Replies
View Related
May 5, 2006
I'm trying to convert various codes from one column of an excel spreadsheet into different codes in another column. I was able to accomplish this with "If" statements, however I'm only able to string together seven of these statements in one command. Is there a better way to add formulas for more than seven conversions? Below is a copy of what I've done so far with the seven converts:
=IF(ISNUMBER(SEARCH("WARN",J2)),"Warning",IF(ISNUMBER(SEARCH("PSSNAP",N2)),"Sales",IF(ISNUMBER(SEARCH("WARN",L2)),"Warning",IF(ISNUMBER(SEARCH("2699",L2)),"Warning",IF(ISNUMBER(SEARCH("4004",L2)),"Warning",IF(ISNUMBER(SEARCH("2036",L2)),"Warning",""))))))
I want to add about 15 more codes to convert within this formula but it's maxed out in the format I'm using.
View 3 Replies
View Related
Oct 28, 2009
I am trying to compare two sets of data.
I have a set of All zip codes in 10 counties in one worksheet:
ex:
Column 1 / Column 2
30303 / Fulton
30307 / Fulton
30310 / DeKalb
30234 / Cobb
But this sheet has about 300 zip codes
Then in a second worksheet I have dollars by zip
30303 / $10,000
30456 / $56,000
I'd like to make a third column in this sheet that fills in what county. Does anyone know how to search sheet one for the appropriate zip, then stick the word beside that zip into the column 3 secion of the dollars by zip code?
I'd rather not have to search every zip code in the dollars section one at a time. Not all the zip codes in the state are represented in the second sheet, so it's not quite as simple as just lining them up.
View 13 Replies
View Related
Jun 7, 2008
Sir Vog-II/Jindon
I have written mentioned below codes to copy data with certain craiteria can you please see them if these codes need to be improved or concise.
HTML Sub ayazgreat()
Dim Header
Dim Header2
Dim LastRow As Long
Dim Rng As Range
Header = Array("Region", "Item Issued", "Invoice Date", "Cheque #", "Amount", "Chq Date")
Header2 = "Total"
With Application
.ScreenUpdating = False
.DisplayAlerts = False
Sheets("Payment").Copy
ActiveSheet.Shapes("Button 1").Delete
ActiveSheet.Shapes("Button 2").Delete
Rows("1:2").Delete Shift:=xlUp
ActiveSheet.Range("C3:H3").Value = Header
ActiveSheet.Columns("E:H").AutoFit
View 9 Replies
View Related
Dec 15, 2011
I got 2 sheets. (1 called "Revenue", the other "101") in the sheet revenue are department codes (variable 100 to 999) they are situated in row "B". the corresponding data in row "D".
this data needs to be transported to the sheet "101"
in the sheet "101" are put the numbers 100 to 999 in row "A"
in row "B" we need the data from the revenue sheet that is corresponding with the department code.
View 1 Replies
View Related
Jun 22, 2009
I'm trying to analyse data and summarise it by date (greater than 60 days, and between 30 days and 60 days), by department using the first two digits of department codes, and where there are multi-line order numbers.
View 4 Replies
View Related
