I would like to distribute values in cells depending on the input value (excel file attached). This file is a test and basically i've been entering the data manualy. The format is flexible, so it can be reorganisaed.
View 2 RepliesI have two cells, A1 = Yes/No and B1 = Date. I want to restrict someone inputting a date into B1 until A1 is marked as Yes...? I've tried data validation, but seem to be hitting a wall with it...
View 2 Replies View Relatedi have a certain column that tells me if a client has withdrawn from the company, and the column just says "y" or "n" i want excel to automatically copy the whole row to another sheet if that cell is a "y" for yes the client withdrew...
View 9 Replies View RelatedI m trying to write a formula for my financial model. If anyone can take a stab at a solution. I'm trying to write a formula that will equally distribute revenue either over the next 1 month, 2 month or 3 month period depending on size of the deal.
Details:
Sales will fit in 1 of 3 categories. Less than 25k; between 25k & 100k; greater than 100k.
- if under $25K, recognize in next month (month N+ 1)
- $25K-100K, recognize in two equal parts in months N + 1 and N + 2
- over $100K, recognize in three equal parts over 3 months
N + 1, N + 2, N + 3 ...
I have an month input in cells B2 (user can enter values from 1-5)
I then have a data table that has month 1,2,3,4,5 running across range G9:K9. the data is held in range G10:K19.
So if user types in 4 in B2, what should happen is that months 1,2,3 and 4 the data for these months should be cleared and data in month 5 moves forward into month 1.
So basically, anything left of the month entered is B2 should be cleared and replaced with anything right of the month in B2.
1
2
3
4
5
£11
£133
£29
£193
£100
[Code] ........
Assuming I have a data set like this :
column a column b
916 12,5
916 23,4
923 34,5
923 23,9
932 98,6
934 67,7
How can I get a sum of all cells in column b when values in column a are equal?
I'd like help writing a single formula that assigns either a numerical 1 or 0 to a cell based on values from two other cells.
By way of background, I'm working with three columns. Cells in column F contains values from 1 to 4. Cells in column G contain values from 5 to 11. Cells in column H are currently blank.
I need a single formula that will insert either a numerical 1 or a 0 into column H according to the following conditions.If F3 = 1 and G3 = 5 or 6 or 7 then let H3=1 otherwise let H3=0
If F3 = 2 and G3 = 7 or 8 or 9 then let H3=1 otherwise let H3=0
If F3 = 3 and G3 = 9 or 10 or 11 then let H3=1 otherwise let H3=0
If F3 = 4 and G3 = 9 or 10 or 11 then let H3=1 otherwise let H3=0
In what would be Cell E2 I want to post the Total posted set Assuming that the Order Numbers and the OP Numbers are the Same, but only if there is a value in the allowed set, So I'd expect E2 in the case below to read 1.83,
Order No OP Posted Set Allowed Set
30761157 00100.731.500
3076115700100.500.000
3076115700100.600.000
3076461000100.000.000
3076461000101.050.500
3076524400100.000.000
3076524400100.550.500
3076639600100.000.000
3076639600101.180.500
3076810900100.000.000
3076862900100.000.000
3076862900100.000.000
3076862900100.000.000
In a macro I want to automatically insert a formula in several cells. The formula depends on other cells, and I want to be able to manipulate these cells with variables. Here is my code:
[Code] .....
All my variables are declared:
Sheets("DATOS YTD 2014").Cells(I, Semana + 1) is the cell I want to be able to chose due to the variables
Sheets("DATOS YTD 2014").Cells(I, 3) is the reference cell for the VLOOKUP formula, that will vary with I
SheetMonth.Range("B3:W172") is a range in the sheet SheetMonth which is fixed
The error message I receive is the following: "Run-time error 13, Type mismatch"
I precise that the next step is to make the ",6," part in the vlookup formula also variable... but I guess once my current issue is solved this one will be a piece of cake.
I new for a forum I have a small problem I just stock and can´t see what to do. So i need to move data from 4 columns B,C,D,E to F,G,H,I depending on input in column J, like if in J2 i enter letted M (or any from column A) i need to move data from B12,C13,D13,E13 to F2,G2,H2,I2 and so on. I dont have experiens in programming in exel or using formulas.
View 14 Replies View RelatedI am attempting to cross reference sheets to match up IPs to building names. I have attached an example. Essentially, I am trying to take column E on the TEST sheet and match it to column A on the IPs sheet. Whatever the Building Name (column B, IPs sheet) is, I want that filled into Column G on the TEST sheet. This would be a very labor-intensive process if it cannot be done automatically.
View 3 Replies View RelatedI am tracking business hours of various business locations. I would like to be able to enter the businesses operating hours on the "overview sheet" and, based on those hours, have a table generated on another sheet that "autofills" based on the date entered on the "overview sheet". For example:
I would enter the operating hours of the business on the "overview sheet" and it would look something like this:
Day
Open
Close
[Code]....
I want a table for each day created. I would then manually enter the data for the "Ranking" column. The issue is that I have numerous location I want to do this for and all with varying hours of operation. It is very tedious to manually create the tables.
I am wanting to transfer data from one sheet to another depending on cell input
Example:
If I enter "2" in sheet1 cell B1, then I want a macro to take A1 to sheet2 but place it in A1 & A2.
Then say I enter "1" in sheet1 cell B2 goto sheet2 and put A2 into A3 and so on.
Depending on the number entered on sheet1 column B, the Macro duplicates the info from Column A in that row into column A on sheet2. ??
I have lot of data in three columns like this. The first column is "Name", the second is "Comment" wherein I want the macro to write some comment, the third one is DOB. The problem is that the names in column 1 repeats many times. I want a macro to write in column B "either Old or Older or Oldest" based on the Name and DOB. Thus David with DOB 13 Sep 1982 be marked Oldest in Column B and David with DOB 25 Aug 1988 be marked Older and David with DOB 24 May 1990 be marked Old. Similarly William and Rita should be marked either old or older or oldest. As the data are enormous I do not want to refer the actual name in the programming. I want to call them using a variable in programming.
NameComment DOB
David 25 August 1988
David 13 September 1982
David 24 May 1990
William 24 March 1980
William 25 July 1987
William 13 August 1989
Rita 17 July 1990
Rita 24 April 1989
Rita 13 June 1988
The example file is attached
I have lot of data in Excel 2010 which I wish to bring in Columns using a Macro depending on the input value which the macro should prompt me. For E.g.:
A1B1C1D1E1F1G1H1I1J1
12345678910
12345678910
12345678910
12345678910
12345678910
If I select data from A1 and J1 (in practical it will be more Columns) the Macro should prompt me how many Columns would be the output on Master Sheet. If the input is 2 then it should create an output Sheet "Master" and should show the following result
A1B1
12
12
12
12
12
34
34
34
34
34
56
56
56
56
56
.. ..
.. ..
It after selection I run the macro and input 3 then the output should go in three columns (A1,B1,C1) one below other. If 4 is Input then 4 Columns (A1,B1,C1,D1) will come below each other so on and so forth.
i have a macro which takes the results generated and puts them into a table and filters the data so that zeros does not show and arranges the table from largest to smallest. Now i have a problem where i need to select the power, fuel oil consumption, weight and area from one table and the other power, fuel oil consumption, weight and area from the other table and combine them together so that i can have all possible combinations of the data, but i don't want the zeros to show in the combinations. i need the combined data to show on a separate sheet. The data of table 1 varies with rows as shown:
The Data of Table 2 varies with rows as shown:
I would like to achieve a result like this in the following columns and rows without having to input them manually:
I am trying to determine Long Term Gain (LTG,) Long Term Loss (LTL,) Short Term Gain (STG,) Short term Loss (STL,) or No Loss nor Gain (NGL)testing two cells (A1 and B1)and setting a third cell (C1) to the text LTG, LTL, STG, STL, or NGL depending on the results of testing cells A1 and B1.
A1 represent a number of years and B1 represent gains or losses (negative)in dolars.
The way I see the logic is as follows:
If cell A1 or cell B1 are either one of them equal to 0, then it is neither a Gain nor a Loss (NGL.)
If cell A1 is greater than or equal to 1, then it is Long Term; else, if A1 is greater than 0 and less than 1, then it is Short Term.
On the other hand, if cell B1 is greater than 0, then it is a Gain; if B1 is less than 0 (a negative number,) then, it is a Loss.
I need to find (if it is posible in Excel) one formula to test the two cells for posible outcomes:
If A1 = 0 then C1 = NGL
If B1 = 0 then C1 = NGL
If A1 >= 1 and B1 > 0 the C1 = LTG.
If A1 >= 1 and B1 < 0 the C1 = LTL.
If A1 < 1 and B1 > 0 then C1 = STG
If A1 < 1 and B1 < 0 then C1 = STL
I have a spreadsheet with 5 columns. A1 to A5
Entries are made into columns A2 to A5 but only ever one entry across all cells
If an entry is made into A2 to A5 the I need a "P" to be placed into A1
I would like to know if it is possible to transfer values of cells this way?
I've attached the excel file for easier reference.
I have problems with my userform's listboxes. I have two listboxes, and I want second listbox's values to be dependent on first listbox's values.
And even more complicated, I need second listbox's values to be dependent on values on certain matrix.
In that matrix, row headings are listbox1's values and column headings are listbox2's values. How ever there are blanc cells on that matrix aswell. So if there is a blanc cell(s) on a row which (heading) is selected at listbox1, then I don't want that column (heading) which intersects with the blanc cell to be included to my listbox2 values.
Finally I want to insert the selected values from listboxes and the value from the intersection of those listbox values (headings) on that matrix to worksheet.
I included an attachment, where you can see my point better. However, as you can see, now the listbox values are not dependent on that matrix. Otherwise it is working like I want it to work.
Got a road block on one of the trackers which I am trying to create which compares some of our agreements. Basically we need to compare current production with our contracts and then bring the tier level they are at in order to then calculate how much in overrides we receive. Belwo is an example:
Input Value = 140000
Out Should be = 1.5%
Set of Values 1
T1
T2
T3
T4
[Code]...
In a nut shell it will look for 14000 in set of values 1 and pick T3 to then go in set of values 2 and display corresponding T3 which will be 1.5% in above example as output.
How can i do sum of the values which are in red color
CODEAMTA1B2C3D4E5F6G7H8
There is a data range in the E + G columns. The content of the E column can be 5 different items with variable GROSS weights in the G column:
.........E..........G..........R...........S...........T
1.....Size.....Gross....%.........Net......Size
2.....200 g....138......56.52...20........45 g
3.....100 g.....84....................35......100 g
4......45 g......52....................60......200 g
5.....500 g....253...................124.....500 g
6.....Strip......15.......................7........Strip
7.....100 g.....90
8.....500 g....280
9......45 g......62
10........................
I have created an excel document with If formulas. If nothing gets entered in that particular row, the row simply says "None". Is there a way to get rid of any row that contains that value by any chance?
Something along the lines of if the row says "None" then that particular row gets automatically hidden? It would probably need to be in VBA?
Example:
Sheet: Users
Column A has "Yes and No"
Column B has "Names"
ex.
A B
Yes Peter Johnson
No Patrick Andersen
Yes John Smith
Now i would like to create a list in sheet [Employee] where one the users with Yes in column A is shown.
In this ex.
Peter Johnson
John Smith
No need to show yes or no.
I have a workbook with several drop down boxes and formulas already set up and working. I want to improve it though. My question is.... is there a formula that will make the drop down list change based on a cell value....
For example: If B6 equals vegetables then C6 equals list (potato, carrot, pea, etc.) If B6 equals fruit then C6 equals list (apple, banana, grape)
So, I have some names and values. Rank function give me order for those values.
Small function gives me ascending order. I want to get first n (let say 5) values back next to each other but can't use VLOOKUP function because sometimes I get duplicates (red numbers).
If there is more same numbersthat small function returns... It need to give me all of them, no matter 5 is limit. how to get back values of rank function that are duplicated. Book1.xls
see attached document - this is a stock record sheet. what i want it to do is when Bed is seleceted in column B - i want colum C to show the BedSize list which is in the lists tab. and the same with Chair i want it show ChairSize
View 14 Replies View RelatedI have some arrays to loop through some code which generates some graphs:
Code:
Sub demo()
Dim aServer
Dim aType
Dim i
aServer = Array("a", "b", "c")
aType = Array("1", "2", "3")
[code]....
I am now trying to populate the arrays dynamically and thought I could work that out myself, but this is my first ever time with VBA and it's not as easy as I thought (Or maybe I'm not as clever as I thought!)
I have a table in a worksheet with column headings and row headings which are the values to be added to the array.
Lets say I have the following:
Column headings = "x", "y", "z" (these are the potential aType values)
Row headings= "1", "2", "3" (these are the potential aServer values)
_,x, y, z
1
2
3
The values in the table are either TRUE or FALSE (Or blank which should be interpreted as FALSE). Where there is a TRUE the column heading should be added to the aServer array and the Row heading should be added to the aType array.
In this 9 cell example (Not counting the row and column headings), if the first 2 rows had all the values as TRUE (See example table below) it would therefore result in the following arrays:
_,x, y, z
1,TRUE,TRUE,TRUE
2,TRUE,TRUE,TRUE
3,FALSE,FALSE,FALSE
Code:
aServer = Array("1", "1", "1", "2", "2", "2")
aType = Array("x", "y", "z", "x", "y", "z")
If the table was as follows:
_,x, y, z
1,TRUE,FALSE,FALSE
2,FALSE,FALSE,FALSE
3,FALSE,TRUE,TRUE
It should result in the following arrays:
Code:
aServer = Array("1", "3", "3")
aType = Array("x", "y", "z")
this would work out how many rows and columns there were based on how many column headings and row headings there were (So I wouldn't have to change the hardcoded VBA when the table grows, therefore making it easier to share the spreadsheet with others)
I'm looking to get a formula to counting unique values listed in a column depending on a condition also find the attached file for more details
[URL]