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

[cbrooke1313]

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) SourceRange.Copy DestRange.Select 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...

 

[VBE313]

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?

 

[cbrooke1313]

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...