Automatic Cell Recognition
Sep 4, 2009
In the Yellow Cells, I am looking for a formula or Macro that will automatically recognise and give the correct Premises number. As an example, in the first block of Yellow cells, it should be MOR001&2; The second block should be MOR003 and so on...
One could copy and paste the Premises number for each premises, but with 100 entries or more, it becomes a tedious and time consuming task. An autonomous function to recognise the correct premises number would be much easier
In the end, this is to be used when drawing up a PIVOT so that one may easily pick up a premises number or numbers and their related charges
View 7 Replies
ADVERTISEMENT
Nov 7, 2012
I have a spreadsheet where I need the end result to be auto filled after recognition of an adhoc "top up" amount. How do I get E7 (3170) to automatically recognise the insertion of C7 (3000) to give this result without having to manually update the formula in E7?
DATE
PURCHASE
ORDER
PRODUCTIVE
[Code].....
View 4 Replies
View Related
Nov 11, 2009
I have the following two formulas in a spreadsheet, which sum and count, respectively loan information within a certain date range. Both of these are working correctly.
My problem is in substituting the dates with cell references for dates located in a worksheet with a table of dates. The dates from this table work with simpler formulas, but I cannot get them to work here, regardless of how many () "", etc that I try....
View 9 Replies
View Related
Nov 30, 2009
I have a spreadsheet that uses data which is linked to dates on which events are being held.
So, event 1 happens on 5/12/09, event 2 on 6/12/09. What I would like is the spreadsheet to identify the next event occurring and copy the information into a different cell on the spreadsheet.
View 10 Replies
View Related
Apr 25, 2008
I have a column of data (numbers) that are expressed incorrectly i.e 1,000 actually means 1, 0,3 actually means 0.3 and 15,500 actually means 15.5. I can make the first and second examples OK by using the replace command, but how can I check the number format of the cell and then make it change to what I want. All these numbers are in random order and sequence. I'm using the replace command in a macro, is there something I could put in the macro to solve my problem?
View 9 Replies
View Related
May 25, 2009
I play Keno a lot and have what is probably a very simple formula request. If I’ve got for example this result pasted into Excel: 06 12 15 21 23 27 28 31 32 34 48 49 54 56 60 67 68 74 76 77. How would I go about determining if a pair or two numbers had come in?
Say I was looking for 31 and 76, is there some way I can get Excel to recognize the two spot hit then post in response either 1 (in bold) if yes or 0 (not bold) if no the two did not come in together? Note this would not be only one or the other, they both would have had to hit at the same time.
View 3 Replies
View Related
Jul 22, 2014
if it is possible for vba to recognize the name of the file is into.
example:
You file's name is : Track
you change for : Music
Your code automatically recognizes that and change the code.
View 2 Replies
View Related
Aug 1, 2006
I do not write code in it, just use it for school and work on the most basic levels. I am trying to suck an Excel document into SPSS ( Statistical Package for Social Science) for my Engineering internship. I am logging temperature using Omega Engineering, Inc temperature probes. When converting the data recorded on the probe to excel, it does something funny. I will try my best to explain what it is doing. If you format the cell to general, it gives you this (38917). Then simply changing the format to date I get this (7/19). How do I change this to make it so the date is the only memory in the cell. I don't need the other number in there because when I suck it into SPSS it only sees the 38917 number and not the date. I need the date to do my statistical analysis on the data.
View 4 Replies
View Related
Jun 4, 2009
I have a bit of a complicated one here so I have attached my book, its probably easier to undertstand the query by looking at the book. Hopefully somebody can help me out here.
I have 2 worksheets:
A results worksheet - this contains all category of results, each category has been given a specific sort id.
A foreign worksheet - this contains all categories found on the results worksheet with sort id = 7.
Now the complicated bit. The whole exercise is too pick up the correct exchange rate for the foreign category (sort id 7). each foreign type has a specific number at the end of its description in brackets e.g (3).
At the bottom of the "results worksheet" is a key which says what type of exchange rate to use for this type e.g. (3) Price of CAD 22.9 converted using exchange rate of CAD 1.9645 = £1.
What I need to do is on the foreign worksheet, it to recognise the number in the item description, keep it in mind then switch over to the "results worksheet" find the keys at the bottom of the page (the key is not necessarily in the same place all the time!!), match the number in the items description with the corresponding key and then find the exchange rate.
As i said very difficult to explain, since I reletively zero experience with this kind of work in excel. Luckily I been muddling my way through a work project gradually thanks to a forum member here. so i hope we can keep this up!
edit:
two restrictions in the way this solution can be done:
1- Results worksheet can not be modified, but it can be referenced to using INDIRECT, and you can make any number of modifications outside of the results worksheet.
2. Any solution must be automatic.
/edit
I have attached my workbook and highlighted the bits I need to fill and the picks I need to pick up.
Would be ever so grateful if some1 could show me the light here.
View 14 Replies
View Related
Jul 29, 2013
I've created a spread sheet of hockey stats to prepare for my fantasy league. I've created a formula weighing different stats differently to create one "Fantasy Value" score. I've done it over 3 years of data and want to find the average 3 year score for each player on the front page of the workbook. There are 300+ names I'm tracking so I'm wondering if there is a way that Excel can recognize a player's name and pull the data from one specific column in 3 different sheets.
I've attached the excel file for your reference : NHL Stats.xlsx‎
View 4 Replies
View Related
Oct 3, 2008
So here's the setup: A customer purchases a service which lasts 6-months. As such, revenue recognition for the company can only occur as the service is provided. So if a person orders a $600 service at the beginning of a month, revenue will be recognized at $100 for the next 6 months. I'm trying to create an Excel Waterfall chart which will show the monthly revenue recognition amounts for all orders depending on the date which they were ordered.
I've attached a sample of what I'm looking to do. It's become tricky for me because Revenue Recognition is pro-rated based on the date ordered (i.e. order on the 20th of a month so at the end of the month 1/3 of the month is recognized as revenue). I'm looking for something that will populate the percentage of the order amount, each month, that will be recognized. I've populated what row 3 should be, but there doesn't seem to be an easy way to apply a formula or something across all cells.
View 2 Replies
View Related
Mar 15, 2014
I know I will earn $5,000 of monthly revenue from a client. $5,000 is represented in a monthly revenue cell. I have 12 columns showing the 12 months of the year. There is an additional cell showing the customers implementation date.
If a company's implementation date is on or after the 15th of the month (example: 3/28/2014) then the next month (April 2014) is skipped and the $5,000 is returned to columns May through December. All months prior to May return $0.00. If the implementation date is before the 15th day of the month (example: 3/13/2014) then the next month (April 2014) and all months after will return $5,000. All months including March and prior must return $0.00. If the implementation date is unknown then 12/31/2099 would be in the implementation date cell and $0.00 is returned for all 12 months.
Essentially, if the implementation date is prior to the 15th of the month the revenue will show as of the following month. If the implementation date is on or after the 15th of the month the revenue skips the following month and will show the month after.
View 6 Replies
View Related
Oct 19, 2007
I am looking to add a function to a current spreadsheet that writes the current date to a cell when another is updated. The sheets function is to have a user record when a particular action has been completed and then remind them after a given amount of time. The user is faced with an option to input a "1" to essentially "start the timer" and then the date that is automatically input by the code will be used to compare with the current date. I have tried the following on the Workbook_SheetChange sub
If ActiveCell.Column = 9 And ActiveCell.Value <> 1 Then
ActiveCell.Offset(-1, 2).Value = Date
End If
But cannot figure out how to eliminate user error. Let me explain. If a user enters a value (will only ever be a 1 to indicate "yes") into column 9 then the date appears in the correct place (two cells to the left) If however a user deletes a value then the date will update one cell too high!? I also thought of using a cell based if statement (if j7 = 1 then today() else "" but this only updates with todays value each time you open where I want the date to stay as the day the cell was updated. The date is going to be used as a way of working out when to change the original value of the cell in column 9 to "".
View 2 Replies
View Related
Apr 5, 2012
I have data in table-1, in current shape
Table-1
B C D E F
Adda Zakeera 1239987801310037
Ahmed Pur East 5559998803310042
Ahmed Pur East-2 8888874805510041
Arifwala 3545555805510045
Bahawalnagar 3336666802610046
Bahawalnagar-2 1257777806610038
Bahawalpur 2206666804410044
Bhakkar 2223333805610042
I want to arrange my data like Table-2. I have tried vlookup() formula, it worked but every time I puldown the formula I have to manually increase cell # (e.g) In the row of Adda Zakeera, I have 4 values. When I use vlookup() and drag it downward the values should be shown as in Table-2 under Adda Zakeera.
=VLOOKUP($D$2, Sheet1!$B$4:$AF$90,2,0)
Table-2
D E F G H I J
Ahmed Pur EastAhmed Pur East-2 Adda ZakeeraBahawalnagarBahawalnagar-2BahawalpurBahawalpur-2
555 888 123 333 12548 2201#N/A
555 888 9987 333 77777 66666#N/A
View 1 Replies
View Related
Oct 31, 2013
which will calculate value of a cell when one of the variables has been changed?
To illustrate what I have in mind, an ecxample:
* User can add values to cells using UserForm
- Component name (to Cell "A1")
- Component price (to Cell "B1")
- Component quantity (to Cell "C1")
[Code]....
View 3 Replies
View Related
Sep 9, 2009
I have a cell that has a formula in it- "=IF(C13="","",VLOOKUP(C13,DATA,2,)).
Right now it works fine and returns a value of either "NANN" or "HZ". Which is fine.
But, what I would like it to do is, if the result equals "NANN", I would like the result to display "BURLINGTON". Or, if the result equals "HZ", I would like the result to display "CONOCOPHILLIPS".
View 8 Replies
View Related
Dec 28, 2006
I have a worksheet with the following in Column E and F respectively:
ID Name
100 aaa
100 bbb
200 ccc
200 ddd
200 eee
300 fff
400 xxx
500 yyy
500 zzz
500 kkk
500 lll
500 mmm
When I input an ID number in Cell A1 and if it is found in the above table, it should bring all the names corresponding to that ID in a cell comment.
The ID Number is repeated in the above table because the names in Colum F are the dependants name for that particular ID.
View 9 Replies
View Related
Jan 20, 2014
I am trying to have one cell automaticall input infromation based on another cell. For example, if A1 = 10, then I want B1 to automatically equal 20.
The formula I am using in B1 is as follows - =if(A1="10","20","")
I have 2 problems though:
1st - I don't want to put the furmula in any cell because other information is put in there also. I tried putting it in conditional format but I don't think it is meant for such usage. Also, I already have something in data validation. so I can't put anything there because to my knowledge you can only put one validation per cell.
2nd - I have multiple numbers (around 7) that I need to be automatically inputed along the columns.
View 9 Replies
View Related
Jul 23, 2014
How to automatically change the colour of a cell if another cell is a certain colour.
For example, if Cell A is red, then Cell F also needs to be red.
If there a way for this to automatically update?
View 1 Replies
View Related
Feb 16, 2010
I am trying to automatically capture and record the date of a cell's last change in value (date stamp). I have an item price list and if a particular cell gets updated I want to automatically record the date of change of that cell. I realize that after I change cell A1 I could tab to cell B1 and enter Ctrl+; but if I have a hundred new prices to enter I don't want to do that (plus me or the data entry person might forget).....
View 14 Replies
View Related
Mar 19, 2010
I copied the code that was used to inserting the date when the cell next to it was updated, the original post can be found here: [URL]
The Code below will check a range of cells between c3 and c20 and if I make a change to the value in any of them, then the cell to the right of them will have the date inserted. I've had to modify the original code from the other post a little bit to stop an error appearing when I insert a new Row:
[Code] ....
On the first example that was posted it all ran ok until until I attempted to insert a new Row, then it would put the date into about 5 of the cells to the right of where it should do and I received an error message with the usual Debug stuff on it. It would also delete my column descriptions that I had on Row 2.
Would it be possible for it to not auto insert the date on any new blank row that I insert?
What would be the correct range for me to get the code to work on c3:infinity....
Is it also only possible to enable macro's and code like this in the current document instead of every document that you load through excel.
View 8 Replies
View Related
Nov 23, 2012
I want to learn how i can able to do a function or ... to LOCK a Cell automatically after editing or typing and no one able to edit it again without entering Password.
View 14 Replies
View Related
Jan 29, 2013
I want to be able to enter a single digit figure into a cell and have the cursor move automatically to the next cell to the right. then enter a figure in there and have it do the same thing. is this possible and, if so, how?
View 2 Replies
View Related
May 26, 2014
I have built a spreadsheet for my business which has an annoying feature I can't get rid of. When I click in a cell I want to enter data in the spreadsheet automatically highlights that cell and the neighbouring cell. I have to move away then back with the arrow keys to get back to the single cell I want to enter data in.
View 2 Replies
View Related
May 28, 2014
In a spreadsheet a macro calculates some percentages and populates a small table, copies the table and generates an email. The table includes conditional formatting to fill a cell in green if it is above 95%, or red if it is below 95%. The spreadsheet works fine, but with every possible solution I have tried including converting to HTML, the color cell is always red in the outlook email. I am guessing it is because in the transition to the email, the small table no longer is located in the cells that are used to do the color format.
Whats odd is when I copy and paste into outlook it works fine, but I have been unable to figure out how to paste into the body of an email using excel vba. I have read many posts from people with similar issues, most have no solution. I am currently attempting to convert the table into a gif and try inserting that into outlook. Due to the proprietary nature of the spreadsheet, I am unable by policy to publish any code.
View 4 Replies
View Related
Aug 15, 2014
With "sheet1" there's a table c14:m38 with all formulas. But i want to expand the table if cell c38 contains a value or text.
Is it possible to automaticly insert a row containing the above formulas in row c14:m14?
View 12 Replies
View Related
Jul 16, 2008
I have a workbook with several sheets, each of which represents a client. At the bottom of this sheet, I have a call log to record the times that we attempted communication, left a message, etc. Since it's tedious enough as it is, I'd like to save my team 2 seconds and a slight bit of that tedium by having the date an attempt was made automatically recorded in the row where the attempt is entered.
Let's say this all takes place in A47:D47. For example, if I called the client on 7/15/08 and left a message, I would record who was called in B47, the name of the caller (me) in C47, and the result in D47. If possible, I would like to have the date of the attempt (today's date on the given day) entered in A47 upon the entry of information in B47:D47.
View 14 Replies
View Related
May 10, 2007
know if there is any way of getting a cell produce an X (for example) when it is selected and blank when selected again? Basically a tick the bok response. I know i could use a drop down list but i was hoping for a quicker method as a lot of ticks accross a lot of cells are required.
James
View 9 Replies
View Related
Dec 15, 2013
I have a drop down list in cell F1 with all the months. In G1, I would like it to automatically display the number of days in the selected month. Of course it depends on the current year (due to leap year, etc).
View 14 Replies
View Related
Apr 22, 2014
See attached example. all details there and noted clearly.
current code:
Sub emailinstant()
Dim OutApp As Object
Dim OutMail As Object
On Error Resume Next
[Code] .......
View 6 Replies
View Related
