Reference Existing Cells
Nov 19, 2007
I have a rather extensive, for me that is, excel report of daily stats with rows for daily, weekly totals, monthly totals. All have various formulas along the way for various needs - some are simple sums, some are averages, some are percentages.
I need to make some new reports for various purposes that pull the existing information/calculations over into another sheet(s).
For instance - I want to do a report for *just* the week totals so I can do more analyzinig, but I don't know how to pull the already calculated info over so if it ever changes (as it sometimes does) - then my whole dataset reflects the change.
View 9 Replies
ADVERTISEMENT
Oct 5, 2007
In a workbook made in Excel 2003, I have the following for a UserForm:
Private Sub UserForm_Activate
Me.Calendar1.Value = Date
End Sub
I copied this workbook to a computer with Excel 2007 and it bombs out at "Date"
It comes up with a compile error, "Can't find project or library"
In the references window (Tools, References) it has the "Missing: Ref Edit Control" checked and the location at the bottom of this window states "C:Program FilesMicrosoft OfficeOffice11REFEDIT.DLL".
The reference to Office11 is from the computer with Office 2003 as the computer with Office 2007 has Office12.
There is another "Ref Edit Control" in the References window and when I check it and browse to the Office12 folder, highlight REFEDIT.Dll and click on open and in the references window on OK it comes up with "Name conflicts with existing module, project or object library". I have tried to delete the "Missing: Ref Edit Control", change the priority and change the reference in the missing control to Office12 but all to no avail.
How can I change the reference to the Ref Edit Control from Office11 to Office12?
View 3 Replies
View Related
Jun 11, 2008
I have 5 columns set up: A,B,C,D,E
D is the sum of A and B
E is the sum of A,B,and C
As I add in a new column to the right of C (call it C2), I need D (which has shifted over one) to sum A,B, and C.
I also need E (which has also shifted over one) to sum A,B,C, and C2.
Essentially I need a function in a cell that will be able to reference two cells to the left even as more cells are added.
View 9 Replies
View Related
Feb 15, 2010
I have lets say 12 months of data. I have formulas that reference the latest 6 months. When I insert a new column to input a new month, how can I make the formulas include the new months without manually updating them.
EXAMPLE:
12 months of data exist in cells B3:M3 going from B3(oldest) to M3(newest). Formulas reference latest 6 months of data in cells H3:M3. When a new month hits, I insert a column after column M.I would like the formulas to now reference cells I3:N3 which is now the newest 6 months.
View 14 Replies
View Related
Jul 14, 2014
Is it possible to reference a cells value to define a range reference?
[Code] ......
I am trying to define the row value in the range reference with a value in a secondary cell?
View 3 Replies
View Related
May 2, 2013
Suppose I have done a spreadsheet of,say 100 entries in alphabetical order in a column and I discover that I have missed 6. Is it possible to move the existing part from where I missed below this to make room for more entries without losing any of the entries?
View 3 Replies
View Related
Feb 2, 2010
I want to add (same) text to the end of a whole bunch of cells. What would be the formula or function in Excel 2007 for this? I want to add ":59" to the end of each of these:
manages
blankdisk
dentist4u
desktopsupportservices
oznc
5st
divinehope
kcdm
pokeroyunlari
hdgallery
View 3 Replies
View Related
May 2, 2007
need to add same data to every other existing cell in the column, but not replace the data already in it, but to add to it. I've tried to google the answer and look here, but I probably use bad search terms.
For example, I need to add "QW" after each of these lines:
data1432
data9292
data3933
data3939
so it would look like this:
data1432QW
data9292QW
data3933QW
data3939QW
I have a few thousand rows of data, so wouldn't rather not do it manually cell by cell by typing :-)
View 9 Replies
View Related
Jan 16, 2013
I am creating a large vlookup. I have multiple instances of a name, twenty to be exact. I copy the first cell it is name1 and then I paste it for the next 19 cells below. Is there a quick way that I could have it go name2 name3 name4 etc all the way to 20? Currently I am just having to click and change the value manually.
View 11 Replies
View Related
Mar 21, 2013
I run the following formula down a column of records in row AR
=IF(COUNTIF(lookup!A:A,N2)>0, "Exclude", "Not In Lookup")
I want to add to this formula an additional function that if any cells in column A1 are blank, then do not perform the remainder of the equation.
View 1 Replies
View Related
Jun 28, 2008
When I use a macro to refresh all pivot tables, I get a whole bunch of messages popping up, asking me if I want to overwrite the existing cells. Is there a way that I can say no to all of these messages automatically?
View 6 Replies
View Related
Aug 6, 2009
i have a list of about 20,000 phone numbers that I need to edit.
all start
01 123456
01 123457
01 123458 etc
I am trying to create a macro that will change them to
+001123456
+001123457
+001123458 Etc
However, no matter what I try it always comes out as
+001123456
+001123456
+001123456
F2 Copy and paste is going to take me forever, and I am sure that there must be a quicker way to do this.
this is my first time creating macros so am completly lost.
I want to keep the text that is in the cell that I am editing, just add the country code and delete the space.
View 9 Replies
View Related
Sep 3, 2013
Currently, I am using countifs to count data within a specific range if it meets a criteria. Now I want to add to the formula. I need to count the cells within the specific range meeting the existing criteria I already have but only if one cell is greater than another cell.
This is what I currently have: =COUNTIFS(Report!$F$4:$F$1048576,">=8/1/2013",Report!$F$4:$F$1048576,"=8/1/2013",Report!$F$4:$F$1048576,"
View 2 Replies
View Related
Jul 11, 2013
I have a sheet (sheet1) of around 900 rows with data in columns A-K. Column B contains a unique value.
Sheet2 contains data which needs to be highlighted in sheet1 and each row in identified by a unique value in Column B.
sheet1.PNG
sheet2.png
In sheet 1, I only want to highlight the cell which exists in sheet 2 for the matching reference number in column B. For example, in sheet2, the value 'PM328491CN' exists and the value 'CN' is present in cell c2. I want the cell that contains this value in sheet 1 to be highlighted (cell C6).
So, find the matching reference, the highlight the cell in the row which is present in both sheets.
View 1 Replies
View Related
Aug 11, 2008
I would like a macro that when run, finds empty cells in a column within the used range and fills them with the same formula in the other cells in the same column but relative to the row.
I have a basic understanding of VBA so if someone can set me on the right track i'll have a go myself as i appreciate this would take a while to write out from scratch.
View 9 Replies
View Related
Oct 31, 2011
I am using the find function to establish the row number in a spreadsheet for an edit process.
Code:
wks.Range("B:B").find("something").Row
the cells in Range("B:B") are linked to another worksheet. If I copy paste values on the cells in Range("B:B") the code above works a treat.
View 4 Replies
View Related
Dec 19, 2013
I am trying to use the SUMIFS function to sum the cells in one column if the cells in a reference column ARE blank, and if the cells in a different reference column fall within a date range.
-Column F contains currency, and is the sum range
-Column H contains dates as well as blanks, and I need the criteria to look for cells that ARE blank.
-Column A contains dates, and I need the criteria to be <= 01 Feb 2013 and > 31 Dec 2012
This is the formula I have tried, but it keeps adding the cells in F if the cells in H ARE NOT blank. Clearly, it's an issue with "<>" and probably a very simple solution, but I just can't figure it out.
=SUMIFS('2013'!F9:F59,'2013'!H9:H59,"<>",'2013'!A9:A59,">"&DATE(2012,12,31),'2013'!A9:A59,"<"&DATE(2013,2,1)
View 3 Replies
View Related
Jan 29, 2013
I am trying to create a user form that will allow the user to type int values in boxes. Then when the user clicks the submit button the code needs to add the values from each user input box to the existing values in specific cells accross multiple worksheets. Then the form needs to be cleared after the cell values are updated. I can create the form it is the code on the submit button i am lost on. Also it is important that some boxes may be left blank.
If you click the Grey "Qty Form" button on sheet1 the form will open. User data numbers can be entered in the blank boxes. When the submit button is pressed the form needs to add the user entered numbers to the numbers in the corresponding cells in sheet 1 and sheet 2. How to code the submit button to do this properly. Also after the data on the spreadsheet is updated the form needs to be cleared and start the cursor back in the Item 1 box on the form.
View 1 Replies
View Related
May 1, 2014
I need the macro to look at cells B9:B84 on the Sheet1 tab of the Cost Template. If it finds an x I need it to copy the 3 cells to the right of the x and paste them in a template. For example if it sees an x in cell B9 it would copy cells C9, D9 and E9, open the Purchase Order to the Detail tab, then paste it to cells B3, C3 and D3. It would continue looking for an x down to B84. So if it found 5 cells with x, it would give me 5 instances of the Purchase Order with 3 cells pasted into each.
I've attached my Cost Template and the Purchase Order it needs to copy to. In the Cost Template is a macro called Create_PO. This is what I was trying to alter to make this happen. I can't seem to get it right! FYI in case it matters, I had to change the Cost Template from .xltm to .xlsm in order to upload it on this site.
View 11 Replies
View Related
Mar 15, 2013
I need a macro in a workbook to look at all the files in the same folder that have "*att*.xls" in the name and determine and copy from the range A15:W515 only the rows that have data in at least columns A, C and D. Each file will vary as to how many rows there will be and there are more than the files with "*att*.xls" in the folder. The data will be on the only worksheet in each file and the worksheet is named "G2WAttendee_xls" the data from all the files need to be copied to the file called "Consolidated webinar reports.xls" (I am using Excel 2003) and to a sheet called "Attendance Data" and added to the end of the last paste.
At the start of the macro the current file "Consolidated webinar report.xls" should be saved to a sub folder of the current directory and have the date saved added to the name. The sub folder is called "Completed reports". The data in the original file on worksheet "Attendance Data" should be deleted.
At the end of the process all the files that have had data copied from them should be moved to the sub folder "Attendance reports consolidated" (This could be done as each file is closed if that is easier).
I have headings in row 1 of the "Attendance Data" worksheet that match the headings in the various files in the folder (which will always be in row 14 of the individual "*att*.xls" files).
The folder with all the files and the "Consolidated webinar report.xls" file is at path "Z:P and S MEvaluationsWebinar series 2012-13TB".
View 9 Replies
View Related
Nov 6, 2013
I have a spreadsheet list that has no merged cells. I need to copy that spreadsheet into another spreadsheet where each row is actually 2 merged rows. I thought that I could manually reference the first few rows, then drag them down to copy and hope that excel would be smart enough to pick up the pattern. But it doesn't, and keeps copying over every other row. Is there a way to quickly copy a spreadsheet with individual rows into a spreadsheet with merged (two rows) cells? Seems like it should be doable.
View 6 Replies
View Related
Nov 22, 2013
I have cells that all reference from a 4x147 table, each row has one piece of data in, but only one piece from a choice of 4 cells (3 of which are blank).
the cells that are referencing this data, i would like to make them reference to all 4 cells, ignore the blanks and choose the one with the data.
View 1 Replies
View Related
Jan 22, 2014
how to use cells to create refrences in a formula so I don't have to hand write each refrence in a cell. I wanted to do this by creating a row with accending numbers (1,2,3,4,etc.) and a column with decending characters (a,b,c,d,etc...). Can this be done?
So for example I have a cell formula that reads the following
'Raw Data'!A2
Can I modify this formula so that the formula will automaticly refrence a different letter that corresponds with the cells location? So I would use this cell to change the (A) automatically depending on where the cell is in the spreadsheet. Likewise can I do that with the (2) the same way?
Does this make sence, or should I just continue to hard write each cell?
The reason why I need to do this is because the raw data is in a single column and the formulas that I need to use this data for needs me to convert the data into rows instead of columns.
View 7 Replies
View Related
Dec 7, 2008
I paste a weekly information sheet into a workbook. I pull information from cells within the sheet to form a weekly report. The sheet I paste is identical in columns but not always identical in rows; sometimes it may not have any information on one of the accounts. That account will be deleted and the sheet is 10 rows shorter. The sheet is over 1000 rows so it is too time consuming to add and delete the rows each week to match. My guess is I need to use VLOOKUP & OFFSET but I cannot get any of the formulas I tried to work. The account number appears on each 10 lines for that account. I have made up an example workbook to give an idea of what I need.
View 2 Replies
View Related
Nov 20, 2009
i have this formula:
=COUNTIF(INDIRECT(A2&"_RES"),"Yes")
A2 refers to a cell to complete the indirect query (DCA_RES) which refers to another range in the workbook.
Now I want to have count if A2&"_INIT" is "BB" AND if A2&"RES" is "YES"
So that I get the amount of records of resolved queries (Yes) that match my Initials (BB).
I cant think of a different formula that might work?
View 10 Replies
View Related
Jul 29, 2008
I am involved with regional sales and have developed a spreadsheet to track various statistics and information regarding the various cities and clients.
On Sheet1 I have Column A for the city names. Columns B through R are various statistical information (all numerical) associated with that city.
I would like to use Sheet2 to quickly draw that information into a generic "printable" spreadsheet such that I type a city name into Sheet2 A1 and the rest of the cells automatically draw the information for that city into their respective cells.
Looking through other threads I thought that maybe VLOOKUP would be the ticket, but it's only returning a #VALUE! error.
View 9 Replies
View Related
Feb 6, 2012
Essentially 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.
View 5 Replies
View Related
May 23, 2013
I need the syntax that allows me to reference the cell directly beside my target cell for each row in a column
Like:
Col A Col B
S D
T F
S F
If i was targeting Col B
View 6 Replies
View Related
Jul 8, 2014
i have a code to save file referencing to a certain cell on a sheet, but how do i reference to 2 of them, since one is for a month and the other one is for year:
ChDir "X:SSS2014" ActiveWorkbook.SaveAs Filename:="X:SSS2014SSS Report* " & Format(Sheets("CP").Range("D3").Value, "mmmm yyyy") & ".xlsm", FileFormat:=".xlsm
so i have D3 and F3 to reference to together?
View 3 Replies
View Related
Apr 3, 2007
I've got an error whereby if i open a new workbook and enter the formula "=Sheet2!A1" in cell A1 on sheet1, Excel throws up a circular reference error. If i then try to select sheet2, excel highlights both sheets 1 and sheet 2 (i am not clicking incorrectly here).
Excel then thinks that i've entered the formula into the second worksheet - it dissapears from the 1st and the circular reference makes sense. In other words, it appears that i'm not actually entering a forumla on the hightlighted worksheet.
This was happening in Excel 2003 so i updated to 2007. I'm guessing this is something to do with an incompatibilty with another application but i can't work out what i might have installed. Does anyone have any idea of apps which might cause this type of error?
View 9 Replies
View Related
