Column O is names and column P autogenerates email addresses with
=CONCATENATE(SUBSTITUTE(O2," ","."),"@company.com; ")
So, John Smith becomes
"john.smith@company.com; "
That's perfect. I want to move it to another worksheet. When I paste it to Worksheet 2, A1 I get
=CONCATENATE(SUBSTITUTE(Sheet1!O2," ","."),"@company.com; ")
So why does that only generate "@company.com; "?
I have 3 Sheets (Sheet1, Sheet2, Sheet3)
Sheet 1
---------
ClassID
Sheet 2
---------------
AccountID, Fname
Sheet 3
----------------
FundID, FName
I want to match the value from Sheet3!FundID to Sheet1!classID and Add Corrospinding Sheet3!Fname value to Sheet2(Fname). I tried using VLOOKUP, MATCH & INDEX but somehow not getting the correct formula.
I have a workbook with two worksheets.
Worksheet one is "Company ID" info, in it are the columns:"Company Name", "street add", "city", "state".
Worksheet two is "Company Contact Person" in it are the columns: a reference to worksheet one column one "Company Name", and column two is 'Contact Person name'.
I want to be able to sort worksheet one at will, By city, or state, or any of the various columns. When sorting worksheet one I want the relationships in worksheet to to remain intact.
"Company Name" - "Company Contact Person".
I have a workbook with a main worksheet where all the data entry and calculations are made and then I have "sub" worksheets which mirror subsets (specific columns) of the information from the main worksheet. When information in the main worksheet changes, or rows/columns are added, I want the sub worksheets to update automatically.
The only way I can figure out to do this is to use absolute references in the sub worksheets. Does anyone know how to change the references to be absolute (using the F4 function) without having to do them one by one? Or can anyone suggest how I can achieve this in another way.
I'm working on an excel report that handles reporting for a large number of people. Another person at work is doing the VBZ coding, and I'm doing the formulas that aggregates the data. which has led to an interesting problem whenever I use a vlookup. I copied the sheets with the formulas from the test document I had been working on to an updated version. And all the vlookups automatically adjusted themselves to reference the document they had been created in. I had also moved over the tabs they were supposed to reference, but they keep looking at the old document. I spent an hour removing the references last night, but I don't want to do it again when I get the final product.
How to get excel to copy a formula EXACTLY as written, and not att in the name of the original document?
It should say
=VLOOKUP($A438,totals!$A$1:$AH$63,32,FALSE)
and instead it says
=VLOOKUP($A438,'[reporting formulas.xlsx]totals'!$A$1:$AH$63,32,FALSE)
I am linking a row of data in worksheet x to a column of data in worksheet Y and would like to be able to drag down the cell reference in worksheet Y in this way:
=MIN(RuleofOriginData!AR$4:AR$63)
=MIN(RuleofOriginData!AS$4:AS$63)
=MIN(RuleofOriginData!AT$4:AT$63)
However, when I drag it down, the formula is automatically absolute for both the row and the column number instead of just the row as I indicated. Can anyone tell me why this occurs and whether there is a way to fix it?
Here is the scenario:
A B C D
BananasApplesOrangesTotal113=A1+B1+C1
I need to reference the formula in D1 with the cells headers names.
In a perfect world, it would take
=A1+B1+C1:
and produce:
=Bananas+Apples+Oranges
I am using vlookup to get a cell value from another sheet, but if the cell has "&" or "/" I need to substitue "&" and "/" with "and" so that the cell can be added later to a url.
i am using =VLOOKUP(a1,Sheet2!A1:W17968,6) to get the value of a1 in sheet 2 and return the value of column 6
this will return "Audio Cables & Leads" but i need it to say Audio Cables and Leads
I need the formula to also check and replace "/" with "and" as well so cables/wire will be Cables and wire
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")
Is it possible to make a back-reference in a cell formula similar to regular expressions?
Example:
=IF(I15=FALSE, [H17 * INDEX(Percentages,I17+1)], IF(A17="", $1, 0))
...where [ ] indicates the actual reference and $1 indicates the reference number
I have a worksheet that will be referencing data from a 2nd open workbook.
Although the 2nd workbook will contain common worksheet names (eg stats), the file name will be volatile.
Consider the formula in wb ws1 cell A1 used to copy a value from wb2 (39961(28-May-09).xls) ws1 (stats) cell B1 if workbook 2's name was static.
='[39961(28-May-09).xls]Stats'!$B$1
In my case, the reference file will always be different and must be substituted in the forumla above (red portion). How can I adapt my formulae recognize the available workbook. I have the file name available now in cell C4 of WB1.
I have a spreadsheet that imports data from a database and into pivot. Several departments use that spreadsheet, but depending on the department, they may want to see e.g. 3, 4 or 5 different fields than other departments.
My spreadsheet also includes a great number of formulas included in a macro, so whenever I get a request to include field X, Y and Z, I end up spending a lot of time adjustning all my formulas to reference the new fields.
I am trying to code the following in VBA. Excel Cells have formulae like: '=+BZ165-BZ163-BZ162-BZ160-BZ159-BZ157-BZ153'. Now I want to write code in VBA which will be able to give me the individual cells referenced in this formula: BZ165, BZ163 etc. These names can be stored in individual variables or arrays.
I will then use these for further processing. Note in cases where formula refers to data in another sheet like: '=Projects!P49' or data in another excel like '='C:Documents and SettingshoskopDesktop[Annual.xls]Quarterly'!BA$502': I would like to store the path , file name and sheetname in variables/arrays as well.
Any pointers as to how I should approach this problem?
I want to sum A2 to B10. The catch is: I have the letter "A" in cell A1, the number "2" in cell B1, the letter "B" in cell C1, and the number "10" in cell D1. In effect, I want to make a function =SUM("A1B1":"C1D1") such that it gives me =SUM(A2:B10). Hopefully, the answer will work for any function (e.g. SUMIF, COUNT, VLOOKUP, etc).
View 4 Replies View RelatedI 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*")
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 sheet which needs to look up one reference and then fill a table with the rest of them.
EG:
Cell A1 contains '0091 911'!$E$2 (cell E2 contains value 100)
Cell A2 contains =indirect(A1) and displays value 100
I need a formula which will auto fill the remaining cells in the table.
eg:
Cell A3 fills to contain '0091 911'!$E$3 (row +1)
Cell B2 fills to contain '0091 911'!$F$2 (column +1)
so it needs to fill the Indirect reference and not =indirect(A1),=indirect(A2)....
I am running a research experiment in triplicate. Upon generation of data for all three, I create an averaged value of the three. I then take this value and do more calculations with it on a separate sheet. I wanted to know if there was a way in which I could quickly generate formulas based on a changing reference cell.
Example Data:
V1A 28
V2A 26
v3A 30
AvA 28
V1B 20
V2B 22
V3B 18
AvB 20
Vxy represents the value of y sample, x triplicate. V1A being sample A, first triplicate. AvX is the average of the three values.
I want to be able to generate a formula that references AvA which can be applied to the cell immediately below to reference AvB. A simple example would be to square the average.
For example things like "=1+A1" or "=A1*B1"
Right now it changes the cells even with Paste Special!
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 have a userform that populates a list of names. As a name is added in column "D" column "C" updates a reference number using the formula =IF(D2="","",C1+1). I can get the formula to copy to the spread sheet but as a new name is added the formula stays the same. If I enter a name in cell D2 everything is fine but when I make the next entry in row 3 the same formula is entered as if it were text and not as a formula. so I wind up with a column of formulas that all say =IF(D2="","",C1+1) reguardless of what row the formula is in.
View 4 Replies View RelatedI have a number of Cell names LR0Cost, EX0Cost, IM0cost that hold %
formula might be
can Excel do a double if formula by looking at cells and not fixed type info.
EG: I have a % achieved and $ bonus to pay: however the % achieved & the $ bonus to pay information in the cell will change each month
So... I want the formula to say
=IF (look for cell A1 (which has %achieved) and if found use A2 ($bonus) to give me an answer... otherwise false
The second dillema I have is that A1 has 3 ranges from 100-150, 150-200 & 200-300 to calculate with a set % in A2
I am working with some fairly long formulas and I am looking for a way to highlight those cells AND work in the document. I know you can click in the cell and it will highlight them, but if you click off the cell the highlights go away. Is there a way to keep them highlighted?
View 1 Replies View RelatedI have a "CURRENT MONTH" tab in a workbook; in that Workbook, I have a standard report that pulls Total Revenues from cell C6 of the tab I want to reference.
For example: In my "CURRENT MONTH" tab B6 has the label "CURRENT MONTH REVENUES" C6 has the formula "='May 08'!C6"
Every month I have to change that formula to "='Jun 08'!C6" as an example for June. (it's not just one formula, there are dozens referencing May that I need to change to Jun).
I could do an Edit/Replace, but I'd rather just type in the current month tab name in another cell and have the formulas pulling the data referencing that cell.
I have noticed that if I create a row and put a reference formula in one of the cells (i.e. in Cell B3 I have "=B4", to show contents of B4), then after I do Sort and Row 3 moves else where, formula in what used to be B3 no longer points to B4, but rather to B<new row + 1>.
Absolute reference doesn't seem to be an option (i.e. "=$B$4") since after sorting rows in question the cell references in my B cells do get screwed up. I guess I want to bind a certain cell to a certain other cell, and keep that bind no matter where I move the row.
I'm using a macro to update individual formulas in a sheet that contains data from external documents. (i.e. ='C:September[Book2.xls]Sheet1'!$AZ$1 + 'C:September[Book3.xls]Sheet1'!$BA$1 ) Every quarter I have to change the column references in this formula (i.e. $AZ$ will become $BD$ and $BA$ will become $BE$ and so on). Also I have to change the September reference to December etc (but I've mastered this already). My current code works well, but requires manual updating:
Dim ColumnReference1
ColumnReference1 = "BD"
Dim ColumnReference2
ColumnReference2 = "BE"
Worksheets("Sheet1").Range("H48").Formula = "='C:September[Book2.xls]Sheet1'!$" & ColumnReference1 & "$1 + 'C:September[Book3.xls]Sheet1'!$" & ColumnReference2 & "$1"
My problem with this method is that I need to automate the updating of ColumnReference1 = "BD" to ColumnReference1 = "BH" to ColumnReference1 = "BL" and so on and so forth. It's handy that the reference shifts across 5 columns, which remains constant. Ideally, I'd like to tell the macro to look at "AZ" then add 5 columns.
I have a rather complicated workbook with many VBA formulas. When I close it down a window displays "A formula in this workbook contains one or more invalid references". verify that your workbook contains a valid path, workbook, range name, and cell reference.
This only happens when I close the workbook. I seem to have all functionality. How can I find the invalid reference.
Is it possible when copying and pasting a formula, to see the values from each cell rather than the cell reference?
View 12 Replies View RelatedI want to copy =d8*k10 into several cells, but the references keep changing. I've tried several things that I've found on the internet, but nothing seems to work and the I can't seem to copy to a columnof cells.
View 3 Replies View Related