I am trying to get the Price to automatically fill cell C11 of the attached spreadsheet when a user enters Grade and Thickness information. I have tried index function, match function, vlookup function, and combinations of all with no luck.
View 5 RepliesI am trying to organize it in a way that I can have all my purchases added based on my text criteria of certain purchase... Ill try to be as clear as possible. I have already organized it in a way that when I paste new months statement in my Excel file it sorts it by date, newest on the top, and I made Conditional Formatting by color on column D which states the Description of purchase. Starbucks appears in description cell but it is followed by number and location making each Starbucks cell in column D different in name. So its hard to use Exact phrase formula to combine the amount for that purchase.
My question is: Is there a formula that find a PORTION of text in a D column and automatically adds the price to the right of it together in one cell. There is a formula that looks for the whole text in the cell but that doesn't work when my name has always some extra numbers at the end of the word Starbucks
I own a Hair Salon which collates its daily takings using a spreadsheet. A section of this takings sheet asks staff to enter which Products have been sold and what they where sold for (RRP), so that once all products have been entered, totals at the end of the day allowing the end -of-day "till-up" process to reconcile purchases and services rendered aginst cash and cheques received.
Unfortunately there is over 600 products to choose from and this evergroes as new products are introduced by manufacturers. Names are long and often very similar, and product price manual listings are slow to reference, therefore the customer has to wait while the staff find the right product and select the right price.
All of this is subject to human error. What i would like is simply to enter part of the product name into a Cell and the right product to be visibly selectable and the price automatically added to an adjacent cell.
I'm trying to create a supply order form. Is it possible to enter a product name in one cell and have the price for that product automatically appear in another cell? Is there a formula that I can use to make this happen?
View 2 Replies View RelatedI am writing a program in excel spreadsheet and I was wondering how to fill an input cell with color automatically in excel 2007.
I mean that, say I am writing an addition program, I am giving an input numbers in B1 and B2 and i am writing formula in B3 (=B1+B2). I want excel to fill B1 and B2 with yellow/any color automatlically. Only the cells with number input should be highlighted.
i have a an invoice template setup on my computer in excel.
what i would like to know is how do i get my customer address to automatically fill in (cell b) if i select them by customer number (drop down menu in cell a) in an different cell (cell c)
How do you make the auto-fill to not automatically use the next numbered cell:
For example:
I want it to fill with every other cell from a different sheet (same column "D") so i would have a cell ='sheet1'$D10 or whatever then how would i do it so it will auto fill every other cell from then on so the first cell below the initial one would copy from the other sheet as D12 instead of D11?
Suppose in cell A1 I type in the number "100" and in A2 I type in the number "5"
What I want to do: Have the number "100" repeat 5 times in a vertical list (so 100 appears in B1, B2, B3, B4, B5)
I am using this code and it works fine:
Dim MyData As Range
Dim MyResult As Range
Set MyData = Range("E1:E1000000")
Set MyData2 = Range("F1:F1000000")
Set MyResult = Range("J4")
MyResult.Select
Selection.Formula = "=COUNTIFS(" & MyData.Address & ",""=Kim""," & MyData2.Address & ",""=done"" )"
Is there any way I can change the code so that it automatically finds the last cell as Im using Range("E1:E1000000") and Range("F1:F1000000") because there will not be more than 1000000 entries.
I have certain values in cell B4 to B2000. There are 25 unique values in those cells. In cells A4 to A2000 I would like to put a identifier (lets say a letter from A to Y) based on the adjacent B4 to B2000 cell values. I can't use a IF function because I am way over 7 allowed in excel. I could use a vlookup but that seems to slow down the file quite a bit. Is there something else better/faster out there?
View 3 Replies View RelatedI want find the data in some rows that same with one or more cell and automatically fill the data. And for more details, I have attached the examp file (Examp.xls).Antoni
View 3 Replies View RelatedI need J22 to multiply based on years in B22 AND increase 5% for each of those years (compounding) after two years (excludes year 1 from 5% increase). In addition the cell needs to remain blank if D22 is blank. B22 = 1, then the stockprice needs to remain the same, and only increase by 5% after year 1.
Currently...
B22 = a number of years indicated by the formula: =IF(A22="","",DATEDIF(A22,I3,"y"))
J22 =IF(ISNA(VLOOKUP(D22,stockprices,2,FALSE)),"",VLOOKUP(D22,stockprices,2,FALSE))
Example:
If J22 stockprice lookup is $1000.00, and the number of years listed in B22 is 6, then the reported value in J22 needs to be $1494.40.
Windows 7 Ultimate / Excel 2010
I have calculated the implied volatility for different single options using the newton raphson method. But, I also need to calculate the implied volatility which minimizes the sum of squared differences between the observed market price and the model price for each day. I guess one needs to use vectors (jacobian matrix) to do this, but I do not know how to expand the code to be able to do this. Anyone have any idea how this can be done? I have attached the [code] I have used to calculate the implied volatility for one option.
View 2 Replies View RelatedI am trying to have some automation to my WS and what I want to do, is if a certain column (column 104) is changed, it will fill out that column with the data entered as long as the account is the same (column 5). Here is the code I tried, but realized very quickly, it puts me into a loop. This is being called by a change worksheet fuction
If Target.Column = 104 Then
If IsEmpty(Target) Then
Else
testacct = Cells(Target.Row, 5)
temptest = testacct
s_TempSwitch = Target.Value
Testoffset = 0
Do While temptest = testacct
temptest = Cells(Target.Row + Testoffset, 5)
Cells(Target.Row + Testoffset, 104).Value = s_TempSwitch
Testoffset = Testoffset + 1
Loop
End If
End If
The analysis basically has 2 data components to it:
The 1st part, is a basic transaction list of shopping items bought through the year. Each transaction's shopping item also has the quantity of that item purchased at that time.
The 2nd part, is a pricing sheet for all the different types of shopping items. The pricing sheet has different prices for different quantities at which the item is purchased.
What I am trying to do is to find the relevant price for shopping item, which depends on not only what the item is, but also the quantity. In point form, it should follow the logic below:
1) Identify the item in the shopping list (worksheet 1) from the list of prices (worksheet 2)
2) Find quantity in the prices worksheet that is closest to the quantity in the shopping list (i.e. where the difference between the quantity on transaction list and the quantity on the pricing sheet is the least)
3) Pull the price for this "closest quantity"
I have uploaded a worksheet showing the structure of that data.
[url]
Is there some VB code I need to do this, or can it just be a few simple formulas?
to illustrate I attached a spreadsheet.The spreadsheet contains 5 colunms.
1. Names
2-4. Numbers
5. Sum of numbers (the formula for the sum is only entered in the first row)
What I want to do is automatically fill in sums from the second to the last row. I was trying it with the "record macro" function but failed because I don't know how to jump to the last row. CRTL-Shift-down is not useful in this case.
I have an Excel file which extracts data from other excel files (the other excel files are named 1.xls, 2.xls, 3.xls, etc.). So the formula in the cell is for example: ='C:[1.xls]Sheet1'!A1
Is there a way to let Excel fill in the next excel file address for the next cell? So that i would get this:
='C:[1.xls]Sheet1'!A1
='C:[2.xls]Sheet1'!A1
='C:[3.xls]Sheet1'!A1
='C:[4.xls]Sheet1'!A1
etc..
Because i have about 500 excel files, and otherwise i have to change the file number manually...
In the worksheet just say:Cell A1: Jan-11-2009 as today date. Cell A2: What is a date function I need to put in there so when a computer clock change to 12:01am it automatically fill in a next day it should be Jan-12-2009 and continues do so for the next day at A3 ....when computer clock change again. Remember I wanted a cells A2...A3...A2000 are blank until the computer clock change to 12:01am.
View 5 Replies View RelatedAll data is located within one book. I have two sheets with material codes in each sheet which include pricing (existing and current)
Sheet1 (has existing material codes plus existing pricing) Has about 1200 lines
Sheet2 (has current material codes plus current pricing), has about 36000 lines
I need to cross check if the material code (taken from sheet1) are still available in sheet2, and if they are, copy the current price back to sheet1. The current price needs to be pasted back into sheet1 (next to the existing price). If the material code doesn't exist (for whatever reason, in sheet2), the program needs to move onto the next line and leave the current price for that material code blank. The program should finish once all the lines in sheet1 are completed. I have attached a sample of what I'm trying to do,
I am creating a register of first aid kits on my site. What I want to do is have a summary as the first worksheet, and then have contents, and individual expiry dates for each kit on seperate kits. Is there a way to autofill, or auto complete, a different sheet name in formulas?
Ie.
='Kit 1'!$B$27
='Kit 2'!$B$27
='Kit 3'!$B$27
...
='Kit 44'!$B$27
I have a list of people on sheet 1, column A. The corresponding information for each person is listed across 5 columns in sheet B (cols A:E). **The row numbers are consistent throughout the sheets (ie. Joe is listed in Row 5 on sheet 1, and his name and info is listed in Row 5 cols A:E on sheet 2).
Name
June Sales
Sales Quota
YTD Sales
Total June Payout
Joe
$5000
$3000
$20,000
$4,000
Susan
$4500
$3500
$21,000
$4,000
What I want to do is to customize this weekly report for each person: Joe gets only HIS information emailed to him in a "template" that would be standard for each person (see below) -- the template would be the words in blue with the corresponding info, and this would be put directly into the BODY of the email
Name
June Sales
Sales Quota
YTD Sales
Total June Payout
Joe
$5000
$3000
$20,000
$4,000
This would be repeated for all people on the list (100+). I think I can figure out some email code, but it is getting the information into the SAME template for each person repeatedly (not sure how to write a loop for this including the template)
I'm using sheet for ledger. For entering date I use =today() but I want to do that in date column(A) I set a formula only once that if I enter data in 2nd column it automatically add today date and if data enter day after tomorrow it will add corresponding date. Is this possible?
View 9 Replies View RelatedI'm trying to create an invoice with customer list, and the problem now is I'm trying to find a list-dependent data way to input my customer's address.
At the moment, all I can do is a dependent dropdowns from a sorted list (see method here). So it's kinda troublesome in such that my list (of customers) opens up a list for address that has only one option (since there's only one address for each customer).
I hope there's a method out there whereby I can have a list of customers, then select the customer I want, and the address of this particular customer will appear in another cell below automatically (without the need of selecting it in another list).
My address is actually split into three different cells - Address, Building & Postal Code.
I have created a spreadsheet that I must fill out daily. this worksheet has averages that must be automtically shared with a "master" worksheet.
for example
information from sheet2!b23
information from sheet3!b23
information from sheet4!b23
etc...
must automatically be transfered to
sheet1!c6
sheet1!c7
sheet1!c8
etc...
in that order
at this time I am typing in =sheet2!b23 on sheet1 everytime, I want to avoid this.
If you are asking yourself why? I dont blame you but it needs be this way so I can average each days spreadsheet.
I have have a large array of prices (across rows) and am looking for the closest price to match a price that I have been provided with. It's a basic benchmarking exercise on a row by row basis....and the price can be positive or negative. Is there a clean way to reference the closest price?
I have come across a fair amount of solutions, but none worked optimally - particularly the =INDEX(Data,MATCH(MIN(ABS(Data-Target)),ABS(Data-Target),0)) approach....it just didn't work for some lines, and only worked for values less than source price in other instances.
I would also like to reference the source on the next column.
I am trying to do an if statement where I ask if the 2009 price is .50 or less away from the 2008 price, bring back "Check" See below:
2008 2009
$23.95 $24.15
Using excel 2007
I am trying to create a macro that will automatically fill in the days for a user if they fix the date. That is my end-goal, but obviously I have to take it step by step.
My first complication is how can I fill in all of the day rectangle boxes automatically? I was trying to create a macro for it, but it would only change the very first box. (You can see from my sheet, the days go 3 then 2)
After that I want to try and have constant-macro where if they change the month and the year it will automatically update the calendar for the user.
As I am a fresh user of excel 2007 I do not know how hard it is to solve the problem I am facing but hopefully there is a solution that the readers here are willing to share with me.
I have a list of **-s that I have recorded as a hobby with my friends (karaoke, free concerts...) and I have them coded in a specific way, so that I can find them on a shelve easily. The list is built up in a form that is seen on the example file that I added.
So far I have used my own handwriting and a sticker to label my ** boxes. Itīs not a very good looking combo to be honest. Since I already have all the data in my excel tabel it would be good to use excel to take all the things from there and just print it out.
How to do that? The front cover is easy, I used VLOOKUP function. All I have to do is enter a number into the column C1 and the front label gets filled.
The problem for me is the back cover that should take the right track nr, title and length from several rows (VLOOKUP uses only one row at a time)? How to make it work, that excel would know that on the first album there are 3 tracks and when chaging a number in C1 there would be 2 tracks on a second one.
I use a system which produces excel spreadsheets similar to this example (although much larger than this!). This makes it extremely difficult to carry out sorts and to tidy up the spreadsheet - generally I have to do this manually. I am hoping that there is a way that I can easily add in the data as per document 2 in red.
View 2 Replies View RelatedI'm wondering if it is possible to make a table where I can just enter the reference number and excel can fill up the remaining cells with data specific to the reference number from a website. What I mean is that I would like to put in company registration number and hopefully it will be able to pull in other data from companies house website about the company.
View 2 Replies View Related