Sort Page (Sheet 2) #REF! In Cell With IF Formula To Sheet 1
Oct 25, 2009
Sheet 1 is my main page containing all my data, full of formulas and is protected. Sheet 2 is a simple sorting page with no color and is simply for sorting and printing.
Sheet 2 has IF formulas refrencing rows and cells on Sheet 1.
Example Sheet 1 A2= Bob
Sheet 2 A2="" until I put in this formula:
=IF(Sheet 1!A2="","",Sheet 1!A2)
Now Sheet 2 A2= Bob
Simple...
But when I delete a row in Sheet 1 (using my cool new macro...long story)
I get #REF! in the cells on my sorting sheet, as I should I guess, because there is no more row there to reference. It's annoying, but what do I do to fix it without just unchecking the #REF! in autofilter on Sheet 2.
I just want to eliminate #REF!
Do I need to put something else in the IF formula?
View 9 Replies
ADVERTISEMENT
Jul 15, 2006
I am trying to add one cell from every sheet of my workbook to the first page. I have renamed all the pages, and I don't know if that is screwing things up, but I just want to do a simple SUM on one page from a single cell on all the other pages.
View 3 Replies
View Related
Mar 1, 2008
I'm not sure went wrong but my code was working fine until I protected the worksheet. Then I made some changes to the code by adding code to unprotect the sheet before running the code and then protecting it again when complete. That didn't work. I unprotected the sheet and ran the code again and now I'm getting a Run Time Error and the code stops when it attempts to set the Page Breaks which was previously fine. Because the data I use to generate this report is based on data from another report that does not need to recalculate before running. I have a Worksheet Activate Private Sub that sets the Application Calculation to Manual. When the sheet is deactivated it sets it back to Automatic. Hence the line in the following code to calculate this worksheet. When attempting to debug the line that highlights is the first line that attempts to set the page break that corresponds to the matching location criteria from cell A2.
Private Sub Worksheet_Change(ByVal Target As Range)
Application. ScreenUpdating = False
Application.EnableEvents = False
ActiveSheet.ResetAllPageBreaks
Range("A24").Value = Range("A1")
Range("A25").Value = Range("A2")
Range("A26").Value = Range("A3")
Range("A27").Value = Range("A4")
Range("A28").Value = Range("A5")
Range("A29").Value = Range("A6")
Range("A30").Value = Range("A7")
Range("A31").Value = Range("A8")
Range("A32").Value = Range("A9")
Range("A33").Value = Range("A10")...................
View 4 Replies
View Related
Jul 4, 2014
i want to Sort by column E Type,which i can do manually easily.but then i want to run a macro that will copy all the rows with values,ie.Express Checkout Payment Received etc to another worksheet and name that worksheet with Express Checkout Payment Received.
View 1 Replies
View Related
Mar 29, 2013
I have a sheet that I would like to print to one page (11x17, landscape) and have it take up the majority of the page. The largest the sheet will ever be is data in rows 1 - 71, and columns A - AC. Right now I have it set up so that when i click on the button, the print dialog opens up and no adjustments are needed to print to pdf in 11x17 for the maximum amount of data.
Overall what I am trying to do is have the zoom change when rows are deleted (columns will always be A-AC). The top 5 rows and bottom 5 rows will always be there. I want the top and bottom margins to be the same on the pdf even if 10 rows are deleted in between.
View 1 Replies
View Related
Mar 3, 2014
Is there a way to change the page view without activating the sheet? I would like to loop through a bunch of sheets and change the page view.
I currently am aware of the ActiveWindow.View command, but this requires me to first activate the sheet. Is there a similar Sheet.View command, or something like that?
View 3 Replies
View Related
Jun 16, 2007
I'm trying to figure out for my boss also I've done some search in this forum but no avail. He want each page to have large faded page number in the background like 'Page 1', Page 2' etc(there's 12 pages in one sheet) for presentations but do not want the page numbers to appear in the printed copies. Is it possible? I know about the watermark but he does'nt want page numbers in the prints
View 3 Replies
View Related
Jul 7, 2014
refer to attached file.
I have monthly sheet Jan,Feb,Mar.....Dec.
I also have Summary Sheet, Cell A85:C96 is labeled as Jan,Feb.....Dec (Vertical) Cell B84:E84 refers to Store1,Store2,Store3 and Store4.
I need a formula to summarize the monthly value for each storein row 60.
View 2 Replies
View Related
Apr 6, 2010
I'm working with a very large spreadsheet which has somehow divided itself into print areas. Each section has 'Page 1' or the equivalent as a background, behind the data itself. I thought it would be simple to remove this, but for the life of me I can't see how. I'm using Excel 2007.
View 6 Replies
View Related
Jun 5, 2014
I would like to be able to display all of the sheets in a workbook as a list on the front sheet.
I have a front "Contents" Sheet (attached at the bottom of my post)
In the "Ticket No." column I wanted to have the sheet name, in the "Date" and "Description" column I wanted to display cell values from each sheet; which would be the date and description of that sheet/"Ticket". The "Resolved?" column I will be able to do after I have this part figured out.
The workbook is also attached :
Attached Image : contents.jpg
Attached Files : Troubleshooting Tickets.xlsx
View 14 Replies
View Related
May 14, 2009
I am trying to sort a macro out to save my sheet the same as the original when the button is clicked. When i try it from one location it is fine but from another location i get a print error, the error says prit quality is not right, when i debug it highlights: Print Quality = 600 I have tried changing it but keep getting the same error. Their maybe an easier way to do this but not being very good on VB i dont know what to do. This file is on a server and will be accessed from different locations.
View 2 Replies
View Related
Apr 4, 2013
I need a formula to add two (2) cells (one cell in sheet 1 and the second cell in sheet 2) and return a value of the two (2) cells in sheet 3. I would like the formula to return a zero (0) value instead of #VALUE! ERROR displayed in sheet 3 if a cell in sheet 1 has zero (0) value and the second cell in sheet 2 is blank. For example: sheet 1 D2 (0) + sheet 2 D2 (blank cell) = zero (0) instead of #VALUE! ERROR
In addition, if cells B11:B12 are blank in both sheet 1 and 2, I would like the formula to display blank in sheet 3 for cell C11:C15 instead of displaying #VALUE! ERROR.
Here is the formula I have: =IF(OR(ISBLANK(Sheet1!D2),ISBLANK(Sheet2!D2)),"",Sheet1!D2+Sheet2!D2)
Note: I have attached a sample data set for better conceptualization of the problem. Sample_Formula.xls
View 2 Replies
View Related
Dec 4, 2007
I have worksheet1 with 200+ customers and 12 months of data for each customer. I want to link each month to a separate worksheet (worksheet2, 3....12) for all customers. (i.e., customer 1's January activity across row 1, customers 2's January activity across row 2.....)
Is there a way for me to create a formula or copy comand to link row1, worksheet1's data to row1 worksheet2 and then every 13th row in worksheet1 to row2, row3...in worksheet2?
Right now I'm just putting the = in worksheet2 and mousing over to worksheet1.
View 9 Replies
View Related
Jul 20, 2008
I teach in about 30 student a class I have in a sheet their names in column A columnB their mark the third columnI want to put a b c : A for excellent b for good c for bad I want these to be sorted into the other sheet
sheet2 contains all of the studnt with the A
sheet3 contains all of the studnt with the b
sheet4 contains all of the studnt with the c
View 9 Replies
View Related
Dec 21, 2012
I am trying to set up excel sheets for documents that we print often. I would like to print them with page numbers in right footer that increases sequentially.
setting up a macro. But very time I print it starts from same number instead of the number after where I left off. Example: if I print today with page numbers 1 through 12, next time I want it to print number 13 through whatever no of copies printed.
Here is the code I copied from one of the threads here:
[Code] .....
I need the page number in right footer and also the macro to save the last number printed.
View 3 Replies
View Related
Sep 18, 2013
I have a workbook that has a single sheet in it, in cell G2 I want to insert a page number where if I print of multiple copies on the first sheet it will say 1, the second 2, the third 3 and so on and so forth.
I have never written any codes for Excel.
View 4 Replies
View Related
Feb 26, 2009
I am trying to write a macro that publishes the print area as a non-interactive web-page to a file that sits in the same location as the spreadsheet from which the macro is being run.
I am using the the following macro:
View 14 Replies
View Related
Feb 28, 2012
I am trying to dynamically set my print area and page breaks on a large sheet.
Basically I want to get the last column and last row in used in the sheet. And to create boxes (24x30) until I get all the way horizontally and vertically.
View 2 Replies
View Related
Jan 3, 2013
I am after some VBA code that chooses each value from a drop down list (in cell A1) then copies a separate sheet 1 (sheet 1) into another workbook (can have both workbooks open at the same time or the first step will have to create a workbook) with the name of the added sheet renamed to the value chosen from the list. When the second value is chosen then sheet 1 should be copied into the newly created workbook as well.
Sheet 1 has lookups that are updated when the new value in the drop down list is chosen
There are around 85 values in the drop down list so I'm expecting a workbook with 85 tabs (copy pasted values and formats)
View 1 Replies
View Related
Jan 4, 2013
I am trying to 'sort' rows of data into another sheet in the same excel file. For instance, if i have a row that contains the string "SDMH" then it should be sorted to "Sheet2". In the original sheet, the data does not need to be removed, just copied.
View 4 Replies
View Related
Jul 4, 2014
I have a WorkBook with many sheets.
My first Sheet, say Sheet1 has on Column A, the names of the differents Sheets, eg:
A1: Country
A2: Spain
A3: Italy
A4: France
On my Second Column I need a Formula to get:
B2 = I need a formula to get me Spain!A15
B3 = I need a formula to get me Italy!A15
B4 = I need a formula to get me France!A15
But to do this ovbiously I would like to use the country names I can get from the Column A at present A2:A4 = Spain, Italy, France
View 1 Replies
View Related
Jun 23, 2006
I have this formula in cell C5 of a worksheet. =HLOOKUP(C2,OB_RB_MBRSHP_2002!$B$25:$M$35,11,FALSE). It is one of several formulas in a block of data referring to worksheets ending in 2002 (the year). Is it possible to take the OB_RB_MBRSHP_ part of the formula and use the 2002 part of the formula which is shown as the year in a C1l and create a formula without using the the OB_RB_MBRSHP_2002 in the actual cell? i.e. =concatenate("OB_RB_MBRSHP_2002",C1) in say c4
Then have a formula like this: =HLOOKUP(C2,C4!$B$25:$M$35,11,FALSE)
I know this method doesn't work because I have tried it, but I wondered if there was another way. My reason for asking is that this data is copied down on a regular basis and it would be far easier to change the year in one cell than to change all of the 2002 references to 2003 for example.
View 2 Replies
View Related
Apr 26, 2007
I havenamed my sheets with a date in the format of "mmm-yy".
I need a formula to bring this active sheet label (date) into a cell.
View 9 Replies
View Related
May 21, 2014
I have created the bulk of the worksheet, and I am now on the summary page. My challenge for the summary is looking up when someone's name appears in Column A on any of the sheets, then summing the hours per week that the person worked.
The main sheets are to track hours, and billable dollars per project, but the summary is to track total hours per team member per week to make sure staff is being full utilized.
In the examples I have attached Aaron's summary should have 13 hrs per week listed. I do not have the foggiest clue how to do a lookup on a name when the position and sheet is random, and return a value summed for each week.
On the summary sheet I did copy rows 1&2 so at least the summary columns match the sheets.
Example 1 is a sample of the detail on 1 of the 5 sheets that in this example could contain Aaron's name in any random place in column A. Example 2 is my hope for the summary page, that will sum those random hours per week matching the headers on the Detailed sheets.
View 1 Replies
View Related
Jan 2, 2009
I am want to copy a formula across several work sheet and have the formula always take data from previous work sheet.
2) I am working with this formula =C12+INDIRECT((MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)-1)&"!"&"C12")
and it comes from this thread http://www.excelforum.com/excel-gene...orksheets.html. I have included a worksheet attachment that has explanations
View 2 Replies
View Related
Mar 25, 2014
I have two Sheets, first is Service Data, the second is Log Sheet. In the Service Data sheet I have a column of serial numbers in column A. I also have a column of serial numbers in the Log Sheet, column A.
I'm looking for a conditional format whereas I enter numbers into column A on the Log sheet it will auto highlight the cell if I duplicate a serial number from row A in the Service Data sheet. I would like it to highlight the Duplicated number on the Log sheet. I know new versions of excel make it easier to do this but unfortunately I have Excel 2003.
View 3 Replies
View Related
Aug 7, 2012
Is the a more efficient way of sorting a huge sheet (One Column only)? Currently, I use the below code and sometimes it throws an error (due to insufficient memory/ cannot undo this step)
VB:
Sub sort1()
Rows("1:1").Select
Selection.AutoFilter
ActiveWorkbook.Worksheets("Sheet1").AutoFilter.sort.SortFields.Clear
[Code] .....
View 3 Replies
View Related
Dec 30, 2013
I have a sheet protected and filters enabled. I use this code to enable sort and filters:
[Code] ........
Nevetherless, when I try to sort, I receive this message in excel: "The cell or chart you re trying to change is on a protected sheet.
To make changes, click Unprotect Sheet in the Review tab (you might need a password)."
View 5 Replies
View Related
Feb 26, 2011
I have a fairly simple sheet that I'm trying to sort (and have in the past) using Data/Sort on 3 columns.
I do not know what a 'merged' cell is, so have no knowlingly formatted a cell as 'merged'. I have populated some cells with information copied from other applications, so they may be 'merged'.
When I try to sort I get the message: - this operation requires the merged cells to be identically sized. to avoid this behavior, unmerge all the merged cella in the range or....
How the heck do I even find the merged cells that are causing the problem?
View 5 Replies
View Related
Apr 15, 2014
What settings should I use in order to allow a user to sort data in a table when some of the cells are locked?
When I protected the sheet I ticked the option to allow user to sort data but it doesn't work...
View 2 Replies
View Related
