Lookup Based On Variable/Changing Sheet Name

May 23, 2008

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 9 Replies


ADVERTISEMENT

Changing Vlookup Ref Column Based On Vb Variable

Jun 20, 2007

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 7 Replies View Related

Changing Value In One Sheet Based On Value In Another?

Apr 22, 2013

A workbook that has a main sheet, and a few other sheets that main data is copied to. The main sheet is a complete list of work, the other sheets are split down to each machine we use here, to keep track of machine capacity.

What I want Excel to do:

Each works order has a number of hours attached to it (Which is manually entered on each sheet). Once a week a button will be clicked and the VB code needs to look at the different machine sheets, and do some kind of lookup between the works order number on the main sheet. If it says "Complete" in column H on the main sheet then it needs to put the number of hours to zero on the machine's sheet.

View 1 Replies View Related

Double Lookup (lookup Variable Row And A Variable Column)

Mar 27, 2009

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 3 Replies View Related

Name Sheet Automatically Based On Changing Cell

Aug 20, 2008

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 5 Replies View Related

For Loop For Changing Formulae Based On Other Sheet

Jul 27, 2006

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 5 Replies View Related

Extract Data To Another Sheet Based On Variable Conditions?

Mar 24, 2014

I have some problem with the extraction, I would like to extract the data to another sheet based on variable conditions but I can not do it. My code in just case working if the all conditions are specified. The target is that if want the code could extract the data based on one condition even, or two or three or more.

View 7 Replies View Related

Find First Non Blank Cell And Return Number Above It Based On Lookup From Other Sheet

Oct 18, 2013

I have two spreadsheets.

spreadsheet 1:
Lookup from Order numbers listed from A5:A177.
requested formula in I5: I would like a lookup to sheet 2 based on the order number (F19:F191), to return the cell above the first non-blank value.

spreadsheet 2:
Lookup value:Order number listed from F19:F191.
Data search:AY19:CI191
return the (date) which is in the range above the data search from row AY18:CI18.

I've had a look at few forums but i'm getting mixed responses, having to use index / match / lookup / min / --.

View 6 Replies View Related

Lookup With Criteria: Amount To Be Written In Sheet 1 From Taking Value From Sheet2 Based On The Code

May 11, 2009

1 . Now i need amount to be written in sheet 1 from taking value from sheet2 based on the code. The code & description column has the same value. but it will be placed in different order

Ex:

the amount should get filled up only for the status yes & no . others it should return null.

View 3 Replies View Related

Changing Cell Color Based On Changing Values

Sep 16, 2009

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 2 Replies View Related

Changing Cell Background Color In Another Sheet Based On Cell Value

Jun 13, 2013

I am working on a workbook with 200+ sheets and an index linking to each of the sheets. On the sheets there is a cell containing the results of calculations and I need the cell referencing the sheet in the index to change background colors depending if the calculations value is greater or less than 0. I currently have working code that also changes the tab color of each sheet depending on the value, but i am trying to add the index cell background change functionality into that. also, the index listings is subject to change. my working code is

VB:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("d34").Value < 0 Then
Me.Tab.ColorIndex = 3
ElseIf Range("D34").Value > 0 Then
Me.Tab.ColorIndex = 4
Else

[Code]...

and the code i am trying to get working is

VB:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngX As Range
Set myRange = Worksheets("Index").Range("A1:A500").Find(Range("C2").Text, lookat:=xlPart)
' the C2 is the reference the title that would be on the index
If Range("d40").Value < 0 Then

[Code]....

I keep getting the runtime error '1004: application-defined error or object-defined error the thing is, i ran the cell background changing code in a normal macro before integrating it in my other worksheet code first and it worked fine. I have a little code experience, though not much in vba and I am unsure what the issue is. if there is another better way to accomplish what I'm tring to do, that would also be fine.

View 4 Replies View Related

Changing Variable According To For - Next

Apr 18, 2007

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 2 Replies View Related

Stripping Out And Changing Time From Variable With VBA

Jan 31, 2014

I have a macro that needs to work with data including different time from different sources.some of the cells are date and some are text.

I have the following function

Public Function TidyDate(zdate) ' This is coming in as a date #01/02/03 08:00:00#
Dim TempVar1, Tempvar2 As String
TempVar1 = zdate
Tempvar2 = Left(TempVar1, 11) & "00:00:01"
TempVar1 = Tempvar2

[Code] ....

What i need is to change the time of the variable to 1minute past midnight but keep the date the same.

View 5 Replies View Related

Changing Range Selected With Variable

Jan 17, 2009

I have some code that uses offset to select a column of numbers

View 2 Replies View Related

Find Max Value In A Changing (variable Range)

May 11, 2009

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 3 Replies View Related

Changing Font Of A String Variable?

Nov 28, 2012

I'm taking some university classes and one of them is called Applications Programming and we have been using excel VBA. I have a project that I'm working on, and it's very basic. Basically we need to create a userform that has 3 option buttons, 4 check boxes, a couple labels and a couple text boxes. It's suppose to be a ice cream selection terminal. Option boxes for basic flavors of ice cream, check boxes for toppings, one text box for user input of number of scoops of ice cream and the last text box to output a reciept/overview of purchase.

So here's the question... I was wondering if there's a way to change the font of a single string variable, or change the font of a word within a string. My output to the saleBox(My receipt overview) is this:

saleBox.Text = ("Thanks you for your Purchase") & vbCrLf & vbCrLf & _
"You ordered " & numOfScoop & " scoops of " & flavorChoice & "ice cream" & vbCrLf & _ "Topped with: " & toppings & vbCrLf & vbCrLf & total

(not sure how the code is gonna look in the message but I'm sure you get the idea)

i want to be able to change the font for the flavorChoice or toppings variables. They are both strings.

Anyways, I know I can get away with basically what I have there and get full marks, but I like going above what the basics are. If it's kinda complicated.

I'm gonna attach a screenshot, more info is better than not enough right... nevermind its askin for a url!

*** I just now got the idea of putting another textbox over the area that would output the text I want to change and hide the border or something and then change the font of that text box through the properties ***

View 3 Replies View Related

VBA: How To Program A Changing Variable Name In A Loop

May 20, 2006

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 9 Replies View Related

Changing Data In Lookup Table?

May 26, 2014

I am creating, essentially, a product order form (actually a shopping list creator for my wife). Containing 'Quantity', 'Product Name' and 'Price'. When I enter the product name via a drop-down list, the price is displayed using vlookup.... no problem so far.

My problem is... or should I say, my desire is, that when the vlookup price is displayed, if I (my spouse) know the current price to be different from the one retrieved from the lookup table, I would like to post the new price and modify the data in the lookup table so that any future requests for that item will reflect the new price.

View 8 Replies View Related

Changing Lookup Formula To IF Statement

Feb 12, 2013

How the Lookup will break down within an IF statement?

Original

HTML Code:
'ActiveCell.FormulaR1C1 = _
"=IF(R[-1]C[-2]"""",LOOKUP(1000,SEARCH({""Principal"",""Foreclosure"",""Interest"",""Premium"",""Discount""},RC[-1]),{1,1,2,2,2}),"""")"

Non-Working If statement. I realize that the Lookup isn't currently here. Including it kind of defeats the purpost of breaking this out into IF's, doesn't it?

HTML Code:
If MyFirstCell.Offset(0, -2) "" Then
MyFirstCell.Offset(0, -1) = Search("Principal")
MyFirstCell = 1
ElseIf MyFirstCell.Offset(0, -1) = Search("Foreclosure") Then
MyFirstCell = 1

[code].....

View 4 Replies View Related

Changing Font Color For String Variable?

Apr 18, 2012

how to or if it's possible to change the font color of a string variable? or is there any workaround?

View 2 Replies View Related

Chart/Graph Changing/Variable Range

May 28, 2008

I have a vast database where I have linked charts. I send the data from a macro to the database and sometimes there are 1000 inputs and sometimes there are only 20.
How would i set up my charts to only graph the inputs that are present?

View 7 Replies View Related

VLookup To Different File Dependent On Changing Lookup Value

Feb 4, 2014

Can I use a vlookup combined with IF function to lookup up to a different file dependent on what the cell says.

For Example cell a1 could have the value Alan Smith, Tony Brown or John Doe dependent on what value is entered the lookup would "lookup" against the file named Alan Smith, Tony Brown or John Doe.

View 6 Replies View Related

Variable Type Changing Number Of Decimal Places

May 14, 2014

Here's what I'm doing: I'm using a macro to assign a cell value to a variable then set another cell value to the variable instead of copy/paste (because even pasting values only was affecting other formulas in the file for some reason)

The problem: in using the macro, the number being 'copied' is acquiring a few extra decimal places IE 38334.61 is the original number and 38334.609375 is what I end up with. The numbers come from a CSV with only 2 decimal places and I checked by adding decimal places in the format so it's not a formatting/visible digits issue. Since the values are hour meter readings, the extra decimal places end up with very small values outside 0-24 hrs which messes with sorting and usage %. The values are so small all of them together in a month add up to a fraction of a cent but it's one of those things that bugs my OCD by not being right.

My VBA book explains the min/max capabilities, content type, memory bits but not fiddly details like this.

So my question is this: right now I'm declaring the clipboard-substitue variable as an single, is there a different one that would work better without adding anything? Criteria are: numeric, 6 digits before the decimal, 2 after, all positive values. (Ie 123456.12)

EDIT: fixed my senior moment.

View 6 Replies View Related

Vlookup Formula Changing Lookup Range Automatically

Apr 9, 2009

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 2 Replies View Related

Lookup Cell Value Based On Cell In Another Sheet

Jun 14, 2013

I'm looking for a formula that I can put in BA2 in a sheet called Data, and copy the formula down the column.

If Data AE2 is blank, Data BA2 should be blank.

If there is a number in AE2, the formula should look for the matching number in another sheet called Index, in Index column G. The matching number can be in any row, but will only appear once in the whole column.

For the row number that has the pair, look in Index column AG and copy the value in that cell to Data BA2.

Data

1
AE
BA

2
1
put 560 here

3
4
4

[code]....

View 2 Replies View Related

Stop Macro From Changing Onto Sheet Top Sheet And Flickering

Dec 24, 2008

How can I stop my macro from changing onto sheet top sheet and flickering away like mad??

View 2 Replies View Related

Multi-Variable Lookup Table

Jun 12, 2014

I make budgets that have line items that have standard price based on 2 factors: a description, and a number. I want to keep these items in 2 different cells. I also have different clients, and the costs are different. I could simplify so that I can use a VLOOKUP with HLOOKUP and separate the clients but I would prefer not to.

Screen shot 2014-06-12 at 12.36.47 PM.png

View 1 Replies View Related

ODBC Lookup To Populate Variable

Apr 14, 2006

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 3 Replies View Related

Lookup Function For Variable Amounts

Apr 10, 2007

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 3 Replies View Related

Use VLOOKUP Function In Macro With Variable Name For Lookup Value

Feb 7, 2014

I used VLookup function in macro. Lookup value is a variable name. I don't know how to use variable name inside of VLOOKUP function.

Use VLOOKUP function in macro with Variable Name for Lookup value

Function CC(CName As String)

ActiveCell.FormulaR1C1 = "=VLOOKUP(cname,'[Structure File.xlsx]Sheet1'!C1:C3,2,FALSE)"

End Function

How to use CName variable inside of Vlookup function.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved