Formula To Fill Cells Vertically From Source Data Which Is In Horizontal Format?

Jun 14, 2013

I have a formula in cell A1 that I need to drag downwards so that it fills cells A2 to A10. That formula is picking up the figures located in another sheet but the figures on that sheet are arranged horizontally (A1 to J1). Is there a formula to perform this action? I've tried playing around with the $ sign in my formulas but it does not work.

View 2 Replies


ADVERTISEMENT

Copying Formula Down With Horizontal Source Data

May 26, 2013

I am trying to copy a formula in B38, down to B36

My formula needs to refer to data in of cells in a horizontal succession, G1, H1,, I1...etc

Copying my formula down from B38 causes the formula to refer to G1, then G2, G3... instead of G1, H1, I1 ...

The exact formula in B38 is: =$B$3*(G3*(1-$D$13))

Where G3 should advance to H3, when I drag to copy the formula from B38 to B39. Instead, G3 advances to G4.

View 2 Replies View Related

Display Data In Horizontal Format

Mar 5, 2009

I need to show data across the page, currently the data is arranged in column's, I need it displayed in row's.
Each material has a number of components, it is the components which I want to show across the page, with the different headings.

I have attached the current format and the required format.

Note the spreadsheet I will be working on has thousands of materials and hundreds of components.task.xls

View 7 Replies View Related

Vlookup Function: Re-format The Information Into A Horizontal Format By Week

Feb 3, 2007

I have a forecast which is sorted by product code by week vertically. I need to create a lookup to re-format this information into a horizontal format by week. I have started this by transposing the information as my attachment shows but I am hoping there is a lookup formula which will be easier and quicker. I have attached the actual document and the data I want to sort is Sheet 2. I have started in Sheet 1. I want to look up the code in column A, then lookup the week number which would be B2 in Sheet 1 and return the value of that Code in that week from Sheet 1.

View 2 Replies View Related

Auto Fill Formula Every Nth Cell And Lookup Value Every Nth Cell In Source?

Dec 19, 2013

Source tab contains vital information about some clients.

In the aggregated tab (Cell C10) I created a formula that pulls the Inflows from the source in a very specific array. So for client 1, this works fine. Now, if i copy my formula to the client 2 (Cell C14), it obviously wont go and look in the correct array in my source.

What i need to do is to be able to copy/paste my formula

[Code].....

(from cells C10 to CC10) to cells C14 to CC14, but when copied, the look up array changes to:

Formula: [Code] ....

I will have to fill this formula to at least 100 entries down, so i need to make it work with ease

The good thing is that all look up values in the source increase by a fixed number of rows (12). I tried playing with index/rows formula.. no luck..

Attached File : samplev1.xlsx‎

View 1 Replies View Related

Excel 2007 :: Date Format While Copying Data From Another Source?

Jul 14, 2013

I have some data coming in from another source system which has the date format as "DD-MM-YYYY" but while pasting it to Excel (2007) sheet some date fields has just DD-MM-YY format which disrupts my macro. I did changing the language settings to "English(UK)" ,which i wanted, and changed the same in Excel options as well. But unfortunately it doesn't work.

View 3 Replies View Related

Copy Pivot Table Format And Create New Data Source?

Apr 26, 2012

I have created a pivot table in a workbook which relates to data from 2011 - 2012 and this works perfectly. What I now need to do is to copy the pivot table (without the data source) to a new workbook for data which will be collected from 2012 - 2013. Unfortunately when I copy it and try and find the new data source it does not update the filters according to the data in the new workbook.

View 5 Replies View Related

Cell Data Properties To Autofill Horizontal Cells

Jun 21, 2013

I would like to enter a long line of data (text & numbers) into a single cell and have the single cell data populate successive horizontal data fields with automatically. The single cell data would have properties that would correspond to specific properties in the successive fields. The single cell data would remain unchanged. The successive horizontal cells would be looking for a specific piece of the single cell data.

View 1 Replies View Related

Auto Format Fill Colour Background On Certain Cells

Apr 2, 2007

I have been working on a spreadsheet but I use autoformat for.

For example if the date in the cell matches today's date then it turns the text read and makes it bold.

I was wondering if there was a way to format several cells in a row if a certain criteria was met.

for example

A B C
1 Item Cost Quantity
2 Car 11,520 2
3 Bike 7,500 1
4 Tyres 50 4

Now for example if I set a condition whereas the quatity is greater than 3 then it would highlight the whole row.

So in the example above I would like to to highlight row 4 and Fill colour cells A4,B4 & C4 with a yellow background and Red Text and make the texted bold.

View 14 Replies View Related

Fill In Vertical Values To Selected Area With Horizontal Orientation

Feb 20, 2013

I want to put vertical values to a selected space with horizontal orientation

See the excel file : FILL IN SELECTED DATA.xlsx‎

View 9 Replies View Related

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 attempted a formula which is

=INDEX('PIP Input'!$C$17:$C$29,MATCH(H$1,('PIP Input'!$H$17:$H$29),0),MATCH($B4,'PIP Input'!$H$15:$L$15,0))

('PIP Input'!$H$17:$H$29) is column dependent on MATCH($B4,'PIP Input'!$H$15:$L$15,0)

If $B4 returned a different result from ('PIP Input'!$H$17:$H$29) then it would need to be ('PIP Input'!$I$17:$I$29)

How to nest the Horizontal Match inside the Vertical Match, such that the Vertical Match changes column depending on $B4?

View 1 Replies View Related

Incrementing Formula By More Than One Column Vertically?

Apr 10, 2013

I have a formula that i'd like to "click and drag" down but while i do i want it to increment through columns

a
b
c

[Code]....

in cell A1 i'd have the formula

VB: =max(c1:c5)

and it will spit out 15, that's great but when i drag the formula down i want cell A2 to give the value 20

i'd like

VB: =max(c1:c5)

to somehow turn into an equivalent

VB: =max(g1:g5)

by only dragging down, not to the side

View 5 Replies View Related

Incrementing Formula Vertically And Horizontally

Jan 16, 2014

I have a spreadsheet with a summary tab and 30 data tabs. The data tabs are named page-1 to page-30. In the summary page I have the following formula in cell C39: 'page-1'!C20

I want to be able to drag horizontally across 30 cells and have it increment to 'page-2'!C20, 'page-3'!C20 etc.,
and also drag it vertically and have it increment to 'page-1'!C21, 'page-2'!C22 etc.

View 2 Replies View Related

How To Leave Cells Blank Until Data Is In Source Cells

Sep 13, 2013

I am making a spreadsheet for use by my customers. Is there a way to leave cells that have formulas' in blank until the cells that make up the formula have entries in?

View 5 Replies View Related

Pivot Table Retains Old Source Data In Addition To New Source Data

Sep 7, 2006

I have a report that was created for 2005 that contains two worksheets: a "source data" worksheet and a " pivot table" worksheet. I cleared out the 2005 data in the "source data" worksheet and replaced it with 2006 data...after this I refreshed the Pivot Table and everything seemed fine. When looking at the file size I noticed that it was almost twice its original size....upon further investigation I found that the Pivot Table was internally holding onto the old source data (the "Show" functionality of the rows/columns in the table lists the 2005 row/column headers as well as the 2006 headers....even though no data from 2005 is shown in the Pivot Table).

Does anyone know how to purge the old data from the internal Pivot Table memory?

I hope this is enough information....let me know if you need more.

Thanks in advance for any help,

Jon

View 9 Replies View Related

Changing Formula Data Source

Oct 16, 2006

I've a set of excel sheets reading from a single excel data source, the only problem is that the data source is constantly changing, for example:

=DCOUNT('T:GreenbeltProductivity ToolWeek 42[Dept Overall and Data Entry.xls]Insert Data - Current Position'!A:F,5,AC139:AD140)

The week number is the only bit of the code that needs to change week on week, to update the data source on all the sheets and I'd like to password protect the data source.

View 5 Replies View Related

Drag Vertically And Copy Cells Across?

Sep 5, 2008

I am trying to link from one spreadsheet to another and drag the cells down to copy the forumula, however I want to drag vertically on Sheet 1, and Copy the values horizontally from sheet 2.

For example, in sheet 1 I link cell A1 to equal cell A1 in Sheet 2. If I drag down the formula in sheet 1 A1:A10 then it will copy the values in cells A1:A10 in sheet 2.

Now what I want it to do is for me to drag the formula in cell A1 down to A10 in sheet 1, but for this to return the values of A1:J1.

View 3 Replies View Related

How To Modify Source Data When Using VLookup Formula

Dec 17, 2013

I have 2 sheets, lets say SheetA1 and SheetA2. I am using Vlookup to serial number & getting data from SheetA1 to SheetA2. But I want to modifty the data. If i modify the data in SheetA2 that modification should relect in SheetA1. Is it possible...

View 5 Replies View Related

Align & Center Text In Cells Vertically

Feb 22, 2008

I know how to center text from left to right, but how about from top to bottom?

View 3 Replies View Related

How To Display Data Vertically

Sep 9, 2013

How can I submit the data from userform in the spreadsheet vertically like A1,A2,.....

View 9 Replies View Related

VBA To Choose The Source Cells For A List With Data Validation

Mar 10, 2008

when you choose Data Validation for a specific cell, you can choose list. Once you have chosen list for "Allow", you can select the source cells for what will be in the drop-down menu. I want to be able to use VBA (or just use Excel) to modify those source cells.

For example:

There will be 3 lists with drop-down menus using Data Validation.

List 1:
Choose first option in list -> List 2 uses source A
Choose second option in list -> List 2 uses Source B
etc...

List 2:
Choose first option in list -> List 3 uses source C
Choose second option in list -> List 3 uses source D

View 11 Replies View Related

Converting 2 Columns With Repeating Info Into Another Worksheet In Horizontal Format

Jun 28, 2013

taking a spreadsheet that has vertical repeating info in Column A and results in Column B and converting that to another sheet in a horizontal list. The main problem is that the repeating info in Column A may or may not always be the same for every customer; therefore, when it is placed in a horizontal format some cells may or may not have results. I tried a arbitrary lookup. Here is the formula I used:

{=INDEX(INFO!$A$1:$B$300, SMALL(IF($A$1=INFO!$A$1:$A$300, ROW(INFO!$A$1:$A$300)-MIN(ROW(INFO!$A$1:$A$300))+1, ""), ROW(B1)),COLUMN(B1))}

That got me started. I am willing to use a macro. Here is some sample data:

Table 1 has a sample of the info:
IDENTIFIER
CUSTOMER INFO
2000
111111

[Code].....

View 8 Replies View Related

Formula To Fill In Cells

Aug 12, 2009

I am trying to create a nice equation that will look in row 35 for the highest value and then return the value that is 33 rows up and 1 column to the left. See attachment of for more details.

Basically, I need a formula to fill in cells B38:B41 (detail included in Excel doc attached).

View 6 Replies View Related

Fill Down Cells With Formula

Nov 5, 2009

I have this formula in the cell

View 4 Replies View Related

Pick Data From Every 2 Columns And Arrange It Vertically?

Apr 19, 2013

i want to pick data from every 2 columns and arrange it vertically, one under the other ;

sample data:
A 579751 579800 52151 52175 126721 126750
B 546451 546500
C 608971 609000 508081 508110 548941 548970
E 962701 962750 24851 24875

desired outcome:
A 579751 579800
52151 52175
126721 126750
B 546451 546500
C 608971 609000
508081 508110
548941 548970
E 962701 962750
24851 24875

View 6 Replies View Related

Transpose And Consolidate Data Horizontally To Vertically?

Jun 4, 2014

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.

View 4 Replies View Related

VLookup Multiple Values And Paste Data Vertically

May 6, 2014

See the below columns: Column A and Column B. I would like to get all the data in column A to become column names and the data assorted under these column names vertically.

Column A Column B

0_country Canada

0_employee_type HE

0_province" AB

[Code] .........

I would like the end result to look like this

0_Country 0_employee type 0_province
Canada HE AB
USA HO CA
UK HL XY

View 1 Replies View Related

Formula For Counting Cells With Fill Color

Dec 13, 2009

Is there away to count cells with 1 or more different fill colors in a column?

View 9 Replies View Related

Sort Of Transpose Code - Put All Data In Column Vertically On Sheet

Sep 23, 2013

I have 2 columns on sheet 1 as below. I need a code to put all the data in column B vertically on sheet 2 as the result shows. Please note all cells data will be off various lengths all seperated by a comma.

Sheet1  AB2BK
1003 CV1173, CV3133BK1004 CV1010, CV1010A, CV13514BK1005 CV1012, CV1257, CV17995BK1006 CV1836, CV506

Result after code has run.

Sheet2  AB1
BK1003CV11732BK1003CV3133BK1004CV10104BK1004CV1010A5BK1004CV13516
BK1005CV10127BK1005CV12578BK1005CV17999BK1006CV183610BK1006CV506

View 2 Replies View Related

How To Fill Vertical Columns With Info From Horizontal Info

Aug 22, 2014

I have attached a spreadsheet and I am trying to capture the info in lines 2,7,12,17 and return the info into column d,e,f,g

The info in these columns at present has been manually entered but I am sure it could be automated.

OOL Roster Final 18-31Aug14.xlsx

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved