Referencing Cells From Another Sheet, Dealing With Blanks
I have a sheet which I want to populate from another sheet within the same workbook.
I am using the following, where Data = original worksheet, T2=cell to be displayed:
=Data!T2
When i do this for the whole spreadsheet, where there are blanks it is entering "0" or in the date fields, 01/01/1900.
Is there any easy way of defaulting them to be blank if there is no content in that cell?
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Dealing With Cells That Contain #N/A
Cells A1:D1 contain the following data and have been derived at via a calculation: A1 = 100 B1 = 150 C1 = 125 D1 = #N/A etc. etc. Under certain circumstances, I force the #N/A value to appear rather than have a blank or zero value (the data is used in the production of a line chart). I would also like to sum A1:D1 (although it could be A1:IV1 - If you know what I mean). I have tried the following but it does not work. {=SUM(IF(ISNA(A1:D1),NA(),A1:D1))} Can anyone explain why ? and what I need to do to correct it ? Interestingly, the following does work but the calculation returns a Zero value if all four cells contain #N/A (I would like it to return #N/A) {=SUM(IF(ISNA(A1:D1),0,A1:D1))}
View Replies!
View Related
Referencing Cells In Another Sheet
I need assistance referencing cells in other sheets. I have a summary tab which I would have each refenced sheet listed (named for the activity being billed). In a separate cell in the summary tab, I want to bring back the value of the total amount billed for that activity. The total value is in the same cell of each sheet, as all invoice tabs needing to be referenced are identical in layout. There is one hitch to this, the sheets referenced have not yet been created (that would be too too easy), and the activity list in the summary sheet has place holder names that will change when the activity being billed is defined (hence prompting the biller to create the sheet to be referenced).
View Replies!
View Related
Referencing Cells Dependent On Sheet Name
I have a workbook that has a number of sheets with various project stages and dates on them. These details are arranged in small tables displayed vertically down the page. Each worksheet's name is the project code - e.g. 30033_99 I have now been asked to create an overview page that displays all these stages and dates on one page. What I'm trying to do is get Excel to look at the column with the project code and then pull information from the worksheet with that name.
View Replies!
View Related
Macro Dealing With Images
I am in serious need of a macro that will search a folder for image names and replace them witha determined image name col. If this is possible it would be a live saver. For an example Skus.................Image Name from File (Imported)........Renamed Image 725564..............725564.jpg.................................."Text that I insert" 894646..............atol-894646.jpg............................"Text that I insert" 713246..............713246-atoll.jpg..........................."Text that I insert" So baically I need images to be searched by sku and replaced with determined image name from a different column.
View Replies!
View Related
Dealing With Negative Time Formatting
It seems that time (i.e. -1:00) will be default as #########, etc. This makes me very unhappy. How to get around? I could be fine with converting time to a total in seconds (i.e. 1:00 converted to 60 seconds)... but I'm not sure what kind of formula could do that.
View Replies!
View Related
Referencing Sheet Tabs
Is there a way to use the "value" of a name in a drop-down list to reference a sheet tab name in a formula? A1 has a drop down list. When a name (Bob's Sales) is selected from the drop down list, B1 shows the value of cell D5 from sheet "Bob's Sales".
View Replies!
View Related
Referencing A Sheet From An Inputbox
I have a quick question that'll hopefully have a quick answer. Part of my workbook requires an input box to appear. When you type the name of one of the worksheets into the box and hit enter I want a VLOOKUP formula I've written to compare to that worksheet. Heres what I mean, with some descriptions of what i would like:
View Replies!
View Related
Referencing Another Sheet In Same Workbook
I have two sheets in the same work book and I want to pull values through from one to the other. So its a simple =Sheet2!B45 or whatever yeah? But no! What Excel 2007 does when I hit enter is to swap the Sheet!2 bit for "C:documents and Settingsmy documentsspreadsheetsmyworkbook!Sheet2!B45" or some such. Why the hell does it do that and how can I stop it.
View Replies!
View Related
Vba Sheet Referencing
I have 8 sheets, named cpt1 to cpt8 in vba. Now i need to loop through these 8 sheets, storing the same range of each sheet into a matrix. I have the following dim test () as variant For i = 1 to 8 (1) test = sheets(i).Range("b2:u21") (2) test = cpt1.Range("b2:u21") next i Now (1) doesn't work, but (2) does. However i do not know how to reference the cpt1 sheet name using the for loop (ie "cpt" & i) Can anyone help me with getting this to work?
View Replies!
View Related
Highlight/Flag Formula Cells Referencing Other Cells
I am trying to write a macro which highlights all the cells with any reference to other cell. The macro should highlight a cell with formula like = 3 * A25 but not highlight a cell with following formula. = 3 * 0.4535566. 'Hasformula' property is not useful here as it can't distinguish between above examples.
View Replies!
View Related
Formula Referencing Previous Sheet
After a formula to copy the contents of a cell in the previous sheet in a workbook, so that if I was to copy the last sheet in a workbook the formula would automatically reference the cell from the copied worksheet and so on if I copied tht one. Hope this makes sense I have a lot of formulas referencing the previous sheet and everytime I copy this sheet to create a new sheet I have to change the sheet number in the formulas. eg, in sheet 8 this formula get info from sheet 7 cel J30 ='7'!J30 when i copy the sheet to create a new one (Sheet 9) I would like the new formula to automatically be ='8'!J30.
View Replies!
View Related
Vlookup - Referencing Sheet Index
I am trying to find out whether it is possible to reference to a sheet index number, instead of sheet name, in a VLOOKUP formula. For instance, normally I would write: VLOOKUP(A1,'ExampleSheet'!A1:B10,2,False) (I hope got the translation to English language Excel correct) Let's say that the sheet index of Examplesheet is 2 (at least it is 2 the way VBA sees it). Is there a way I could reference sheet number 2 in the VLOOKUP formula, instead of its name? My sheet names vary, but the formula should look in the same area, regardless of name.
View Replies!
View Related
Referencing Active Cell's Value From Another Sheet
I'd like for users to click in a cell in col A in a sheet named "Period7", have the cell value placed in cell C11 in a sheet named "Per7Report," and change the focus or active sheet to sheet Per7Report. Can this be done simply by clicking in a cell, or must a command button be inserted into each cell (note - odd rows only) in col A in Period7. on the code to do this? Note that there is a formula in col A in sheet Period7
View Replies!
View Related
Referencing Sheet Code Names By Variable
I have a set of worksheets (Sheet31 through Sheet49 by codename) produced by a machine in the lab. I want to create a summary sheet that references cells in those worksheets. Rather than go through the annoyance of doing this manually 40+ times I'm working on creating a macro to populate the summary sheet for me. This is what I have so far For J = 31 To 49 ResultSht = SheetJ.Name Sheet1.Activate ActiveSheet.Range("A4").End(xlDown).Offset(1, 0).Select It hangs up on the "SheetJ.Name" however. My objective is to be using the object SheetJ, where the object SheetJ is the codename of the results sheet I'm trying to reference (for example, in the first pass through the loop it would be Sheet31). Could someone point out my mistake in this object reference?
View Replies!
View Related
#REF! Error Referencing Worksheet After Importing Sheet
I have a template i'm making that is full of formula references to sheets not yet in existance in the workbook. what i want to happen is this: My code imports data files, renames the tabs to 1, 2, 3, 4... The formulas i already have are set in the CONCATENATED tab.... an example of such a formula is: ='1'!$P2. so this should work, and yet it doesn't. the sheet is imported as it should be, the tab is renamed to 1, and yet the CONCATENATED sheet still shows #REF for the cell value. if i click in the cell, click in the text bar and enter out, the formula updates. there apparently is no option that i KNOW OF (although i'm sure there is something out there that does...) that will update this without me entering into the text bar and entering out. Question: how do i manually update all references once the sheets to those references have been imported?
View Replies!
View Related
Blanks Cells Treated As Zeros
i am creating a football prediction sheet, and have a problem where blank cells are treated as zeros. The enclosed spreadsheet shows the formulas in green working fine, but the blanks are treated as zeros in cells j6, I7 & J7.
View Replies!
View Related
Selecting Cells Between Blanks For Use In Graphs.
I'm trying to write a VBA code that'll select cells in column B that contains text until it comes to a blank row, the amount of data changes each time, i've tried two different approches but i'm unable to get it to hold the cell, it only moves down one cell each time i run the macro, no errors though.
View Replies!
View Related
Replace Blanks With Adjacent Cells
I use an old accounting program to download sales history into excel. For sales where there is only one line item and therefore 1 row, there is no problem. Sales with multiple items show a summary of the sale in the first row and the line item details in rows after. On multiple item sales (shaded in yellow), the total postage is shown in the summary row (shaded in orange). The postage fields in the line items are blank (shown shaded in blue). I need to split the total postage by number if line items and put the value in the line items. i.e. if the total postage is $10 for two items, I need to show $5 for each line item. The summary row identifies the number of line items in the "Qty" column ( cells in green). This cell could be used to divide into the postage (in orange) and also count how many rows below to put the answer in
View Replies!
View Related
Referencing Cells On Different Sheets
I currently have 3 sheets 1, 2,and 3. Data is input onto sheet 1, this is copied into sheets 2, no real problem there. However i need to enter data onto sheet 3 and if that data appears on sheet 2 i want the line, not just the cell to be coloured on sheet 2. The data on sheet 3 will not be in any specific order in the column I have tried conditional formatting but keep getting a message to say it cant be done and offering a formula which i dont understand.
View Replies!
View Related
Not Referencing Cells Correctly
I'm trying to simply reference a cell from another tab in the same workbook. In other words I press "=" and then point to the cell in another tab and I would expect to see that value - however I don't. What I see, as an example, is: ='Nav&SPDiary'!B24 instead of the cell value (which I know exists because I've just pointed to it. Anyone have ANY idea why all of a sudden it's started to do this? The most annoying thing is it worked fine before and now it doesn't do what I want...
View Replies!
View Related
Referencing Non-blank Cells
Say I have a column with cell values 4, 0, 0, 5, 0 , 0, 0, 2, 0, 3 What I am trying to do is have in the next column a formula which returns the number adjacent to it multiplied by the number of blanks between it and the next non-blank cell. So, for the example above, the output would be 8, 0, 0, 15, 0, 0, 0, 2 etc
View Replies!
View Related
Referencing Blank Cells
I would like a formula that checks for blank cells only between C1:C10 and E1:E10 and then insert the first and last names of the person associated with these blank cells (which are located in A1:10 and B1:B10) on another worksheet, which for the sake of argument can be called 'sheet2'.
View Replies!
View Related
Limit On Referencing Cells?
For example in this formula I typed in WWW as the max: =SUMPRODUCT(($F$5:$WWW$5>=C2)*1,($F$5:$WWW$5<=C3)*1,ABS($F$6:$WWW$6)) If I try to type in a value higher than WWW it will give me an error. I was just trying to put in an amount that I would never hit within the row.
View Replies!
View Related
Referencing Empty Cells?
I have set up a spreadsheet in order to track and calculate the number of students we teach at our organization. It is running prohibitively slow when entering new data. On one sheet each individual session is entered (the school, program, date, number of hours etc.) This is the Session worksheet. On the next sheet(the Program worksheet) the maximum number of students for each unique program (this is usually a combination of school and the program) is calculated to ensure we are not counting students we teach weekly as new students. This is calculated using a formula: :{=MAX(IF(Session!G:G=Program!A2,Session!D:D))}. "Session!G:G" = the unique program name on the Session worksheet. "Program!A2" = the unique program name on the Program worksheet. "Session!D:D" = the number of students in that individual session...........
View Replies!
View Related
Referencing To Cells Next To A Given Cell?
My current worksheet is being built for use in Eve-Online, a game I play. In specific, I am keeping track of profit made by manufacturing different items in the game. I am trying to build it like this: Column 20 is a list of items, and the next 7 cells to the right of each item contain the mineral requirements for that item. So C20 says Bantam Frigate, C21-C28 will all contain numbers. Now, I am trying to create a function in the upper-right cells (like A1-A8) where if I type in "Bantam Frigate" into A1, A2-A8 will fetch the numbers that I put into C21-C28. Now, I will be working with thousands of items, so I have to make sure I name both the 'database' and the query exactly right, but that's not a problem to me. Can I make something that will reference a value from (cell+1 column right) or (cell+2 columns right)
View Replies!
View Related
Concatenate Cells, Ignoring Blanks, Then Trim
=IF(Z2="","",Z2&","&IF(AA2="","",AA2&",")&IF(AB2="","",AB2&",")&IF(AC2="","",AC2&",")) I'm using the above formula to join text from columns Z through AC, separated by a comma. I now want to remove the comma at the end of the new string. Also, I would prefer it if the four cells were separated by a slash rather than a comma, but when I simply replace the commas in the formula with slashes I get an error.
View Replies!
View Related
Counting The Number Of Instances Between Blanks Cells?
I like to think that I am pretty good on Excel 2003, can't stand 2007 but hey thats for another thread. I can do most forumulas and write some pretty elaborate macros, but one thing I am trying to do has completely stumped me. Each month I compile a Pivit table that shows by day if a product was in stock or out of stock, this is represented by a blank cell (in stock), "1" Out of stock. All this is fine, but I want to be able to show the number of instances that a product was out of stock in any given month...
View Replies!
View Related
Referencing Cells From Microsoft Query
I have a worksheet that pulls data from an ODBC datasource (import, External Data). The datasource is a SQL server 2005 database. the query's sql is complex enough that it says it cannot be represented graphicly. part of the WHERE clause fro my query specifies a date range for one of the date fields. I am wondering if from within the SQL query in Microsoft Query I can reference the value of a cell. that way i coudl have the user enter his date range values in two specific cells.
View Replies!
View Related
Referencing A Row Of Cells In A Column
I have a row of cells on one "Sheet1." I am trying reference these cells into a column on "Sheet2." I know I can reference one cell at a time, but I have a large number of cells in the row on Sheet1 and I know that there has to be an easier way to do it, I am just not excel-savvy enough to know what it is. I know you can copy and "paste special" (transpose), I am just wondering how to do that while referencing. I have heard that the "offset" function can be used, but after extensive trial and error, I haven't figured it out.
View Replies!
View Related
Referencing Numerous Cells/columns
Let's see who's got the logic. What I have: Column C containing either 1 or 0.5 or "" Column E starting at E9 which may contain "deal" or a few other things Column I starting at I9 which may contain "here", "away", or "no" Cell J4 which will contain a number........
View Replies!
View Related
Referencing Merged Cells In Formulas
Take the following simple sheet. My data is in column A, and includes some merged cells. I place the formula INDIRECT("RC[-1]",0) in column B, and copy it down the sheet. I get the result as shown in column C, but I would like the result shown in column D! | A | B | C | D | --|-----------|------------------------|-----------|-----------| 1 | Bob | =INDIRECT("RC[-1]",0) | Bob | Bob | --|-----------|------------------------|-----------|-----------| 2 | Harry | =INDIRECT("RC[-1]",0) | Harry | Harry | --|-----------|------------------------|-----------|-----------| 3 | | =INDIRECT("RC[-1]",0) | Charles | Charles | | |------------------------|-----------|-----------| 4 | Charles | =INDIRECT("RC[-1]",0) | 0 | Charles | | |------------------------|-----------|-----------| 5 | | =INDIRECT("RC[-1]",0) | 0 | Charles | --|-----------|------------------------|-----------|-----------| I realise WHY this doesn't work (as Excel refers to the merged cells A3:A5 as just A3, and A4 & A5 don't essentially exist any more), but I would like to know if there's a way to get around it, and achieve the result in column D.
View Replies!
View Related
Referencing Cells By Page Break
I have a macro that uses subtotals on a dynamic list to generate page breaks by Department. Rows 1:6 of the table are set to print at the top of each page and column A is not set to print at all. Would it be possible to get cell B6 to return the value of the first cell of each page break (from column A) when the page prints. (Column A contains the department names, and though I don't want to print the column itself, I would like the name of each department to appear at the top of the page.)
View Replies!
View Related
Countif -- Referencing Criteria From Cells
I want to be able to count the number of cells which meet a specified criteria. The COUNTIF function works if I enter the criteria manually within a COUNTIF formula. But the function doesn't seem to work if I try to reference criteria from another cell. For example, suppose the range of cells I'm searching for matches within has been named Possible_outcomes . In addition, the criteria I'd like to use is in a cell called PROFIT_THRESHOLD, and is the result of a formula. Let's say the formula result equals 130. Countif works when I enter the criteria manually, i.e. =COUNTIF(Possible_outcomes,">=130") But it returns only a 0 when I write the formula as =COUNTIF(Possible_outcomes,">=PROFIT THRESHOLD") I can't make the function work even if the criteria is in a cell which is not named either. This is a serious obstacle to model automation.
View Replies!
View Related
Copy Cells To Adjacent Column, Removing Blanks
In a worksheet, J2:J52 contains a list of unique strings; each cell populated by an embedded formula. Some of the cells contain a valid 'blank' "" given by the formula. I'd like K2:K52 to contain the values J2:J52, with all the populated cells stacked at the top and the 'blank' cells at the bottom. 'Blank' cells do not have to be in any particular order. I've been fiddling with the formula kindly provided by WindKnife on the second post in the thread below for the past couple of hours, but to no avail: http://www.excelforum.com/excel-work...om-a-list.html Columns A:H in the same worksheet are populated and unavailable for use.
View Replies!
View Related
Macro To Add 2 Blanks Cells Below Cell With Words
REPLACE ENGINE OIL & FILTER, VEHICLE INSPECTION, FILL FLUIDS RESET THE OIL REPLACEMENT REMINDER LIGHT. (blank row ) (blank row ) E I have an excel file with many columns containing information at the end of each column like this. It lists car verbage, then 2 blank cells are required , followed by an E. I was wondering if a macro could be used to where it looks at a column, finds where the verbage ends, and automatically inserts 2 blank cells below it followed by an E. In other words it needs to make sure only 2 blank cells are after the verbage and an e should follow in the cell below. If the column has 3 blank cells below the verbage it should fix it and add the E at the appropriate space.
View Replies!
View Related
Linked Sheets, Sort Cells, Blanks To Bottom
I'm using links to pull our sales prospects from our salesmen's individual sheets into one master sheet for reference. Once the prospect information arrives in the sheet, I need it to sort alphabetically by customer name so that we can see which customers have been visited and don't visit the same customer twice. So far in VB, I've been working on this Private Sub Worksheet_SelectionChange(ByVal Target As Range) Range("A5:L" & Cells(Rows.Count, "L").End(x1Down).Row).Sort Key1:=Range("B6"), Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub But it's not sorting the way that I need to. All I really need is for the sheet to sort automatically by Company Name and for all of the 0's to go to the bottom. They are 'blank' data that will eventually be imported from the salesmen's individual sheets as they encounter new customers.
View Replies!
View Related
VLOOKUP To Miss Blanks (the Dots Are Blank Cells)
I have about 90+ lists of data which looks like this: (the dots are blank cells) Set 1 D0 Jimmy ... D1 Keith D2 Dave ... ... D3 Sandra Set 2 ... D0 Andy D1 Ryan ... ... ... ... ... D2 Lee Each list was originally a set of names and i have populated the "D1, D2" cells (where D1 = the first name, D2 = the second, etc). Is there any way, on a seperate sheet i can get a list which shows the following (ie. no spaces between lines of data): Set 1 Set 2 Jimmy Andy Keith Ryan Dave Lee Sandra Is this possible through a VLOOKUP?
View Replies!
View Related
Linked Sheets, Sort Cells, Blanks To Bottom ....
I'm creating an excel sheet that is linked to several others. Basically my salesmen will enter their new prospects into their own sales sheets and that will transfer via a link to my main one. The problem I'm having is mainly with my sort. When the information is transferred, I need the sheet to auto sort the Company names alphabetically and move any blank cells to the bottom.
View Replies!
View Related
Referencing Selected Tabs To Print Selection & Update Table Of Contents Sheet
I searched but didn't find exactly what I needed. I have a workbook with 31 sheets. It is a price guide with each category on a separate worksheet. I figured out how to list the sheets on a separate tab. What my client wants is the ability to: 1. select certain categories for printing, the ToC, Cover and backcover pages have to print in every case 2. the ToC has to change depending on the sheets selected. I'd rather write some code and give him an an easy command button rather then teaching him how to select non-concurrent sheets and printing only active sheets. What I'm really stuck on is the updating of the ToC with active sheets only (category and starting page which changes depending on pages selected).
View Replies!
View Related
Creating Sheets While Referencing A New Row In Some Cells
I am trying to find a way to create new sheets in a workbook that when created will reference a new row on a data sheet in that workbook. Each workbook could end up containing varying numbers of sheets (anywhere from 2 to 150+) based on size limitations. There are some lookups and referenced data on each sheet that would be copied as is and not reference the data sheet. I have not included these in my example. I'm trying to avoid having to change each cell's reference manually for all of these sheets (which is one of the solutions I've come up with so far). My current method of creating these workbooks does not involve the data sheet and each workbook starts off with data only on sheet1. I select and copy that sheet into sheet2 and every subsequent sheet. I want to incorporate the data sheet so it can be filled out by someone else and copied into that tab to populate all of the data sheets.
View Replies!
View Related
Formulas Referencing Cells Stopped Working
I made a spread sheet last year that used various COUNTIF formulas that referenced other cells. =COUNTIF(I40:ER40,"A") It worked perfect. I copied that spreadsheet to use for this year. I cannot get this formula to work. I have reentered in various locations and forms, but to no avail. If I enter the data and then enter the formula, it works. However, if I enter the formula first and then enter the data (which I will do over the course of the year) it does not work. I even tried to reconstruct all of this in a brand new spread sheet and it still is not working, although last year's spread sheet still works.
View Replies!
View Related
Cross Referencing Cells To Input Values
I am attempting to cross reference sheets to match up IPs to building names. I have attached an example. Essentially, I am trying to take column E on the TEST sheet and match it to column A on the IPs sheet. Whatever the Building Name (column B, IPs sheet) is, I want that filled into Column G on the TEST sheet. This would be a very labor-intensive process if it cannot be done automatically.
View Replies!
View Related
Sumif Is Referencing Blank Cells And Providing A Value
How do I get 'SUMIF' to ignore blank cells? sumif(a1:a10,h1,b1:b10) - then fill down a= ID number h1 = ID number b= cost info eg 'matt' in 'h' is finding 'matt' in 'a' and reporting on the cost info against that name in 'b', but in the middle of the list a blank is matching a blank and returning a subtotal which is in the cost column. I want to put sumif(a1:a10<>"",h1, b1:b10) but this doesnt work!
View Replies!
View Related
Copy Cells To Empty Column Each Time And Remove Blanks
Is there way i can have a macro attatched to a button that when clicked, will copy BX:BX on sheet1, remove all the blank cells and put it on D:D on sheet2. Next time it is clicked, it will put BX:BX on to the next available column after removing the blanks and so on, filling a column at a time on sheet2? If the above is easy, it would be really good if it could paste into columns until it reaches column H and then go back to D again but paste below the data already there, then E, but below, etc. I think though that the last bit is asking a bit much and is just cosmetic and easier to print, so dont worry too much.
View Replies!
View Related
Referencing Multiple Cells From Inside A Single Cell
I have 4 cells with simple data in them. In another cell, I would like to make a phrase and include all the data from the 4 cells in that phrase. Example: ="Jackpot: &G2 (&G1) / &G3 = &G4 each" I already tried this: ="Jackpot: "&G2" ("&G1") / "&G3" = "&G4" each" Am I missing something? I'm pretty sure this is doable, I just don't know what's missing.
View Replies!
View Related
|
TRACKER
