I have a excel sheet with 3 columns. In column B and C there is either a number, or empty. If both for instance B1 and C1 is empty, i want A1 to automatically be filled in with the number 1. If there is value in one of them, then i dont want anything in column A.
View 3 RepliesI 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
which formula achieves the following:
i have a simple table design with the dates running across the row, below each row i write the name of the person on shift. in the 3rd row i mark the time they are late for work (if any).
below that in a seperate table i have a list of my staff next to that i would like there to be a running total of their lateness. I need a formular that will look for their names and then total any lateness that i have inserted. I have include a basic spreedsheet of what i want to achieve, the column in RED is where i wan the totals.
I have 64 code ie: a1.c4.f6 etc and 64 corresponding value ie: $160,$180,$200 etc.
How can I get excel to check all the options to give the correct valuation.
I have got 102 quantity of TextBoxes in my form.
Each field represents the number of "SALE QUANTITY" , "FREE GIVEN" , "RETURNED" products. We have got 34 PRODUCTS range. (34 x 3 fields = 102 textfields)
The name of the TextBox fields are as follows :
PR01S , PR01B , PR01K
PR02S , PR02B , PR02K
...
...
PR34S , PR34B , PR34K
All of the fields are required to be numeric data only. How can i check this fields to be numbers on the same time of the data input (or when jumping to the next field. There will be a message box mentioning that the field can only contain numbers + SetFocus to the wrong input field) ?
I dont want to write same code for each item, its too hard and long..
If i can solve this problem, then I will need help to enter the values from this fields to the worksheet because I must find a way to enter the values with a for next loop or something.. I dont want to do it one by one.
Here is how the form looks :
I dont know if there is a possibility to exclude the product to be entered in the worksheet if all 3 boxes are Zero (0) for that specific item.
Example : If all fields are "0" for item "Coke" then I dont want this products information entered in the excel worksheet.
0 Sale , 0 Free, 0 Return
I have an excel file with several sheets. I have a macro with IF-functions to check the values of two fields in sheet1 (one text input field and one drop-down menu with two values A and B), and based on these values I get different data from sheet2 and inserts it into a field in sheet3
View 5 Replies View Relatedi have weights entered in A1 , B1 , C1 and I manually enter almost same weight in D1, E1, F1 . Now I need the result as " OK " in G1 if the weights A1,B1,C1 match with D1,E1,F1 if it doesn't match then result should give "Please check again".
CountryHourDataTotalData
Austria - A10Sum of SeiA51CountryHourSum of SeiASum of SeiT
Sum of SeiT4.88Austria - A10514.88
1Sum of SeiA561562.83
[Code]....
left side pivot created in vb 6.0 & right side pivot table created manually in excel.
i want to generated pivot table using vb 6.0 same as right side pivot.
Set PRange = ws1.Range("R1:Y" & finalrow)
Set PTCache = wb.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)
Set PT = PTCache.CreatePivotTable(TableDestination:=ws2.Cells(1, 1),
[Code]....
My company uses 4 types of barcodes 8, 12, 13, & 14 number barcodes for our products my problem is that I can't figure out how to force the barcode to format properly no matter how somebody enters it if they don't use spaces or put them in the wrong spots, I can't use custom formats because there is 4 different layouts
8 digit should be "#### ####"
12 digit should be "###### ######"
13 digit should be "# ###### ######"
14 digit should be "# ## ##### ######"
these barcodes are in columns L, M, & N also right now 'm using a formula in another cell to verify the barcodes by calculating the check digit and comparing it to the check digit typed the formula i'm using is
I have data in Row 53 that spans 7 columns, but stays in the same row. I want to design a loop to select every 7th cell in that row and check if it is empty. If not, add onto a "counter" then display the final number of occupied cells (the value of the counter) at the end. This is what I have so far, but I get all sorts of errors.
Code:
Sub Tester()
Dim WB As Workbook
Dim WS As Worksheets
Dim modCounter As Long
Dim Cell As Range
Set WB = Workbook("Transverse Series.xlsm")
Set WS = WB.Sheets(BM18)
[Code] ......
I am using this code
View 5 Replies View RelatedI have an Inventory Workbook were Item codes in Left column and Date in top row. I am using the Vlookup formula Its displays only the first value. If their are repeated dates for the same Item code I want to sumup the value according to the Date for that Item code. find the attached sample file
I need to update the vlookup formula in the output, so that it takes the values accordingly and sumup.
I am not sure if I have explained this clearly; however, you will understand better if you see my attached file with examples of my expected output.
Im trying to figure out to give a certain word a numeric valuve in excel?. Lets say that the word "car" has the valuve of 10, and the word bus has a value of 20. Have found one possible way to get around being forced to give a certain word a value by doing alittle macro, but havent got it to work yet (and im not sure if its even possible to get it to work), I have a post in the macro section about it.
View 3 Replies View RelatedI have a formula now that is =right(C2,5)+0 that is working well. However the data has grown and sometimes there is also 6 digits now instead of 5. So I need it to pick up either one 5 or 6. When I change the formula to 6 it works but picks up a / which happens to be before the 5 digit # sequence when there is only 5 digits. It works great for the 6. Is there another way around this so I only get the numbre digits if there are 5 or 6 and not the /. Maybe an if statement. I've tried several ways but none work right. The only other thing I can think of is to get it as above with the =right(C2,6)+0 and then afterwards to a find and replace and remove the / from the data. I was just tryign not to add an extra step to the process. Any ideas please?
Example of the data in coloumn C2 is:
15/2000/4567/NA/NA/97305or with 6 digits at the end15/2000/4567/NA/NA/973052there is always just 5 or 6 digits at the end that I need.
I want to count a range of cells and if the value of those cells is 5 and over, i want it to give a 1 and if its under 5, i want it to give a 0. I've attached a file of what i'm after, row 13 is where the formula would go, i have just put in the values.
View 1 Replies View RelatedI would like to write a formula to have a result in a cell that gives me the maximum value of a column, but smaller than another cell.
For example:
C1 = Maximum value in column A1:A20, but smaller than B1.
Can i find a list anywhere of the new Excel 2007 ALT numbers to give the symbols eg Alt0186 = the degrees symbol in 03 but doesnt work in 07?
View 10 Replies View RelatedI have around 500 csv files, each with four columns of data. However, the first column does not have a heading. Is there any way I can give the column name to the first column in each of 500 csv files? Here is one of the csv files:
name1 name2 name3
1774.77 4.15 29.28
17810.45 6.2450.36
18257.86 16.9169.11
18326.46 20.1 18.93
1841.48 20.29 0.95
1850.84 20.45 0.79
2031.49 3.44 8.86
2040.06 3.41 -0.87
2050.02 3.37 -1.17
2060.91 3.51 4.15
20711.35 2.77 -21.08
2085.23 3.51 26.71
2090.02 3.51 0
2100 3.5 -0.28
2110.06 3.33 -4.86
21229.25 6.91 107.51
Excel 2003
Lets see if I can even explain what I want to do.
will have a date column
Will have a total calorie column (this will be a sum of several column containing individual foods)
On a separate worksheet (food sheet) will be a list of foods in one column and their calories (per serving) In the next column.
On the first sheet I want a drop down that lists all the foods on the "food sheet). selecting from this drop down will use a vlookup (or hlookup - depending) to arrive at a caloric value.
So here is (are) the problems. (and the solutions need to be NON-VBA)
1. How do I tie the drop down selection to a vlookup (macro maybe)
2. How do I use that one drop down to populate any cell on sheet one?
can this be done without coding?
This will be an exercise for an excel class to teach vlookups, linked sheets,
making a dropdown, using a macro, formulas, IF statements............
The class is probably not ready for VBA which is why this needs to be a non-coded solution.
I am open to other solutions (related topic) that can bring together all of the above learning concepts
I'm doing a statistics workbook there I need to add untill the given value in the cell
ex: if gave 5 in the cell we want to get the total until only to 5th cells
(M/M/S)FIFS/INF/N)
No of Server
3
l
0.20
n
Coeffient
P(n)
[code]....
We changing the value in N=5
I have a LOG sheet where I am getting all my data from,
and a TIME and DUTY sheet..
COL A Has Dates, a4 to a34, Limited to 31 lines for the dates
and in col F I have # values, (1.2-2.2 etc).
The formula I have in Col F is
=SUMIF(LOG!$A:$A,#REF!,LOG!F:F)
but when i get to the end of the month, say for example the last 2 lines of the time and duty sheet, when a32 is 29/ Feb, the next row is blank and same with next row. (leap year).
How do I get the formula above to ONLY calculate when the cell in COL a on the same row, when there is a DATE value in COL a?
I have two workbook, test1 (master file) and the other one is test_10_05_2014 with 100 some of them being updated daily. The last portion of the file "test_10_05_2014" is the date and we have different file on daily basis.
using Indirect function in vlookup should give corresponding value for the particular name. Formula used is giving error,
[VLOOKUP(A2,Indirect(" ' "&E1&" ' !$A$2:$X$1000),6,FALSE)]
I need a formula to look in column B and find all "jun" (or anything else that I put in there) THEN look in column J and find only all "d" (d is one of 3 choices there) that correspond to the "jun" then give me the count of the "d".
This is an example. With the formula I can do a variety of things to get info that I'd like to have.
I have undertaken a project and have hit a brick wall.
I'll try and translate my request in the easiest way I can.
I have a piece of software that stores what software is installed on a machine when it connects to my domain.
I therefore have a list of software that is installed on machines that connect to my domain.
Each machine that connects to the domain has a unique identifier code.
At present I have about 500 different pieces of software on the various machines.
The unique identifier for the software installed on each machine is a combination of the application (column B), the program (column C) and the version (column D)
I wanted to give each seperate piece of software a unique ID number.
I was wanting to run a formula similar to the pseudocode below:
If $b$1="adobe" and $c$1="8.0.0.1" and $d$1="adobe reader", then "1",
elseif $b$1="adobe" and $c$1="7.0.0.1 and $d$1="adobe pro" then "2", else "no software ID"
I am thinking that if I can get every piece of software that is installed on the machines to have a software ID then I can create a software ID allowed list then I can create a formula to identify what laptops have software on the "allowed" and "banned" lists.
The problem at present is getting the sotfware to lookup what software ID they have as they need to check 3 columns with a lookup list.
This one sounds easy but I have spent a lot of time on it and no success. I have a list of students with various levels next to each name. EG: 4.5, 5.6, 5.7, 6.1 etc. From this range I want to work out the percentages of achiveing Level 5, 5 and above, Level 6, 6 and above. By doing a countif it counts 5 not 5.1 5.2 etc. How do I get over this? Attached is a sample.
View 10 Replies View RelatedI have a table with 5 columns and approx. 85-90 rows.
Column A has the Branch name in it e.g. Beavers or Bedfont (11 Branches in total)
Column B has User Type - Adult, Child, Guest (Adult), Guest (Child), Catalogue
Column C has Session Type - Booking, Drop-In
Column D has Total Session Time (mins) - which gives a number in minutes of the total session time used
Column E is not needed
I currently get a calculator and add up e.g all of the adult Bookings for Beavers and enter them onto a Report Sheet, then all of the Adult Drop-Ins for Beavers etc. I want an Excel Spreadsheet that will give me a total number for each so I can do away with the calculator.
I am thinking of creating a new sheet with a number of cells that have a formula similar to this
=IF(AND(A2="Beavers",B2="Adult",C2="Booking"),E2,0)
But I want it to see Adult, Guest (Adult) and Catalogue as the same thing / and I want it to pick up Child and Guest (Child) as the same thing.
I have the attached worksheet with clients' names in four columns (A-D). The first names and last names must remain separate. I need one additional column that shows whether a full name (first and last) in C and D also appears in A and B.
Then one more column that shows how many times the name in C and D shows up, but only if it is also in A and B.
I am trying to make sure that I give my worksheet a unique name in the workbook. To do this I create a name that really really really should be unique, but just in case I also try to check it against existing worksheets, and this is where I am having the problem. I thought the following code would trigger the 'goto' on a 'subscript out of range' error (ie. the sheet doesn't exist), and then carry on:
View 3 Replies View RelatedThe following formula shoud give an answer of 178. I think I am placing the
parenthesis in the wrong position.
The answer I get is 308669
=(((F22*71449)*0.4)+16147/C22)
Cell-F22 number is 1826
71449 is a constant number
0.4 is a constant number
16147 is a constant number
Cell-C22 is 10.8
I am currently using the .resize function to give a specific value to each cell within a range.
First of all, is it the most efficient way to do it?
If so, why am I getting error "Application-defined or object-defined error"
with this code :
Code:
ThisWorkbook.Sheets("Merge").Range(Cells(3, 2), Cells(4000, 2)).Resize.Value = "TEST"