# Reference Range Cells From VLOOKUP Results

Oct 22, 2009

I have a workbook where one tab contains the data that I am given:

Project-A data1 data2 date1 date2 date3
Project-B data1 data2 date1 date2 date3
Project-C data1 data2 date1 date2 date3
Project-D data1 data2 date1 date2 date3

In another tab, I have a report where a row containd the project name, and I want to find the maximum value of the dates in the ither tab, where my project name matches the project name on the tab data I am given.

So, if I have "Project-C", I need to know the max of the dates in the row for Project-C; but I dont know what row that will be on in the other tab. For info such as 'data1' I have been simply using vlookup using the project name as a key.

## A Formula To Reference Another Page And Skip Cells To Get To Desired Results?

May 14, 2014

I am trying to find a formula that will allow me to reference another page in my spread sheet. In my scenario the row I am referencing has 5 consectutive numbers after the reference point I would use for a vlookup. These numbers are all zeros except for one number in each row which will be a positive number greater than zero. I want my formula to check the first cell in the row, if it is not a zero it will show that number, but if this cell contains 0, then it moves over to the next cell. If all the cells in the row have a zero, then i just want zero to appear on my summary sheet. I tried to do a vlookup with an IF function, but I couldn't get it to work. I have attached a sample of what the spreadsheet looks like.Example doc.xlsx

## How To Reference Cells Value To Define Range Reference

Jul 14, 2014

Is it possible to reference a cells value to define a range reference?

[Code] ......

I am trying to define the row value in the range reference with a value in a secondary cell?

## Auto Updating Range Reference In Vlookup Macro

Feb 20, 2008

I have the following macro which runs vlookups between two sheets in excel. Whenever i add columns to the range the vlookup column reference is not the correct cell. Is their any way I can adjust the macro so that the column number adjusts in the formula when a new column is added??

Sub template()
Range("C7").Select
ActiveCell.Formula = "=if(VLOOKUP(C6,'Project master'!B7:BG150,3, FALSE)="""", """",VLOOKUP(C6,'Project master'!B7:BG150,3, FALSE))"
'category
Range("c9").Select
ActiveCell.Formula = "=if(VLOOKUP(C6,'Project master'!B7:BG150,4, FALSE)= """", """",VLOOKUP(C6,'Project master'!B7:BG150,4, FALSE))"
'sub-category
Range("c10").Select...................

## Find Dates Between Monthly Range And Sum Another Cells Results That Are In A Range

Oct 10, 2009

I'm trying to make a by month spreadsheet that has all twelve month ranges starting in for a3. in a3 it would have the start date and in a4 it would have the end date. I'm trying to locate all of the dates between those two dates and pull in the profit ammounts from another sheet, the results would be in row 5. I would also like to pull in the loss amounts and have them in row 6. All corresponding with the date range in rows 3 and 4.

## Cascading Ranges - Vlookup (make My Reference Range Access Multiple Columns)

Feb 10, 2010

I need to run a vlookup to find some data. But I have a lot of data about 600,000 lines. Currently this list is spread over several columns (as the limit is something like 50000). How can I make my reference range access multiple columns?

## Excel 2007 :: Searching Range Of Cells For Certain Characters And Displaying Results

Jun 25, 2012

Column A & B has a list of Supplier Part numbers and Buyer Part numbers as below.

Supplier P/N

HGFYE/12
111111

HYEYDH/14
222222

[Code] ..........

Cell D2 is an open cell that any data can be entered into as a search term. What I am trying to do is search for a Supplier P/N that have the characters "H", "G" or "E" in it, so entering "HGE" into cell D2 would display the results into columns F & G as below.

Supplier P/N

HGFYE/12
111111

HDGTEY/56
333333

I can easily do a formula for 1 character or a string of characters.

To complicate it further, if the search term has in this example has "YFF", I would like the same formula/code to workout that the result in F & G should show this time

Supplier P/N

YHDHFF/58
555555

I am using Windows 7 and Excel 2007.

## Copy Pairs From 2 Columns To Formula Reference Cells & Copy Updated Formula Results

Jun 24, 2008

I am currently working on a data analysis project (data mining) and need to collect and later analyze statistics for the inputs which control a series of calculations. These statistics are shown in the Statistics 1, Statistics 2 and Statistics 3 cells in the workbook that I attached. The inputs are X,Y; all possible values for these inputs are listed in the N,O columns. Basically I need a macro which would take the values from these two columns and place them pair after pair into the controlling cells (K3, L3), then it would copy cells H2 through L3 (updated stats) to a new sheet after each copy operation - so that I will finally have a list of statistics for all of the input pairs.

## Dynamically Reference Range Of Cells?

Jun 27, 2012

I have a 'receipt' worksheet with about 500+ lines of data, here is an example of what could be in cell A166: 1.1 NET_AMOUNT 742,523,253.83

In another worksheet, is there a way to look at the whole worksheet for column A in receipt to say:
if 1.1 is true, capture text to the right of 'NET_AMOUNT' then format using =Dollar?

## Reference Range Of Cells For A Log From Closed Workbooks

Nov 20, 2012

I've been trying for a while not to reference a range of cells in closed workbooks to create an ongoing automatic log.

I've attached a test log with the details required and the cells are staggered, I have been using the formula;

='NETWORKPATHFOLDER[121113 12.42.xls]Enquiry Form'!\$G\$1

then the cells are (SAME PATH) J1, A5, A7, A9, A11, A13, A15, A17, A19, A21, A31, B31, E31, J31

and the attached test will placed then in that order under their respective headers.

So, all in all, is there a VBA code where it can run it from closed workbooks in the order above?

log text.xls

## How To Add Reference To Array Or Range Of Cells In SQL Query

Jul 15, 2014

Using VBA, I am importing data from Access (Access2007) into Excel. The code that I use works as required but I need to refine it so that I can filter for the records that I need to import. I can import ALL records. I can import selected records by manually changing my SQL query. I would like to reference an array of values or a range of cells so that I do not need to manually change my query every time I want to run it.

[Code].....

This code gets all records for "Jeff" and "Bob" from the Access database. I need to query about 100 names at a time so manually inputting them is going to take forever. How I can make it work?

By using an array:
[Code].......
or
by using a range of cells:

## Fastest Way To Reference A Cell - Range() Or Cells() ?

Dec 22, 2008

I have a loop that executes roughly 7.7 million times when my VBA program runs. Neednless to say it take a long time to run - usually a couple days.

The inner-most loop contains a line of code from way back in my early vba programming days when I knew even less than the small amount I know about programming now (and if you can follow that sentence you might be able to understand some of the spaghetti code I write :-) ).

for k = 1 to n

if Worksheets("Personell").Range("D" & Trim(Str(k))) > dtCompleted then ...

Would it be faster to use this syntax (which I just found out about):

if Worksheets("Personell").Cells(k,4) > dtCompleted then ...

It would cut out 2 functions calls, trim() and str(), so it would be faster, right?

I am almost embarassed to post the Range("D" & Trim(Str(k)) line of code because it looks so convoluted now, but that's how I learned to program, just fumbling through until it ran...

So in short, I just wanted to confirm that the cells() syntax run faster before I spend an hour editing and tested.

## Cells Range Reference Not Working Properly

May 25, 2009

I often have problems with the cells range reference method. For some reason I get an error and can't tell why. Other times it works fine. Is this just an unstable method to use or is this completely wrong? I'm assuming I am using the wrong syntax. It is definetly the range reference causing the problem.

Dim cnt1 as integer, cnt2 as integer,cnt3 as integer

cnt1 = 2
cnt2 = 50
cnt3 = 2

dim myrange as range

set myrange = sheets("sheet1").range(cells(2,cnt1), cells(cnt2,cnt3))

I have no idea why it won't work. I'm basically searching a columner range of cells.

## Use Range Of Selected Cells As Relative Reference

Mar 8, 2013

I have a data that is split into multiple cells and needs to concatenated. Unfortunately, the number of columns wherein lies the data varies throughout the workbook. As such, I wanted to select a range of cells, define this range as the reference point for the macro, run the macro, then move on (selecting a different number of columns on the next try).

For example, I have this:

A
B
C
D
E

1

No
Not Very Far

[Code] .....

I want to select cells A1:B1, run a macro concatenated the two columns, then select cells C1:E and run the same macro to get this:

A
B

1
No
Not Very Far

[Code] ........

So far, I have this:

Code:
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Offset(5, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[1],RC[2])" '

## Reference Cells Relative To Activecell In A Range

Nov 4, 2008

I've been trying to get the sum of a range of cells based on their relation to the active cell. Here is the Pseudo
If the SUM of (the cell 2 rows up THROUGH the cell 2 rows up and 2 columns to the left) = 0 then .......

*****end pseudo

I think that activecell.offset is the best way to do it, but I can't figure out how to work that in a range....

Here is my code so far:

Worksheets("Generic SPC").Activate
Worksheets("Generic SPC").Range("c2").Select

'Do for all cells in the row
Do

ActiveCell.Offset(0, 1).Select
If Application.WorksheetFunction.Sum(Range( _
ActiveCell.Offset(-2, 0), ActiveCell.Offset(-2, 2))) = 0 Then
ActiveCell.Value = 0
ElseIf Application.WorksheetFunction.Sum(Range _
("ActiveCell.Offset(-2, 1), ActiveCell.Offset(-2, 3)")) = 0 Then
ActiveCell.Value = ActiveCell.Offset(-2, 0).Value
Else: ActiveCell.Value = 0
End If

Loop Until IsEmpty(ActiveCell.Offset(0, 1)) = True

******End Code

Range doesn't like activecell.offset. Does anyone know how to do this?

## Reference To A Range Of Cells As Input For A Macro

May 13, 2009

I want to create a macro which asks some information like functions do. For example, I would like the macro to ask for some data to be filled by the user. For that purpose I thought about using the inputBox vba function. I wanna fill the field in the inputBox with a reference to a range from an excel sheet. So, I click on a cell and then I try to hold ctrl + down cursor to get all the range starting from that cell on until the last consecutive filled cell on the sheet. Nonetheless, the problem I have is that when I do ctrl+down cursor, I just do not get the reference I want, because it seems that the ctrl+down cursor combination does not work in the inputbox for filling a reference to a range of cells. Does anyone know some workaround to achieve this?

## Convert Cells Reference To Range Address Code

Sep 7, 2006

I have to convert a range reference in the form of

Range(Cells(1,1),Cells(2,2))

to a reference in the form of

"A1:B2"

## Vlookup If Range Of Cells Greater Than 0

Jul 3, 2014

I have made a spreadsheet that essentially works out a cost dependant on which department the work was completed in.

I have a seperate tab with the department names in column A and the charge rates in column B

On the second tab I have a spreadsheet that has columns A and B for hours used and material cost.

The third column is the dept name

The 4th column is the variable charge rate which changes dependant on which department is put in colum 3

The 5th Column is the total cost.

In the 5th column I have this formulae:

=VLOOKUP(F7,'Variable Costs'!A3:B16,2,FALSE)

This work fine.

However, the spreadsheet has about 200 entries per week and all the cells in column 4 and 5 are showing "#N/A" becuase there are no values for them to work with.

I don't like seeing this and normally will use the "IF" function to say that if column A and B are greater than 0 then do the sum, if not show "0"

This for some reason doesn't work with VLOOKUP. Not the way I am using it anyway

=IF(D7:E7 >0,"=VLOOKUP(F7,'Variable Costs'!A3:B16,2,FALSE)",0)

## Vlookup: Search A Certain Range Of Cells

Apr 3, 2008

1. can i limit vlookup on sheet2 to search only a certain range of cells on a sheet1.

example, I want vlookup to look in sheet1, colum A - Y but only look between rows 1 and 100

2. If 1 can be done, and i happen to add a row in worksheet1 (making it 101 rows vice 100) will the vlookup code on sheet2 include that 101st row or do i have to redo the range manualy.

## Multiply Range Of Cells By Cell Reference Without Changing Hard Coded Values?

Aug 20, 2014

I am trying to multiply a range of cells by a cell reference. The cells currently have hard coded values in them. I know with past special you can multiply a range of cells by a copied #. I want a similar function to that just instead of a copied cell its a cell reference. No VBA.

## Add Two VLOOKUP Results Together In VBA

Aug 20, 2014

I have a macro which returns the results for

=IF(ISNA(VLOOKUP("EMPLOYEE",Z:AA,2,0)),0,VLOOKUP("EMPLOYEE",Z:AA,2,0))

As of today, the entry EMPLOYEE is sometimes written as it was EMPLOYEE, and sometimes as EMP. I have to add these two together. How do I change the code of the macro?

Before it was:

ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(""[EMPLOYEE"",C[-16]:C[-14],2,0)),0,VLOOKUP(""EMPLOYEE"",C[-16]:C[-14],2,0))"

I tried "=IF(ISNA(VLOOKUP(""EMPLOYEE"",C[-16]:C[-14],2,0)),0,VLOOKUP(""EMPLOYEE"",C[-16]:C[-14],2,0))"+
"IF(ISNA(VLOOKUP(""EMP"",C[-16]:C[-14],2,0)),0,VLOOKUP(""EMP"",C[-16]:C[-14],2,0))"

as I would in Excel, but of course it doesn't work.

Mar 4, 2009

I have this function trying to added 4 VLOOKUP results together, but it only works for 2 rows and all others show as #NA.

=VLOOKUP(A8,MA!\$A\$8:\$H\$10,3,FALSE)+VLOOKUP(A8,PQ!\$A\$8:\$H\$10,3,FALSE)+VLOOKUP(A8,ON!\$A\$8:\$H\$19,3,FALS E)+VLOOKUP(A8,TW!\$A\$8:\$H\$22,3,FALSE)

## Vlookup Results

Oct 11, 2006

I wan to create a table using 2 column of data.Below are the data

Type Name
A Danon
B Chris
C Hilton
A Jin
A King
B Ling
C Seng
D Aaron
B Halim

I wan to convert these data to a table which have four dimension. A, B, C, D. With Vlookup function, i can do it. But, i find difficulty when the result is overlap, when in the type A, got Danon, Jin, and King. My result only can lookup is King. Other cannot display.

## Cell Reference- Able To Reference Two Cells To The Left Even As More Cells Are Added

Jun 11, 2008

I have 5 columns set up: A,B,C,D,E
D is the sum of A and B
E is the sum of A,B,and C

As I add in a new column to the right of C (call it C2), I need D (which has shifted over one) to sum A,B, and C.

I also need E (which has also shifted over one) to sum A,B,C, and C2.

Essentially I need a function in a cell that will be able to reference two cells to the left even as more cells are added.

## Excel 2003 :: Dynamic Named Range Of Non-contiguous Cells Suitable For Chart Series Reference

Nov 11, 2012

I am running Excel 2003 on a Win7 system.

Here is my situation:

Each of my data sets spans roughly 75 columns by 250 rows at present, but this could expand. The first 7 rows contain metadata. Columns 2-25 or so contain the raw data, from which everything to the right is calculated. The data sets have most columns in common, but not necessarily all.

In order to tease out the most meaningful information from my data, I frequently sort all or part of it based on varying criteria. When I find a useful sorting criterion, I create a new column with a header that describes the criterion and populate it with a formula that returns a 1 if the condition of interest is met for that row, or a 0 if it is not. For example, if I am doing this in column AA, I might enter

=--(AND(\$AX8>\$AA\$4,\$Y8>0))

and copy it down to the end of the data. The resulting vector of 1s and 0s quickly re-identify data that meets that criterion even after subsequent resorting. It also makes locating data that meets multiple sorting criteria extremely simple. Essentially, I create a truth table.

Cell \$AA\$4 in the above example contains a "comparator" value I might wish to change at some point, which would change the subset of data the condition selects for.

Here's the first hard part:

For each data set, I need the ability to generate meaningful plots that includes separate series based on the criteria I have described. However, I also need to retain the ability to resort the data or change the comparator value without disrupting these plots. In other words, the plots must NOT change when the order of the data is changed, but MUST change to display the appropriate data when the comparator changes.

Here's the 2nd hard part:

Once I have this working for one data set, I need to be able to port it to other data sets (which are contained in other workbooks), so that I can compare equivalent plots from each. I also need to minimize the number of manual steps involved in doing so, to avoid human errors and excessive time consumption.

The only other possible complication I can think of at the moment is that, to this point, I have been inserting blank rows to isolate subsets that I do not wish to perform further sorting on from each other.

Right now I am angling toward VBA code that loops through the entire data set to generate base dynamic ranges using the column header row (row 1) as the names, and the entire column of data for the rangeloops through the truth table columns to generate "branch" row ranges for each of the sorting conditions,loops through the entire data set one more time to create "branch" ranges for each of the base ranges.

I could generate some code to accomplish a one-off solution for a given configuration of a single data set (provided there is not a list length limit in a chart series that I'd be violating)...but without a dynamic named range, I don't know how to get to something that would update appropriately. So in essence, I am still stuck at the dynamic range part of this.

## How To Use Results Of VLOOKUP In Calculation

Apr 9, 2014

Worksheet1 has data.
Worksheet2 uses VLOOKUP back to worksheet1 for data to populate cells on worksheet2.

The VLOOKUP command below returns a value of 126 to cell C6. I want to use the number result of the VLOOKUP in C6 (126) as part of a calculation for another cell. In thiscase, cell D6 is keyed in. I want to get the result of =(N(D6)-N(C6)) and put it in cell F6. To further complicate things, if C6 is blank, I want F6 to be blank.

VLOOKUP formula that returns a value of 126 from worksheet1 and puts it in cell C6 on worksheet2. It also returns a blank if there is a blank on worksheet1.

=IF(ISNA(VLOOKUP(A6,Pivot_Table!\$A\$3:\$D\$36,3,0))+(VLOOKUP(A6,Pivot_Table!\$A\$3:\$D\$36,3,0)=""),"",VLOOKUP(A6,Pivot_Table!\$A\$3:\$D\$36,3,FALSE))

Dec 10, 2013

Add together D3+G3+J3+M3+Q3+U3 and put the result into W3? I think the reason it wont work is because there will not always be a value in D3/G3 and J3/M3

## VLookup With Multiple Results

May 8, 2012

I have a large spreadsheet of data, with lab results by date. Sometimes, there are multiple data points for a single date.

I need to produce monthly reports that list all the results for specific tests in a given month.

So, for example:

Date SG Property Tank
1/5/12 1.015 567324 2044
3/15/12 1.002 568210 2103
3/18/12 1.025 568056 2044
3/18/12 1.036 565200 2102
4/1/12 1.019 566713 2103

I would like to make a report for March that looks like this:
Date SG Tank
3/15/12 1.002 2103
3/18/12 1.025 2044
3/18/12 1.036 2102

And the next month, make a report for April, then May... etc.

I tried to think of a way to do this using vlookup or index/match, but couldn't figure it out.I also tried using filters and then automating some kind of copy/paste, but there is an extra line between the heading and the data, so that the date column is filtered as text instead of date. The source data is not my spreadsheet, so that would be difficult to change.

## VLookup With Multiple Results?

Dec 17, 2012

I would like to do a lookup function, in which multiple results are returned.

i.e.

VLookup Result 1
Result 2
Result 3
Etc.

I know how to do it if there is only one answer but say I have a unique identifiers in which I want all the results in column B:B displayed one after another, say locations have numerous products sold/manufactured at it.

## Hyperlink To Another Sheet Using VLookup Results

Apr 22, 2013

I have a Sheet named Main, Juz and another called wordforword.

Currently on Main sheet I have a vlookup that displays the results from wordforword. Instead of showing the result, I would like the user to be directed to the wordforword sheet result, with the click of a hyperlink.

I do prefer formulas over macro if possible.