I have one spreadsheet in which data was inserted vertically, and in order to move it to SQL, i would like to first mirror it horizontally in a 2nd spreadsheet (the reason i want to mirror it and not just copy the data is that the information in the first sheet changes dynamically).
So i figured out the basic function - (=sheet1!B0), but doing this on more than a 400 cells could be a problem. So my question is, how can i make, and lets take an example, cells A1 through I1 (in spreadsheet2) mirror A1 through A9 (in spreadsheet1), without editing functions cell by cell?
Im working on a financial forecast in ecxel and I'm trying to drag a formula from left to right, while referencing horizontal cells from a different tab:
Specifically, I have created a "loan schedule" which shows 12 monthly interest payments starting in cell B46 and ending in cell B57.
In a separate tab labelled "Balance Sheet", I would like to show the first interest payment ('loan schedule'!B46) in cell C28. Next, I would like to select the cell and drag it to the right so that the remaining 11 interest payments will be transferred into D29, E29, F29 etc. Unfortunately,the spreadsheet doesn't reference to the vertically listed interest payments from the loan schedule but instead references the cell to the right of the interest payment. Using "$" seems to be of no support.
In column CT between rows 11:210 is the vertical data that includes duplicates. I am looking to create a formula that can lookup each value between CT11:CT210 and return only the unique values horizontally starting in cell CW9 and onwards.
Example: CT11:CT14 looks like 5x20 6x4 5x20 5x8 and so on and so forth..
I would then like this formula to return the above data (which goes all the way to CT210) in this format starting in cell CW9 - 5x20 then CX9 - 6x4 and finally CY9 - 5x8.
I have a list in column L2:L352. I would like to be able to start at AD2 and drag horizontally so that AD2 = L2, AE2 = <BLANK>, AF2 = <BLANK>, AG2 = L3, AH2 = <BLANK>, AI2 = <BLANK>, AJ2 = L4, etc.... until L352 has been autofilled.
In the attached spreadsheet, I have the original data display horizontally (sheet2). Col A is Patient #. The header in row 1 are the test codes. Each patient took only 1 test and have result reported either neg, pos, pending or not eval. How do I transpose the header and have the test results consolidated in 1 column accordingly as display in sheet 3.
I have data in columns E through J under the headings of Monday-Saturday (E is Monday, F is Tuesday...J is Saturday). Then out to the left of the data, in column A, I have the week ending date (using Sunday as the last day of the week) which corresponds to the data under each day. There are multiple rows with different week endings but all the data is under columns E-J with the week endings in column A. So for instance, on row 13 the week ending in column A is 12/22/2013 (a Sunday) which means the corresponding data in the same row under columns E-J (the Mon-Sat columns) belong to 12/16/2013 (the Monday in the week ending on 12/22/2013), 12/17/2013 (the Tuesday in that week), 12/18/2013...and so on and so forth until 12/21/2013 which is Saturday in the week ending of 12/22/2013. Lastly, out to the right starting in column N and going horizontally out to column FD (will go out further as more days are added) I have the specific dates by day, it starts with 12/16/2013 and goes on incriminating by 1 day at a time until the end of all the dates included in the data (which at this point so happens to be 5/11/2014).
What I need done now is to take all the data which currently sits in columns E-J (the Monday-Saturday columns) and copy and paste it out under the correct specific date column that it belongs to starting in column N (going all the way out to FD) based on the week ending in column A. So with row 13 since the week ending in column A is 12/22/2013 the data in columns E-J needs to get pasted to row 13 columns N-S, then the same thing for row 14, except the week ending in column A row 14 is 1/5/2014 so the data in columns E-J row 14 needs to get pasted to columns AB-AG (AB is 12/30/2013...and AG is 1/4/2014). Below is the loop I came up with that accomplishes this, but for the reasons already stated.
Code: Sub newtest() Sheet2.Unprotect Dim drd As Long Dim rrd As Long drd = 13 rrd = 14 Do Until IsEmpty(Cells(drd, 1).Value)
I currently have everything on one worksheet, but wish to add more sheets that will deal with the same rows (students) but will contain different data in some (but not all of) the columns. The problem is this:
I can see how to have Sheet2 update from Sheet1 normally. I cannot see how to have Sheet2 update from Sheet1 when I insert rows in the middle of my data so that I can insert new students into their classes. Can I have Sheet2, 3 etc all manage rows based on Sheet 1 even with inserts?
I have a spreadsheet (Sheet1) that I am currently trying to mirror onto another spreadsheet (Sheet2) within the same workbook. I've tried to accomplish this task with a regular excel formula but, the excel formulas are not dynamic enough to account for the adding/deleting of rows. Sheet2 needs to mirror Sheet1 in the sense that all cells on Sheet1 that contain a value must show up on sheet2 in the same format. However, sheet2 also contains formulas that do not/are not needed on sheet 1. Sheet2 needs to be dynamic enough to expand/contract with the adding/deleting of rows on sheet1 but, also needs to contain formulas that are not overwritten in said expanding of information.
One spreadsheet (Sheet1) sometimes goes out to a client and can not contain cost information. However, the cost info is needed on Sheet2 for some of the formulas I need to run.
The worksheet contains 20 different datasets which are divided into separate 30 x 10 grids, all of which help comprise a larger 300 x 20 grid. I have defined each smaller grid/dataset as its own named range. Originally the entire dataset started in A1, but I am shifting it dataset down by 40 lines to create space to work at the top of my workbook.
I am wanting to create a data validation list in A1, then have cells A3:J32 populate with the named range I select in A1. However I also want this populated dataset at the top of the sheet to be a mirror -- not a copy.
Therefore once I have the data in my range available A3:J32, anything I manipulate would be mirrored in the original range in the lower portion of the workbook.
I have a workbook that will have multiple sheets that I wish to have the exact same formatting, however, the data on each sheet may vary.
I want to be able to use a "Master Sheet" to make all formatting (ie. cell size, text alignment, font size/style) changes and have the other sheets automatically update.
I do not want this to affect or interfere with the data.
The reason is that some workbooks may end up with a large amount of worksheets and when I want to make a change to cell sizing or alignment I don't want to have to go through all of them to do this.
Any way with standard excel array functions to achieve a mirroring of a vector? I would like to use it in further calculations, thus a cell-by-cell OFFSET(.) solution unfortunately does not work for me.
I have a one column spreadsheet. The column contains this data:
1 Name 2 Address 3 City 4 State 5 Zip 6 Telephone 7 Fax 8 URL 9 10 11 Name 12 Address 13 City 14 State 15 Zip 16 Phone 17 URL 18 19 Name 20 Address ... and so on
Where there may be one or two blank rows between the individual records and where there may or may not be a Fax number (or row) in the record.
I am trying to convert this data to a horizontal column format - which works fine if I do a copy/paste special/transpose. However I have to do this for 1,800 records and cannot figure out how to do this reliably.
I gave the above illustration to simplify but, actually this is a two column spreadsheet with individual row labels for every record using the above terminology. In other words the above text is in the first column and the data is in the second. Just thought I'd mention in case there was a way to do some kind of if/then formula.
reformat a table in which data for each named person is presented in one row with mutiple columns into a table in which each named person has multiple rows and one column of data. The solution to to this has eluded me so far.
I'm trying to rearrange vertically aligned data so that they are horizontal. But at the same time, I need this macro at an interval.
To give you an example,
Mr. Yang Xin 221-421-5123 Male College Grad Ms. Taylor Cindy 534-123-5512 Female Uni Grad In this case, we need the data to be arranged this way. Mr. Yang Xin 221-421-5123 Male College Grad Ms. Taylor Cindy 534-123-5512 Female Uni Grad
A B 1 Name: A 2 Address: NY 3 Gender:Male 4 Cell Numbers: 123
[Code].....
I know that this has been asked a million times, but I need the new table in a new sheet (which is sheet 2 and also I skip the Cell Phone numbers data).
What would be the quickest and most efficient way of doing the following:
Example
Sheet 1: clicking once, twice, three times in the following cells to change the fill to Red, Yellow, Green respectively (in Row 22, COLUMNS E H K N Q T W Z AD AG) and mirrorring this format/fill on:
Sheet 2: , mirror the selected fill on sheet1 in E22, H22 , K22 in in E3:N3 respectively example E22 (sheet1) is mirrored now in E3 (sheet 2); H22 (sheet 1) is mirrored in F3 (sheet2) etc etc.
I have 35 tabs formatted in the same way - each tab, as with Sheet 1 is linked to a different row on sheet 2 in the same range as is sheet 1 and would need to replicate the mirorring on Sheet 1 to each of the other 34 tabs.
I have two vertical ranges that I need summarized into 2 adjacent vertical ranges.
" A B C D | SUMMARY model qty| modelqty 1 4.12922.0000| 4.12952.2000 2 2.000012.1250| 2.000025.1250 3 4.12929.0000| 318.0000 4 318.0000| 5 4.1291.2000| 6 213.0000| "
A1:A6 is my SKU's model number B1:B6 is my inventory C1:C6 should contain formulas that result in a summary of the models D1:D6 should contain formulas that result in a sum of the inventory count for each model
how do you create a graph with a field such as vendors on the X-axis, but with dollar values spent on the left side of the y-axis and percentage of total dollars spent on the right side vertical axis?
I have a very large Spreadsheet (time Sheet Report) that is very messy, how to reorganize the data properly.
We have employees that worked multiple shifts and our system record all their punches. However the system list the data in a vertical position, but I need it to be display horizontally. But there is another problem, there is some employees that did not punch for lunch and would like to add the 30 minutes lunch for only those that worked 8 hours shift.
I have a huge data table, and I just can't seem to find a way to sum the numbers the way I want.
Here's an example of the table:
___ A B C A B C A B C A B C A B C Jan 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 Jan 3 4 5 6 3 4 5 6 3 4 5 6 3 4 5 Jan 3 4 5 6 3 4 5 6 3 4 5 6 3 4 5 Jan 3 4 5 6 3 4 5 6 3 4 5 6 3 4 5 Feb 3 4 5 6 3 4 5 6 3 4 5 6 3 4 5 Feb 6 3 4 5 6 3 6 3 4 5 6 3 6 3 4 Feb 6 3 4 5 6 3 6 3 4 5 6 3 6 3 4 Feb 6 3 4 5 6 3 6 3 4 5 6 3 6 3 4 Feb 6 3 4 5 6 3 6 3 4 5 6 3 6 3 4 Mar 6 3 4 5 6 3 6 3 4 5 6 3 6 3 4 Mar 6 3 4 5 6 3 6 3 4 5 6 3 6 3 4 ... ...
But, in essence, I need to sum together every value that's in January AND is in a column labeled "A".
I could just write up a bunch of "sumif=" functions but I feel like there's a simpler way.. I tried using "sumifs=" but I don't think it can handle horizontal AND vertical criteria...
