Copying Excel data across 2 separate sheets

**akpetty** · 12-14-2015, 01:53 PM

I have a spreadsheet (Work Flow) with data going horizontal from columns F to Q. I am trying to copy this data to a separate spreadsheet (monthly income). This data needs to go vertically. The problem I am having is trying to copy this down vertically. I need the column to change not the cell number. I am trying to reference just the cell. No additional formula. Below is an example of how I need it to transfer. I would like to be able to drag or copy and paste this information and not have to actually reference every cell on the monthly income spreadsheet.

Work Flow Monthly Income
F3 H2
F5 H3
F7 H4
F9 H6
F11 H7

Work Flow Monthly Income
G3 H10
G5 H11
G7 H12
G9 H14
G11 H15

When I copy or drag down the formula to the new columns on Monthly Income it changes the cell number not the column.

Is there a way to get the column to change and not the cell number?

Thank you in advance.

**akpetty** · 12-14-2015, 02:28 PM

I have attached a sample worksheet of what I am trying to do. On sheet work flow the data goes horizontal. I need that information to go to monthly income but change the column to correspond with the month which goes vertical.

**newdoverman** · 12-14-2015, 04:09 PM

I found the data quite awkward to work with so made an adjustment of the row headers on the Work Flow worksheet to match the row headers on the Monthly Income. This could be done the other way around as long as the headers match exactly. In addition, I filled down the dates on the Monthly Income worksheet and hid the dates added by formatting the text as white. The column headers with the months I edited so that there was a consistent presentation of the months and not a mix of full month names and abbreviations. Doing the above allows the use of INDEX and MATCH to extract the values for the months going across.

Enter this formula in 'Monthly Income'!H2 and fill down then replace the formula in the column for the rows titled "Total: this is your Base for this month" with the amount.

Formula:

=IFERROR(INDEX('Work Flow'!$F$2:$Q$10,MATCH('Monthly Income'!B2,'Work Flow'!$E$2:$E$10,0),MATCH(TEXT(DATE(YEAR(G1),MONTH(G1),DAY(G1)),"mmm"),'Work Flow'!$F$1:$Q$1,0)),"")

**akpetty** · 12-14-2015, 04:29 PM

Thank you for the response and help. You solved this problem for me.

**newdoverman** · 12-14-2015, 04:54 PM

Thank you for the feedback.

If possible to do so, I would really consider changing the data to proper data format which is Headers for each column (each column is a data field) and 1 row per record. Your data would look much different but would be much easier to work with.

Copying Excel data across 2 separate sheets

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Copying Excel data across 2 separate sheets

Re: Copying Excel data across 2 separate sheets

Re: Copying Excel data across 2 separate sheets

Re: Copying Excel data across 2 separate sheets

Re: Copying Excel data across 2 separate sheets

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Macro needed to Concatenate data in 2 separate tables of 2 separate sheets

[SOLVED] VBA Help need for Copying data from another sheet and create separate sheets

[SOLVED] Excel vba copy data from general sheets and organize them in separate sheets.

Finding Matching Values in Separate Sheets and Copying Accompanying Data

Excel 2007 : Finding Matching Values in Separate Sheets and Copying Corresponding Data

Finding Matching Values in Separate Sheets and Copying Accompanying Data

Matching data on two separate sheets and copying from one to another

Bookmarks

Bookmarks

Posting Permissions