I have a column of numeric values in column B. I have the date that corresponds with each value in column A. I am using a max formula to find the highest value in column B. What could I use to return the date of the highest value from Column A?
View 2 Replies
I have a report that is run everyday showing a list of orders, on separate tabs based on facility code, that has all kinds of information. I want to be able to take today's file, and have it lookup any comments I posted on the previous day's file.
I've been messing with code all morning and can't get it right. I think my problem is the vlookup part, and I don't think my loop is right either. Here's a little bit of the code i'm trying:
I am experimenting with sparklines and wanted to know if there is a way that I can lookup the last 12 results of a particular field and return a sparkline for that.
For example, on my report tab I have a list of names in column A and in my data tab, I have a list of the same names with a different value in each column (each month).
I want to have a sparkline beside each persons name in my report tab which will lookup the last 12 months of data for that person and display it in a sparkline.
I have a list of airport codes and I need to look up these 2 digit codes and find the relevant airport and country.
For some reason when I use the lookup function it is finding the correct lookup value but returning the column cell in the previous row.
I have a spreadsheet with a list of thousands of products in Column A. In Column B, C, D, E, & F there are headers that read: Cincinnati, Dayton, Columbus, Toledo, Cleveland.
In the rows under these column headers it’s either blank or there is a 1. The value 1 represents usage of products (column a) in those markets (columns b thru f). I guess I could use a Y for yes but I used a 1 instead.
In another workbook, I have a short list of a dozen random products that I am trying to match up to the cities that use them. Is there a simple formula that would search for a specific product in column a & return the first market with the value 1 in the corresponding cell?
i need a formula that tells me if a value exists in a column. for example if there is a value in a cell in the b column of 23 no matter where it is, i want cell c25 to return the word "yes".
View 2 Replies View RelatedI'm trying to write a formula that searches for the name of the column on one sheet, finds that column on another, then results in the column letter. So, ideally, if the first three columns in the first tab were "Team," "Name," and "Total," respectively, I just want a formula to search for "Name" and give me back "B."
I want to do this in case anybody ever moves the "Name" column, for example, from column B to column C -- so then it would search for "Name" again and change the range in the formula to "C" because that's where that data lives now.
Currently, I'm using it for a COUNTIF() function that is a very simple: =countif(indirect("'"&$A2&"'!K:K"),$B2), where A2 is the sheet name. The latter half of $B2 is fine -- but I would love to have a more complicated formula take the place of the range "B:B" in case the "Name" column ever got moved.
I need a lookup formula that matches first three digits of column, U (153 - LINE PIPE), and match Total 13153 - LINE PIPE, 7th, 8th, and 9th digit in column C. and bring value from either column T,
The formula that I am using is not obviously not correct, I am putting it here to show what I am trying to do.
{=INDEX($T$2:$T$16,MATCH(LEFT(S2,3),MID(C2:C273,FIND(" ",C2),3)),FALSE)}
I am running Win XP and Excel 2003.
I have a macro I found here on the boards written by Lenze to delete an entire row based on what is found in column A. I would like to delete any row where Col. B contains 10 or less characters and I have modified it to do so (or at least I think it does). My problem is that it takes about 12 minutes to run the macro (I have about 50k lines to run through). I was wondering if this is the fastest method or if it examines things other than just column B.
Sub Test()
Dim i As Long
LR = Cells(Rows.Count, "B").End(xlUp).Row
For i = LR To 2 Step -1
If Len(Cells(i, "B")) < 11 Then Cells(i, "B").EntireRow.Delete
Next i
End Sub
After this runs, I am left with Columns A to somewhere around AH. The columns are generally in the format of text followed by a numeric column. An individual text column has the same name through all of the rows. The numeric columns have varied values whether negative or positive.
Ideally what I would like: If a given cell (ie. C2) in Row 2 is numeric, then copy the cell to the left (ie. B2) into (ie. C1) and then delete Column B. I need this to work for multiple columns from B to C, skip D and E, and then from F to AG (and maybe beyond).
I have a lookup tab which contains three columns: Column A is a tax ID #, Column B is a set of numbers and Column C is a list of street names. I need help with creating a lookup type formula for my column AB in the (worksheet/tab) titled 2009, which will look at and compare the data in columns F & G and compare them to columns B & C in my "lookup" tab and if a similar address is found based on both the street name and number then the tax ID # affiliated with that address from the "lookup tab" will be automatically placed in the column AB in the 2009 tab. I hope this makes sense.
View 4 Replies View RelatedI'm trying to find a formula that will find an original entry using 4 criteria original entry is cases ordered.
columns A,B,C,D will have to match then give me a result in column E to find original entry if there is one otherwise I will have to enter new row and original entry for cases ordered.
Eg.
A = section
B = description
C = size
D = region
E = cases ordered
Row 2 = fruit, apples, medium, north, 25
Row 45 = fruit, apples, medium, north,
I have a spreadsheet which is updated daily. Row A of the sheet has the date in it, and every day a new column is created for the that set of data. I have the below code which works at the moment:
[Code] .....
I want to use this same code on another spreadsheet to do the same process (I need to copy and paste 4 columns (A,B,C + D, into E, F, G & H, then tomorrow it will copy E, F, G & H into I, J, K &L etc etc)). The problem I'm having is that A1:C1 is a merged cell, then D isn't (used as a border to separate). So when it is copied I need to select the merged cell columns and column D (i.e. A:C & D on day 1) and paste it into E:H with E1:G1 merged.
Is there a function to put in a new column to get the abbreviated year and month of a previous column in the same row???
Ex. Row A B C D
3/14/2007 5/16/2007 2007-3 2007-5
Instead of using the YEAR MONTH funcion for each row is there a universal function that I could designate to the whole column of C and D something like C=YEAR(A(row#))???
I am after a macro to insert a column lets say between column A and B. Once column inserted new column B should be filled by a text say "January" but it should be only filled upto the data of column A so if column A has data upto A600 then column B should be filled from B1 to B600.
I tried recording but it fills data upto B65000+.
see attached file. Need to find latest non blank value - in attached file it is highlighted in yellow. From there, want to summarise 26 weeks back so, in the attached file:
Row 2 would be finding 750 and summarised back 26 weeks from 30 sep 2012
Row 3 would be finding 2250 and summarised back 26 weeks from 2 dec 2012
Row 4 would be finding 5000 and summarised back 26 weeks from 4 nov 2012
Yesterday (13/03/2007) was the 9th working day of the month (basing a week on Mon – Fri) – today is the 10th working day
Is there any formula, or mix of formula’s I can use to automatically calculate this?
I have a month number in H2 (1-12). I want a formula that will give me the previous month number. So, if h2 = 1. I need my formula cell to equal 12.
I need a macro or some idea on how to sort the following numbers (I hope this makes sense!)... The problem is with the zeros get sorted to the top (or even if I have blank cells sorted to the bottom) and I need them to ignore the zeros and sort according to the value in their column but not any other column. However, I need them to be sorted in order from D, C, B, A.
A B C D
0000
4000
101000
0540
0050
6660
ie I need them in the following order.
A B C D
0000
4000
0540
0050
6660
101000
Therefore they need to sort based on the following columns.
This is because I am charting the values in a column clustered chart that need to be in ascending order.
I have a table of data (say Column1 to Column 5) with multiple rows.
Column 1 to 4 will have the lookup values in multiple rows and Column 5 data should be picked up using vlookup or other lookup function.
I managed to somehow bring all these lookup values in (Column 1 to 4) in a single column in another sheet. I am now trying to use some lookup or other functions to match this single column and pick column 5 data in original sheet. Result i am expecting is lookup value in first column and next to it column 5 value.
It is basically a lookup wherein lookup value is spread over multiple rows and columns and result column is fixed. I tried using vlookup, but lookup value column and column number had to change every time when i moved from column1 to 4.
I am trying to perform a lookup (vlookup) function in a cell in excel and wish to have the range as a variable, so that I can adjust which column the lookup function refers to.
View 4 Replies View RelatedI have a sheet that shows employee absences, I require a formula that will look to see if a friday and a monday are populated and auto-populate the saturday and sunday to match the run, if the only the monday is populated then the saturday and sunday need to stay blank, if only the friday is populated then I also need the saturday and sunday to stay blank.
I have attached a sheet with the current display and the desired outcome.
fill test.xlsx‎
I am using the following formula to show the first day of previous 6 month cycle.
=DATE(YEAR(TODAY()),MONTH(TODAY())-6,1)
That formula would display: 07/01/2009
I want to have another formula show the last day of the previous month.
Basically it would display: 12/31/2009
After a formula to copy the contents of a cell in the previous sheet in a workbook, so that if I was to copy the last sheet in a workbook the formula would automatically reference the cell from the copied worksheet and so on if I copied tht one.
Hope this makes sense I have a lot of formulas referencing the previous sheet and everytime I copy this sheet to create a new sheet I have to change the sheet number in the formulas.
eg, in sheet 8 this formula get info from sheet 7 cel J30 ='7'!J30 when i copy the sheet to create a new one (Sheet 9) I would like the new formula to automatically be ='8'!J30.
Let's say there is a one column with 9 cells. I need in another column to be aranged various numbers in previous column and in another column to be counted every number how many times appears.
Below is one small example of what I need.
4 2 2
8 4 3
10 8 2
4 10 1
8 15 1
15
2
2
4
I found some VBA code that saves the previous value entered into a cell and displays it as a comment. I've attached that workbook as an example, but I want to change 3 things.
1) I only want to capture the previous values from Sheet1.Column1 (you'll see the example captures changes on the whole sheet1).
2) I want to 'paste' the previous value of Sheet1.Column1 in the corresponding row of Sheet1.ColumnD (i.e. the previous value of A5 will be displayed in D5). The code currently pastes the value in the corresponding address in sheet2.
3) I do not need the comments part. Simply displaying the previous value in the corresponding row of Sheet1.ColumnD is good enough.
I have a spreadsheet that I need to rearrange to improve readability. I have an example attache, which is easier to understand than describing it, but I'll try:
I want to start in a1, move to a2
copy a2 to b1
delete line 2 (the previous lrow 3 now becomes row 2
if a2 > 0.01, skip it and
move & select cell a3
... and repeat the above logic, on the next series of cells...
E.g.
copy a3 to b2
delete line 3 (the previous lrow 4 now becomes row 3
if a3 > 0.01, skip it and
move & select cell a4
...and repeat the sequence again.
I have a spread sheet with bunch of rows and columns. Columns goes all the way from A ... BB and rows from 1 to 40,000
The main focus is column Y and AC
If there is a value of FALSE found in column Y then macro should go to previous row and copy the value that contains in column AC and then move down to the next row, , where the value FALSE was found and that's in column Y and paste that value in column AC the NUMERIC VALUE
Below is the data ...
This is just a sample worksheet. I have got a worksheet with having 3 coloumns A, B & C. Column A contains E Code, while Column B is of time which user will enter. Column C contains the time in Hours.
I have entered one record for example. Now, whenever user enters the value in B3, then formula from C2 should be copied to C3 i.e it should be =B3/60.
I want this to be done using VBA. Pls help me out. I want to use this feature in one of my another files which requries this feature.
Excel 2003. I have a list of names in column A, dates in column B, I need the difference between dates for the people in column A, I am doing it now with the formula shown but I need a formula to put in column C that will look at the name in column A, for that row, and then find that name above it and give me the difference between the to dates in column B. So in the example below if I put the formula in C7 it would look at A6-A2 for harry and then give me the difference between B7 and B4, 6 days, if there is not a match return blank.
Excel 2003ABCD1NamesDateDays Between2tom2/8/123****3/2/124harry3/9/125tom3/10/1231=B5-B26tom3/12/122=B6-B57harry3/15/126=B7-B48****3/26/1224=B8-B3Sheet1
if it is possible te refer to a cell in another worksheet.
I have 10 worksheets in sheet one I put in cel A4 a digit en sheet 2 I put the formula that refers to sheet 1 cel A4.
Now I want a formula that refers to sheet -1 so in sheet 3 the formula automatically refers to sheet 2 cel A4 and in sheet 4 the formula automatically refers to sheet 3 cel A4 without corrections to the formula.
