Vlookup And Offset: The Figure Of 10 Rows Across And Then 2 Rows Down From That Point
Jun 22, 2007
I have done a search and I think this is possible but just can't convert any of the examples to work for me.
This is the calcuilation I would normally enter -
=VLOOKUP(A80,'[CF227.xls]Sheet1'!$A$9:$J$9,10,FALSE)
However I don't want the cell 10 rows across when a match is found I want the figure 10 rows across and then 2 rows down from that point. i.e. if the match is in row A9 I want to return the figure in J11.
View 4 Replies
ADVERTISEMENT
Feb 19, 2010
I need the macro to put Rec sheet data for each bank in Sheet Summary under the Each bank header, the macro needs to figure out the number of rows required and adjust if necessary as the rec data is not fixed. See below examples
Rec ABCDEFG5CategoryRecAccountValue DateEntry DateTypeAmount6CITIBANKCASH AMP1WTGDPFUSD1-Oct-075-Dec-08LDR-2,203.677CITIBANKCASH IMS02WTFPT2USD24-Jun-0817-Dec-08SCR2,001.388JP MORGANCASH DESWTFPT2USDSUSP15-Sep-084-Sep-09LDR-3,608.919RBSCASH DESWTFPT2USDSUSP15-Sep-084-Sep-09LCR3,889,582.9410BOACASH DESWTFPT2USDSUSP16-Sep-0829-May-09LDR-1,557,609.9511BOACASH IMS09ZPBRKQUSD17-Sep-0817-Dec-08LCR4,554,511.1112 13 14 Excel tables to the web >> Excel Jeanie HTML 4
Summary ABCDEF6CITIBANK 7RecAccountValue DateEntry DateTypeAmount8 9JP MORGAN 10RecAccountValue DateEntry DateTypeAmount11 12RBS 13RecAccountValue DateEntry DateTypeAmount14 15BOA 16RecAccountValue DateEntry DateTypeAmount17 18 19 20 21 22 Excel tables to the web >> Excel Jeanie HTML 4
Summary ABCDEF4 5 6CITIBANK 7RecAccountValue DateEntry DateTypeAmount8CASH AMP1WTGDPFUSD1-Oct-075-Dec-08LDR-2,203.679CASH IMS02WTFPT2USD24-Jun-0817-Dec-08SCR2,001.3810 11JP MORGAN 12RecAccountValue DateEntry DateTypeAmount13CASH DESWTFPT2USDSUSP15-Sep-084-Sep-09LDR-3,608.9114 15RBS 16RecAccountValue DateEntry DateTypeAmount17CASH DESWTFPT2USDSUSP3970640060LCR3,889,582.9418 19BOA 20RecAccountValue DateEntry DateTypeAmount21CASH DESWTFPT2USDSUSP3970739962LDR-1,557,609.9522CASH IMS09ZPBRKQUSD3970839799LCR4,554,511.1123 24 25 26 Excel tables to the web >> Excel Jeanie HTML 4
View 9 Replies
View Related
Nov 8, 2007
Is there a way to delete all rows down a sheet until Cell A contains ""
or if the criteria "" would work?
i only want to keep the data below that point.
i will have to do it 11 times for different files
View 9 Replies
View Related
Jun 27, 2014
I have a set of data, for example:
Code Manager Level
abc 123 ab1
def 456 cd2
What I need is to create output that looks like this:
A B C
abc Manager 123
abc Level ab1
def Manager 456
def Level cd2
So essentially for each code, I need two separate rows that say what heading and the value is.
View 1 Replies
View Related
Dec 16, 2006
I have a worksheet of data that I am trying to reference to. I will have the data as a "dump" on one worksheet, and lookup from that sheet. I've attached an example of the data dump. I need to essentially VLOOKUP the value (i.e. 054_6425) and then count over 3 columns, and down two rows. I've tried INDEX MATCH, but can't get that to work. Can you get VLOOKUP to count over and down and return a value? File "example" is attached.
View 2 Replies
View Related
Jan 3, 2008
given a range named xyz which refers to d1:e3
range("xyz")
where the 3rd row is blank. I want to examine the row after the range with
range("xyz").end(xlUp).Offset(1,0).Cells(1,1).value
I find that this code refers to the last row of the range which is blank. How can I be sure that I will be 1 row (offset=1) below the range?
View 2 Replies
View Related
Dec 15, 2006
I need help in simulating the functionality of the OFFSET function with some of standard Excel functions which are shown at this webpage (I will be using this converter to generate the webpage from the sheet):
Spreadsheet Converter Supported Functions
I ask this because I need to create an interactive online calculator which absolutely needs to have the ability to allow the user to select the RANGE of the data to be analyzed. I included an example sheet with the offset function in the pink cell…Do you think any other standard excel function can be used to simulate its functionality?
View 9 Replies
View Related
Mar 25, 2013
Below is a table of data I am trying to sum. In the first column, is the account code, second column has the Hotel number code, third column is the description and the 4-6 columns have amounts for March, April and May.
I am trying to sum the data for March, April and May for account 107000 using this formula, but it is only picking up the first row.
=-IFERROR(SUM(OFFSET(PnL!$C$9,MATCH($A29,INDEX(PnL!$A$10:$A$500,0),0),1,1,ControlMonth)),0) to sum
FYI:
PnL!$c$9 = is the block referred to as C9 below
$A29 contains 107000
PnL$A$10 is the block below A9 below
ControlMonth is currently set to 3
[code].....
View 5 Replies
View Related
Nov 4, 2006
I have sheet1 with four columns. Team, Issue, Discussion and Recommendation. Discussion and Recommendation headings were placed in column B along with the data in the file I was sent. I need to get it back into table format to eventually load into Access. What I'm trying to do is to loop reading down column B. When I find the word “Discussion” (exact format) I need to drop down one row below the word, cut whatever is in the row, move one column to the right, Column C, and two rows up and paste. For example, cell B3 has “Discussion”. I need to drop to B4, cut whatever is in the cell and move to C2 and paste. I also need the same to happen for Recommendation. Find the word, drop down one row, cut whatever is in the cell, move two columns to the right and up four rows. I would like to delete the rows I moved data from, but will do that by hand if it’s too hard to do in a macro.
An example of my data:
TeamText
Design4. Reuse existing code to extent possible.
DesignDiscussion
DesignNo discussion
DesignRecommendation
DesignEstablish a baseline and work to the plan.
Design5. Delete old code
DesignDiscussion
DesignNo discussion
DesignRecommendation
DesignNo recommendation
Design6. Plan to work without full details.
DesignDiscussion
DesignIncremental design
DesignRecommendation
DesignWork on smaller details first
Sub Discussion()..................
View 3 Replies
View Related
Jan 15, 2010
I have created a spreadsheet to show some reports and I wanted to serch for some datas which overloops themeselves. If you can have a look at a test file I attached you will see the full picture. I have 2 tables, where the 2nd one is on the right side of the 1st one. 1st table:..............
View 3 Replies
View Related
Jun 16, 2014
I have a name which uses the formula
=OFFSET(Query!$F$1,0,0,COUNTA(Query!$F:$F)+1)
That populates a dropdown
However I want to exclude hidden / filtered rows?
Iv'e tried sumtotal etc but didnt get any results. I'm after using the rows as options.
View 1 Replies
View Related
Jun 17, 2014
I need an OFFSET function to start off by moving down 1 row from the starting point and then on the next run through the loop, to move down 2 rows, then 3 rows, etc.
Code:
Workbooks(Thisname).Worksheets("Run Set Up").Range("Duration").Offset(1, 0).Value = Workbooks(Filename2).Worksheets("Front Page").Range("T20").Value
This is what I'm working with. At the moment, I have the value for the number of rows to move set to 1 because I can't figure out how I'd accomplsih what I need.
View 1 Replies
View Related
Jan 29, 2010
I'm using Excel 2003. The sumif function will not allow me to ignore hidden rows in my data set that have been autofiltered. I tried using the following formula and it worked:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(CopiedB!$I$2:$I$65000,ROW(CopiedB!$I$2:$I$65000)-ROW(CopiedB!$I$2),0,1)),--(CopiedB!$A$2:$A$65000=$A22),CopiedB!$I$2:$I$65000)
The problem I am having is that the sumproduct and offset funtions used to ignore hidden rows are considered "volatile" and force a recalculation. Moreover, I have this fomula pasted 100+ times and this dramatically slows things down even when I turn autocalculate off. Is there another way to accomplish a sumif with a user defined custom function in VB? I really need to have the ability to do a sumif that ignores hidden rows and is not dramatically slow.
View 9 Replies
View Related
May 4, 2009
i have a range of cells which is nxn where n>1. i want to place a header 2 rows above this range which is aligned on the left. this is what i tried:
View 3 Replies
View Related
Dec 6, 2009
This is almost embarrassing, but I have a simple VLOOKUP function that I can't seem to figure out. On Sheet2 cell A11 is the value I want to look up in Sheet1. Sheet2 B12 is the cell I am entering the VLOOKUP function. The value I want entered is in column 7 of Sheet1. I hope I have explained this enough. My formula entered is =VLOOKUP(A11,Sheet1!$A$9:$A$42,7,FALSE).
For whatever reason this is not working, and I am getting a #REF! error.
View 3 Replies
View Related
Feb 4, 2014
I am trying to find a macro that can search a sheet for any cell that contains the text "Not on AOI" selects a range that contains that cell, 81 rows below, and 2000 columns to the right, then cuts the selection and pastes it 162 rows below the original cell where the text was found. What's hard is that the number of columns between the "Not on AOI" cells is variable.
I'm very new to excel macros and the parts I think I've put together are:
Cells.Find("Not on AOI", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=True).Activate
[Code].....
View 1 Replies
View Related
Jan 3, 2010
The number of rows you are pasting and then you can select that same number of existing rows and use "insert". This is what I'm trying to achieve:
(before paste)
A
B
C
(after pasting unknown number of rows)
1
2
...
n
A
B
C
Is there really no built-in way to do this?
View 6 Replies
View Related
Apr 10, 2014
I have an excel file with thousands of rows and I'm trying to pull certain data from one tab to another. In the example, I want to pull all Listings that are "Open" (I know, just filter), but if the listing is "Open" I want to pull all of the other statuses it once had as well in the new tab. Notice how a listing can have multiple statuses.
I was thinking I could filter by Status (Open) and then do a vlookup (using the listing number as the lookup number), but it doesn't work.
View 4 Replies
View Related
Jul 23, 2014
I'm trying to analyse some data, consisting of a table with clients "checking in" at a certain restaurant at a certain time. In another table I have the restaurant data, including information on different discounts they offer on different days and at different times. If a restaurant offers distinct discounts for distinct days of the week (or hours), it will be listed several times. Now, for each check-in I'd like to know, how much money the client safed (all check-ins are within some of the discount hours of the restaurant they checked in at). If there were only one line per restaurant, VLOOKUP would do the task without any problem, however, as there are several rows per restaurant, I don't know how to look up the correct discount according to the date and hour the client checked in. I've attached a data sample.
View 1 Replies
View Related
Feb 5, 2009
Just learned a little about vlookup and wondering if it can be use to solve the following. I have attached "worksheet1" that shows what I'm trying to do. The "detail" sheet is downloaded from another software package. There may be one to five rows of Product #'s with their component # and percentage on the same row. I would like (on a new sheet) put one Product # and all the corresponding component #'s and percentage's on the same row. (An example of how I would like it to be is on the "one row" sheet.)
View 3 Replies
View Related
Feb 18, 2014
My workbook contains four sheet: input, letter 1, letter 2, data
Data contains customer records, column A is numbers 1-500
Input sheet is a customer form containing details for 1 customer. The customer details are pulled from the data tab using vlookups based on a value in cell a1 on the input tab.
Letters 1 & 2 are letters to a customer. They again use vlookups to populate various fields on the letters with the customer information on the input sheet.
What I need:
A macro to populate the input form by working down the records on the data sheet. when the input form is populated with customer information it is to export the letters as a pdf and then move on to the next row of customer details.
I have the export to pdf macro, i just cannot get the input form to loop through the customer records..
View 1 Replies
View Related
Oct 13, 2009
i am trying to create an interactive calendar that displays what is happening on different dates depending on the activity. i.e. if 'workshops' is selected then it will show all the workshops happening in the month. the problem is that i have created a new line for each workshop in my table array and want it to display all the returns in a single cell on the calendar. I am using a vlookup and have used a unique reference so that it picks up multiple rows, but it will still only return one value.
is this possible or do i need to collate all the data into one cell? I have attached the spreadsheet as i am sure the above prob does not make sense.
View 2 Replies
View Related
Apr 10, 2012
The row in blue is a successfully created result from my VLOOKUP formula:
The row in red is unsuccessful.
=VLOOKUP($A$11,$A$3:$G$9,(COLUMN($B$2)))
The above formula lives in the blue "Pottery" cell (cell B11 in my spreadsheet).
How can I get this VLOOKUP formula to work in rows below it?
When I just copy and paste the formula into row 12 (in red text), the formula still refers to row 11 (in blue text), and I get the same exact results no matter what unique signifier I put in (see the red row for unsuccessful results).
It's strange because in the successful blue row, I simply copied and pasted from cell B11 through G11, and everything came out fine due to the relative(??) identifiers, but no luck transferring that formula to rows.
My unique identifiers seem to be off the page, but they are just 1 (starting at Old Masters) through 6 (at Impressionist).
When I type 6 (for the red row), I get the same results as for when I type 5 (for the blue row), so my unique identifiers aren't registering somehow.
Item Code
Dept
Description
Qty.
Price
In stock
Line Total
1
Old Masters
Painting
100
5
2
New Masters
Sculpture
200
10
[Code] ........
View 9 Replies
View Related
Nov 26, 2012
I am writing some code for a fast vlookup. I have the following template. The formulas in column AU and AX need to be automated. actual file has 170000 rows, so the vba code needs to be fast.
AU formula is: =VLOOKUP(A2,$BA$2:$BC$180000,3,FALSE)
AX formula is: = IF(AN2 = "Test1","To be adjusted",
IF(OR(AN2="Test2",AN2="Test3"),VLOOKUP(A2,$BE$2:$BF$180000,2,FALSE),
VLOOKUP(A2,$BA$2:$BC$180000,2,FALSE)))
View 9 Replies
View Related
Dec 31, 2009
as i look at examples all over the place for this... im not sure why my code is giving me an error...
Dim lookForRng As Range
Dim lookFor As Range
Dim rng As Range
Dim found
Set lookForRng = ThisWorkbook.Sheets("Sheet2").Range("C3")
Set rng = RS.Sheets("Sheet1").Columns("A")
For Each lookFor In lookForRng
found = Application.VLookup(lookFor.Value, rng, 1, 0)
If Not IsError(found) Then
RS.Sheets("Sheet1").Unprotect Password:="7/*-+7"
lookFor.EntireRow.Delete
RS.Sheets("Sheet1").Protect Password:="7/*-+7"
End If
Next lookFor
I'm getting a "delete method of range class failed" on line "lookFor.EntireRow.Delete" but... im not really sure why its failing
View 9 Replies
View Related
Feb 18, 2013
I've got a spreadsheet with several rows and the numbers within those rows are totalled up at the bottom. Is there a way where i can highlight the cell within the spreadsheet and it takes it off the accumulative total at the bottom? or something similar to this effect? I just don't want to have to remove figures from this spreadsheet and highlighting will be a good way of seeing what has been done
View 1 Replies
View Related
Apr 6, 2014
I am designing a workout template i have a table with sets and reps and i would like to be able to select a heading and return all of these values to a specified box.
I have attached the file : excel help forum.xlsx
View 2 Replies
View Related
Dec 3, 2009
I'm trying to get a result of all rows in a table that has the same lookup value.
For example:
David25
Brian40
Steve57
David68
David902
John11
Gerry3
I want to look for David and that the result will be all rows that starts with David:
David 25
David 68
David 902
I tried using vlookup but it always returns the first row.
View 3 Replies
View Related
Sep 13, 2013
I have used VLookup function to reference some information between books. The formula used is:
=VLOOKUP('[Book1.xls]Sheet1'!$A15,'[Book1.xls]Sheet1'!$A$7:$BA$67,12,0)
When I drag down I want the formula to skip from $A15 to $A25 and so on. Is there a way to do this? Or am I better off using another formula?
What I am trying to achieve is:
Book2 Cell D54 = Book1 Cell L15
Book2 Cell D55 = Book1 Cell L25
Book2 Cell D56 = Book1 Cell L35
View 3 Replies
View Related
May 28, 2009
I'm using a vlookup formula, and the thing I'm searching by is a part number. The problem is that in my table array, all the cells for part numbers have that green error flap in every cell, saying "The number in this cell is formatted as text or preceded by an apostrophe."
Some part numbers have letters on the end of them, ie: 12345N. When I go into a particular cell's error drop down menu and choose "Convert to Number," the vlookup will work with this cell. My problem is that the table array is several thousand rows, and I don't know how to fix them all at once.
View 3 Replies
View Related
