Formula or do I need to break down and use a macro...


New Member
Feb 19, 2020
Office Version
365, 2013, 2010
Windows, Web
Hello All.
I am not the best excel user but definitely not brand new to excel, and if something like this has already been answered just direct me to that thread.

I have a very large proforma spreadsheet that I am using to track our company's estimate/cost per job vs overhead spending vs. profit.
I have these 2 problem sheets:
1 is a summary sheet with job number/information, shipping charges and estimated job cost. (See example image of sheet setup.)
2 is a break down by machine what the job costs to produce (Most of these numbers are hand entered coming from a different program all together). At the bottom of that sheet the row are totaled. (See example image of sheet setup.)
So here is my problem - I have a single "totals" row from sheet 2 that I want to some how auto link back to sheet one "proposed estimate column" and its driving me nuts.

I already have so much to hand enter that I really don't to =cell it several places.

I have tried transpose(..., index(address...

Here is the macro I have tried, which I totally swiped from a different website:
Sub TransposeColumnsRows()
Dim SourceRange As Range
Dim DestRange As Range

Set SourceRange = Application.InputBox(Prompt:="Please select the range to transpose", Title:="Transpose Rows to Columns", Type:=8)
Set DestRange = Application.InputBox(Prompt:="Select the upper left cell of the destination range", Title:="Transpose Rows to Columns", Type:=8)

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False

End Sub

Version: 2013 - running on a virtual desktop.

Let me know what additional information I can provide...


Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.


Well-known Member
Mar 22, 2019
Office Version
So whatever you enter in L3, you want columns L and M to be L3's Proposed Estimate Cost? So if GPO had a Proposed Estimate Cost of $1, L38:M53 would equal $1?


New Member
Feb 19, 2020
Office Version
365, 2013, 2010
Windows, Web
Opps. I did not explain very well apparently.
I need Sheet 2 L74 to link into sheet 1 J4 and then I want to be able to auto fill that J column in sheet 1 with Sheet 2 row 74 for as many jobs as we have this year.

I know how to link cells manually, I just don't want to... but I will if I have to...

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...