Hello all,
I have a workbook that has multiple sheets, the first being a balance sheet. I am trying to get revenue numbers from a column in sheet 5 into a row in the balance sheet on sheet 1. I know that if I enter "=" and then click on the cell in sheet 5 that works for one cell, but when I drag to autofill it doesn't ever work right. I have to do this for a bunch of rows&columns so I need a quicker way than one at a time.
Any help would be much appreciated!
Also, I need to make sure they are linked so if I change them in the revenue sheet it is reflected on the balance sheet (sheet 1). I know how to just copy and transpose the values, but I need them linked. Thanks again!
Try something like this:
=INDIRECT("'Sheet1'!A"&COLUMN(A1))
where Sheet1 column A is the column containing the numbers to transpose. COLUMN(A1) refers to row 1... so that this will begin to extract from Sheet1!A1
If you are starting in another row, say row 8, then you would start with the corresponding column number... e.g. Column(H1)
and then copy across...
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Let's see if I understand you correctly....
The data on Sheet 5 is in a column (vertical) and you want it to be in a row on Sheet 1 (horizontal)?
One way is to use the OFFSET function to get this to drag properly. The exact formula will vary depending on what column your first formula is being inputted to butChemistB
Please Login or Register to view this content.
Or you could do what NBVC suggested.
ChemistB
I tried with this a few times no luck. Let me get specific about what I am trying to do.
I need Sheet 6 K74:K133 to show on Sheet 1 H5:BO5.
Thanks again for all the help today!
In Sheet1!H5....
=Indirect("'Sheet6'!K"&Column(BV1)) dragged across to BO5
I am getting a #REF! with that. @ Questions
1) do you not have to reference the specific cells in Sheet6 i.e. K74?
2)what is the BV1?
Thanks for the help!
is your Sheet 6 actually called Sheet6 ... the names have to match exactly...
The BV is column #74 so Column(BV1) is just used to automatically get the number 74 to append to the "K" so that I retrieve what is in K74...
That was it. I had it named REVENUE. Thanks for clarifying the BV. I really appreciate your help today. I have learned a lot and you are saving me tons of time.
Not sure why you can't get NBVC's to work but here is mine updated with your specificsChemistBPlease Login or Register to view this content.
Why is it necessary to put the "1" after the BV? What would happen if you put in BV2?
Originally Posted by fusionman
No the 1 is irrelevant... you could put any row number... it the column BV that is important so that you get the number 74 returned...
Thanks to both of you! I'll pay it forward.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks