Build Range From Column And Row Values
Apr 24, 2008
I'm trying to copy a pivot table cell onto another worksheet that has a table with Month-Dates across the top in columns and left-most column has several cells, each with a named range. I get an application error with:
Sheets(shtSrc).rngSrc.Address.Copy _
Sheets(shtDest).Range(Sheets(shtDest).Range(rngDest).Row, Dt.Column)
shtSrc and shtDest are Strings passed in to Sub for worksheet name.
rngSrc is a Range passed in to Sub.
rngDest is the Named Range of the destination cell.
All of the values get passed in; I'm just not using the range properly I think.
I'm not married to what I've got so far. Basically, as long as the pivot data gets copied at the intersection of the correct Date (Column) and Row I'll be happy.
View 9 Replies
ADVERTISEMENT
Feb 10, 2009
In column A, I have the following values:
build
chil rat
datcen
mang
nextg
ost rat
omp
sco rat
In column B, I need the formula so it shows values that if column A = "build", "nextg" or "datcen", then the value in column B is "nextg" and if column A = "chil rat", "ost rat", "sco rat", then the value in column B is "mig". For everything else, it should be blank.
View 5 Replies
View Related
Aug 1, 2006
I'm trying to write a macro that will build a tree from 3 sets of lists and I'm really stuck!.
The thing is:
1. I have 1 worksheet with 2 sheets. First sheet "Lists" contains in every column a list of data.
2. Range("B:C") contains list od SERVICES1 - every value in list has its corresponding ID_SERVICES.
3. Range("D:F") contains list od SERVICES2 - every value in list has its corresponding ID_SERVICES2 and a parent id ID_SERVICES.
4.Range("H:I") contains list od SERVICES3 - every value in list has its corresponding ID_SERVICES3 and a parent id ID_SERVICES2.
5. Second sheet "Project" in Range("A:C") contain example of tree like: ...
View 3 Replies
View Related
Jan 14, 2013
I have some raw data, and in that data are 2 columns. They are Manager and Role. I am trying to create 2 lists, that correspond to their role. I can create a dynamic named range from the column no problem using this formula:
Formula:
[Code] .....
Now, what I need to be able to do is, add a criteria option to the formula. So build the list off of column B, but only if their role (in column C) is Perm, or Temp.
See the attached spreadsheet : dynamic range criteria.xlsx‎
View 8 Replies
View Related
Jun 3, 2009
Hi, I'm very new to writing Excel Macro's and wanted to know if I could do the following. Conceptually, I understand what I need done and think it should be fairly straightforward.
There's 2 main events in this loop (I hope that's the correct terminology):
Input 1) User defines the beginning cell to start the loop. In this case, A2.
Input 2) User defines the range of columns/rows to display. The formula for rows that I've thought of is 4r. So if a user wants 20 rows below cells A2, they simply input 5 for r. The number of columns is a constant 5. So if r=5, then I'd want the range to be A2:E22......
View 13 Replies
View Related
Mar 25, 2009
I was just recently forced to create my first UDF and after how well it worked I now am very interested in learning more. I am trying to create a function to sort a range by the values in a specific column and return the range. I know this should be really simple but for some reason my code dies whenever it gets to my inner-most loop. I need to use this in a larger function but for now this is my only question. I did find that Excel 2007 has built in Functions for this but my company still uses 2003.
My
Public Function SortRange(rngToSort As Range, valCol As Integer)
Dim Swapper As Variant
Dim i As Integer, _
j As Integer, _
k As Integer
For i = 1 To rngToSort.Rows.Count
For j = 1 To rngToSort.Rows.Count - i
If rngToSort(j + 1, valCol) < rngToSort(j, valCol) Then
For k = 1 To rngToSort.Columns.Count
Swapper = rngToSort(j, k)
rngToSort(j, k) = rngToSort(j + 1, k)
rngToSort(j + 1, k) = Swapper
Next k
End If
Next j
Next i
SortRange = rngToSort
End Function
View 9 Replies
View Related
Sep 30, 2008
I have a column that I want to check if a column contains any instances of particular values. If any are present I want it to return a 1, if not then return a zero.
View 11 Replies
View Related
Oct 5, 2013
First I have a excel with a selection of customers:
Column A = customer id
Column B = customer name
...
this excel contains about 500 premium customers
Secondly, on an external server I have a database with the details (adress, phonenumber, ...) of about 100.000 customers
Now I would like to display the details from the database into the existing excel. For that I was about to write a sql-query to copy the data I need to have into the excel (sheet2) to access it from sheet 1 with vlookup (based on the customer id).
But the large amount of data makes excel to work very slowly and it is impossible to keep on working like that. So now I was looking for a way to create a selective sql-statement.
Something like this:
select details from db
where customerid in (all the customer id's mentioned in sheet 1)
without writing all customer id's manually
View 2 Replies
View Related
Mar 21, 2014
I built a Microsoft Access database for my church to track member information. I was able to import a lot of the needed information to create the records for each individual and household easily. What I can't do is import their offering donations until it is "Normalized." Which means that I need each row to be 1 record with the fields listed as the column headings. My church has a spreadsheet with several sheets one of which is named Input. I've attached the desensitized file for you to view/play with.
What I need to do is loop through the input sheet and find all the values in range B:4 to GN:387 that are greater than zero or not null (I believe they entered the default as zero so their other calculations would work). Then I need those values to be transferred to the column labeled Offering Amount on the ImporttoAccess sheet. Somehow I also need to define three variables associated with the cell that is contains a value greater than zero.
1. I need the row heading value for the envelope number (Column A of input sheet) which intersects with the amount
2. I need the column heading value (3 field merged) for the date (Row 1 of input sheet) which intersects with the amount
3. I need the column heading value (labeled: GenFund, WLA, or Missions) for the designation of the offering (Row 2 of input sheet) which intersects with the amount
Each of these I need to attach to the value offering amount and insert into their corresponding column fields for that record. I have entered 2 example rows of data into the ImporttoAccess sheet for you to see what I am after. I am sure it's possible since the data is there, But how to go about it.
View 4 Replies
View Related
Jan 23, 2007
I m trying to make a button to add values to another sheet in my xls. Ive done that... now i try to autofill the percentages from left and above one row....
View 9 Replies
View Related
Sep 9, 2013
I have this code working fine in excel 2007 but I need to get it working in excel 2003.
=0.02*(_xlfn.SUMIFS($D$2:$D$98,$C$2:$C$98,">="&I5,$C$2:$C$98,"<="&I4))
I have attached a test document to show what I'd like to do. It is basically adding values if the column next to it is in a certain range.
counting columns in Excel 2003.xls
View 3 Replies
View Related
Apr 20, 2012
I have a column of values I need to multiply by a random number (percentage) within a range of numbers (15%-22%).
How can this be done?
View 1 Replies
View Related
Sep 18, 2012
I have following code (see below) which finds the duplicates within columns, but I require copying and replacing duplicate values within different row cells, as I am currently finding this task hard to implement.
Input Data example:
Name
Short_code
Lng_code
[Code]....
View 9 Replies
View Related
Dec 30, 2007
I have a worksheet that has hourly data. So Column C of this worksheet looks like
1
1
1
1
2
2
2
3
3
3
3
3
3
What I want to do is, given an hour h, automatically create a named range that contains all the rows such that the value of Column C is equal to h.
View 9 Replies
View Related
Mar 24, 2008
I have another little VBA macro problem that appears to be beyond my coding knowledge.
I have attached a small excel spreadsheet, with a macro recorded (CTRL-P) of what I'm trying to accomplish. Basically, If the data in column A matches a single entry in column F, I need to copy and insert the row (columns F-L) that matches.
View 4 Replies
View Related
Jan 26, 2012
In A1 of Sheet1!, I need a formula that lists and sorts all the 'unique' values from range
ECWP!$O$6:$Q$1505
View 3 Replies
View Related
Jan 30, 2014
I'm trying to create a macro that can lookup values down a list, find that value within another list and copy the adjacent cells.
The values to look for will always be on the same column and the values to look into will always be in the same range or columns but not always in the same row.
For example.
AI:AI contains a list of ID's which will be manually input every day so they might have a different order.
C:AF has all the data to look into. C always being a list of ID values and I want for every ID found on AI:AI to be search for on C:C: and then once a match is found, I need specific adjancent cell values to be copied next to the ID found on AI.
The values that need to be returned are found on columns B,D,F,G,H,I,J,P,AF and they will always display on those columns.
I've been doing this based on vlookup and INDEX/MATCh formulas but I've come across some limitations for something I want to develop further so I'm trying to do this based on VBA.
View 5 Replies
View Related
Jan 8, 2013
I have attached the file, Problem.xlsx
How I will populate data of Weekday through the cells.
Is there a formula or function to populate data.
View 1 Replies
View Related
Sep 24, 2006
I need a cell formula that will (a) identify the highest N values in an above specified column range, (b) color the interior of those N cells (I suspect that this is not possible), and most importantly (c) return the average value of N corresponding cells, where the corresponding cells are located on the same rows as the identified N high value cells but in a specified column to the left (not necessarily adjacent)
Does anybody know what this formula would look like?
Example:
-----------------------
...| A | B | C | D | E | F |
-------------------------
1 |....| * |.........| 7 |...
-------------------------
2 |....................| 2 |...
-------------------------
3 |....| * |.........| 6 |...
-------------------------
4 |....................| 1 |...
-------------------------
5 |....| * |.........| 5 |...
-------------------------
6 |....................| ? |...
-------------------------
? = average of B1,B3,B5 where (N = 3) and (specified column to the left = B)
View 4 Replies
View Related
Jul 3, 2007
Sheet1 has following two columns
----A----------B
01-07-2007---10
01-07-2007---20
02-07-2007---30
02-07-2007---40
02-07-2007---40
03-07-2007---60
03-07-2007---70
03-07-2007---70
03-07-2007---90
04-07-2007---100
04-07-2007---110
Sheet2 has following three cotrols
Textbox1=02-07-2007
Textbox2=03-07-2007
combobox=?
I want to Extract Unique values from column B between date range textbox1 and textbox2 and finally how to add these unique records in combox1
View 3 Replies
View Related
Mar 17, 2014
Please see attached sample worksheet. Column A will be generated by the user manually.
I'm looking for a way in VBA to have A1:D20 in Sheet2 copied and pasted in the "Bank Reconciliation" Sheet based on how many "Markets" there are in Column A. Then, once that's complete to have A22:D30 (the smaller box in Sheet2) copied and pasted directly below those results.
I have what the macro would hopefully generate to the right in "Bank Reconciliation" (B6:E54) as an example. So if there's a market in A1, copy and paste the box to B6. If there's a market in A2, copy and paste the box directly below the first (B26) etc. etc. until it's done, then paste the smaller box directly below whatever the macro generates.
Book2.xlsx‎
View 3 Replies
View Related
Oct 25, 2005
I have a single column of numerical values that may repeat many times within
the column.
I require a flexible Formula:
Use an Input Cell for the specified and changeable Percentage(s) %.
Column may be filtered – so only take into account Visible Filtered Cells.
The Formula will calculate and Return the numerical range of values that fall
between the specified percentage % (using the Input Cell) e.g.; 70%.
The Formula should Return two numeric values: a Start Value and an End Value
– NOT necessarily the minimum and maximum per se BUT the MIN and MAX of the
values that appear 70% of the time in the column; therefore, taking into
account Repeat / Duplicate values.
The calculated Results: the two numeric values will be returned to separate
cells on a new Sheet.
View 9 Replies
View Related
May 30, 2009
I have the following table:
Month / Year / Value
JAN20060,73
FEB20060,76
MAR20060,76
APR20060,76
MAY20060,73
JUN20060,69
JUL20060,69
AUG20060,73
SEP20060,76
OCT20060,75
NOV20060,74
DEC20060,72
JAN20070,67
FEB20070,68
MAR20070,71
APR20070,75
MAY20070,75
JUN20070,74
JUL20070,71
AUG20070,68
SEP20070,67
OCT20070,7
NOV20070,73
DEC20070,75
I want excel to multiple the values in a specific range.
For example:
I will give 2 dates: MAR 2006 and MAR 2007. Excel should multiple the value 0,76 and 0,71. It's simples because is 2 months. But I have a lot of months (2006 until 2009).
So I would give MAR 2006 and MAR 2009 and excel should multiple the values from MAR 2006, 2007, 2008 and 2009. All the values in the range, but only for March.
The month will never change between the first and the last date.
I think on using VLOOKUP. Then I have the first value and the last one. But how can I tell excel to multiple the range?
The ideal will be a VLOOKUP that returns the ADDRESS of the cell, not the value. But I didn't see this possibility.
View 11 Replies
View Related
Aug 22, 2013
[URL]
I have Column C and D filled with numeric temperature and humidity data. I need next 16 rows (8 more set) to be filled with randomly generated dummy data by the values exist in C and D. Same numbers but randomly distributed.
View 2 Replies
View Related
Oct 1, 2013
There should be a formula to extract the largest values and its corresponding values. For example, if column
Products Costs
A1 Pen 200
A2 Pencil 125
A3 Radio 670
A4 Apple 1500
A5 Xbox 222
A6 TV 100
The desired outcome
Products Cost
Apple 1500
Radio 670
Xbox 222
Pen 200
Pencil 125
TV 100
The second goal is extracting the top 3 largest values using a formula.
Sample file is attached for your convenience. Desired outcome is in GREEN.
View 6 Replies
View Related
Dec 5, 2013
Below the two columns represent Column A (date and time) and Column B is temperature.
How can I take the mean of all temperature values for May 1, then take the mean of all temperature values for May 2, etc.?
5/1/2013 0:0012.4
5/1/2013 1:0012.4
5/1/2013 2:0012.5
5/1/2013 3:0012.5
5/1/2013 4:0012.5
5/1/2013 5:0012.4
5/1/2013 6:0012.4
View 2 Replies
View Related
Dec 24, 2013
I have two columns in excel, col A and col B.What I would want to achieve is following:
Col A Col B
A 2
A 2
A 2
B 3
B 3
B 3
B 3
C 3
C 3
C 3
Now I want a total of values in column B pertaining to unique (or say 1st occurences) of values in Col A. So in this case the output should be 8 ,Achieved as follows(2 +3+3).
[URL]
View 5 Replies
View Related
Jun 11, 2014
Here is a table
Col1 Col2 Col3 Col4
8821015m14:44.0E__uMail
9266321m34:13.0E__uMail
92677165m23:43.0E__uMail
9309415m07:24.0E__uMail
[Code] .....
Here is some context
Each one of these rows represents an entry into a ticket in our incident management system. Col4 represents the method by which the incident was reported to us (Email, Voicemail, Walk In, Ask IT). A ticket can be open and closed with a single entry if the issue is easily solved or it can have many entries if the issue is complex and requires more troubleshooting or escalation to a higher tier of support. I need to determine the number of unique tickets submitted by each reporting avenue (Email, Voicemail, Walk In, Ask IT).
In this table the number of tickets that were:
submitted via Email is 14
submitted via Voicemail is 1
submitted via Walk In is 2
submitted via Ask IT is 1
Col1 Col2 Col3 Col4
8821015m14:44.0E__uMail = 1
9266321m34:13.0E__uMail = 2
92677165m23:43.0E__uMail = 3
9309415m07:24.0E__uMail = 4
[Code] ....
What I need is a formula that will produce this result.
View 5 Replies
View Related
Jan 8, 2009
I have a brand Master in below format in sheet 1
brand codebrand Description10a11b12c13d14e
and Product master in sheet 2
product codeProduct Description000roof paint001floor paint002exterior paint003marine paint004wood finishes
I need in sheet 3 for each brand
Brcode + Product CodeBrand Description and Product description10000a roof paint10001a floor paint10002a exterior paint10003a marine paint10004a wood finishes
View 9 Replies
View Related
May 17, 2014
how to build a formula that would allow my remaining UOM and it's associated cells to move up to the days remaining UOM once my time goes below, say 31 days.
View 3 Replies
View Related
