Copy Formula Down / Across Taking From Data Table With Vertical And Horizontal Parameters
Mar 6, 2013
Excell file eWorked Example.xlsx
I am trying to set-up a formula that needs to be copied down and across and draws upon a data table that has a horizontal and vertical parameter that needs to be checked for the formula to return the right answer.
I have tried all manner of Index/Match, Offset etc but can only get one of either the vertical, or horizontal parameter working.
I have to fill an invoice template, whereby I need to lookup the unit price in a pricing sheet, based on the effective date (horizontally placed) and the specific tasks to be invoiced (vertically placed).
The effective date cell (B1) is the one that can be changed, the formulas in the unit price cells (yellow highlighted) then needs to look up the price for that specific task in the table on the tab "pricing sheet".
I was trying something with INDEX / MATCH, but can't get it to work.
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...
I need a solution for the equivalent of a SUMIF combining both vertical and horizontal data. The vertical cells align to the horizontal ones, but they're in a different table.
My attempted formula is: =SUMIF($H$22:$H$30,"TRUE",D7:L7) *note that this is just an example set of data...my real data set is much larger (both rows and columns)
I need to be able to do this without transposing any of my data.
Things I've tried: - Another option I tried was making D7:L7 a named range and using the transpose function (as an array) within the SUMIF formula above. I received an error. - I tried using a bunch of IF statements added together (i.e. =IF(H22=TRUE,D7,0)+(H23=TRUE,E7,0)...); this actually works properly, but I get the "formula too long for cell" error when I put them all in (too many characters)
I need to transpose or move some of the data on Column B to horizontal position, is there a formula that I can use to do this? Attached is sample of what I want to do.
I was wondering is there a way to convert the format of one excel spreadsheet to a different format in another. I have attached a spreadsheet which illustrates what I'm trying to convert from and to. Perhaps VB Script? I have no idea how this could be resolved.
I am working with two spread sheets; one is my take-off/summary sheet which is where I populate all the data for the project I am working on, the second sheet is also a summary sheet but it has also other functions; hence, I need to have two spread sheets.
I need to link/reference the data I have on my take-off sheet row 119, column K through ACL (lots of columns) to my other sheet but it now needs to be referenced in a column direction. At first, I enter = (sign) in T9 (the sheet I need to bring the data in) and then I go to the take-off sheet select N119 and hit enter, this first data is good, but then I try to drag it down to populate all the data from the other sheet but even though I lock the row (N$119) and let N free the formula doesn't populate this: =!N$119, =!O$119, ='!P$119 and so on so for...all what it gives me is =!N$119 all the way down...
I am trying to reference data that is currently horizonal in excel and would like to reference that data to get output horizontally with some additions. I have pasted a screenshot of my test spreadsheet. how to do it step by step as I am completely a newbie in excel. As displayed below my data is in Cells A1 thru E3. I want to get output in Column H for this data but with text additions. For eg: in A1 I have Rachel 1/1 however in H1 that should become "My name is Rachel 1/1"....
I have a spreadsheet that I am trying to create a formula for that will bring back the data found when you compare an X and Y axis. A sample is attached as the data is huge and I figured what ever you all created I could modify.
I need it to bring back the data found when I run my finger down the column till I hit the appropriate row.
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.
I have a set of workbooks-all located in the same folder-in which data are shown "horizontally"
i.e. file 1 - sheet1 looks more or less like this:
1 2 3 4 5 6
same with the other files.
what I am trying to achieve is that in another woorkbook, all the data are copied in one vertical vector
i.e
1 2 3 4 5 6 from file 1 and then 1 2 3 4 5 6 from file 2
I can seem to be able to resize the array to the correct size but the only values copied are for the last file. in the example that I have above, the result would look something like:
I'm having an issue transposing vertical data to horizontal rows. I know how to do the first set, but I would like to continue down the file for about 40,000 names....
Name: Steve Address: 415 East Falls Ave Zip: 19258 School 1: USC School 2: None Relocate? Yes
Name: Phillis Address: 584 Fourth Ave Zip: 19555 School 1: UCLA School 2: NYU Relocate? Yes
Same
Same
I would like it to look like this:
Name Address Zip School 1 School 2 Relocate? Steve 415 East Falls 19258 USC None Yes Phillis 584 Fourth Ave 19555 UCLA NYU Yes
For m = 2 To 5 For n = 1 To 23 For b = 2 To 100 Sheets("Hierarchy").Select Cells(m, n).Select Selection.Copy Sheets("HierN_H2V").Select Cells(b, 1).Select
I need to create a Macro that will link Horizontal data to vertical data in a separate sheet. Here's the Example:
I have data in a forecast spreadsheet that is horizontal, So, J2:U2 (12 columns for 12 months). This row of data needs to be linked in a single column of data on a separate sheet called "Price_Quantity". So I need C3:C14 (in price_quantity sheet) to be linked to the data in J2:U2 (in forecast sheet). Then C15:C26 needs to be linked to J3:U3, so on and so forth. 12 rows linking to 12 columns. All the way until C4959:C4970 is linked to J415:U415.
I need to create a macro to move variable multiple horizontal data to vertical format with certain infomation on horizontal will be duplicated following that variables. It's looks like below where you can see variables data in column F, G, H and I are moved vertically and at the same time column A, B, C, D and E will be duplicated following the variables allocation. I've tried to use transpose but it too manual and now looking suitable macro to help on this function
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).
