Hey I have a column of Cell Links that I would like to make static
so I have the cell formulas:
and I would like to transpose a the column into a row with the cell values='Sum Data'!B49 ='Sum Data'!B50 'Sum Data'!B51 .... 'Sum Data'!B312
Any ideas?='Sum Data'!$B$49 ='Sum Data'!$B$50 'Sum Data'!$B$51 .... 'Sum Data'!$B$312
The requirements aren't very clear...
If you're saying you want the links to B49:B312 to be stored horizontally as opposed to vertically then one obvious problem given the implied use of XL2003 is a lack of columns
(your range B49:B312 covers 264 rows and you have less than 264 columns available in XL2003)
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
sorry i'll update that now, right now I'm using office for mac 2011.
OK - but I'm still not sure I follow the requirements exactly...
If we assume for sake of demo you're saying you want Sheet1!A1:JD1 to hold links to 'Sum Data'!B49:B312 respectively then
modify the starting Range (With) as necessaryWith Sheets("Sheet1").Cells(1, "A") .Resize(, 264).FormulaR1C1 = "=INDEX('Sum Data'!C2,49+(COLUMNS(RC" & .Column & ":RC)-1))" End With
If the plan is store just values then obviously the links themselves aren't necessary
edit: adding code re: above
Sheets("Sheet1").Cells(1, "A").Resize(, 264).Value = Application.Transpose(Sheets("Sum Data").Range("B49:B312").Value)
Last edited by DonkeyOte; 10-25-2010 at 07:26 AM.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks