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

cbrooke1313

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

  • Sheet one setup.PNG
    Sheet one setup.PNG
    14.4 KB · Views: 14
  • Sheet 2 setup.PNG
    Sheet 2 setup.PNG
    31.9 KB · Views: 13

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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?
 
Upvote 0
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...
 
Upvote 0

Forum statistics

Threads
1,223,939
Messages
6,175,532
Members
452,651
Latest member
wordsearch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top