Use Cell Address' To Call Up Sheet Name In A Formula
If I have 12 sheets in a workbook (Jan, Feb,etc..) can I have those names in specific cells (A1,B1,etc..) and call them out in a formula as the cell name as opposed to the actual name?
like A1!C4:C100
instead of Jan!C4:C100
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Automatically Call Macro Based On Changed Cell Address & Text
I have the following code pasted into the worksheet module which used to work fine but no longer does. I didn't touch the code, it just stopped working. Private Sub WorkSheet_Change(ByVal Target As Range) Select Case Target.Address Case "$A$16" Select Case Target Case "Custom Color 1": Call CustomColorInput1 Case "Custom Color 2": Call CustomColorInput1 Case "Custom Color 3": Call CustomColorInput1 Case "Custom Color 4": Call CustomColorInput1 Case Else: End Select Case "$A$17" Select Case Target Case "Custom Color 1": Call CustomColorInput2 Case "Custom Color 2": Call CustomColorInput2 Case "Custom Color 3": Call CustomColorInput2 Case "Custom Color 4": Call CustomColorInput2 Case Else: End Select Case "$A$18" CustomColorInput 1-6 are the same except they input data into different cells F16-F21. When I attach any of the CustomColorInput macros to a button it works fine. When I try to call it from the worksheet module, the data input box pops up but the data doesn't get entered into the cell. When I try to step through the CustomColorInput macros using F8, the input box pops up, I enter data and press ok. Then I continue with F8 and the debugger jumps to a custom function that I entered (below), which is not called for and is in no way related to the code. Function PullAfterLast(rCell As Range, strLast As String) PullAfterLast = Mid(rCell, InStrRev(rCell, strLast) + 1, 256) End Function When I close VBA, the data that I typed into the data input box is in the correct cell. So basically, I'm getting three different responses from the offending code depending on how I call it up. Can anyone tell me what's going on? More generally, in this and previous occasions, I've had problems with macros that cease to work for no reason that is apparent to me. What are some things that would cause a macro to stop working, without actually changing the code (other than the obvious, like referring to names or worksheets etc. that don't exist anymore)?
View Replies!
View Related
Lookup Function Call: Vlookup Call In Sheet
I have a work book with 3 sheets. Sheet 1 is the main sheet and sheets 2 and 3 will use (I hope vlookup) to update 3 columns from info in sheet 1. my attempt at a vlookup call in sheet 2 is: =VLOOKUP($b3,[master_AoJ_2.xls]Sheet1!$B$3;$B$65,false) my understanding is that: - $b3 is the cell in sheet 2 that will be updated as a result of the vlookup call. - [master_AoJ_2.xls]Sheet1!$B$3;$B$65 refers to range $B$3:$B$65 on sheet1 of master_AoJ_2. it does not work... infact I get nothing at all. when I type the call into cell $b3 of sheet2 excel thinks it is stariaght text. this is how I coded the function call =VLOOKUP($b3,[master_AoJ_2.xls]Sheet1!$B$3;$B$65,false)
View Replies!
View Related
Run-time Error '5' Invalid Procedure Call (CommandBars)
I create and load a commandbar, prior to loading it I attempt to check if it exists, then delete it, and recreate. I dont understand why this seemed to work for months and now creates an error. It appears that every now and then the created commandbar is not created when I open a file. Thats when the error pops up. I can manually run the Create_Bar sub and it will be fine, for a while. Run-time error '5': Invalid procedure call or argument This section is in my personal.xls file in "ThisWorkBook" of personal.xls.
View Replies!
View Related
'C:Documents...' Inside Worksheet Function Call
I have a spreadsheet that uses a function defined in VBA code that is housed in an add-in which is stored in the default location (C:Documents And Settings{user_name}Application DataMicrosoftAdd-Ins). To share this spreadsheet, I first install the add-in on the other person's computer. Then, when the other person opens the spreadsheet, the function doesn't work because the function call on the worksheet contains the whole path of the add-in (and the path contains my {user_name} rather than the other person's). So, the immediate solution is to just delete the path of the add-in from the function call (so that only the name of the function remains, plus the arguments of course). But this is quite inconvenient given the number of times the function appears in the worksheet, and it's not practical as we continue to share files like this. My question is: is there a way to make sure that the 'C:Documents...' never gets added to the function call in the first place? I mean, why doesn't Excel just try to run the function locally before it adds that path that refers to a separate computer? I do realize that there are other methods of sharing VBA code. For example, I know that I could include the code in the specific spreadsheet that I'm trying to share, and this would work. But, that's not the answer I'm looking for (for one, the function refers to a very large amount of data stored in the add-in, and there are other reasons that make the aforementioned method impractical). I'm wondering if I'm missing something, and if there is a way to stop Excel from tacking on the path string to the function call, so that the function would just work on any computer as long as they have the add-in installed?
View Replies!
View Related
Access A Cell's Contents Via A Variable Containing Cell Address??
How can you access a cell's value via a vb variable that contains the cell's address. I have a vb variable named cellAddress (string) that contains "$A$1" and I want to assign the contents of what cellAddress points to another VB variable - how do i Do this? e.g. if cellAddress = "$a$1" and A1 contains "xyz", I would like to assigne "xyz" to a new vaiable by referencing just cellAddress??? Also - Can anyone reccomend a good Excel VB book? A book that maybe stresses the VB language rather then a cook-book approach.
View Replies!
View Related
Store The Selected Cell's Address Into A Variable
I have a table in which I have a "patient" column and a "page" column. The macro searches for a patient's number, then looks if this patient has the page "900.--". A patient may have more than one page, which will result in many rows with the same patient number. So far, my macro uses the search function to find a patient number, then compares the cell next to the active cell to see if it contains the page "900.--". If not, then my macro searches for the next patient and so on until the page is found and noted into another workbook or none is found. In order to stop the loop, I am trying to store the address of the first cell found into a variable "rFirstCell" so that it can later on be compared to another variable, "rSecondCell", which represent the active cell. When both are the same, it means all the available search results have been tested and the loop should stop. Sub testing() Dim rRng As Range, rFirstCell As Range, rSecondCell As Range Set rRng = Worksheets("Overview").[a1] Dim sDeath As String sDeath = "death" ActiveSheet.AutoFilterMode = False If LCase(rRng(2, 15).Value) = "x" Then If LCase(rRng(2, 9).Value) = sDeath Then Workbooks("DM Endpoint pages_test.xls").Activate Range("A1").Select...........................
View Replies!
View Related
Formula To Call Variable File Name In Cell Links
The master has rows corresponding to numbered files: 4300 | CellValue1 | CellValue2 | etc 4301 | CellValue1 | CellValue2 | etc In attempting a simple formula (a couple VBA codes I tried did not work and were probably substantially more than I needed anyway), I made the following: ='CMain FolderSub Folder[concatenate(cell w/file number,".xls")]Sheet1'!A1 This is identical to the copy/paste link formula currently in use except that I am trying to have the actual file number/name generated off the master list instead of what is in the file itself. Purpose: in case I was not clear on this above, I am trying to automatically populate the master worksheet with the data from the workbook. The current procedure is to copy a line of the data from the workbook, then paste as a link in to the master worksheet on the row corresponding to the file number.
View Replies!
View Related
Getting The Final Sheet And Cell Address Of A Value
If coded correctly, there are various Excel functions & tricks for creating a formula to send someone on a wild goose chase through many worksheets and cells before finding the final cell that contains the final value. I was able to use =Cell("address",...) function to cut through the many paths and retrieve the address of the final cell. Is there a VBA trick for doing this to the active cell? I would like to be able to run a small VBA routine that would trace back the fomula of the active cell and return the final cell's worksheet and cell address.
View Replies!
View Related
Use Sheet Name From Cell To Address Sheet?
I am wondering if it is possible if I can use a cell which holds the name of another sheet in my workbook to address this sheet? Example. A2 has the name of a sheet on my workbook entered (Name: Testsheet). ow I would like to display the value from C5 on this Testsheet: Normal: =Testsheet!C5 (works, of course) Need: =A2!C5 (doesnt work, of course....)
View Replies!
View Related
Incorporate Adjacent Cell In Formula W/o Naming Cell Address?
I searched and I keep finding something that's not quite what I'm looking to do so I think I'm missing something really simple. Let's say that I want each cell in col b to be the sum of 5 plus whatever value is in the cell adjacent to it on the left (col). I know that you can just write a formula in each cell like... b1 will be =sum(a1+5) b2 will be =sum(a2+5) ... but is there a shortcut so that you don't have to write out the actual cell address for each one? Meaning - is there a predefined name or something that represents the cell to the left or right so you can just use that instead, allowing you to just copy paste the same formula all the way down the column? Something like... b1 will be =sum(left+5) b2 will be =sum(left+5) ... where "left" represents whatever value is in the cell to the left of that particular cell?
View Replies!
View Related
Using 'IF' With Conditional Format (change Cell Color Based On The 'P' Or 'F')
My cell has a 'P' or an 'F' plus some additional text. I'm trying to change cell color based on the 'P' or 'F' but I can't get it to recognize the letters. Samples of what I've tried: =IF(LEFT(L23,1)=P) =IF(MID(L23,1,1)=P) etc. I can 'FIND' the 'P' or 'F' but there may be a P or F else where in the cell so the color may come up incorrect. I'm trying to get it to just look at the first character in the cell. Can the 'IF' be used with CFs?
View Replies!
View Related
Formula That Returns Address Of The Active Cell
Is there a formula in Excel that returns the active cell address (ie dynamically). Excel updates the activecell address in the Name Box dynamically as you make a selection but I cannot find a standard formula to access it. I know I can achieve this with code using the selection-change event but this action then disallows use of the Undo button - which I specifically want to avoid. Perhaps there is an add-in available?
View Replies!
View Related
How To Restrict The Cell Data To Letters 'a' Through 'e' AND 'A' Through 'E'
I am trying to come up with an excel shee which can track the progress of a student on practice exams. This exam only has multiple choice questions, with answer choices A,B,C,D or E. What I am looking for 1) I want to restrict the cell data to A,B,C,D or E (both cases). Note that I am not looking for a drop down/combo box. It would take the user to use one or two mouse clicks to drop down the list, and I am not in favor of that. 2) I would like this validation/formula to be applied to all the cells in a particular column. 3) Nice-to-have. If the user enters a lower case letter, then I would like to convert to upper case.
View Replies!
View Related
Formula Reference Cell Address In Cell
I have 2 sheets. In the first sheet, I have 3 columns. The first column contains transaction codes. The 2nd column contains the destination code (like a mapping cell where the value of the 3rd column would go but in a different sheet/2nd sheet) and the 3rd column contains the values for it. Would it be possible to reference a destination in a cell itself? Or should I just code it individually? I wouldnt want to reference each data one by one because the total would be 480 reference cells. Is there an easier way?
View Replies!
View Related
Calculating A Call Center's Average Workload
I have a rather complex worksheet, but it needs lots of manual intervention, it calculates the number of staff needed at any given hour for a call center based on the calls offered for an hour, the calls for a day are copied into the calandar and the last 7 "Mondays" "Tuesdays" etc: calls are averaged to give me a call trend, what I need is a way of automaticly adding this data. I have created dropdown boxes with all the dates, if I choose a date, I want to be able to fill the last 7 "Mondays data"
View Replies!
View Related
VBA Code Doesn't Work When Formula In Sheet Is Removed
I had to remove a lot of pictures in the xls file in order to attach it, (since the original is about 5.3mb) but it worked out. When you move your arrow over the titles in column A a picture shows up thanks to a code. When you change the status in column G, the color changes together with the title in column A, as it should.
View Replies!
View Related
Macro To Call Sheet Number Instead Of Name
I've got a macro working that updates things in different sheets, the sheets have peoples names in (this can't be changed). When running the macro its fine, but if a new person is added i would need to update the macro for every sheet (the macro can be run from every sheet within the workbook). I've noticed if you right click the sheet button and go to properties (or something) that it shows (Name) Sheet2 and Name Joe Blogs. Instead of the macro calling to Name i want it to call to (Name), is this possible? The code that calls the sheet looks something like this: Sheets.("Joe Blogs") Instead of looking for Joe Blogs i want it to look for Sheet2
View Replies!
View Related
Call Tracking Sheet - Electronic
I am trying to turn this call tracking sheet into an electronic one. Is is possible to have check boxes in the empty cells that will allow someone to just click them as they go along and it would create a total at the bottom to avoid countless hours of counting these sheets. Or even turn "Trans CM" into a button that you can click and it would keep track of how many times that specific button was clicked.
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
Sort Page (Sheet 2) #REF! In Cell With IF Formula To Sheet 1
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 Replies!
View Related
If Statement: Return 'V,' If Cell='&'
When I tried using if & or statements I got an error - so I tried this: =IF(K7="&","V,",""),IF(K7="1 Space + &"," V,","") I want to return 'V,' if cell='&' or if cell='(space)&' I want to return '(space)V,' What is wrong with this statement..?
View Replies!
View Related
ISDN Call Cost Formula
I have an Excel workbook with 2 worksheets, labelled as "codes" and "data" The "codes" worksheet contains three columns of data. Column A shows Country names Column B shows Country dialling codes Column C shows Cost per minute to the country ...
View Replies!
View Related
Lookup Numbers In Sheet And Assign It's Cell Reference
I have a worksheet(Shed) and the range where numbers will be entered are B3:E46. In another sheet(Location) I have a in column A "Shift #", B "Shift Bus #" C "Location". what I am attempting to do is when a bus number is entered into Shed! ie.. b3=900, d15=350 etc.... Location! would lookup ie.900 in the Shed! and match it to Location! (column B) and from there provide the cell ref in column C. So I could then print Location! that would give me in Shift # order where each Bus # the shift is assigned to.
View Replies!
View Related
Run-time Error '1004' :: Method 'Range' Of Object'_Global' Failed
I am trying to create a macro in my personal macro book such that whenever any workbook is opened the calculation settings (tools, options, calculation tab) are set to semiautomatic and do not calculate before save. The macro works when I am opening Excel itself (Book1) but when I open an already saved file it gives me Run-time error '1004' Method 'Range' of object'_Global' failed From there I choose Debug, the VBE window comes up, and I hit F5 to continue the code without doing any actual debugging. Here is the code that I am using. This is in the Personal Macro book on the "This Workbook" section....
View Replies!
View Related
Run-time Error '1004' Method 'Range' Of Object '_Worksheet' Failed
I have an interesting error that only happens when there is one row of data in the worksheet (sheet2 or "Half Payout"). Rows 1 & 2 are headers, row 3 is when the data starts - if any. With either no rows of data or more than one the coding works just fine. Here is the exact error message I'm getting: Run-time error '1004': Method 'Range' of object '_Worksheet' failed. The following code is supposed to sort the rows of data when opened and then activate the first open cell below B2.
View Replies!
View Related
Run-time Error '1004': Methd 'Range' Of Object '_Global' Failed
I have constructed the following code to set the print area of worksheets that have been selected to print to the range referenced in a worksheet level named range "xPrintArea". This named range is set using the OFFSET function. The procedure also sets the left footer to be a copyright notice that is also contained in a cell referenced by a named range. Set oPrintArea = Range(sPrintAreaName) is generating the error "Methd 'Range' of object '_Global' failed". Note that the line Set oCopyrightNotice = Range("CopyrightNotice") does not generate this error. From what I've been able to determine from other research on this forum and others, I believe the problem is that I need to more fully qualify the object which Range(sPrintAreaName) is referencing. I've already tried to use Set oPrintArea = wkSht.Range(sPrintAreaName).................
View Replies!
View Related
Method 'Add' Of Object 'CommandBarControls' Failed (run-time Error '-2147467259')
i get this error on the line in bold Method Add' of object 'CommandBarControls' failed (run-time error '-2147467259'). im not sure why its giving that problem, but im not very fluent in custom toolbars. '//The following two procedures add a custom menu to the workbook programmatically//' '//and then delete it//' Public currentMonth As String Sub CreateMenu() Dim mybar As CommandBar Dim myControl As CommandBarControl Set mybar = Application.CommandBars.Add( Name:="CustomButtons", _ Position:=msoBarBottom, Temporary:=True) mybar.Visible = True Set myControl = mybar.Controls _ .Add(Type:=msoControlButton, ID:=1) With myControl...........................
View Replies!
View Related
Copy Cell's Value Only And Not The Formula
I am trying to write a macro so that when a user click a submit button, the value of a cell is copied and paste into another worksheet. However, the cell that is being copied is a forumla (=3+3 for example). But when it is pasting, I would like it to be just the value "6" and not the formula.
View Replies!
View Related
Code 'To Invoice' Copy The Filtered List, And Paste On Sheet
I have recorded a macro to filter data on sheet 'To Invoice' copy the filtered list, and paste on sheet 'Invoice' in C16. The code just keeps looping (not looping in a code sense, it just seems to keep flickering the screen like its going over & over) until it locks up 5-10 seconds-ish and then I have to re-start Excel. The range B2:E22 is not always populated, it could possibly be B2:E2 (one row), I dont know how to copy the exact data so I expanded the range to what I think would capture any eventuality....
View Replies!
View Related
Information Dissapears From Graph When Section Of Other Sheet Is In 'group'
i dont know if there is a way around this, i have a sheet, it has one large sheet with hundreds of rows under 3 headings, and i have a group on each of these so people can go in and expand their section and add details, i have a second sheet that contains some graphs simplifying the data collected in the 3 sections of the other sheet.. but i have just noticed when a section of the sheet is grouped its details are removed from the graph. is there any way around this? i need the groups on as the sheet is so big and people do not want to be scrolling through other area's data when they can just expand their own.
View Replies!
View Related
Exporting Macros To Another Workbook From The 'sheet' Code Module
I've used the following code to export macros from one workbook to another but I was wondering how you export macros from the ' Sheet' code module to the 'Sheet' code module of another workbook. Sub Import_Macro() With ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule strRet = .Lines(1, .CountOfLines) End With Set wkb = Workbooks("Test Workbook.xls") wkb.VBProject.VBComponents. Import ("G:SCSSCSALLReportsVB MacrosGeneral.bas") wkb.VBProject.VBComponents.Import ("G:SCSSCSALLReportsVB MacrosMJ Selections.bas") With wkb.VBProject.VBComponents("ThisWorkbook").CodeModule .DeleteLines 1, .CountOfLines '//Delete Codes already wrriten .InsertLines 1, strRet '// Write Code End With End Sub
View Replies!
View Related
Make A Cell's Values Become A Formula
I have on my Sheet named "Data" in Cell K4 =CONCATENATE(L16,O25,L18,O25,K20,S25,N22) The values of that cell become a formula. I try to make a macro that pastes 'Data'!K4 into 'Data'!L3 and then have that formula functioning in cell 'Sheet1'!A31. The problem is when I try to make a macro to do this it will always paste the values that were recorded during the macro rather than the unique formula that is created via cell 'Data'!K4 at the time. Is there any way to have A31 actively using the values that are created with 'Data'!K4? at all times?
View Replies!
View Related
How Do I Convert A Cell(s) From The Formula To It's Contents
Using Excell 2000 ... I have data being retrieved from another sheet Cell A1 of the Input Sheet, for example might contain the words "This is a TEST" Cell A1 of the Output Sheet contains the formula: ='Input Sheet'!$A$1 .... thus displaying whatever the contents is of cell A1 of the Input Sheet. I want to save the formula results in time ... I want to convert the cell(s) of the Output Sheet to the results ... getting rid of the equation(s). I'm sure I have seen this before, I just cannot remember? ... getting old!
View Replies!
View Related
Converting Cell's Formula To Plain Value
I have whole bunch of formula's added to cells by code. So far so good. How could I then set in macro code that the result is stored in cell as value rather than formula? Now when I go to cell you can see =PIBVSearch(.... yadda yadda yadda in the field above. I would prefer to change it so that content of cell changes to it's value so there's no formula visible(and above all not stored to harddisk(LOTS of disk space lost. Whole project started to trim down 269Mb excel file into smaller piece! I don't want it to go back there too easily...) or in danger of getting updated which might take a very long time easily). Code works so that as long as there's rows which haven't got update yet but do have product run code(or something like that. Not quite sure what that number is but I figure it's product run) after code reached that far(which it copied from another file). Then for each row it calls functions for each cell which are responsible for filling invidual cells. So either way to replace content of whole row with values or invidual cells, I can insert both into loop easily though whole row after cells are calculated would be a) faster to add(just one place to add, not 30...) and likely faster to run(project is about getting the file to run faster afterall...).
View Replies!
View Related
Return A Cell Formula's Parameters
i need to essentially find and replace udf's and the complication is rearranging the parameters in the udf's into a new formula structure. so my question, is there an easy way to extract a parameter (or all parameters) from a function? for example, if you had = SUM(A1,A2,A3:A6) you would return 'A1' as the first parameter, 'A2' as the second and 'A3:A6' as the third. Obviously it gets complicated when you nest functions such as =SUM(IF(A2=1,0,A1),A3) etc and the if() function would be parameter 1.
View Replies!
View Related
Formatting Won't Apply To My Cell With A Formula
I downloaded the checkbook register from Microsoft.com and am trying to add a few cells at the end to quickly calculate my outstanding debits and credits so that I can balance my statements automatically. I figured out how to write the formula properly (eventually), but the cells will not take on the currency format no matter what I do. I have tried pasting the format from the other cells within the register, tried clearing the format and reapplying the currency format, tried accounting, etc.
View Replies!
View Related
Sheet Has Lost The 'UNDO' Facility
I have created a spreadsheet in the field of marine surveying. In that sheet, I have used a lot of macros (21 modules) and a lot of VBA, most of it provided by kind people on this forum. But what has happened now, is that when I use that particular worksheet, the 'Undo' menu item in the 'Edit' menu has become permanently grayed out and it says 'Can't Undo'. In other words, I cannot undo, even if I have made the wrong entry in an unprotected cell. If I use any other sheet, or make a new sheet, everything returns to normal. So, is there any way I can re-gain the 'Undo' facility, while using that particular sheet. I am using Excel 2003 on a Windows XP Home (SP3) platform.
View Replies!
View Related
|
TRACKER
