I would like to reference cells in another tab in my worksheet, but change the orientation.
For example, in Tab 2, I want to make rows A1:A26 equal to columns A1:Z1. Do I have to click back and forth between worksheets 26 times, or is there a way around this?
I have a listbox that gets filled via '.rsRS.GetRows', which works great; all rows are being displayed neatly.
However, when the recordset only holds one record, i.e. one row is desired to appear in the listbox, then the listbox changes its orientation: instead of one row with seven columns I get one column with seven rows!
The listbox properties in the local window states that the lbx.List() object has a column count of '7', but what I see looks like:
[] name
[] surname
[] age
[] zip
[] colour
[] dob
[] phone
It should look like:
[] name surname age zip color dob phone
Someone Im working with says she used to change Excel to work on it on some vertical monitor. Is there a way to do that in Excel 2010 or even 2007?
Obviously I'm not talking about print layout, but viewing the whole application in portrait view, as if she turned a monitor sideways and altered the Aps view.
I am using the dsum formula to sum some values...the formula in B2 is:
=DSUM(BaseSistemasFebrero,"vlfinf",OFFSET('Planes Entidades'!B$1,0,0,COUNTA('Planes Entidades'!B$1:B$49),1))
The Planes Entidades sheet the data is layed out like this: ....
Is there a way of getting the cell references in a formula change following a change in the content of a cell in the worksheet?
View 9 Replies View RelatedI'd like to just copy or move cells or cell ranges and have lines and shapes come along for the ride, and disembark with the same orientation. E.g. if cell E29 has an arrow through his head, I'd like AA66 to after copying there.
I haven't got the knack of when I've selected enough to grab the shape. Sometimes I think I've got it but when I paste I discover that the slippery critter stayed at home. But sometimes I just cut one cell and the shapes move too.
Any advice? BTW, I stay on the keyboard, and off the mouse, as much as possible - I'd like to avoid slowing down to make mouse selections.
It is suppose to be that if the employee is "FT" and has worked >=4 years the return is 15. But if the employee is FT and has worked 2 years but less than 4 years then it is suppose to return 10 (these are days off) Or if the employee is FT and has worked 1 year, but less than 2 then it should return 5 days off. And all the others in the column get no days off.
I have tried to do it with structured references and with cell references I get a column of zeros!
I have a spreadsheet that queries another spreadsheet, using MSQuery. The query is of a named range on another workbook. What I am trying to do is change the workbook reference, to another similar workbook, with the same range name. MSQuery is not easily letting me do this. I figured there was a way to simply change the file that the created table (in MSQuery) is using. There doesn't seem to be a way to do this.
The named range is "data." This is what the SQL view looks like: ...
I have a large excel matrix that contains many references to a Word document. The path to that document includes /ref Doc1/ in every reference. I want to change that to /ref Doc1 rev1/. Is there a 'replace' tool to do that?
View 2 Replies View RelatedI am setting up VBA code to create different excel workbooks. One of the differences is in the VBA Tools References that must be enabled to make the whole thing function.
How do I write VBA code to turn on and off specific tool references ie. if i had a reference called OrbitCOM Library how can I turn it on and off using VBA code.
I have many files that has a filepath references in the cell values, for example:
='\serverfolder1subfolder2[Filename.xls]Sheet'!$B$82
Is there a way to change all the references to direct to another place?
Filenames don't change, only the '\serverfolder1subfolder2 path
This issue is similar to absolute/relative references, but it's related to the data being referenced, not the cells doing the referencing. If I have a call the references cell A3 then I insert a row above row 3 (making the original A3 cell now A4), my reference automatically changes to A4. How can I get it to stay at A3 no matter what happens?
This may sound like an odd request, but I reference a bunch of cells on a worksheet. This sheet retrieves data from a database which changes regularly. The changes that it receives are not just updated in the cells, but rather the rows are deleted and reinserted changing all my references to this sheet. For example, say I import data and it fills cells A1 to A20 and I make a reference to A15. If I change the data that is imported and the data now only fills cells A1 to A10, the reference to A15 is now set to #REF since it actually clears all cells from A1 to A20 and then reinserts new data.
How to change from absolute references to relative references.
Example :
ws.Range("G" & NextRow).Formula = "=" & Range("H" & NextRow).Address & "+" & ws.Range("I" & NextRow).Address
This code return the absolute references---> =$H$365+$I$365
, and i want change to relative references, like this ---> =H365+I365
Is there a way to absolute reference multiple cells at one time?
View 5 Replies View RelatedNeed to create Working order (in Excel 2007) for filling machine in my factory. Working order is changeable from day to day and the deference is product quantity. Product quantity in working order [File - Working order] is changing according to the each sales plan [7-Jan-13, 8-Jan-13] - (File Working order is Linked to the appropriate sales plan in this case - [7-Jan-13]).
Anyway, I need to change the source workbook "sales plan" from [7-Jan-13] to [8-Jan-13] by changing source workbook name in red cell [File - Working order] and not to browsing location?
All source workbooks [sales plan 7-Jan-13, 8-Jan-13]are at the same location in the same folder, only difference is the source workbook names. So, is it possible to change source workbook name (File - Working order - red cell) for all external references (File - Working order - green cells) at the same time, and avoid annoying browsing?
how would I change the orientation on ALL my worksheets, I'm saying every single one, imagine I got like 100 for the purpose of this excercise (sorry I'm not trying to make this harder, it's just there's always that one guy that'll come on and say 'Go to Print Print Preview, then click on Setup' or something like that.
So yeah, that's pretty much the question that I have for you brainaics:
How do I change the printer orientation setting to landscape on all worksheets (maybe a loop?).
Assuming I’ve the following sample data and wishing if the output data could be formatted using some excel formulas, refer to below sample.
Sample data (Sheet1):
StudentA | Lesson1
StudentA | Lesson21
StudentA | Lesson10
StudentA | Lesson5
StudentB | Lesson5
StudentB | Lesson6
StudentC | Lesson9
StudentC | Lesson8
StudentC | Lesson9
StudentC | Lesson15
StudentC | Lesson11
StudentC | Lesson30
StudentC | Lesson13
StudentD | Lesson1
StudentD | Lesson2
StudentD | Lesson3
StudentD | Lesson4
StudentD | Lesson5
StudentD | Lesson6
StudentD | Lesson7
StudentD | Lesson8
Sample output (Sheet2):
StudentA | StudentB | StudentC | StudentD
Lesson1 | Lesson5 | Lesson9 | Lesson1
Lesson21 | Lesson6 | Lesson8 | Lesson2
Lesson10..................Lesson9 | Lesson3
Lesson5....................Lesson15 | Lesson4
.................................Lesson11 | Lesson5
.................................Lesson30 | Lesson6
.................................Lesson13 | Lesson7
..................................................Lesson8
The sample data could be long and a lot, so it's best if the formula can be made flexible to cater for this requirement.
Please refer to the attached file for some sample data.
I've created a workbook with several pages, on all of the first pages they are set up in Landscape view since it contains several columbs of accounting amounts. Below each (page 2) I would like to create a receipt template that I can use the sum formulas and text formulas which is no problem. However, when I print the selected page 2 of 2 to get the invoice, it also comes out in landscape set up. Can I have one page (ie: rows 1-44) in landscape and then page two (rows 45-83) in portrait.
View 2 Replies View RelatedIs it possible to use AutoFilter by Rows (with Orientation - Left to Right)?
View 9 Replies View RelatedI have a normal line graph I would like to see plotted vertically rather than the normal horizontal layout. In other words, points/lines would appear around a vertical axis and go from side to side. The normal graph would look as if it was tipped on its left side.
I want the data field to change to a different field when a command button (cmd_view) is clicked. So when the command button has a caption of "Toggle to Feeds", the code changes the field from Sqm to Feeds, and changes the caprion on the command button to "Toggle to Sqm". When this is next clicked, the fields are swithed over, and the command button caption changes relative to it.
The code I have written is below:
The problem I have is when the code changes the view from feeds to Sqm, it works fine, but when the code changes from Sqm to feeds, it crashes out in the true part of the If statement on the code line ".orientation = xlhidden" with the error "Unable to set the Orientation property of the PivotField class".
As the code for the change of view is the same, apart from the field names, I am at a loss on what is the cause.
I have tried to record a macro to see if the field names appear differently, but they do not.
Sub Tog_pivot()
Dim Toggle As String
Application.ScreenUpdating = False
Toggle = Sheet5.cmd_View.Caption
If Toggle = "Toggle to Feeds" Then
'switch view from Sqm to Feeds
Range("F12").Select
' hide existing field
With ActiveSheet.PivotTables("Pivot_forecast_old").PivotFields("Sum of Sqm")
.Orientation = xlHidden
End With
' Create & display new field "Feeds"......................
How do you control the orientation of a floating toolbar? The code below puts the elements horizontally, while I'd like to make the toobar have a vertical format (one macro per line). I'd also be interested if there's a way to do sub-menus to organize the toolbar buttons better. how to revise the code below, and/or a pointer to where I can read about this. I tried searching google and this forum to no avail.
Option Explicit
Public Const ToolBarName As String = "My Toolbar"
Sub Auto_Open()
Call CreateMenubar
End Sub
Sub Auto_Close()
Call RemoveMenubar
End Sub
Sub RemoveMenubar()
On Error Resume Next
Application.CommandBars(ToolBarName).Delete
On Error Goto 0
End Sub
Sub CreateMenubar()......................
I have a number of statements within the Sheet Event Code (Excel 2007). Three times lately I have added a column and had to go back into the code and find all of the references that needed changing to reflect the new column.
I have been working on this for a couple of days and even tried EE, but to no success.
I have read that Defined Names / Constants should be used as often as possible, but even trying that, the VBA code errors out or "hangs up". Even within Bill Jalen's book (VBA and Macros 2007), there is nothing that addresses this, especially using Intersect.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
On Error GoTo mEnd
Set rng = Sheets("Log").[F14:F10000]
If Not Intersect(rng, Target) Is Nothing Then
If Target = "" Then
With Sheets("Log")
I set up formulas to count text characters in a range of cells. I'm tracking attendance and payments for a small yoga studio.
All I need to do is count "Y"s for prepaid attendance and "DI"s for drop-ins. I have the formulas working but they are absolute so inserting a row will break my sheet.
=COUNTIF(E14:Z14,"*Y*")
=COUNTIF(E11:Z11,"*DI*")
I want to put vertical values to a selected space with horizontal orientation
See the excel file : FILL IN SELECTED DATA.xlsx‎
I'm trying to do a formula that references a cell and returns a different result dependant on the number in the cell being referenced.
For example I've said if A1 has a 3 in it then put the word TEST as the result, plus if it has a 4 put the word RESULT.
What I wrote as my formula is as follows-
=IF(A1=3,"TEST")+IF(A1=4,"RESULT")
It works fine when I only use one result but goes wrong when I add two. If I change the words I want to show to numbers it comes up fine but with words it just returns a Value error.
I have 2 inventory reports: what my store has and what my supplier has. I need to copy Tab1:K# to Tab2:T# provided that Tab1:A#'s contents match Tab2:A#'s contents. The A column represents the SKU of the item, but there is a difference in the amount of SKU's in each (my store sells ~6,000 items, supplier has ~10,000 items), so it's not as simple as sort by column A and copy pasting column K to column T.
For instance:
On Tab 1, A2's value is [1], K2's value is [9.38].
On Tab 2, A70's value is [1], K70's value is blank, but I need it to be [9.38], to match Tab 1's respective SKU.
I almost thought I had it figured out with VLOOKUP, but I cant seem to get it right... It doesn't reference the correct number.
Screenshots for reference
First tab, from the wholesaler: [URL]
Second tab, store's stock: [URL]
Assume I have a cell M24 with a formula like
=M10 + $H24 - $I24*0.35
As you can see B10 is a fix reference (due to omitted $) which should NOT be auto-adjusted but be kept.
Now I want to copy the formular to lots of cells below cell M24. therefore I mark cell M24 and click copy in context menu.
Then I drag/expand the blinking cell border to lets say the 20 cells below. As I result I expect e.g. in cell M25 a formula like
=M10 + $H25 - $I25*0.35
Unfortunately I got
=M11 + $H25 - $I25*0.35
So the fix reference is adjusted as well.
How can I tell Excel 2007 to NOT auto-adjust fix references in formulas?
I'm using Excel 2003 and I'm trying to make my LINEST function and the source cells for a scatter graph use the cell range specified in another cell in the document. I figured out that it was simple to do the following:
I250 contains: 10, shows: 10
I251 contains: ="I"&I250, shows I10
But I can't figure out how to do this in a larger function. I've tried a few things but none of them work. Here is an example where I want it to do a LINEST with y values in cells Ja - Jb and x values in cells Ia - Ib (where a and b are integers specified in cells I250 and J250 respectively):
Below is my code which isn't formatting the cells it's suppossed to. It looks like it isn't doing anything. I think the issue might be with the highlighted section of my code, but when I go to "Manage" my rules for conditional formatting, excel references the appropriate cell under the "Applies to" section. I am using relative cell references for for the majority of the rest of the code and this section follows a section that selects the correct cell for this conditional format.
Code:
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=""ABS()>.005"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority[code].....