I have a small sheet that has a number in Column A and a larger number in Column B along with some data in Columns C and D. I wish to make a separate sheet that has rows numbered using the numerical range from Column A and Column B along with the data from Columns C & D
View 2 Replies
I have been perusing various Excel sites in hopes of finding a macro or function that can create columns based upon two dates input into specific cells. My ultimate goal is to use this macro or automated function so that when the user inputs the dates, the columns would automatically be created for them, rather than having them do it manually.
ideally, the 'start' and 'finish' dates are named cells and the macro could determine the difference between the two and automatically insert columns to the left of a ' Totals' column.
I have been trying to create a userform that will generate a numerical list starting in a given cell using a starting number, an ending number, and an increment that the user will imput into a textbox. I have a userform set up, but I do not know how to do any coding. I have very little experience with excel.
View 2 Replies View RelatedReference the attached excel audit example. Column F highlighted in Yellow is finding the difference between "Authorized Post Allow" D9 "Post Allowance" B9 . The Column location of the two Columns changes on each audit. This means I have to manually fix the formula in Column F every time. Is there a formula that would find the difference between these two columns based on their names "Post Allowance" and "Authorized Post Allow" preventing me from manully fixing the formula.
View 2 Replies View RelatedI know how to get the last row for columns, usedrange... etc but I don't know how to get the lastrow based on multiple column range
For example, find the last row between columns A:M but IGNORE N:O however we still want to check/get the last row for P:Z
I am trying to create a line graph that will incorporate multiple columns of data in one series of data. The reason I do not place all of the data in one column is because it could exceed the maximum amount of rows allowed in excel. Also I need the data split up for viewing purposes.
I can easily just graph one column but how do I combine all the columns into one line graph with the data being in separate columns. Basically all the columns will be my Y values and X values are just 1:n.
Example Below:
Column AColumn B Column C159261037114812
Now in the example all of the values are x values.
I have a userform which saves its info to a spreadsheet. what i want to do is to also open a new workbook, save the same info to the new workbook, copy and paste from existing spreadsheet will do instead of linking the userform to a different workbook (i can do all this) I then want to save the workbook in a specific folder(folderA) but named by the next number ie workbooks 1, 2, 3, 4, 5, 6, 7, 8 etc exist so the next workbook should be called 9 so I need some code to look for the names of existing workbooks in the folder and determine the next. Then I want a spreadsheet to at a pre-set interval(15 minutes) open each workbook , copy the line to the next blank row and move the workbook to a second folder(folderB). The folders A & B will only contain these workbooks.
View 2 Replies View RelatedIn a column, I have a repeating series of 24 different strings corresponding to the hours in a day. The strings are formatted as: 0, 10000, 20000, ... , 24000. Some days do not contain a complete series. What I would like to do is to complete each partial series by inserting a row with the proper hour string and zero's in adjacent cells.
View 6 Replies View RelatedIn have a worksheet with about 5,000 rows. In column there is a unique identifier for each row, however the unique identifier can take one of 12 different formats, namely:
L1314LAND00001 through to L1314LAND0XXXX
L1314CUST00001 through to L1314CUST0XXXX
L1314COMM00001 through to L1314COMM0XXXX
M1314LAND00001 through to M1314LAND0XXXX
M1314CUST00001 through to M1314CUST0XXXX
M1314COMM00001 through to M1314COMM0XXXX
N1314LAND00001 through to N1314LAND0XXXX
N1314CUST00001 through to N1314CUST0XXXX
N1314COMM00001 through to N1314COMM0XXXX
S1314LAND00001 through to S1314LAND0XXXX
S1314CUST00001 through to S1314CUST0XXXX
S1314COMM00001 through to S1314COMM0XXXX
These unique identifiers won't always appear in sequentiual order. In Cells B2 to M2, I want to be able to determine what the next entry for each of the twelve variations will be, so for example, if the current highest value in column A for the 'S1314LAND' range S131400421, in cell K2, I would want it to display S1314LAND00422, and if the current highest value in column A for the 'M1314CUST' range M1314CUST0036, I would want cell C2 to display M1314CUST0037, so anyone at a glance can see the next highest unique reference for the relevant set, rather than having to go through the unique identifiers in column A manually to determine the next entry in the series.
I'm not really sure where to start, when there can be more than one aplhanumeric entries ending in the same number, e.g. S1314CUST00500 and M1314LAND00500.
I have a long list of numbers (1 to around 3000) in Column A and I need to print out only the numbers missing from that list. The numbers are sequential (except the ones missing) and there are no blank or missing cells, only missing numbers.
EXAMPLE:
1
2
4
5
7
.....
So I would only need to print the number 3 and 6. - it can be all in one cell, or in one column, it doesnt matter.
I would like to find the blanks within a column. Using the available cell values above and below compute a sort of linear function then, using that linear function fill in the blanks in between. So the values are linearly increasing or decreasing (whatever the case maybe) to the next available nonblank cell value. spreadsheet attached.
View 3 Replies View RelatedI need to create a formula (not a VBA code as it is a shared spreadsheet) to bring up the next number in a sequence. For example
1
2
4
5
The outcome would be 3 as it is the first number that has not been used.
I'm using the following code to set the colours of the slices of all the pie charts in my spreadsheet based on each slice's legend's label: ....
View 9 Replies View RelatedIf my spreadsheet has columns A-H.
A is Date
B is Room Number
C is Location
D,E,F all have numbers results in them.
G is the average of D,E,F for each person
H is group average.
If I want to define a group average in column H where it calculates the average of a group that attended the same location, date and room, for each group that attended would that be possible?
So For Example (Columns DEF left out for clarity)
Date
Rm
Location
G Inidivual Average
[Code]....
So because room number changed it calculated as a different group. Is this possible?
I need to create a report based on three columns. I have explained in detail about my requirement in the attached sample workbook.
I hope someone will help me to solve the problem through VBA because the items & expenses may vary at times. Hence, I feel a Macro will be the best solution.
[URL] ......
In that file, you can see there is a list of somewhat similar data across 5 columns. Some of the entries are duplicates, some are different by only one character. What I want to be able to do is generate a list of all the unique rows of data, taking into account the data from ALL 5 columns. The list can be on another sheet or simply just further down on the same sheet, that doesn't matter too much.
I could do it if it were just one column of data, but struggling trying to do it with several columns.
I have spent a good amount of time trying to create the below using botched IF functions, SUMIFS, SUMIF formulas etc.
Basically I have a sheet (called Sheet1) with the following columns:
A - Date (by individual day DD/MM/YYYY)
B - A catagory (so using the old example, Apples, Orange, Pears)
C - Price.
I would like to be able to create a formula, whereby I can sum the Total sales for each catagory across each month onto a summary sheet (sheet2).
I would use pivot tables, however am not sure how i can make all the require data appear permanently in the best way, especially if there is a one liner that can be done more easily. So far I have managed to create a sum dependent on month, but have been unable to encorporate the catagory.
I want to ask that I have got a workbook with different number series i want user form where i can enter its start number and end number and then it finds and delete shift cells up said series number i have entered in user form please see mentioned below example.
Series
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
and i want to delete 1 to 5 numbers delete to shift cells up.
create a macro that will find and calculate based on the details of 4 columns on the same spreadsheet.
Basically, here is what I need to do:
1. Need to calculate how many QTY IN (Column F) and PALLETS IN (Column I) of a specific PART# (Column A) and LOT CODE (Column B) For example if enter a PART # and LOT CODE, it will calculate how many QTY IN and PALLET IN of that PART # and LOT CODE.
2. Need to do the same for QTY OUT (Column G) and PALLETS OUT (Column J) also.
3. Output should contain the PALLET # , QTY IN and PALLETS IN based on the PART # and LOT CODE search. I just want to simplify the search function in this spreadsheet because it is very cumbersome if you need to find how many items left for that PART # and LOT CODE.
Attached is the spreadsheet..
I have a simple list of all purchases made. ie)
Name.......Purchase date
John........01.01.07
Susan......06.08.07
John........07.07.07
John........01.05.07
I'd like to roll up the sames to create one customer row, but so I see the varience between purchase times. ie)
Name.......Ist Pur date....2nd pur date.....3rd pur date....time from pur 1 to 2
John........01.01.07.........01.05.07..........07.07.07........120 days
Susan......06.08.07...................................................(not sure to include this)
Is this possible in excel?
I need to get a macro that will auto plot number of series and name them according to a value. The attached file has an example, I have highlighted column H (x-axis) & K (y-axis) and the name of the series should be from column B. Each series has to end at the begining of the other series. The graph there is what I want to get. This is only example, I have over 20 series to be plotted!
View 5 Replies View Relatedhow to create a new XYScatter series based on dynamic conditions. For example, I have a very large, unsorted table with three columns: TGT, X, Y, Z.
TGT is an integer from 1-99 and is being filtered dynamically with an Advanced Filter. Anywhere from 0 to 10 conditions are being filtered by the Advanced Filter.
I would like to create a new series for each unique instance of TGT, with or without filtering.
I have 3 fields:
Person, FinancialYear, AmountUSD
Pete, FY12, 8000
Pete, FY13, 7000
Jeff, FY12, 5000
Jeff, FY13, 5500
Sam, FY12, 4000
Sam, FY13, 3500
How to create a pivot table that has a calculated field for the difference in AmountUSD between FY12 and FY13 (for each person).
I've created my dynamic named ranges using the OFFSET function, ex.
="OFFSET(SAMPLE!$D$4,1,0,COUNTA(SAMPLE!$D:$D)-1,1)"
I'm now trying to get my chart to use that range. I read at [URL] .... that I need to make sure my series reference is a fully qualified reference. So I've entered that series reference using the name of the workbook followed by the range name (=2012-PIRS.xlsx!SAM_CLAR2DEL). I have two copies of my workbook (one as xlsm with VBA project, and one with xlsx without VBA) and I can't get Excel to accept the series value in either workbook.
ERROR MSG:
The formula you typed contains an error. Try one of the following:
- Make sure you've included all parentheses and required arguments.
- To use a function, click Insert Function on the Formulas tab (in the Function Library group).
- If you include a reference to another sheet or workbook, verify that the reference is correct.
- If you are not trying to enter a formula, avoid.........
I am looking to create a number of series on a chart (xyscattersmooth) from a datatable. Below is my data Table....
View 9 Replies View RelatedI need to create a long series of checkboxes in which each box is linked to the cell directly to the right of it. When I try to copy and past the checkbox, the link doesn't update. Which means I have to go in manually and change every checkbox link.
View 3 Replies View RelatedI require calculating difference between two columns. (Actual and Estimated).
Difference between these two numbers are calculated in third column, but it is reset every time actual number is non-zero.
Scenario:-
-Estimated Numbers are always followed by an actual number.
-After actual number is obtained, estimated numbers till that date will be added and deducted from actual number.
-After this, again estimated number will be added till next actual number is obtained for deduction.
If estimated number is not followed by actual number, then no need for difference calculation.
I get delivered flat files of data sometimes with 10 columns of data, sometimes with 20 or more. It could be alpha or numeric
I want to programatically subtotal all the columns that hold numeric data, ignoring dates. I know that the subtotal will always be grouped by the first column, but then the numeric columns could be in any of the remaining 19 or so columns.
I'm just trying to calculate the difference between dates in two columns and applying conditional formatting based on if it is a positive or negative number. But when there is a null value in either of the date columns I don't want anything returned.
So far I'm either getting a 0 or a FALSE.
Column A = Date 1 Column B = Date 2 Column C = Formula
=IF(NOT(ISBLANK(B2)), B2-A2, )
I am trying to determine the average reduction amount of appraised property values. I have two columns in my spreadsheet. Column H has appraised value of property. Column I has the accepted value of the property. Sometimes the accepted value is the same as the appraised value, sometimes it is reduced, and sometimes it is rejected.
I want to be able to find the average reduction amount when the accepted value is less than the appraised value and is not rejected.
Column H always shows a numerical value (i.e. $250,000), but Column I may have a numerical value or show "rejected".
