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.
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
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??
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.
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?
Column A & B has a list of Supplier Part numbers and Buyer Part numbers as below.
Supplier P/N Buyer 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 Buyer 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
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.
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?
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:
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.
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.
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:
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....
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?
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
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.
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.
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?
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.
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.
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.
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.
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
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.
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.
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.
