Lookup Based On Variable/Changing Sheet Name
I'm trying to figure out a way to lookup a value based on a conditional sheet name. I'm finding it difficult to explain in words what I'm trying to do, so I'm going to try and describe it in a miniature example of my spreadsheet. I apologize if this is going against forum rules, this seems like the quickest way to get my question across:
The following is the way my spreadsheet's first page is set up:
ID Apr-08 Mar-08 Feb-08
1
2
3
The column labels depend on another table, which changes monthly.
The rest of the sheets (24 of them, labled "200804", "200803", etc) look like this:
ID $ amount Date paid
1
2
3
I have another table upon which the na
I'm trying to return the "Date Paid" value for each ID for each month. Each month, however, more data arrives, and the labels change. I have several dozen spreadsheets formatted in this way, and I'm trying to avoid having to change the vlookup (or other formula) manually each month for each sheet. Is there a way to make the vlookup depend on a table?
This is essentially what my formula looks like right now for cell A2:
=VLOOKUP($A2,'200804'!$A$1:$G$10000, 3, FALSE)
If I could replace '200804' with a cell reference, that would be ideal. I haven't been able to figure out how (if possible); I tried experimenting with index sheets but that went nowhere.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Changing Vlookup Ref Column Based On Vb Variable
I am pasting vlookup formulas into a spreadsheet using a macro, and want to change the reference column number based on a variable generated within the code eg = vlookup(RC1,table,i,false) where i is a predetermined variable in the code for i=23, I need the result to be of the form =vlookup(a1,table,23,false)
View Replies!
View Related
Double Lookup (lookup Variable Row And A Variable Column)
I have a file that I would like to lookup variable row and a variable column. I have tired vlookup and hlookup but these do not work because you have to specify a given column or row versus having that column or row be variable. Is there a way to do this. Think of a set of times tables. I would like to input 8 and 9 and get 72 as an output. How could I go about doing this? Or in the attached file, I would like to say A and 15 and get A15.
View Replies!
View Related
For Loop For Changing Formulae Based On Other Sheet
I want to write a For loop so that it checks the range G2:R2 of Sheet1 for the first non-zero cell in that range. When it comes to the first non-zero cell, it will change the formula of A2 on Sheet2 to have the SUM of that non-zero cell and the next two cells to the right. Example: SHEET1- G2 = "" H2 = "" I2 = "" J2 = "3" K2 = "4" L2 = "8" M2 = "9" N2 = "2" O2 = "5" P2 = "3" Q2 = "11" R2 = "7" SHEET2- A2 = SUM('Sheet1'!J2:L2) --->"15"
View Replies!
View Related
Name Sheet Automatically Based On Changing Cell
In my sheet called summary I have the names of the rest of the tabs in the book in cells B5 to B34. We want to be able to change the names of the tabs by changing their respective cell on the summary tab. So “sheet1” corresponds to B5, “sheet2” to B6, “sheet3” to B7, etc.. So if I change the name in B5 to say “APPLE” I want the tab for sheet1 to change to APPLE. When I change B10 from “sheet6” to “Lemon” I want sheet6 to be titled “Lemon”.
View Replies!
View Related
Changing Cell Color Based On Changing Values
This may have been answered on here but can not seem to find it. My situation is I have values in A1,A2 & A3 that are like counter reading so the value is always changing. What I am looking to do is change the cell color if one of the values is over 500 from the other two values. Say A1 is 3000, A2 is 3250 and A3 is 3500. I would like the cell for A3 to change color.
View Replies!
View Related
Changing Variable According To For - Next
Sub Macro1() Dim blue1 As String, blue2 As String, blue3 As String, num As Integer blue1 = "one" blue2 = "two" blue3 = "three" For num = 1 To 3 ActiveCell.Value = blue & num ActiveCell. Offset(0, 1).Select Next num End Sub What I am trying to achieve is to use the appropriate "blue#" variable according to the num variable. For example, when num = 1, I want the macro to use the variable blue1. I am not sure how I should code this.
View Replies!
View Related
Find Max Value In A Changing (variable Range)
I am trying to find the max value within a range (14 cells) contained in one column ("C" in this case). This range will change corresponding to the current cell. Ex. as the current cell moves down (or increases in number) the range of cells to find the Max in moves down (or increases in number) as well. The Max value will then be used as a variable in a equation but for now just getting it into a cell is fine. This seems like it would be easy but it is driving me absolutely batty! I currently have the non-working code located in a For loop that contains other calculations as well. I've taken those calculations out since they work fine but cloud the bad part. So far I have tried:
View Replies!
View Related
VBA: How To Program A Changing Variable Name In A Loop
Trying to code my first custom VBA function. The function is supposed to translate the words in a cell by repeatedly looping through the string and looking through a list of provided translation words. Problem is the variable name changes with each loop, and I can't figure out how to program the name change. Function TestTranslate(TranslateCell, ProductCell) As String Dim t As Long Dim TranslateCeiling As Long Dim SS1 As String Dim SS As String Dim Q, R As String Dim W As String Dim LLRString, MLRString, SLRString As String Dim LRString, MRString, SRString As String Dim TArray() As String Looks like the loop keeps defining Q as "SS" & t instead of doing the select case formula. I tried removing the Q variable altogether and making it read just "SS" & t = Replace(R, LLRString, LRString) for example and keep getting a compile error.
View Replies!
View Related
Vlookup Formula Changing Lookup Range Automatically
As I copy and past my lookup formula down the page it is changing the lookup range which I think is what is giving me so many #N/A results. My first Formula is =IFERROR(VLOOKUP(A2,Coors2!A2:D3765,3,FALSE),IFERROR(VLOOKUP(A2,'AB2'!A2:C13944,3,FALSE),(VLOOKUP(A2 ,'WM2'!A2:C4843,3,FALSE)))) Then for instance at line 59 the formula is =IFERROR(VLOOKUP(A59,Coors2!A59:D3822,3,FALSE),IFERROR(VLOOKUP(A59,'AB2'!A59:C14001,3,FALSE),(VLOOKU P(A59,'WM2'!A59:C4900,3,FALSE))))
View Replies!
View Related
Select A Variable Based On A Variable
On my userform I have 20 comment icons (imported pictures), that when clicked need to bring up an InputBox for the user to add a comment, and store that comment in a Public variable specific to that comment which will later be written to the spreadsheet. To keep it simple, lets say I have two comment icons to click, one to add comments to the "Testing Completed?" field, and one to add to the "Sign-Off?" field. The first comment icon is named TestCompIcon, the second is SignOffIcon, and the public variables they write to are called TestCompComment and SignOffComment respectively. To avoid having to code the InputBox procedure for every comment icon on the userform, I was hoping that upon click, the icon would call a centralized routine that would establish the name of the variable that needs to be written based on the name of the icon comment that was clicked. Something like as follows:...............
View Replies!
View Related
ODBC Lookup To Populate Variable
I have no problem setting ODBC connections in vba, that return the results to a selected Range destination, but short of then setting txtVariable = Range("A1") is there any way to directly tell the ODBC connection to set the result of the query to txtVariable?
View Replies!
View Related
Lookup Function For Variable Amounts
I have a worksheet where I only want to pull over details for rows that have a amount listed in the variance column. Is there some type of macro I can create to perfrom this for me? There will be many rows that I do not need to pull, but for the ones that have an amount listed in variance, I will want to pull over certain cells from those rows. Is there any way excel can do this for me?
View Replies!
View Related
Lookup Single Value In One Sheet, Return Multiple Results From The Other Sheet
i have two sheets, one to display results (Reults tab) & the other tab containing the data (Data tab) what i am trying to do is some how create a search function and have a forumula which contains a LIKE function that looks up the data table RANGE = Data!A2:K255 the search needs to lookup the primary column Data!B2:B255 ... if any results are found .. show them on the results tab.. and if multiple results are found, display those as well.. (in either instance, the whole row of information in respect to the results need to be dislayed and hopefully no duplicates are found .. eg, Data!A:K of a hit) is there a formula that can achieve this? oh, the search is TEXT based and there should be no empty cells within the dataset after some MASSIVE googling, i have stumbled accross this B1 = Search box (txt field) A6 (which will be a hidden column) contains =MATCH($B$1,Data!A2:A255,0). this formula provides the first instance of the result and provides the row number A7 contains =MATCH($B$1,OFFSET(Data!$A$1,A6+1,0,8-(A6+1),1),0)+A6. this is supposed to look for the next row number which contains a match and provide that row number and througout my other columns, i have B6=OFFSET(Data!$A$1,A6,1) B7=OFFSET(Data!$A$1,A6,2) B8=OFFSET(Data!$A$1,A6,3) and so on 2 things i cannot recitify.. 1, the match has to be EXACT ... unfortunately i cannot use exact .. needs to be LIKE .. eg, i cant use the search word "boat" as the range of data has "boats" 2, it comes up with multile .. irrelevent results.
View Replies!
View Related
Lookup Formula: Find The Longitude And Latitude Data From My "lookup" Sheet
In my workbook I have multiple sheets but I'm attaching a very simple workbook to demonstrate what I'm trying to accomplish. In my "Lookup" tab/sheet. I want to have known Latitude and Longitude data that will exist in columns A&B. Columns C & D will have address numbers and Street Name. I would like my lookup formula to find the longitude and latitude data from my "lookup" sheet, when the matching address information is typed in, in my 2009 sheet. I have to keep the street numerics and street name separate on this worksheet as well. I believe I'll need two separate lookup formulas as I need these formulas to start in cell G4 & H4 in my "GeoCoding1" sheet. Is it possible to have four columns of data to be viewed in a lookup formula? I tried this formula in cell G4 (GeoCoding1 sheet)
View Replies!
View Related
Index Match Lookup, Variable Return Column
I would imagine that the title of this thread made little sense, so let me try to explain: Sheet A has columns A-C populated with data. Column D needs to search Sheet B columns A-C for a match. (ie, see if the data on sheet A is also on sheet B) if this is true, then it needs to return the value of the LAST column of the matched row in Sheet B. So, if Sheet A R1 A-C is 1,2,3. Sheet A R1 D needs to search Sheet B for 1,2,3. and when it finds it, (for example on row 9) return the value of the last column of Sheet B row 9. The issue is, the last value could be in column R or S or AA, there is just no way of knowing.
View Replies!
View Related
Prevent Changing Of Sheet Name
I have been searching for a way to prevent a user from changing a worksheet name. I ran into some code from the post below (see link) but I find that the code lets the user change the name and save the changed name of a sheet; only when the user selects a different sheet does it change the name back to the set name (sname) from the code. I'm sure there must be a way to make this happen without deactivating the sheet, but I can't figure it out. Is there some other sort of event that could cause this to happen.
View Replies!
View Related
Changing Hard Coded Column References To Variable References
I have a number of statements within the Sheet Event Code (Excel 2007). Three times lately I have added a column and had to go back into the code and find all of the references that needed changing to reflect the new column. I have been working on this for a couple of days and even tried EE, but to no success. I have read that Defined Names / Constants should be used as often as possible, but even trying that, the VBA code errors out or "hangs up". Even within Bill Jalen's book (VBA and Macros 2007), there is nothing that addresses this, especially using Intersect. Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range On Error GoTo mEnd Set rng = Sheets("Log").[F14:F10000] If Not Intersect(rng, Target) Is Nothing Then If Target = "" Then With Sheets("Log")
View Replies!
View Related
Event Macro Changing Sheet Name
I have a worksheet called "Roster" with a list of names from D7:D17 each person on the list has a separate sheet that is called by their name. the sheets are not linked up. the names were just manually typed into the list and into each sheet name. If any name is changed on the list on "Roster!D7:D17" i would like the sheet that has that name to change as well. This is the code that i was sent, but i do not know enough about VBA code to understand exactly what is happening, thus i cannot diagnose the problem. I placed the following code into the page "Roster" (by right-clicking and then pasting the code) then i closed, but nothing happens. I can mess with the code and get an error message. but that is about it. error messages usually say something about the debugger, or runtime error or something. there is the Private Sub Worksheet_SelectionChange(ByVal Target As Range) OldVal(0) = OldVal(1) OldVal(1) = Target(1).Value End Sub
View Replies!
View Related
Changing Size Of Excel Sheet
Is it possible to make a worksheet smaller. What I want to do is to be able to tab from, a1 to b1 to c1 to d1 etc up to g1 and then if I tab again it goes to a2. Is this possible? Is there any way of making it skip the a coloumn when tabbing even if it has data in it?
View Replies!
View Related
Continual Changing Sheet Names
I want to capture data from another workbook each month where the sheet name that I need the data from changes each month. i.e. 0810, 0811, 0812, 0901. Are possible sheet names. I've tried to build this unsuccessfully into the my -
View Replies!
View Related
Changing A Chart Source Sheet
I have a sheet ( Graphs A) with 50+ graphs on all linked to a datasheet (Data A). I now want to copy these graphs to another sheet (Graphs B) which links to a different data sheet (Data B). The data is in the same layout format etc. The problem is that the find-replace function will not replace the sheet name in the charts series formula from Data A to Data B Is there an easy way to change the source sheet for the new graphs?
View Replies!
View Related
Graph Based On Changing Range
I am trying to automate a graph for data that is not always the same amount but always in the same format. I have attached a spreadsheet that has an example of the data. Columns B, D, & E are the ones that I am interested in charting in a line with markers type graph, but the rows may be 100 for one set of data and 1000 rows for another set of data. I am trying to get the macro to open a new worksheet and as part of the macro create a userform that will ask for the text information on the top of the worksheet (as this is not always correct when imported).
View Replies!
View Related
Adjust Formula To Allow For Changing Sheet Reference.
I was using the formula below which was working fine for copying across 20 columns and down however many rows, but now the requirements have changed to 90 columns. I have tested this out with the 90 columns but the Indirect function is bogging down the spreadsheet with the constant recalculating. =IFERROR(INDIRECT("'"&COLUMNS($A$1:A$1)&"'!P"&ROWS($A$1:$A8)),"") I rearranged the formula to the one below, but how can I have the reference to the sheet name changed as in the formula above without using the Indirect? =IFERROR(INDEX('1'!$A$8:$AT$115,MATCH($A3,'1'!$A$8:$A$115,0),MATCH("PAT",'1'!$A$7:$AT$7,0)),"") The sheet reference will change from '1' through '90'.
View Replies!
View Related
One Sheet Saves A Column To The 1st Empty Row On A 2nd Sheet After 1st Looking For A Match On An ID Variable
I am trying to create a macro so that when i save an entry the form sheet looks in the tavela master sheet to see if there is a match on the record number (in the A column) and saves there as an update if it matches and then if there is no match, saves it in the last empty row. this is a column to row copy so it has to be paste special to transpose. also i have a password on the sheet which i use to unprotect then reprotect. i have successfully done these two things separately but cannot combine them! i know not very much about visual basic code just what i have read here and so i am trying to make this code work. Code:....
View Replies!
View Related
Changing Cell Content Based On Button
I have created two buttons in a worksheet to navigate to two other worksheets. I would like a cell to display 'YES' automatically if one of the two buttons is clicked and or to display 'NO'. I also would like to change the colors of these cells change automatically depending on which button is pressed.
View Replies!
View Related
Changing Values In Column Based On Value In One Cell
Based on the attached (much simplified) spreadsheet, I want the values in the green cells on the "Month" page (B6:B10) to vary depending on the value in the yellow cell (B4), and for these cells to to look up the relevant column in the "Full Year" page - i.e. if B4 (on "Month") = Jan, look up C6:C10 in "Full Year"; if B4 = Feb, look up D6:D10, and so on. I know I can nest IF functions in order to achieve this, but could be a bit messy. I'm sure there must be a simpler formula within Excel ... just don't know what it's called!
View Replies!
View Related
Changing Interior Colors Based On Cell Value
i'd like to use a macro to loop through a range of cells and change the interior color index based on the cell values (ex. if cell value < 10, set interior to blue, if cell value > 400, set to green) i'm trying a for each loop with an if statement but i keep getting syntax errors, i know this is probably a simple problem but i'm not experienced in vb,
View Replies!
View Related
Changing Color Of A Cell Based On A Legend
I think looking at the attachment might make more sense, but here is a brief description of what I want to do. I am trying to change the colour of a cell based on looking up the value contained in that cell in a predefined list (which in my example I have called a legend). I need to do this in VBa but my VBA is not at all good.
View Replies!
View Related
Changing Column References On 1 Sheet For Automated Entry
I have formulas in wksheet #1 going down 20 columns (they are all the same formulas). On wksheet #2, I need to understand how to switch all column references in only one column to another (from wksheet #1) to show different sums. Clear as mud? The way I want to do this is by having one cell on wksheet #2 where I change a number from 1 to 20. This in essence would be the column heading in wksheet #1. Example: *There are more formulas but for the purpose here I'll use 3 for each Wksheet In wksheet #1 I have C24 is =SUM(C14*C15,C16*C17,C18*C19,C20*C21,C22*C23)*12 C55 is =IF(B66=0,"",C7/B66) C56 is =IF(C11=0,"",C7/C11) In wksheet #2 I have .......................................
View Replies!
View Related
Changing Which Row Is A Table Header When Queiring A Sheet
I have a excel worksheet which i am exporting into a word document through a macro labeled "Go". Basically it will query the excel sheet and the word document i have, will take the values and put them in the correct placing. My problem is... I know that excel will use the first row as the table headers, but what if i want it to start at like row c? and use that row as table headers. How would i go about doing that?
View Replies!
View Related
Sheet Indirection. Refer To Sheets With Changing Names
I've been referencing sheets in my code directly with things like Sheet1. Range(NamedRange) however in recreating a new workbook (to reduce bloat), the sheet-numbering has changed... to avoid having to mess around adjusting things again, I was wondering if it's possible to do some sort of indirection... something similar to #define SHEET_SC_CONFIG Sheet1 #define SHEET_CUSTOMER Sheet2 'and then use these throughout the code.... SHEET_SC_CONFIG.Range(NamedRange) (cannot reference by sheet name, as end user may change these)
View Replies!
View Related
Changing Column Letters In Formulas Based On Month
I'm trying to update a series of different sheets in a book based on what month I have filled in data for. For example, at September Month End, I will fill in data vertically. Now, I have different tabs that reference certain cells in that column. There row number never changes, but once a month I have to go through and increase their column letters by 1, and there are a lot of places to update. Can anyone lend me some ideas or a solution to how I should go about using formula/macro to update these cells.
View Replies!
View Related
Changing Font Color Based On Change From Previous Value
I want the order size to be colored red. If the most recent order is larger than the previous order, I want the order size to be colored blue. If the most recent order is the exact same size as the previous order, I want the order to be colored black. How would I make code that would let me "remember" the previous order size and the most recent order size, and let me compare them to conditionally format the font?
View Replies!
View Related
Changing Values Of Cells Based On Change Of One Cell Value
I am trying to do is to write a code that will change the values of cells B17:B25 to "false" when the user selects "true" from the drop-down box in cell B16. Here 's my Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "B16" Then If Target.Value = "TRUE" Then Range("B17:B25").FormulaR1C1 = "FALSE" End If End If End Sub This is not working! Nothing happens when I select "TRUE" in cell B16!
View Replies!
View Related
Changing Row Collor Based On One Cell - Repeat
I have 4 condition which change row 3 to different color: example of one - cells on row 3 will change to Blue "=$A$3:$AE$3" on condition =If($AE$3="Absent"; True;False). How can I repeat this formula for rows 4 to 500. i.e. on each row cells A to AE should change color based on the value of Cell AE? (Colum A was hidden, MUST be visible to work)
View Replies!
View Related
Changing Values Based On Drop Down Menu Selection
Attached is the file i am working with. The table spanning from A:AA5:16 contains hours worked per day of the week with respect to the Project chosen from the drop down menus in A7:16. Cells in the range of F,K,O,R,U,X,AA7:16 total those hours worked on a daily basis. The table below should sum the total hours worked per day according to the Project selected in A19 from the drop down menu, with respect to the Project(s) chosen in cell range A7:16. For example, if i am looking for total hours worked Monday on Project RSB i select "RSB" from the drop down menu in cell A19, i want cell F18:19 to be the sum of cells A7 and A9, with respect to the Project selected from cells A7:16. If I select "FRG" from the drop down in cell A19, i want cell F18:19 to reflect the sum of cells F8 and F10.
View Replies!
View Related
Automatically Filter Worksheet Based On Changing Cell Value
Private Sub Worksheet_Change(ByVal Target As Range) ActiveSheet.AutoFilterMode = False Range("d6:g6"). AutoFilter Range("d6:g6").AutoFilter Field:=2, Criteria1:=Range("e1"), Operator:=xlAnd, _ Criteria2:=Range("e2") I've attached the file also. What i intend to do is that : (1) if i enter a value in E3, the filter should only apply using E3 value (currently its applying E3 value but if E4 is kept blank, it takes that as = " " ) . Unfortunately, i need to have the and condition, so i have to find a way in spite of this condition. Any way out ?? (2) If i enter values in A) E3 & F3 B) E3, F3, G3...then it ahould make multiple filtering possible. But when i try to apply such a condition, the same problem as in point (1) occurs, it takes the and empty criteria range as = " "
View Replies!
View Related
|
TRACKER
