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

cbrooke1313

New Member
Joined
Feb 19, 2020
Messages
2
Office Version
365, 2013, 2010
Platform
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)

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

Attachments

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

VBE313

Well-known Member
Joined
Mar 22, 2019
Messages
508
Office Version
365
Platform
Windows
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

New Member
Joined
Feb 19, 2020
Messages
2
Office Version
365, 2013, 2010
Platform
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

Threads
1,100,190
Messages
5,473,034
Members
406,843
Latest member
David_Welland

This Week's Hot Topics

Top