Formula Needed To Copy Same Set Of Data From Cells And INDEX Into A List
Nov 27, 2012
I have a list of data in columns Q R S and i need to in between each line insert the data from columns D E F underneath and then underneath that the data from G H I
so bascially i have a list at the moment like this
Q
R
S
[Code]....
There is 2 sheets in total , 1 i need to inc 1-1 draw as above and the other sheet to be exc the 1-1 draw . I have attached an example of what i am trying to achieve . can offer as i have had this on excelforum.com with over 150 views and no solution
View 2 Replies
ADVERTISEMENT
Jul 12, 2007
formula to calculate how many cells in a column contain data of any kind. The data might be a number, a word, or even a symbol, such as "):>:)" for example. The range will change from time to time. For example, one day it might be a2:a4956. A few days from now I might delete a few rows from the top, such as rows 2 through 26, so then the range would be a2:a4931. Then I might add rows at the bottom with data that I need to be counted. I dont want to have to change the formula evertime I add or delete rows with data. I do not want the formula to count a blank space created by the space bar as "data." One formula I used sorta worked, but if the cell appeared empty, but had been cleard out by pressing the space bar instead of the delete key, the formula counted it as a cell with data.
View 9 Replies
View Related
Mar 21, 2014
A
B
c
d
[Code]....
So the data I put in Is Cells A to D the reast are calculated for me. so for example E2 is looking down column D to see if the number matches the number in A2, If any of them do it will put the name in the corresponding cell in E2 (Or you ca use a different cell if you prefer) so in this case D3=A2 so B3 is the answer also in the cell next to it I need whatever number is the cell next to the answer so in this case C3
View 3 Replies
View Related
Jun 6, 2006
I am looking for a formula or something - that when a reference number is used - it popluates cells from a list. Attached is a sample spreadsheet - 2 worksheets are being used - 1 is Purchase List and the 2nd is Fax Commitment. When reference no is filled in on the Fax Commitment sheet and it = the same reference no as on the Purchase List - I need it to populate the appropriate fields (in this case I have colour coded)
View 3 Replies
View Related
Sep 13, 2008
way to take 18 different workbooks, each containing the same 5 worksheets, and create a TOC that reference the workbook name only.
For Example, I would like to have one workbook with the first worksheet as an index or a table of contents that lists the name of 18 workbooks. When I hover my cursor over the name of a particular workbook in the index or TOC, it will open that workbook where I can now view 5 worksheets of that workbook.
Each workbook will contain the same 5 worksheets.
I am using excel 2002
View 9 Replies
View Related
Mar 11, 2009
I have just inherited an Excel spreadhseet that displays job titles and what types of documents each one should be trained on. What I am trying to achieve is a formula, or something to that sort, that will look at each job individually and tell me only those documents that they need to be trained on and list it on a seperate sheet.
If you look at the attached document you will see that the first 2 columns contain a document number and description. Across the top you see each of our job descriptions. In the middle you see an X that indicates that person should be trained on that document.
So once more, I would like to take each Job Description (seperately) and list only the documents that have an "x" in their column on another sheet. I already have the sheets created, I just need to know if there is some kind of formula or macro that can do this for me.
View 4 Replies
View Related
Jun 28, 2013
I'm trying to have one cell be the constant place I enter data and then when I hit enter, I'd like the data I entered to be moved down to the cell below it. Then for every new number I enter in that first cell, it would auto shift the previous data down and add the new data to the cell below it.
Basically, I'd like to record data but I dont want to have to scroll to the bottom every time to enter new data since this list will be continuously updated. And I'd like the most recent data entered to be on top.
Then I want to take the top 100 cells in that column, and do a running average of them.
View 8 Replies
View Related
May 8, 2007
I have a multiple column spreadsheet (Call it- "Money") whereby I need the data identified based on a list of account numbers and return this data to a new sheet.
In "Money" I have:
IE; columns B, C respectively have cust #A100 & 20.00
columns E, F respectively have cust #B100 & 40.00
columns G,H respectively have cust #C100 & 60.00
Etc.
(above for illustration-there are 100 lines of data in these columns with varied account numbers and respective dollars)
So what I have now is a new sheet I have named "Control". I have listed all my account numbers like A100,B100 etc. in column A. These are the account numbers for ident purposes.
I need the data entered in "Money" identified by those columns B,E,G with respective amounts from C,F,H and based on the list I have in "Control" whereby in "Control" if A100 is listed in column A then the figure to be returned in column B is all the data bits found in all columns C,F,H from the entire sheet "Money". Tough to explain but ie below....
View 17 Replies
View Related
Dec 12, 2011
I have a code that paste my data into a new row.
At the same row I have a column with formulas, and I would like the macro to copy that formula into same column in next row.
Code:
LstRow = Bilag1.Range("C65536").End(xlUp).Row + 1
Bilag1.Cells(LstRow, "B") = Brreg.Range("B4").Value
Bilag1.Cells(LstRow, "A") = Brreg.Range("B5").Value
Bilag1.Cells(LstRow, "C") = Userform1.txtProjectNo.Value
This is what I have, and my formula is in column "D".
View 4 Replies
View Related
Jan 3, 2014
how i can select the most recent 10 cells from a list of data to be included in a formula. I have selected it manually, for example F122:F131. This is fine but i don't know how i can put it so that it will update to the 10 most recent results when i input another line of data. So when i put in more data it stays on the previously selected. I want it to move shift down to include the new data but only the 10 most recent.
View 5 Replies
View Related
Apr 27, 2014
I have a worksheet (Data) that lists when pupils are in for Nursery sessions during the week. If they are in they have a 3 (hours) by their name in the relevant columns.
In the AM worksheet I now need to pull through a "register" so under each daily heading I need to pull through everyone that has a 3 next to their name under Monday AM / Tuesday AM / Wednesday AM etc. from the Data sheet. However, I don't want it to copy any blank cells. I then need to do the same for the PM sheet.
View 2 Replies
View Related
Oct 12, 2008
I'm trying to make a spreadsheet for work so when im not there they can just enter the day for example sunday and what they have and then it will show them what they need to prep
View 4 Replies
View Related
Apr 9, 2014
I have a big list with names, money spent and date. I need somehow with a formula to do:
Take all the money spent on some specific day from a specific person an put it into a field.. and this for every date of the week and for everyname... Im gonna upload a photo.
Name
Money
Date
Daniel
5
01.03.2014
[Code] ........
I need the list of the left to produce me list of the right somehow, at least just the sum of everything spent everyday.
View 7 Replies
View Related
Jun 24, 2008
I am currently working on a data analysis project (data mining) and need to collect and later analyze statistics for the inputs which control a series of calculations. These statistics are shown in the Statistics 1, Statistics 2 and Statistics 3 cells in the workbook that I attached. The inputs are X,Y; all possible values for these inputs are listed in the N,O columns. Basically I need a macro which would take the values from these two columns and place them pair after pair into the controlling cells (K3, L3), then it would copy cells H2 through L3 (updated stats) to a new sheet after each copy operation - so that I will finally have a list of statistics for all of the input pairs.
View 3 Replies
View Related
Sep 23, 2009
I am having a little trouble with multiplying a few formulas. I am looking for a formula that will multiply the last three cells in a row that contain data and subtract 1. Below is an example of the type of data I am working with and the formula I am trying to use but is not working. The formula is for the cell highlighted in red. Every quarter the last three cells being referenced will change.....
View 9 Replies
View Related
Jun 24, 2014
I have the first sheet named "DPF" where I have a column "Date", "FIT", "Group" On my second Sheet named "Tracker" what I do want to do:
I select a date for example 18-07-2014 (Cell E3), if cell E4 I Have "Fit" and F4 "Group" In C6 I have E3-15, to get the date minus 15 days (03-07-2014) In C7 I have E3-12 (06-07-2014) and so on
What I want is, if the date of today is C6 or C7 or whatever, use the index match function to get the number corresponding to the date chosen (18-07-2014) for the FIT. But, once I get the figure, I want to keep it, because with the if function, once we are the 4th of July, the figure for C6 disappears...
Fri
18-Jul
FITGroup
Start
D-1503-Jul
D-1206-Jul
D-909-Jul
D-612-Jul
D-615-Jul
D-216-Jul
D-117-Jul
D18-Jul
So either a macro to copy the specific data into another sheet or a another way to keep it. Because the idea as the end is to do different scenario based on the result got per period..
View 2 Replies
View Related
Jan 9, 2014
Essentially, I would like a user to be able to select a PUB from the Data Validation drop down in row 2 of the PUB RATES sheet so that the corresponding information in the DATA sheet autofills.
Currently, this works only for the first column under each PUB when selected and this fills across all 4 columns (rather than the respective information for each column filling).
Also, the Data Validation dropdown includes blanks which I would like to exclude.
I have used a Range Name for the Data Validation of each PUB so that these can be drawn from a separate sheet as I don't want all the DATA content on the same sheet as the PUB RATES content.
View 4 Replies
View Related
Jan 14, 2009
In order to avoid embedding 12 if statements, I want to take an average of a selection of cells based on what month it is. So I have a "now" function, and then a month function referencing the "now" cell so that I get the current month as a number 1 through 12. I have a row of expenses (C14:N14) and I just want the average from January up to the current month. The reason I can't take an average of the entire selection is because the users don't want to have to enter a 0 everytime there's nothing, they'd like to leave it blank, so the sheet needs to recognize what month we're up to. And the average function in Excel only takes an average of the data that is present, not the entire range. For example, if the data in the first cell was 15, second cell was 15, and third cell was blank, the average function returns 15 and not 10. So what I need is, for example, if it's March, the average will calculate =average(C14:E14) and so on. Any ideas? Maybe it's a simple change of formula instead.
View 13 Replies
View Related
Jan 21, 2014
Basically I have in the first column, different products (lets call them a, b,c and d). in the second column is a counter which starts at 1 and continues counting down the column.
In the third column I need to identify if it is the last time the product has been run. i.e there are no more duplicates of product a further down the 1st column.
I have attached an example : example.xlsm‎
View 2 Replies
View Related
Feb 5, 2012
I'm in need of some VBA code to control the updating of information from 16 active workbooks to a master workbook and vise versa. I would like it on command as it slows down the use of the workbooks when they calculate after every change of data as they are being used. These workbooks are connected over a company intranet and are used 24/7.
The desire is that cells in the master workbook will read specific cells on each of the other workbooks and complete a "map display" of the data. The data is not in a column or row range side by side but scattered across the sheet to form a map when data is entered.
I've tried using code to open and close the workbook with on time events but that hasn't worked.
View 5 Replies
View Related
Sep 21, 2012
I have a phone list set up as LastName, FirstName PhNum all in a single column. The problem is, some last names have a space, some phone numbers are in the xxx-xxx-xxxx format while others are just an extension like xxxxx.
Example:
Smith, John 519-123-4567
Van Smit, Joe 12345
What can I use in a formula to extract the numbers so that I can end up with 2 columns, one with names, the other with numbers.
View 6 Replies
View Related
Dec 14, 2012
In my spreadsheet, I have data for each sale in each row. Column C is the buy price, and column D is the sell price. I need column C to be subtracted from column D to get the profit into column E so that I can AutoSUM any cells in column E for total profit over any time period. The problem I'm having is that in columns C and D, I have either a dollar sign in front, or the letter m following without a space (ie $45.00 or 150m). Many cells in column C also contain another word following the buy price, and many cells in column D have a date following the sell price (ie $32.00 12/11 or 42m buyout).
Examples of how my spreadsheet looks:
A1: 12/13/12 B1: Item #1 C1: 100m D1: 152m
A2: 12/14/12 B2: Item #2 C2: $20.00 D2: $35.00
A3: 12/15/12 B3: Item #3 C3: 90m bid D3: 120m 12/16
So looking at my example above, I would need cell E1 to automatically show "52m", cell E2 "$15.00", and cell E3 "30m"
How I can get a formula for all of column E that will give me my profit for both $ and "m" for each item (row). I have tried the SUM function but just keep getting the #VALUE! error, it does not ignore the text like many people say it should.
View 6 Replies
View Related
Jul 7, 2014
I am working on a spreadsheet where I need to have data from different sheets added but based on the instructor whose data I want to see in the summary sheet. I want to be able to type the name of the instructor in a cell and have all of the data appear below. I believe that an index match formula would work, but I do not know how to have the data from the different sheets added nested in the formula.
View 2 Replies
View Related
Jan 9, 2014
I am an architect and recently I was required to do some extensive calculation relating to water requirements and toilet requirements for a building that i was designing. i created a excel file, with a basic vlookup function to put certain values in the cells that are pre-defined for a particular building typology (mostly created by governmental bodies as guidelines). for example, when i choose a particular type in column E, column F and G fills up automatically. now this table also gives me occupant load based on what is the area i enter in column D.
The problem is that I want to automate the calculation of toilet requirements. Now, each typology will have a different formula to calculate WC/Urinal/washbasin.
1. there will be 17 different formula - one for each typology (as shown in sheet 5)
2. Column j will calculate what is the number of WC required based on 2 criteria - first id what is the typology and then looking what is the number of occupant.
The problem here is for example, if in E9, selected typology as "offices", the formula of offices typology will be used in J9 and occupant value from H9 will be used but if i have E18 also selected as offices typology, the same formula will use occupant value mentioned in H18 instead of using H9.
Hence, excel will first have to look what is the typology selected. based on that, it will load formula - out of that 17 different formula - specific to that typology and finally it will refer to corresponding row for occupant load and will use it in the formula to get the result.
View 3 Replies
View Related
Nov 10, 2009
INDEX($D$19:$D$22,MATCH((LARGE($E$19:$E$22,1)),$E$19:$E$22,0))
I use this formula to give me a "name" associated with the largest number in a list. But I can't get it to work when I use it on a win/loss number such as
"7-1". It won't read it.
View 5 Replies
View Related
May 10, 2013
I need a formula that effectively reformats data.
Original data:
Country
Name
2010
2011
2012
A
John
5
6
7
B
James
3
4
5
Into this format:
Country
Name
Year
Value
A
John
2010
5
[Code] ...........
View 5 Replies
View Related
Jun 11, 2014
I was working on a chart to calculate my Amazon seller rating to see how many more perfect order I need to hit my next target grade. Here is how the calculation works.
Order with no problem gets 100 points per order
Minor problem gets 0 point
Moderate problem minus 100 points
Severe problem minus 500 points
Grade are evaluate by:
<84.5 Fair
<96.5 Good
<98.5 Very good
Above is Excellent
Total points / total orders = final grade (or percentage)
As of now i got : 53800 (points) / 624 (orders) = 86.22(%)
Now that I know what grade I've got so far and I also want to know how many more perfect orders I need to achieve my next targets or in case some bad orders come by and how far they pull me down.
Attached is my work sheet so far and I'm just missing the calculation for the orange shaded part.
Amazon seller rating calculator.xlsx‎
View 3 Replies
View Related
Jul 3, 2014
I need identifying correct title and corresponding company name. For this I need a UDF which looks for first occurrence of title having event or marketing or meeting word in data and if found the remove other titles and company names already separated with "/".
Sample Data
Producer, Target BTC@Periscope/Events Manager, Recognition Events@Minneapolis Park & Recreation Board/Event Producer@Events by JLS
Required Result
Events Manager, Recognition Events@Minneapolis Park & Recreation Board
Sample Data 2
Sales Associate@Teavana/Event Assistant@City of Saint Paul
Required Result 2
Event Assistant@City of Saint Paul
Sample Data 3
Sales and Catering Manager@Bunker Hills Golf Course at Town & Country Caterers/Marketing Coordinator@Town & Country Caterers
Required Result 3
Marketing Coordinator@Town & Country Caterers
View 3 Replies
View Related
Apr 7, 2014
We have our system set up with 2 folders one with blank forms and the other with these forms filled out and completed (records), is their a trick to removing the data from a completed form fast with out removing tables and text used to make the forms questions? If I could do this it would save time from opening in the blank forms folder then saving in the records folder.
View 4 Replies
View Related
Oct 26, 2009
I have a spreadsheet with multiple columns. (Sheet1) The heading row is my customer name. (Sheet1) My column are the dates. On another spreadsheet, I would like to find the customer in Sheet2 in Sheet1, then do an index to find the relevant data I need, returned in Sheet2.
Sheet2: find customer "ABC" in Sheet1, if found, then find the date that also matches Sheet1 and Sheet2, and return data "apple" .
sheet1
DATE ABC DEF
2007-04-01 £55.60 £61.13
2007-04-02 £56.33 £60.27
2007-04-03 £52.14 £62.58
2007-04-04 £51.69 £61.28
sheet2
CUSTOMER DATE RATE
DEF 2007-04-04 This should be £61.28
ABC 2007-04-02 This should be £56.33
View 3 Replies
View Related
