I have a vlookup function that works for me in my workbook, when the data range and array is specific, but my data range will expand as will the array, as time goes on. I've used the "max" to produce the most recent results. Is there a way to accomplish this with an ever changing range and array? The most recent date will always be added to the bottom of column "A." I've used =VLOOKUP(MAX(A4:A25),A4:B25,2,0)
View 3 RepliesI have numerical data in column A rows 1:14.
I'm adding it up in row 15. Sum=(A1:A14)
Now I've been asked to insert more rows with data above row A14.
What can I put in my Sum formula in place of A14 so when I insert
a new row, any numerical data above it in column A automatically adds up?
Sum=(A1:?)
I don't want to have to go and change it to sum=(A1:A17) if I
insert 3 rows.... I have a huge file to create with sections like this. User
may have to insert new rows. They need to all add up.
What I would like to do is copy the format of a range of cells (A2:O2) and paste that format into all the other rows of data beneath it. But my last row of data will change periodically, and I don't know how to tell Excel to find the last row each time. Here's what I was able to figure out on my own:
Code:
Sub CopyFormats()
Range("A2:O2").Select
Selection.Copy
[Code]....
I have a worksheet called Sales that could contain any number of columns of data. On another worksheet, I would like to find the last two columns and use a formula like:
Sumif('Sales'!C:C, c5, 'Sales'!D:D). (I have Excel 2007 so D:D is good)
The problem is how to change the C:C and D:D part to be the last columns.
I'm looking for something simple like this:
Sumif('Sales'!Secondtolastcol:Secondtolastcol, c5, 'Sales'!Lastcol:Lastcol)
In a chart series, I have and end number for a row that I have been updating manually everytime I enter a new fuel tank fillup. Is there a simple way with the chart series to just say always go to the last data in the specified column?
See my example here, instead of the 137 in the series is there someway to just say "last data row"?
[URL]
I am trying to AVERAGE an Open-Ended (entire) column, while keeping the first 4 cells, "Out of the loop!"
Example:
=AVERAGE(E:E) less cells E1 through to E4, inclusive.
I need to average a column, without a fixed number of rows, and within an interval (and excluding zeros).
Example: Average of all numbers in the bracket 28-35, in column B2-B??
By this I mean that the total of rows in column B will vary from time to time, so in order to not having to manually change the number of rows each time, I need an "open-ended" formula, that also incorporates say 4 intervals (for instance 1-27, 28-35, 36-70, 71-100).
I'm trying to open multiple files based on an array of WBnames that are on a 'Dashboard' tab, but I only want to open them if they are not already opened. The code below doesn't seem to check if they are open and just opens everything....
VB:
Sub OpenWorkbooks()
Dim WorkbookOpen()
Dim WBnames() As String 'Array of WorkBooks to be Open
Dim WorkbookCnt As Integer
[Code] .....
I have a Vlookup which I want to modify so that it can become dynamic as the table array part of the vlookup will change.
So the basic vlookup is as follows:
=VLOOKUP($R$3,ATTRIBUTION_FACTSET!$M$60:$P$73,2,0)
but the data I am looking for wont always be in the range M60:P73.
So I tried to make it dynamic by doing the following:
=VLOOKUP($R$3,INDIRECT("ATTRIBUTION_FACTSET"&"!M"&U1&":P"&V1),2,FALSE)
The idea being that U1 and V1 would be numbers that can change so in this case U1 would equal 60 and V1 would equal 73
This vlookup is giving me #N/A and no matter how I modify it I cannot get it to work.
I'm trying to do a Vlookup on a file that gets automatically downloaded to the computer from a website. The data is in lots of different data sets, like so:
Loans to countries
Mar
Apr
May
Jun
Loans to banks
Mar
Apr
May
Jun
Every month a new row of data gets added to each table, meaning the start and end cells of the array also shift each time.
last item in we 13-9-2013 is 13-9-2013
last item in we 20-9-2013 is 13-18-2013
last item in we 4-11-2013 is 30-9-2013 as shown in the attached
1.jpg
I want to know how to copy a range into an array then an array back into an range.
Code:
Dim a(3,3) As Double
a(3,3) = Range("C3:E5")
Range("C10:E12") = a(3,3)
I am currently using this code to place names in a combobox (active X). How can I use an array to perform each line to all 12 months without repeating the code over and over?Here is what I have:
Code:
Sub auto_Open()
Sheets("FEB").ComboBox1.List = Array("ALL", "ACT", "ROF", "MM")
Sheets("FEB").ComboBox2.List = Array("ACTvsROF_2", "ACTvsPLN_2", "ACTvsLY_2", "ROFvsMM_2", "ROFvsLM_2", [code].....
I am trying to write an array formula that will work out how many minutes of tickets are open within a particular month. I have a formula shown on the first table which calculates how many minutes a ticket is open for within a particular month, but this only works for an individual ticket, Basically I neet to populate the 2nd table with an array formula that will calculate the number of minutes within the month that numerous tickets are open, effectively a SUMPRODUCT but I can't get it to work.
The right hand side of Table one doesn't exist in my main file, I am trying to populate the 2nd table using only the first four columns within Table 1.
******** ******************** ************************************************************************>Microsoft Excel - My God.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutF3G3H3F4G4H4F5G5H5F6G6H6F7G7H7F8G8H8F9G9H9F10G10H10F11G11H11F12G12H12F13G13H13F14G14H14F15G15H15F16G16H16=ABCDEFGH1 01/02/2008 Jan 08Feb 08Mar 082 3129313W00000220/01/2008 17:33:2629/02/2008 02:36:5456037 16,035.8540,001.15-4W00000127/01/2008 01:05:4620/02/2008 03:17:52200 41.13158.87-5W00000122/03/2008 09:06:1824/03/2008 08:33:57789 --..............................
I have below code apearing couple times in my macro and I'd like to make a variable.
Plese see belowe
Workbooks.OpenText Filename:= _
"S:ReportsMy ReportsGuidefilename20080430", Origin:=xlMSDOS, _ ' filename should be another variable as it referes to different files
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, _
Space:=False, Other:=False, FieldInfo:= Array(Array(1, 1), Array(2, 1), Array( _
3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10 _
, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), _
Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array( _
23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array(27, 1), Array(28, 1)), _
TrailingMinusNumbers:=True
Basically have a spreadsheet to track an athletic competition going of for the purposes of a fantasy game (like fantasy football). The scores from each event are being copied and pasted into a data pages and then other pages pull from that for calculations. I'm using rankings (rank.eq equation) on a calculation tab, and then using those rankings on a leader-board tab find placement via the VLookup function. The issue I'm running into is ties, when two people are ranked the same. I've been playing with this:
=IF(ISERR(VLOOKUP($J17,Men!$A$1:$G$43,7,FALSE)),VLOOKUP($J17,Men!$A$1:$G$43,7,FALSE),
VLOOKUP($J16,INDIRECT("Men!A" & LOOKUP(J16,Men!A1:A43)+1):$G$43,7,FALSE))
Where it checks for an error in the Vlookup, if its not an error then it does the VLookup, if it is then if looks up the previous ranking and the VLookup array uses Lookup to find the position of the last rank, increments it by one and starts the new Vlookup there.
I have the following information (small sample only):
CodeItemList
162AVS0001 AVS0001
162AVS0002 AVS0003
162AVS0003 BUD0002
162AVS0005C5O1208
102BRE234CAL0007
102BRE-508EQU0003
102BRE-AB09 FXUK21916/2
162BUD0002GMC0003
162C5ENV-TNT HSS0001
162C5O1208 HSS0006
162CAL0005 IBM0003
162CAL0007 KUN0002
The list on the right is derived from the following array formula which has been copied down the column to end of the data range:
{=VLOOKUP($G$130, INDEX(G133:I252, SMALL(IF($G$130=INDEX(G133:I252, , 1), ROW(G133:I252)-MIN(ROW(G133:I252))+1, ""), ROW(1:1)), , 1), 2, FALSE)}
$G$130 refers to a value in that cell which is user defined from a drop down list and in this instance, the value is 162. Therefore, I would expect to see all of the items that contain 162, under the list column. As you can see, it has not returned all of the values relevant to 162. It has missed AVS002, AVS005 C5 ENV and CAL0005 in this example. The ommissions seem random
my data is formed of 2 columns column 1 (currency) & column 2 (balance)
i am writing an array formula using the vlookup function, the formula enables me to add all balances after converting them to $. i dont know whats wrong with my formula
please find attached an example of ....
I am getting the hang of this formula array stuff but I have another issue. I have gotten this to work in the actual Spreadsheet but when I try to put that same string into Code I get an error. I am trying to add a Vlookup into an Formula Array. I am building a new sheet and adding this to the sheet to do the calculations. I was able to get the code to work fine until I added the Vlookup section. I am doing this in small sections to make sure each part works. I am getting "unable to set the FormulaArray property of the Range Class" error message.
NewSheet.Cells(CurrRow, 4).FormulaArray = "=IF(ISNA(INDEX(FY08Rates!A:F,MATCH(LEFT(A" & CurrRow & ",SEARCH(""-"",A" & CurrRow & ")-2)&Vlookup(B" & CurrRow & ",FY08StdBillingRoles!A:B,2,False),FY08Rates!A:A&FY08Rates!B:B,0),3)),0,INDEX(FY08Rates!A:F,MATCH(LEFT(A" & CurrRow & ",SEARCH(""-"",A" & CurrRow & ")-2)&Vlookup(B" & CurrRow & ",FY08StdBillingRoles!A:B,2,False),FY08Rates!A:A&FY08Rates!B:B,0),3))"
I have created a spreadsheet some time ago and have been asked to improve on it but I'm rusty with VBA.
I have an automated ordering system that saves each sent order as the date e.g "05-04-2013.xls" but the management team want a graph with the data for the last 4 weeks compared. I have created a seperate workbook called "consumables report.xls" which has a column with the products listed followed by columns "Quantity" and "cost" which is repeated for the 4 weeks of the month.
I want to add a button to prompt the user to choose the saved order e.g "05-04-2013.xls" (all orders saved in same directory) to copy and paste the quantity and cost columns (c8,D69) into "consumables report.xls". I got this to work earlier but it would only paste the formulas and not the values. So I need
A prompt to open workbook
Copy range (c8,d69)
Close work sheet
Paste special .value (c8,D69)
I dont care if it has to open the workbook to copy the data as this will only be used once a month so it dosnt matter how slow the code is.
week 1 week 2 week 3 week 4
Product
quantity
cost
quantity
cost
cost
quantity
cost
1
2
3
4
5
6
7
8
I have a spreadsheet with 51 worksheets - a "Master" worksheet and 50 datasheets (labelled 1-50). Within the "Master" worksheet the first column has numerical values ranging from 1-50.
One of the columns in the "Master" worksheet is a VLOOKUP that is trying to pull data, of which the table array is dependent upon the value in the first column. For example:
If cell A2 has the number 1 the VLOOKUP would be:
=VLOOKUP(E2,'1'!$A:$E,3,0)
If cell A2 had the number 2 the VLOOKUP would be:
=VLOOKUP(E2,'2'!$A:$E,3,0)
If cell A2 had the number 3 the VLOOKUP would be:
=VLOOKUP(E2,'3'!$A:$E,3,0)
etc.
Tried nesting an INDIRECT function with the VLOOKUP but it didn't work.
In sheet1 I have name(column A) and value(column B)
In sheet2 I have name (column C) and key(column D)
In sheet 3 I have results
Example ( It is just a total nonsense example, the real data is net inflow of some funds that are unique)
Sheet1
House 1000
Car 1500
pet 2000
Sheet 2
house1
car1
pet2
So in the sheet 3 I want to put a formula that is capable to sum all values of the sheet2 if the name has a key of 1in the sheet2, the key columns has values of 1 or 2
I think in SQL will be something with join and group by with having clause.
The result will be 2500 in the sheet 3.
What I tried: My attempt is to do something like this code ( using array functions).
I am currently using the following formula:
=VLOOKUP($A$3, Sheet1!A:B, 2, 0)
=VLOOKUP($A$3, Sheet1!C:D, 2, 0)
=VLOOKUP($A$3, Sheet1!E:F, 2, 0)
The problem is I am trying to get the table Array portion (A:B , C:D , E:F etc.) to auto fill when I drag it across and it will but not correctly. How to autofill with the pattern that I need?
Currently it would auto fill with D:E , F:G, H:I G:H I:J
I need to be able to do a look up in 9 different table arrays using 9 different ranges.
I have attached a worksheet to help explain. My problem is that I can't have 9 IF statements in one formula.
I am not sure how to make this smaller or work.
I'm looking for a formula to return the correct value without using any help-column and it should be typed in no more than one cell.
View 10 Replies View RelatedI have a workbook that if I use just the array formula for the totals it works fine and if I use data validation and vlookup and the array formula for the totals it is giving me a #Value! instead. I have tried a couple of things and did find that as long as I do not use the vlookup in the one column (column d) the array formula at the bottom works but as soon as I add the vlookup to column D I got the error.
I want to use the vlookup to get the values for items on a different page - along with the data validation and then use the array formula to get a total for multiple items in the above columns. I have attached a workbook.
I am making a report with product usages for a given week. There is a file for each week.
At the moment I am using vlookup function and manualy change path to source file every week.
=VLOOKUP($A$1,'A:FilingDataMatrix 2012Production Matrix[Production Matrix 2012-Week15.xlsm]Mon - Ingredients'!$E:$F,2,FALSE)
Now I would like to avoid the part with manual changing. Instead I would like to have an ability to take the file path from separate cell.
I am trying to build a udf to replicate a vlookup where the table array is in another file.
The scenario is that very often I use store numbers and want to add the associated name quickly without having to open up the reference file and use a vlookup. So I am looking for something like =storename(number)
I'm trying to lookup XYZ plc in an array where the company names in col A are like XYZ plc (UK, London), ABC plc (Boston US) and I want the lookup to return a date in column 3. I've tried VLOOKUP(B2, LEN(LPR), 3, FALSE) where B2 is the cell holding XYZ plc and LPR is my array and it's not working.
View 2 Replies View RelatedI have this formula: =VLOOKUP(E24,OFFSET(Data!E$23,0,0,MATCH(TRUE,Data!E$23:INDEX(Data!E:E,MATCH(9.99999999999999E+307,Data!E:E))="",0)-1,14),14,0) in column e and looking up from a sheet called Data!. This formula looks up off of column e into the data worksheet from the point where I insert a blank row at a chosen point in the data. The problem is that this formula needs to calculate in excel where is shows in the bottom left of the screen...calculating 5%...50% etc. and I have to wait. Does anyone know how to rewrite this formula so it does not have to re-calculate after each move? This formula above requires a CTRL+****+ENTER.
What the formula above does is simply looks up into the other worksheet from column e and looks up only into the data where I insert a blank row. So it creates a lookup range at the point where the blank row is inserted.