i 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 RepliesI have a worksheet "parent child" with product data, cells F4 and BK4, pull pertinent data from cells T2 and M2 respectively on a different sheet "products".
A5:A196, D5:D196, F5:F196 is dependent on cell F4 and BK5:BK196 is dependent on BK4.
Once we get to row 197, the cycle starts over again. F197 and BK197 needs to equal products!T3 and products!M3. Then rows 198 through 389 will be dependent on row 197.
I basically need this to repeat perpetually for about 1000 different products on the products sheet, thus the ability to create approximately 193,000 rows.
I am not sure what it will take to do this, i am fine if I have to drag and copy all rows, which I have tried to create and failed at, I end up with products! T196, instead of T4.
I 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 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 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 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 Replies View RelatedI need to take a list of about 1,500 rows of data (single column) and convert it into columns of 9 entries. I know that I can select nine rows and then paste using transpose... But any way of "automating" the process?
So
1
2
3
4
5
6
7
8
9
ETC
ETC
becomes 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 etc....
my problem involves displaying rows that meet certain ‘criteria’, on a separate worksheet, in real time. The criteria will be determined by a selection made from a dropdown list in the row in question.
ILLUSTRATION: Let us say (for example) that I am dealing with a worksheet for MEN’S DATA. Each man’s data will be on a separate row. On each row, one of the cells has a dropdown list having 2 options, SHORT and TALL. I have a separate report worksheet for TALL men. I mean, I want every row for which I select the TALL option, to be copied to my TALL report worksheet. I want the selection of the TALL option to be a kind of trigger that promptly copies the row to the TALL worksheet, once I make the selection from the dropdown list.
i have one main sheet, and two resource sheets. In main sheet i have resource and project Matrix and in top of the cell i can select the week number. in other sheets, i have week and project matrix for resource. Now what i want is if I enter some details in main sheet, those values should be automatically posted to Resource sheets.
View 2 Replies View RelatedI have 41 Sheet in my Workbook.
Sheet1 Contains all the Data
Now For every Staff I have Four Individual Sheet of "Seg"
What I want is to Copy Data From Sheet1 to each sheet of the Staff according to there Respective Details.
1st record to be copied automatically to Sheet7 (CAS B) in Bought Side Column,
2nd record to be copied automatically to Sheet6 (CAS A) in Sold Side Column
with name of toys in both the sheet.
I am trying to create a single input sheet that automatically feeds data into several other worksheets within the same workbook. Some of the sheets use the same data, some are completely independent of each other. The point of having this is so that users can go in, fill in the input sheet, and the other sheets will automatically be filled with data from the input sheet.
View 1 Replies View Relatedi have on sheet one a cell that requires an order number ( e.g something like j2345a ) what i would like to do on sheet 2 cell c10 i would like to extract the letter after the 4 figures so that what i would see in that cell is j2345.
what i should add is that the letter after the four numbers isnt always an "a" so it could be any letter that needs to be removed
i also need cell c10 on sheet 2 to remain empty if there is nothing entered in cell e8 of the first sheet
I'm trying to create financial statements on a sheet with quarterly and annual data. Is it possible to create like a button to change inputs on a cell
Income Statement for Abercrombie Fitch Company Class A ANF from Morningstar.com (2).jpgIncome Statement for Abercrombie Fitch Company Class A ANF from Morningstar.com2 (2).jpg
whats the best way to insert a button like in the jpg example
a code that would automatically lock a cell every time I input a value to it?
View 2 Replies View Relatedwith the attached spreadsheet?
I want to populate "selections" sheet with data from the previous sheet (it will be the date) depending on the value of a cell in column G. You will see I have already got the data from 27th April into my selections sheet but wondered if it can be done by a formula to save me copy and paste time?
I want to do a loop where you can copy say A3 worksheet 1 then add another sheet naming the work sheet "A3" then copying A3 worksheet 1 to A1 "A3". After that looping to A4 to a new work sheet naming the work sheet "A4"copying the value to A1 "A4", etc...
Is there a simply way of doing this loop? I can probably fit my other coding into the structure.
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] ........
I have a couple of spreadsheets that requires me to copy data from Sheet 1 that matches certain criteria to Sheet 2.
I have tried using posted VB codes that have been written for other people but they dont seem to work (this is probably to do with me not actually knowing what I am doing, as I am not sure what I should be replacing and what I need to be deleting etc out of the pre-written code )
I need to be able to search on column e in Sheet 1 for anything that has just h written in the cell and then copy all the rows that match into Sheet 2. I also would like to ensure that when it is copied it doesnt go in row 2 but maybe 4 or 5 as I have quite allot of headings.
I have a workbook, "Assessment District" with 3 worksheets. In worksheet "Original," I have 252 rows with columns A-V. I want to copy two coloumns "C" (range 6-252) and "N" (range 6-252) from the worksheet, "Original" into a new worksheet titled "Send" in columns A & B. I only want to copy the two columns "C" and "N" for each row, if column Q does not contain "beach town" or "freeway."
For example, row 6 contains beach town, so I wouldn't copy C6 and N6 onto "Send," but row 100 doesn't contain beach town or freeway, so I would copy C100 and N100 into "Send."
This is a workbook that would be updated annually.
I'm not skilled in VBA at all, but want to impress my supervisor where I'm interning.
what I need, but can't seem to string anything together for the final product. What I want to do is search one sheet, in column BQ, for the word Complete. If a cell has that word in it, I want to copy that row, paste it into another sheet, and than go back and delete that row, and continue the search until all rows have been searched. Is there a simple way to do this?
I was thinking of an auto filter, but I'm not sure it'd work how I want it based off of what I've read.
i am trying to copy rows from several worksheets into one master worksheet. if column F of any row has a value greater than zero then the rows should be copied to the master worksheet with all data.
View 5 Replies View RelatedI have one excel 2007 workbook with three sheets in it.
Sheet 1 is a master sheet with 1858 rows and 45 columns of data.
Sheet 2 has the qualifier data, that is, one column with 255 unique 6-digit integers each in its own cell.
Sheet 3 is a result or output sheet that is currently empty.
Here is what needs to be done.
Compare all of the 255 unique 6-digit integers from Sheet 2 with the integers in column number 15 (or Column O) of Sheet 1.
If there is a match then copy that entire row from Sheet 1 into Sheet 3.
I'm trying to create a macro that examines a sheet of data, and if the data in a certain column (column P) contains either A,B, or C then it would copy the entire row over to another sheet. If it contained a blank or D,E, F, etc,etc then it would just ignore that and move to the next row.
I have no idea where to start, i've searched google and here on Mr.Excel.
I have an excel sheet which looks similar to below and with some more columns.
HTML Code:Â
AreaBranchEmpolyeesProductsShelfs
Area1Area1 Branch110Product 110
Area1Area1 Branch25Product 16
Area1Area1 Branch38Product 28
[Code] ......
I want to have data in other sheet With respect to Product details. Where It gives me the count of Product 1 and so on.
I am able to get the count using countif function and it displays like below.
HTML Code:Â
Product Count
Product 16
Product 28
Product 34
Product 49
Product 510
Product 68
Product 78
Product 89
Product 97
Product 108
What I want is when I select "Product 1" It should display some selected columns data from sheet where complete data is available.
HTML Code:Â
Branch Empolyees Other
Area1 Branch1 10 abc
Area1 Branch2 5 xyz
Area1 Branch5 7 pqr
Area2 Branch5 5 wxy
Which function we can use. I tried Vlookup but was not successfull.
I have a Workbook that I use to log invoices and credit notes and it is password protected so that the cells that contain formulas are locked and therefore not selectable.
What I wanted to know is. Is it possible to lock other cells depending on what values are in certain cells?
Let me explain. I have Columns G & H which contain the heading 'Credit Note Number' & 'Credit Note Amount'. Now in columns I & J I have the heading 'Invoice Number' & 'Invoice Amount' respectively.
What I have done is to make a condional formatting so that If I put a value in cells in column G it will change the colour of the adjacent cells in columns I & J, likewise If I fill in cells in column I the conditional formatting will change the colour of adjacent cells in coulumn G & H. This helps me to see whether a row contains a credit note or an invoice.
What I would ideally like is for a Macro that would automatically locks cells G & H in a particular row that I am currently working on if I enter data into the cell in column I. Likewise if I am entering data in column G in the row I am currently working on for a credit note, then lock columns I & J in the row I am currently in.
Any Ideas how this can be done? Also can locking and unlocking of cells be done in VBA while the sheet is protected or would the macro need to temporarily unprotect sheet, lock the cells then protect sheet again?
PS: I have uploaded a Jpeg of what my conditional formatting does to try and illustrate what it is I need.
I have a macro that adds a row with predefined formulas and formating. The macro is launched by clicking on a button. However, I would like to make it possible to add more than one row at a time. My plan to do this was to use the number of selected row as input to the current macro. If the user selects row 1,2,3 and 4 (or 15, 16, 17 and 18, and so on) four new rows should be added. I would just add;
View 3 Replies View RelatedI am using the below macro to copy the rows (Starting from Row 2 excluding header row) to another sheet (Starting from Row 2 excluding the header row). I have a huge number of rows which i need to copy to the new sheet (crossing 1.5 Lakh records) which is probably causing "Out of Memory Error: 7" to occur when i execute the below code.
[Code].....
My knowledge of Excel 2007 is minimal, from formatting individual cells, to creating column totals. I've used it for years in my household budgets. I have an attendance form which is about 3 pages long; currently, it's separated at page breaks, with column totals at the bottom of each 'page'. This is easy because the Row #s are infinite, right? Lately there have been regular adjustments, and I believe the project might be less cumbersome if it were spread out on separate sheets, but how to bring the rows of column totals to cumulative totals on the last sheet.
View 6 Replies View Related