I have done a Scenario and they list the cell address (example R2C35) in the Report - the cell has the text "R2C35" in it.
I want to get the contents of a cell 2 columns to the left of Cell R2C35.
So what would I use for the Reference Cell in the Offset formula?
How can I modify the following formula to find the contents of reference cell, for example if the cell reference (G$3) is equal to 9 I want it to use 8 instead.
Code:
IF(ISERROR(INDEX(tra!$E$2:$E$1100,MATCH(G$3,tra!$C$2:$C$1100,0))),"",
INDEX(tra!$E$2:$E$1100,MATCH(G$3,tra!$C$2:$C$1100,0)))
I tried using G$3-1 but this doesn't appear to work.
In VBA (Excel 2003), I'm wanting to get a reference to an ActiveX comboBox that is inside/attached to a cell.
I can iterate thru all the OLEObjects in the OLEObjects collection of the Worksheet and test the .TopLeftCell property to see if it matches my target cell. However, this seems like a long way around the problem - particularly if there's hundreds of comboboxes in the sheet.
Intuitively, I would have thought there would be a Cell.OLEObjects collection, but it appears that there isn't.
Is there a quicker and easier way to get a reference to the OLEObjects within a target cell?
I've got 2 worksheets in the same workbook.
Sheet 1 contains huge amounts of data - thousands of rows and multiple columns
Sheet 2 - I want to extract the data from sheet 1 column A into sheet 2 column A but only the data from every 21st row.
I want to be able to copy the formula automatically down, otherwise it will take hours to do it manually. So far I can only get the cell reference to increment by 1 each time after copying.
So what I'm trying to achieve is :-
Sheet 2 A1 = Sheet 1 A1
Sheet 2 A2 = Sheet 1 A22
Sheet 2 A3 = Sheet 1 A43
In a large range of cells, how I can remove the single quote at the beginning of the contents of each cell? (without single quote they would be formulas). It didn't work using CTRL + L and trying to replace the single quote by blank applying this to the whole range. I am currently using Excel 2003.
Example:
cell a1 contents: '= IF (I17 = "----------", "----------", E17-C17)
cell a2 contents: '= IF (I18 = "----------", "----------", E18-C18)
cell a3 contents: '= IF (I19 = "----------", "----------", E19-C19)
....
etc., etc., etc.
On Sheet2 I have a table where I want M2 to do the following:
1.) Look at cell A2 where the name "John" is and B2 where the name "smith" is.
2.) Look at sheet1 and look in B2 and C2 for "John" and "smith"
3.) Once it matches the name, place the contents of sheet1, cell I2 into sheet2, cell M2.
So in short, match the name on sheet2 to the name on sheet1 and return the number in I2 to M2 on sheet2.
Maybe combining the persons name in to one column would make it easier?
How do you create a reference number to be posted in a userform2 that is opened from an earlier userform1 where you have entered some initialisation data and generated a source reference number. ie set of data has reference X and there will be y items in that set want references in form X01 to Xy (y unlikely to exceed 20). Want to put references 'X01' into the userform2 and cycle through data entry in userform2 from source reference X01 to Xy. (I'm having problems with the transfer from 1 to 2!)
View 2 Replies View RelatedIs there a way to use the offset function with a cell reference for the first reference?
I have many columns of named ranges, each representing a month of financial statement data. I want to pull up certain cell values based on the months being compared.
For instance, if the user selects "May", I want to pull only the sales data for May 2013, May 2012 and May 2011. That data is in the 311 row of the columns for each individual month.
=OFFSET(ytdmay2013,311,0,1,1)
In short, I am using a vlookup to get the ytdmay2013 based on the chosen month of May in the input section. If the user then chooses June, the lookup will return ytdjun2013. I want to link the ytdjun2013 in the offset function.
I have a sheet (sheet 1) of employee information for multiple buildings as follows:
Employee Name, Building, Pay Grade, Full Time Equivalent (of full time hours worked)
Anne, Centre A, Anne, Grade 2, 1.0
Bob, Centre E, Bob, Grade 3, 0.50
Carl, Centre H, Carl, Grade 2, 0.60
Dan, Centre B, Dan, Grade 1, 1.0
Emma, Centre A, Emma, Grade 3, 1.0
Fash, Centre A, Fash, Grade 2, 0.40
Gemma, Centre C, Gemma, Grade 2, 1.0
Hanna, Centre B, Hanna, Grade 1, 1.0
etc, etc, etc
From another sheet (sheet 2), I wish to populate the total of "Full Time Equililents" at each grade in each centre, eg:
CENTRE, GRADE 1, GRADE 2, GRADE 3
Centre A, 0 1.4 1.0
Centre B, 2.0 0 0
etc,etc,etc
I'm using Excel 2003 if that makes a difference
I'm using excel 2003 and using VBA I want to convert data from my original data below.
The first row is just to identify the columns below it.
REFR1,REFERENC2,COMPANYNAME01,CURNTDATE,QUANTITY,ITEMNUMBER,PRICE
71723,121222010,EXAPLECOMPANY,2-13-2013,1.0000,MPG TBS01 TB,65.9900
71723,121222010,EXAPLECOMPANY,2-13-2013,1.0000,MPG TBS01 CH,86.5800
71721,121221142,EXAPLECOMPANY,2-13-2013,1.0000,MPG PB01,75.9900
71718,121234503,EXAPLECOMPANY,2-13-2013,2.0000,MPLC001,146.9900
71718,121234503,EXAPLECOMPANY,2-13-2013,1.0000,MPMC001,120.9900
71720,121238748,EXAPLECOMPANY,2-13-2013,1.0000,MPS007,63.9900
To be converted to the following:
"COMPANYNAME01",
"TOTALITEMS","REFR1","REFERENC2","CURNTDATE","0","0.00","0.00"
"ITEMNUMBER","QUANTITY","PRICE","TOTALPRICE",
"EXAPLECOMPANY",
"2","71723","121222010","2-13-2013","0",,"0.00","0.00"
"MPG TBS01 TB","1.0000","65.9900","65.99",
"MPG TBS01 CH","1.0000","86.5800","86.58",
[Code] ..........
The converted output data above can be within the same document or output to a text file.
I am running Excel 2003 on a Win7 system.
Here is my situation:
Each of my data sets spans roughly 75 columns by 250 rows at present, but this could expand. The first 7 rows contain metadata. Columns 2-25 or so contain the raw data, from which everything to the right is calculated. The data sets have most columns in common, but not necessarily all.
In order to tease out the most meaningful information from my data, I frequently sort all or part of it based on varying criteria. When I find a useful sorting criterion, I create a new column with a header that describes the criterion and populate it with a formula that returns a 1 if the condition of interest is met for that row, or a 0 if it is not. For example, if I am doing this in column AA, I might enter
=--(AND($AX8>$AA$4,$Y8>0))
and copy it down to the end of the data. The resulting vector of 1s and 0s quickly re-identify data that meets that criterion even after subsequent resorting. It also makes locating data that meets multiple sorting criteria extremely simple. Essentially, I create a truth table.
Cell $AA$4 in the above example contains a "comparator" value I might wish to change at some point, which would change the subset of data the condition selects for.
Here's the first hard part:
For each data set, I need the ability to generate meaningful plots that includes separate series based on the criteria I have described. However, I also need to retain the ability to resort the data or change the comparator value without disrupting these plots. In other words, the plots must NOT change when the order of the data is changed, but MUST change to display the appropriate data when the comparator changes.
Here's the 2nd hard part:
Once I have this working for one data set, I need to be able to port it to other data sets (which are contained in other workbooks), so that I can compare equivalent plots from each. I also need to minimize the number of manual steps involved in doing so, to avoid human errors and excessive time consumption.
The only other possible complication I can think of at the moment is that, to this point, I have been inserting blank rows to isolate subsets that I do not wish to perform further sorting on from each other.
Right now I am angling toward VBA code that loops through the entire data set to generate base dynamic ranges using the column header row (row 1) as the names, and the entire column of data for the rangeloops through the truth table columns to generate "branch" row ranges for each of the sorting conditions,loops through the entire data set one more time to create "branch" ranges for each of the base ranges.
I could generate some code to accomplish a one-off solution for a given configuration of a single data set (provided there is not a list length limit in a chart series that I'd be violating)...but without a dynamic named range, I don't know how to get to something that would update appropriately. So in essence, I am still stuck at the dynamic range part of this.
I want to create a formula that will refer to the cell I want by adding a number to the cell reference, not to the contents of the cell. For example, lets say that the cell C11 holds a value of 22. I want to add 4 to C11 to get my formula to reference cell C15, I don't want it to add 4 to the contents of C11 which is 22 and would give me a result of 26. I have a large spreadsheet and I can't just fill down the way I have it now and I don't want to have to retype the formula every time.
View 2 Replies View RelatedTrying to use INDIRECT to sum the contents of a column on another worksheet upto a certain cell reference which is in another cell on the worksheet.
=SUM(INDIRECT("Sheet1!A4:Sheet1!"&B1))
I have taken it back to simply having sheet1 with numbers in A4 to A23, then sheet2 with A23 in cell B1, and the formula above it C9, but I keep getting #REF!.
The formula works fine if on sheet1 without the worksheet names in it. Formula evaluation gets to =SUM(INDIRECT("Sheet1!A4:Sheet1!A23")) then gives =SUM(#REF!)
I cannot "BOLD" the contents of cells in my spreadsheet by clicking "B" on the menu bar. However I can do so by clicking on "Format - Cell - Font" and then using the selecting the Bold option. I wonder why this happens.
View 2 Replies View RelatedEssentially i'm trying to get the max of 5 cells at a time using the 'offset' reference. Here's my erroneous code:
Dim maxCell As Range
For Each maxCell In Range("y9:y150")
If maxCell.Value "" Then
maxCell.Offset(0, 1).Formula = "=max(maxCell.offset(0,-20): maxCell.offset(-5,-20))"
'maxCell.Offset(0, 1).Formula = "=max(maxCell.Offset(-5, -22) : (0, -22))"
End If
Next maxCell
I have two slightly different lines of code here (highlighted in red), one is commented, but i've been changing both to no avail.
I only want this to be triggered when the cell to the left (maxCell) is not empty, hence I use the offset feature. I've tried to use absolute references for the max formula, but then each cell has the same answer.
I have an income statement with the cities on top (column header) and the expenses below it. There are 5 cities for example. The last line is net profit before it changes to the next city.
New York (column header)
Wages
Lights
Net Profit
Boston
Lights
Wages
Sales
Net Profit
How do you get the row reference for Boston Net Profit without using the offset or indirect function? (doing external linking with workbook closed) The formula would find Boston first and then look for the first net profit after Boston? The small if function may work for this.
I need to match data in cell A to cell B and then if they equal I need to copy the adjacent cell C to cell X . How do I set up a macro to do this automatically? I have over 5000 cells to compare and match up.. I have Office 2003.
View 7 Replies View RelatedIs there any way to use a formula rather than a direct cell reference in the Reference argument of an Offset function?
For example, rather than this:
=OFFSET(H3,0,-7,1,1)
I want to do this:
=OFFSET(max(H:H),0,-7,1,1)
Effectively replacing the starting cell of the offset function with a dynamic formula that returns the maximum value of an array.
we have a Excel 2010 worksheet containing multiple tables.
Table1
ColumnA ColumnB
1 Blue 12
2 Orange 14
15
11
3 Red 10
Table2
ColumnA ColumnB
1 Blue 11
2 Black 13
15
11
3 White 10
19
17
On a separate worksheet we want to calculate with the first worksheet's values.
For example: Calc1: Sum ColumnB IN Table1 WHERE ColumnA = Orange
How do we do such a calculation/formula?
Using Excel 2003, is it possible to use 2 sumif functions in one cell?
i.e., sumif and sumif
I am using Excel 2003 at work.
New cases are entered on to the spreadsheet. The case remains open until a closure date is entered, at which point the case is closed. What I need is the following:
Cell A3 = should say "Open" if there is no value in cell Y3
Cell A3 = should say "Closed" if a date or any value is entered in cell Y3. Ideally it should only say "Closed" if a date is entered in format xx/xx/xx, but am flexible so that it says "Closed" if any data is input.
At the moment Cell A1 has a drop down list consisting of open and closed. I will remove this if it causes complication.
i'm using excel 2003 and i'm trying to mark 7 cells in a row by conditional design by one of the cell
example : if the date on january? mark all line in yellow, for february mark in red and ect
A B C
528602545 assafsarit@walla.com 01/01/2013
527271005 eti_sh6@walla.com 01/01/2013
[Code] ...........
How to Lock/Unlock an Excel Cell Based on the Contents of Another Cell?
View 1 Replies View RelatedHow, via VBA, would I add a comment from a userform (text box name: txtReason) into a cell that may already have a comment in it? I would like to keep the comment that is in the cell and then have the program add a "/" and the next comment from txtReason..
(using excel 2010)
Recently I have been practising writing my own macros in VBA. I am using Excel 2010.
I have been trying to write a macro to look at two columns in a worksheet (say, columns D and E) and to delete the contents (not the formatting, and I don't want to delete the cell itself) of that cell only if it contains a numerical value (e.g. 1, 2, 256 or any other number). If it finds a cell with letters, then it wouldn't delete the contents of the cell. The cells are currently formatted as "currency" cells.
I have been trying all sorts of things and I just can't get it to work. I can write a macro to clear a range easy enough, but I am stuck on getting it to delete the right stuff. I won't embarrass myself by uploading the code that I have been using..
I have created a macro in excel 2010 which enable the file to save (extract) data into separate location and name. The vba code for macro is as follows: Question: How can I save this workbook with reference to the value containing in cell B2? (it is named temporary now - as defined in the code)
Sub aaa()
'
' aaa Macro
'
[Code].....
I am having some difficulties using a combination of IF and the OFFSET function to display a range of cell values from another column based on a simple condition. The values I need to display at the destination cells should be offset by 8 columns to the right and "X" rows down from the reference column. The value "X" is to be determined via the IF function to check for the row index number.
For example, if Index value "X" = 8, then display the value of B2 in cell I9. IF X = "9", display B2 in cell I10 etc.
I have attached a sample worksheet that provides some examples.
We have a query, that looks at an external data source (db2 i believe). It will pull all data under a certain product set, and as an additional Criteria we currently have Serial Number.
Now, if the value entered is the serial number, lets say '1234567' for example, it will happily pull all instances of that into the table.
However, what I would like is a cell where I can enter the serial number, and the query will look at the Criteria, the Value will reference a cell (where we can type any serial number), and it will then populate it! I thought that would be fairly intuitive even for a complete novice like myself - now I am likely to have nightmares about Function Errors!
What I have tried:
*Setting the value to a cell ref... ie instead of '1234567' i have set the value to =$a$1 and in that cell i have a serial number.
*Tried to create a parameter. So value of [Serial Number] I then get a prompt saying "Enter parameter Value (serial number) - Regardless of if i type a cell reference (ie =$a$1, or i input the actual serial that worked before when it was '1234567' I still get a function error!
I keep trying different permutations of things, but I am ill prepared.
I have to file an insurance claim due to a fire in my home. I have a folder with .jpg photos located at C:InsClaimOnlinePhotos . Im working with Excel 2003. I am trying to put the picture of the damaged protery in Column A on the same row as the detail about that damaged property using the ActiveSheet.Pictures.Insert function. The name of the picture is located in Column B. There are multiple sheets in the Excel book and each contains from 10 to over 700 rows. I would like to run the macro based on the active sheet to add the pictures one sheet at a time. I would like the pictures to be about 1.2" High by 1.6" Wide and be actually be within the cell of Column A of the row where the detail information is located. I have played with writing my own code, pieces of others code and full code sets I found in the Forum. Nothing works. Below is the best I found so far but, it uses Column C for the placement of the pictues.
When I try to run the below code I get Run-Time Error "1004" "Unable to get the insert Property of the Picture class".
I'm not sure if this code should work in Excel 2003 or not.
MS Word allows a person to turn off email addresses so that you have to press the Ctrl key then left click the mouse. This makes sure that every time a person touches a cell that it does not automatically load that address into Outlook. Is the same possible in Excel 2003?
View 3 Replies View Related