this function I manage to select the result in terms of the objectives of the product code. and I need to search for a piece of the product code. The results will be the same in accordance with fragments but not the exact code.
View 5 RepliesI've gotten were Cell formulas.
I need this for VB code, for an array that is the product of another macro hence it's dynamic.
B 3
E 2
E 8
D 2
B 1
B 7
How do I subtotal Col A,B according to Col A?
Total 3 B entries with a subtotal of 11
Total 2 E entries with a subtotal of 10
Total 1 D entry with a subtotal of 2
I hv following data in the excel.
I hope some one can help me to create code to do the following :-
1) if have last purchase date , but no last issue date = New Product.
2) if no purchase date , also no last issue date = None stock issue.
3) if have last purchase date , also have last issue date = cal the date between and put the month value .
For example :-
if last purchase date = Oct 07
if last monment date = Nov 07
then the stock duration = 1
I am trying to use a PRODUCT formula to return the product of a list of numbers in a column, between 2 specified dates. The spreadsheet is arranged as follows:
Column BA list of dates
Column C & DNumerical data not used in any calculations
Column EThe numbers to multiply together
Cell A1Start date to be used in the PRODUCTformula
Cell A2End date to be used in the PRODUCT formula
The formula I have to multiply the data in column E, subject to the start and end dates in Column A is:
{=PRODUCT(IF(B15:B1000>A1,IF(B15:B1000<=A2,INDEX(B15:E1000,0,4))))}
This returns zero though works appropriately if I replace “PRODUCT” with a “SUM”.
I need to identify every 5th occurence of a product code (In column B)
the list won't be consistent i.e. Code 80100811 may appear in row 2, 17, 35, 47, 51
So I would need to identify the one in Row 51 in the above example,
WorksOrderNumber
Product Code
DateEntered
Column1
56
80100811
01/08/2012 00:00
[code]......
i would like to create a userform with a textbox to enter a product code , a command button to run VBA and a big text box to show output of VBA from Textbox 1 which i will connect to DB and pull back information relevant to that product code , is this possible i can create the userform no problem and already have code to pull back from DB into Excel ,
View 9 Replies View RelatedI'm working on a order spreadsheet system, and I have one sheet called Product Details, where the product name, list price and product code are found, these link to the Sales order page, and I need them so that they can be added to.
So far, I have created a dynamic named range for the Product name on the Product details sheet, and linked the the range via a list validation on the Sales order sheet.
The drop down list displays the products and can be added to by typing new product names on the other sheet.
What I need now is that when a product on the sales order page is selected, it draws the the list price and product code data automatically from the sheet, I tried using a normal vlookup, but I couldn't get it to work. I also need the list price and product code columns to be 'dynamic' so new values can be added further along.
I'm using Excel 2010 for Mac.
Here's my problem: I need to keep two values subject to two upper limits (i.e: value 1 < x, value 2 < y).
These two values are equations that are dependant on 3 variables. The relationship between these variables are not linear (i.e.: can't just plot a graph and mark where the lines cross).
Is there any computational method using Excel I can use that will give me the LOWEST value for the three variables in order for my target values to be under their upper limits?
I would like to have a Macro to go from the first tab called "Start" and end up with the second tab "End" automatically. BAsically I need to take the product on each line under Tab Start and reproduce it for sizes 35-41 always ending with "-(size)". Then this new Product with Size needs to be multiplied one below the other for as many times as I have pictures (number shown under column B of the tab Start). Next to this value in column B of tab End I'd like the same name listed again but with the number 1, then 2, then 3, etc. as needed and the extension .jpg.
View 4 Replies View RelatedI'm trying to set a cell on one sheet to be equal to the product of two cells on another sheet. The problem is that one of the cells on the other page is dependent on the variable T. This is what I've got and it's giving me errors
Worksheets("Output").Range("K14").Formula = "Worksheets("Calcs").Range("D17")*worksheets("Calcs").Range("D17").Offset(10+T,0)"
When I record a macro it gives me this, but again, I need the last cell in terms of "T"
ActiveCell.FormulaR1C1 = "=Calcs!R[3]C[-7]*Calcs!R[14]C[-7]"
Instead of having the goals and objectives already in the form I would like to have buttons that would allow my clinicians to add as many goals and objectives as they'd like to the attached treatment plan. I'd like them to be able to click a delete button as well when they review the plan and need to delete a goal that's been met. When my team clicks into the second worksheet they would see rows 10-16 (below). They would then type a STG 1 and an Obj A. They then would click the Add Obj button and add as many objectives that they needed. Then when they clicked the add goal button a blank copy of rows 10-16 (including the Add Obj button) would pasted one row down and would update to read STG 2. Neither of these buttons would appear when the document printed.
Here is a screen scrape of what I'd like the attached form to look like before goals and objectives are added.
add goal.jpg
The original form is saved in an .xltm format but I saved it in an .xlsm format so I could upload it here.
HCT ITP (2-25-14) Abbr.xlsm
I found a thread on this forum that came close to answering my question but my attempts to pull out the coding that I needed was unsuccessful. Here's a link to the thread: [URL] ........
I need a formula that will bring up the Model type of a product based on its Material code. All the models I need to generate have a "root" number in them (the first 6 digits define model type). This is what I need to fill out:
View 2 Replies View Relatedi need to look up a product based on the number and the qty. see the example attached.
On tab "Printer - S" 1 need to lokup the costs based on the product code 8 and the qty 100. How do i do this and make it adjustable for any range of priduct codes and qtys. Again, becuase this will be used on a Mac Office, i cannot use macros.
In the first sheet I have two columns, one for the product_id and one for the name of the product. So the Sheet1 is like a small database. The second sheet is for the orders.What I want is when I type the product id in the A column of the orders sheet(Sheet2) to auto insert the product name in the B column so i dont have to write it every time.
View 2 Replies View RelatedI have the following code........
Which I have attached to button in Module 1.
The button is being used to remove duplicate names from a list of names and then copying the result before pasting it into another sheet.
The removal of duplicates works OK but when I try to paste I get a ........
Is it possible to enhance this line of code:
Code:
.Range("A1:EH" & llastrow).AutoFilter Field:=10, Criteria1:="Cows"
Which filters my database to display only rows in which column J (10) equals "Cows", to display all rows that equal "Cow" and "Chicken" and "Giraffe" and "Cheetah"?
I have a situation where I need to highlight different customers based on what there terms of payment are. Conditions 1 and 3 seem to be working fine but for some reason condition 2 is not working at all. I made sure all names are exactly as listed in the A2 cell....
View 9 Replies View RelatedI'm writing a macro to search for letters A to Z in one spreadsheet, and copy it's associated data into another spreadsheet where A to Z already exist.
Thus:
Cells.Find(What:="A", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
That works fine. It find's "A", then the rest of the macro moves to the right and copies the data needed:
ActiveCell.Offset(0, 1).Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
THEN it goes to the NEW spreadsheet (where A to Z are already labeled) and pastes into the corresponding area. Thus:
Windows("The Other Sheet.xls").Activate
Cells.Find(What:="A", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
This works perfectly.
However:
What if, one day, "A" does not exist in the source data? If say, the data started at "B", because there were no results for "A". Currently, the macro will stop working if it attempts to search for a value that doesnt exist.
How do I make it, for example, Search for "A", find that "A" doesnt exist, then skip to Searching for "B", and then "C" and so on?
I'm creating a worksheet that gives a list of part numbers based on the product part code. In most cases I can use the following.
=LOOKUP(O6,{0,1,2,3,4},{"NONE (M25)","SMP-55-001","SMP-55-004","SMP-55-008","SMP-55-014"})
so this gives a part number depending on what number is placed in O6. What I need to do know is look at 2 different cells and for each combination of numbers give a different part number. so if A1 is 2 and B1 is 3 give a certain result.
I have, for my formula, 2 constant values a and x, I then also have a variable, n. The formula I'm trying to convert into excel terms is
SIGMA (with respect from 1 upto n) ((1-x)^(1-n) * x)*a. Unfortunatly thats not drastically inteligable so i've attached a picture file that should explain it in more understandable terms. Now the problem im having is, sigma is a cummulative funtion, which in this example is relative to the variable n, firstly I don't know what formulas I would have to use in order to get a sigma/summination function, and secondly I am uncertain of how to link it to a variable range.
I have created a dictionary with technical terminology. It is in excel format with about 10,000 entries. The format of my dictionary ("Sheet1") is in three columns:
<vocabulary term> <pronunciation> <definition>
Now, I want to create vocabulary lists using my dictionary. The newly typed up vocabulary list (which only has the term, but not pronunciation or definition), will be copy and pasted into "Sheet2".
The "Sheet2" typed up vocabulary list is in the format:
<vocabulary term 1>
<vocabulary term 2>
etc...
I want to run a macro or script to dictionary terms (including pronunciation and definition) from "Sheet1" and put them into my vocabulary list "Sheet2".
The desired output list in "Sheet2" is: .....
I would like to create a code that would summarize any day of the year in numerical terms.
The formula for coding "days" is as follows:
Coding Month value + current day
Where "coding month" was a combination of the year and month.
The year can be summarized as a fixed value:
2007 yr = 4
2008 = 5
2009 = 6
Ignore anything prior to 2007 and anything after 2009.
The month is simply a SINGLE digit to represent the month and where
January=1, feb=2, march=3......Sept=9. Once you hit October, you have to reduce the values to a single digit, so October = 10 = 1+0=1, November =11=1+1 =2, December = 12 = 1+2=3
Therefore, May 2007 = 4 ( 2007 ) + 5 = 9
June 2007 = 4 + 6 = 10 = 1+0 = 1
Now, comes the addition of the individual day to the mix. If you wanted to find the value for May 23, 2007
You know that May 2007 is 5+4 = 9 so adding the 23rd day is 2+3=5
therefore 9 + 5 = 14. Right?
Not really...
Before reducing this final number down further, there are some double digit numbers that DO NOT get reduced further. They are:
a. 13,14,16,19
b. 11,22,33,44,55,66,77,88,99
These numbers have to be left as is. So, today, May 23, 2007 would be left as a "14"
Two more examples:
May 24, 2007 = 5+4+ 24 = 5+4+2+4 = 15 = 6
July 17, 2007 = 7 + 4 + 17 = 7+4+1+7 = 19 = left as 19
Final thought - It would be helpful, though not necessary, to have a notation besides these double digits. If series "a" could have a "KD" next to it and series "b" could have a "MN" next to it,
I have thousands of data entries (each in their own row, all in one column) and need to separate the basic info. In the example below, say you want to isolate the Town names by entering a formula that can produce a TRUE or FALSE result. Is there a way to use the FIND function and a pool of terms to isolate cells that contain excess data (i.e.- produce a FALSE result for cells containing any words from a term pool like : "Police, Park, Power, Water, Community, Bakery, Grocery, Field, Garden, Post Office, School" etc... ) ?
Pic of sample spreadsheet with desired result:
Screen Shot 2014-03-18 at 3.14.09 AM.png
example as text:
A2 = Springfield
A3 = Springfield Police Department
A4 = Springfield Community College
A5 = Springfield Park Of Recreation
A6 = Des Moines
A7 = Des Moines Power & Water Company
A8 = Des Moines Sunrise Bakery
A9 = Des Moines Post Office
A10 = Des Moines Grocery Store
A11 = Lancaster
A12 = Lancaster Baseball Field
A13 = Lancaster Botanical Gardens
I have the formula where i can change a date to its equivalent in terms of quarters system with a date:
=CHOOSE(MATCH(MONTH(A2),{1,4,7,10}),"Winter","Spring","Summer","Fall")&YEAR(A2)
This is a school year configuration.
ex.
A2 = 10/1/2005: with the formula up there it turns into Fall 2005
i want to be able to add any number of years and the formula will still come up with the quarters system
also i would like A2 to be stationary and create a list of quarters for each year i add on
ex.
A2= 10/1/2005 B2=Fall 2005
B3=Winter 2006
B3=Spring 2006
B5=Summer 2006
B6=Fall 2006
etc.
If this is all possible lastly I would like to negate summer quarters
1) This exercise would save me considerable time and would almost certainly eliminate the possibility of typing errors in preparing the input data file for another application.
2) My program requires each input multivariant equation to be specified as follows and in the same order:
--Number of Independent Variables, N (max 10)
--Number or Terms, M (max 20)
--Term 1: power of var 1
: power of var 2
: ..............
: power of var N
: coeff
--Term 2: power of var 1
: power of var 2
: ..............
: power of var N
: coeff
........................
--Term M: power of var 1
: power of var 2
: ..............
: power of var N
: coeff
3) It would be extremely helpful if I could type the equation (of up to 10 variables and up to 20 terms) in a cell on a w/s and automatically get the above particulars extracted and nicely tabulated in a column.
4) Simplified Example: (3 var & 4 terms).........................
I want to have a code where I can have an input box accept a value of text, such as aname and then use that value throughout my code, I will also be saving a file using that name
View 4 Replies View RelatedI have 4 columns in a worksheet.
A B C D
number type date name
I am using an input box to enter the value of column D. After the value of column D is entered, I want a message box that displays the corresponding data in column A and B. with the vba coding?
The following codes work well but when I have changed the following condtion ,
If cel = Tgt.Range("A2") Then
to
If cel "" Then
I got worng result, can anybody help me to correct it?
Option Explicit
Sub test()
Dim sh As Worksheet, Tgt As Worksheet, i As Long, j As Long
Dim d, a
Dim c As Range, Rng As Range, cel As Range
Set Tgt = Sheets("Report")
Set d = CreateObject("Scripting.Dictionary")
j = 0
On Error Resume Next ...............
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 can't seem to get why one code is giving a correct solution and the other one, not even close. They are both identical but there are some difference.
The program is suppose to pick up about 11 cell values, it then store these cell values into a collection, and randomly picking out the 11 values without repeating until there is no more to pick out.
Here is the code: (the top one doesn't give the correct result, it picks it out of the same ones and the number doesn't seem to store correctly, the bottom codes works as it suppose to work.)
Code:
lastCol = Cells(215, Columns.Count).End(xlToLeft).Column
Dim C As New Collection, I As Integer, rdom As Integer
rowFOUR = 216
colFOUR = 2
'cntHIT = 0
rowtest = 216
[Code] ..........