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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
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 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 Replies!
View Related
Formatting Cells Dependent On Other Cells Relation To That Cell.
i have just read the thread started by scabertrain regarding "if" conditonal formating, but mines slightly different and i cant make head nor tale of the fomula shown. So... Monthly budgeting... using a projected outgoing (A1) and an actual outgoing (A2) i want the colour "A2" to change depending on whether i have spent over the budgeted amount, green being underspent, orange for on target and red for over spending.... Example: I predicted (A1) that i was going to spend £50 on a night out, but my actual spending (A2) was £100, i would like A2 to turn to red.
View Replies!
View Related
Dependent Drop Down List With DEPENDENT DEFAULT VALUE
Situation: I have two drop down lists 1) Country and 2) States/province Country has list: [US, Canada] If "US" is selected, [Arizona ,Florida, NewYork] is listed in the 2nd drop down list If "Canada" is selected, [Alberta, Ontario, Quebec] is listed in the 2nd drop down list Scenario: First, I select "US" and choose the states to "Florida" Next I change the country to "Canada" and forgot to choose province Then, the 2nd dropdown list is changed to [Alberta, Ontario, Quebec], BUT the current value is still "Florida" Probem: Now I have "Canada" and "Florida" selected in the sheet Question: If the primary list is changed, can I make the 2nd drop down list to show a default value (e.g. blank or the first entry i.e. Alberta)?
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
Cells Dependent Upon How Workbook Is Opened
I have a workbook with about 30 tabs in it. It is currently protected by a password. The workbook itself has a couple of 1000 formulas across the tabs. What I would like to do is to lock certain cells on each worksheet depending upon if the workbook is opened as "read only" or "in edit mode" (when someone uses the password). So when someone uses the correct password, I would still like certain cells to be locked, or after the initial password is entered for editing, another popup box with "would you like to edit cell formulas? Y/N (only on selective cells), if no, then no editing on those cells allowed, if yes, then prompted to input another password to do this.
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
Formatting Cells Dependent On Results From Other Worksheets
I have a workbook with three sheets. Sheet 1 is an original 'in progress' work. Sheet 2 is a copy of sheet 1 taken on a particular date. Sheet 3 is information extracted from Sheet 1. On sheet 3 what I need to do is change the font colour of a cell to red if the information it contains in relation to Sheet 2 is different i.e Sheet 1 cell and Sheet 2 cell are different For example Sheet 1 cell value = Deb Sheet 3 cell value = Deb Copy of sheet 1 taken to create sheet 2 on a certain date Sheet 1 cell value changes to = Bed Need Sheet 3 cell to change to = Bed as it is different from sheet 2 I did try conditional formatting but it will not work with info. from another worksheet that I can see. Maybe someone out there has another solution.
View Replies!
View Related
Dependent/Linked Drop Downs In Cells
Now he has another task that I am not having any success with. He has asked if we can make an Excel spreadsheet that allows us to access a list of customer units from a drop down menu which is in another workbook, and once you choose that particular unit, another drop down menu will allow only those customers who are assigned to that unit to be selected from that sub-menu. I hope that makes sense. Say customer A1 (we will call him Delta Co, 2-1 Aviation) is selected from this first drop down menu (which I haven't figured out how to make yet either). Then this selection would access another worksheet in that other workbook with a new drop down menu with only the four customers in that unit. As an example we will use the names SSG Wilson, SGT Jackson, SFC Jones, and SGT Phillips. So when the Supply Tech here opens the spreadsheet, the first drop down menu, he will select Delta Co, 2-1 Aviation. Then the next menu will only show four names, and he will select SFC Jones. Once we get this going, I may be able to figure out the other things the commander wants, if not I will be back.
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
Deleting Rows That Have Dependent Cells. Prevent !#REF Value
Deleting rows that have dependent cells. I have one sheet that uses a very simple formula for copying certain values from one cell to another sheet “=VALUE(Sheet1!B5)”. My problem is that rows have to be deleted in sheet1 as the information eventually becomes expired and the following is displayed in my cell in sheet 2 “=VALUE (Sheet1!#REF!)” Is there a work round this, to prevent the formula showing a #REF! Value. Row 5 is replaced and the same formula has to be re-entered. Excel 97 used in our office unfortunately.
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
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
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
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
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
Fill In Blank Cells Referencing Non-Blank Cells Above
I have sporadic cell values in a column. I will be describing the lamen logic I use to fill in these blanks manually. Find the first nonblank cell in the column, then look for the next nonblank cell in the column. I count the number of blank cells in between. If even # of blank cells, then I give the first half of the blanks the value of the first nonblank cell and the second half of the blank cells the value of the next nonblanck. If odd # of blank cells, then I do the same with the exception that the odd cell that falls in the middle will be randomly designated the value of the either first or second nonblank cell.
View Replies!
View Related
Copy Data From One Sheet (Fixed Cells And Sheet) To Another Sheet
I want to be able to copy a name from one sheet (Available Players), paste it to a cell in another sheet (Round 1 through Round 20). The cell that will be copied is fixed but the place where it will be pasted will be different and may be on a different sheet. also i would like to change the color of the copied cell to "greyed" out or cut if it can not be greyed out. I have created a button and put in a macro that i created but have been having problems with it, generic 1004 errors that i can not figure out. i am attaching the document.
View Replies!
View Related
Pasting The Sum Of Two Cells From One Sheet To One Cell In A Different Sheet
I'm trying to write very simple code that copies totals from one worksheet and pastes to a different worksheet. Normally, it is a one cell to one cell copy/paste. For instance, I want to copy the total found in cell "C4" in Worksheet "BalanceSheet" and paste it into cell "C14" in Worksheet "SpreadFinancials." I'm able to do this fine, and the code below is what I've been using. Occasionally though I'm needing to sum up 2 cells in Worksheet "BalanceSheet" and only copy/paste the summed total into one cell in Worksheet "SpreadFinancials". For instance, I want to sum the totals found in cell C8 and C9 in Worksheet "BalanceSheet" and then paste that summed total into cell "C8" in Worksheet "SpreadFinancials". I'm looking for simple code that does this. Here's an example of what I've been doing so far for one cell to one cell copy/pastes: Sub PopulateFinancials() Sheets("BalanceSheet").Range("C4").Copy Sheets("SpreadFinancials").Range("C14") Sheets("BalanceSheet").Range("D4").Copy Sheets("SpreadFinancials").Range("E14") Sheets("BalanceSheet").Range("E4").Copy Sheets("SpreadFinancials").Range("G14") End Sub
View Replies!
View Related
Copy Cells Where Range Address Resides In Cells Of Another Sheet
Tried to write a Macro to Range Cells in a sheet, setting the range values from a another cell. I have encounted a Run-Time error as select method of Range class failed. Below is the Macro. Sub Macro1() Dim r1 As Range, r2 As Range, myMultiAreaRange As Range Worksheets("Pre").Activate Set r1 = Range("J4") Set r2 = Range("K4") Set myMultiAreaRange = Union(r1, r2) myMultiAreaRange.Select Worksheets("Data").Select Range(r1, r2).Select Selection.Copy Sheets("1").Select Range("B5").Select ActiveSheet.Paste End Sub Have the Ranges been set incorrectly
View Replies!
View Related
|
TRACKER
