Excel 2007 :: Lookup To Return Column And Row Label?
Jun 13, 2013
I have the below data. I'm trying to create a lookup that would return the Month and Year in another cell. For example, I have in another cell the min of all the data, I want to get the month/year in the cell below it with lookups. I've tried INDEX/MATCH, but not getting the results I want.
Excel 2007ABCDEFGHIJKLMN3JanFebMarAprMayJunJulAugSepOctNovDecYTD420134.47 (0.35)0.86 4.98 520121.27 (2.29)(0.27)1.55 4.61 (2.33)2.07 1.38 0.62 (0.60)2.50 0.12 8.63 620111.41 3.69 3.97 2.49 2.06 3.14 (2.97)5.83 (1.26)(1.30)0.34 (0.36)17.04 720103.34 2.24 1.59 (0.33)7.70 0.63 (0.26)2.00 2.78 2.81 3.01 7.96 33.47 820098.51 3.12 (1.95)(7.30)7.91 8.94 2.90 0.41 0.66 0.67 1.39 3.10 28.36 920084.09 1.60 6.83 8.65 4.42 6.79 11.01 8.16 1.03 18.41 17.74 7.01 95.74 1020072.08 (0.62)6.02 (0.30)4.74 (2.28)4.04 (1.12)1.29 0.89 11.58 1.79 28.11 1120064.87 (1.83)4.56 5.04 2.19 0.53 3.55 6.00 (0.68)2.76 2.82 4.74 34.55 1220052.43 3.17 1.71 4.44 5.78 5.84 (2.61)5.21 0.04 6.34 1.87 5.76 39.98 132004(0.22)4.98 2.86 (0.52)4.80 3.22 3.45 1.97 2.77 2.61 25.92 Portfolio Summary
View 9 Replies
ADVERTISEMENT
Jun 30, 2014
I have a pivot table with multiple row fields and multiple column fields. One of the column fields is a Date and I need some VBA that will auto-sort the columns into ascending order by the Date column field.
E.g., if the first four column labels are "2-Jun-2010, 13-May-2009, 16-May-2013, 17-May-2012" then i want the sort to arrange them as "13-May-2009, 2-Jun-2010, 17-May-2012, 16-May-2013".
Note: This is the left to right order of the columns i'm talking about, not the top to bottom order of the rows, or the data in the rows but specifically the column labels.
I've tried googling a solution and I can find a variety of code that deals with sorting the data in the rows in all sorts of ways, but nothing on how to order the columns.
i'm using excel 07, and the source data for the pivot table has the Date field formatted as custom "dd-mmm-yyyy". This can be changed if necessary.
View 5 Replies
View Related
Jan 17, 2013
macro for an Excel 2007 file. It has to be a macro. I have cells in two columns I need to look at. Column A will have a value of cat, dog, or mouse. Column B will have a date. If A2="mouse" AND B2<"1/1/2013" Then A2="" ElseIf A2=mouse" AND B2>="1/1/2013" Then A2="mouse". I don't need anything to happen if A2 is equal to cat or dog. The macro has to move down the entire A and B column: A3/B3, A4/B4, and so on until the end of the columns. Hope this makes sense. I've tried a few macro samples I found online but they don't quite fit what I need done, or I don't know how to modify it.
View 2 Replies
View Related
Aug 2, 2013
We just upgraded to Excel 2007 from Excel 2000 and have run into a challenge relating to labeling a form command button. It appears that the length of text for a command button label has been shortened to 31 characters when setting the name using VBA. No such limitation shows up when I manually create such a button.
Following is the code that used to work to create the button and label in VBA (Excel 2000):
VB:
ActiveSheet.Buttons.Add(2.25, 13.5, 443.25, 17.25).Select
Selection.OnAction = "PatientSelectedButton"
Selection.Name = "CheckFormButton"
[Code] .....
If I change the string I want to use for the label to 32 characters, or less, this code works (Excel 2007).
VB:
ActiveSheet.Buttons.Add(2.25, 13.5, 443.25, 17.25).Select
Selection.OnAction = "PatientSelectedButton"
Selection.Name = "CheckFormButton"
[Code] ....
I have to admit that this is the last item that I thought would break!
View 4 Replies
View Related
Jan 16, 2007
Is ther a function that will return the column letter rather thatn the column number?
View 9 Replies
View Related
Jan 12, 2006
I would like a flexible Formula to Return the Column Number of individual
Numeric Labels and their Numeric Value. I have a 2-Row by many Columns
chart/ grid layout. My 1st Column, Column Number "1" is Excel Column Letter
"F".
Numeric Label 17 is housed in Column Number "12" per my chart , its Numeric
Value 5 is housed on the next Row - directly below the Numeric Label.
Sample Data: ...
Scenario:
To Return the correct Column Number: locate the Numeric Label 17 and its
Numeric Value on the Row below, Numeric Value is 5. The Numeric Value is to
be increased by a Value of 1 (one) - NEW Numeric Value = 6. The Column
Number Returned should reflect Numeric Label of 17 remains the same but the
Numeric Value is increased by one.
To Return a Column Number representing the Numeric Label 17 and its original
Numeric Value +1 (plus one): the Numeric Label is still 17 but the Numeric
Value is NOW 6. From the oringal Column Number that housed Numeric Label 17,
that is Column Number "12", SEARCH in Ascending order: moving LEFT from
Column Number "12" and using the Numeric Value as the 1st (first) search
order and the Numeric Label as the 2nd (second) Ascending search order.
SEARCH the Numeric Value Row until the first Numeric Value either equal to 6
or more than 6 is found. Then Return the Column Number of the Column to the
Right of that Numeric Value.
Required Result:
Return the Column Number based on the Numeric Label 17 and the NEW Numeric
Value of 6, search in Ascending order - First search order based on Numeric
Value 6 - Second search order based on Numeric Label 17.
Returned Result should be Column Number 10.
View 14 Replies
View Related
Jan 31, 2008
I've been searching the forums for this problem but I can't seem to find any answers. Anyway, this is the problem. See screenshot.
I want to compare A1 for the values in column B, then return the corresponding cell (column C) in column D.
e.g. D1 = 2, D2 = 1, D3 = 4, D4 = 5 and D5 = 3.
View 9 Replies
View Related
Jun 5, 2009
I have a tab that has 2 columns of data and I want to be able to return a value in column A if my data matches column B. If column B has the text TRUETRUE, I want to bring back the corresponding data in Column A. How do I return all the data in Column A for all the TRUETRUEs in column B? I can only get the first instance of TRUETRUE.
View 4 Replies
View Related
Nov 22, 2007
I have a list of values in column A (part numbers)
In columns B to E I have lists of similar values (part numbers, with row 1 representing their location e.g warehouse, office, etc).
In column F2 I would like to search for the value in A2 in the range B2:E100 and return the value in B1:E1 if the value appears... if it doesnt appear in any of the columns I would like 'not located' to appear in F2.
I've been trying to do this for a while but am making no progress at all.
View 9 Replies
View Related
Dec 21, 2006
I am looking for a formula, to change from this
IF Formula located in Cell F15
=IF(E15=A15,B24,"")
to give the If a Range.
I thought it would simply be:
=IF(E15=A15:A39,B15:B39,"")
But all I get is Blank Cell.
B24 has a Value but it does not appear in F15,F16 and F17 respectively.
View 4 Replies
View Related
Nov 8, 2011
I am working on a sheet that will copy a specified number of sheets, rename them, set a cell within the equal to a cell from another sheet and (the problem) set cells on my input sheet equal to cells in the newly copied and renamed sheets.
I want the cells in sheet "Data Input" to look something like "='newsheet'E26"
I can make it return the actual number in the cell and return the formula in the cell, but not make the cell in "Data Input" reference the cell in the new book.
Here is what I have:
Code:
Sub CopySheetsTest()
Dim i As Integer
Dim wks As Worksheet
[Code]....
View 4 Replies
View Related
Apr 8, 2009
I have a book with multiple tabs in it. The first is a master customer file, including customer numbers. The next two tabs include multiple references to the same customers because they list multiple shipping addresses and contacts for each customer on a separate line. I want to be able to have the additional tabs refer to the master list and find the customer name, then return the corresponding customer # in the previous column for that customer. This is what I came up with: =IF(B:B=Sheet2!B:B,Sheet1!A:A,0). But the results simply returns numbers that increase by 1. It should repeat the same customer # for the number of times that customer name is listed based on how many addresses or contacts there are. What am I not doing right?
View 9 Replies
View Related
Apr 14, 2010
I want to lookup the max value within a group of columns and return the column heading (specified a cell) that corresponds to the max value.
View 9 Replies
View Related
Jul 31, 2014
I'm basically working from 3 sheets for this so I'll start with an example of the data I'm using:
1st sheet:
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
[Code]....
I have 180 rows of data like this one in the first sheet
Second sheet(named sheet 1) is not used for this
Third sheet(named sheet 2):
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
[Code]....
Basically I'm trying to find column #3 value in my third sheet and return the second column value. Problem is that the data is located more than once in the third sheet so I need the value of each one of them. So, with this example, Q5942X is located twice in the third sheet and each time, it has a quantity of 2. I would need either to return the value 2 twice horizontally or even better, add the two together. The first sheet, the example is row #45.
This formula should be in the column following "majoration".
I am using excel 2007 and windows 7.
View 3 Replies
View Related
Oct 15, 2011
formula that would return 'TRUE' if the string contains a number. Example below. Using Excel 2007.
One Part Order 123456 for shortage items shipping to US/TX-USA
View 9 Replies
View Related
Feb 21, 2014
Encryption algorythim.xlsx
Here is my scenario I have all this data i want to search in. In the rows there are repeating values and in the columns they do not repeat.
As seen in the attached i am able to give a column and row by title and then get the intersecting cell data from that. (Encryption lookup)
What i want to do is give the column tittle and row value and get back the row title. (Decryption Lookup)
This shouldn't be too hard seeing that the data in the columns has no repeats.
View 6 Replies
View Related
Apr 13, 2012
I recently learned how to count cells in a range based on the value from another column (excel 2007) How to count cells in a range based on the value from another column but now how I can go about returning a value from another column that matches the conditional counting. For example in the table below I'm first wanting to find the rows matching "chr15" from column A that also have a value from column B that is greater than 25,000 and less than 3,000,000. But what it I wanted to instead report the corresponding values in column C? I've played with VLOOKUP to no avail but I'm not sure if that's the right line of thinking. The answer would be the values in bold.
A B C
1 chr2 12008 AA
2 chr2 149700 BB
3 chr15 51 CC
4 chr15 5624 DD
[Code] .........
View 3 Replies
View Related
Nov 18, 2008
I have a list of names and the chores they need to do on a certain day. I need a formula that returns the chore the person needs to do when the date is filled in.
Ex: Sheet2 Col:A has names filled in A2:11 , row B1:J1 has dates that you would input.
formula goes in B2:J11
Sheet1 is the master with all the data the formula would be pulling from.
column A2:A11 has the names , row B1:S1 has the chore that needs to be done.
B2:S11 has the dates already filled in.
View 5 Replies
View Related
Aug 25, 2006
Got a bit of a stinker here. (Well, it is for me anyway. I haven't used Excel in months)!
I want to use a VLOOKUP function (I think) to retrieve data from a list of Exchange Rates on a separate sheet.
The user enters a 'Period' number, selects an 'exchange' variable (the blue text cells). The hope is to lookup the data on the 'FX RATES 06-07' sheet and return the result in the green cells.
I've tried using VLOOKUP but one of the arguements specifies that you nominate a column to return the information - that's the problem, we don't know that information when asking Excel.
View 4 Replies
View Related
Oct 1, 2007
Basically what I am trying to do is use a Lookup formula on a sales invoice where the product name and product price will be automatically entered into the sales form when the product ID is entered. What would the formula be?
Product Information
RegionProduct IDProduct NameProduct Price
Product IDHPPHomeware Pizza Pan19.95
Product NameSPPStone Pizza Pan 29.95
Product PriceHCDHomeware Casserole Dish19.95
Units SoldHCPHomeware Cookie Pan 9.95
Total SaleCWChina Wok 19.95
HEWHomeware Electric Wok29.95
WCMWilson Coffee Machine29.95
HBMHomeware Bread Machine49.95
HBMDHomeware Bread Machine -Deluxe89.95
HRHomeware Rotisserie119.95
OGGOlson Gas Grill 159.95
OEGOlson Electric Grill 159.95
View 2 Replies
View Related
May 15, 2014
I need code for a search box function, that returns the information recorded in a cell for example, "Barcelona" or "London" etc), instead of the location of the cells.
I will need to narrow it down to search only the information in the following columns:
Sheet2
I2:J10932
I am totally new to VBA coding and have stumbled my way through a few things, but everything I have searched for so far has had at least one error when transposing to Excel.
I am running Excel 2007.
View 3 Replies
View Related
Apr 20, 2012
I have two columns with team abbreviations, one simply says whether they are AL or NL the other is the result of a search with multiple occurrences of the team name. I need to match the long list with the short and put the column header AL or NL in the adjacent cell.
I'm using Excel 2007
The last formula I tried was =INDEX($AY$1,MATCH(BB2,$AY$2:$AY$15,0),0)
The screen shot actually just part of the sheet copied since the screen shot look like a broken html page Ok I give up. I copy and paste a jpg it turns into code, I copy and paste the spreadsheet and formatting vanishes, I don't have a URL for the picture... I did look at FAQs and didn't find picture rules but I will look again. Meanwhile. . .
It's just six columns of data. The short list is in AY from AY2 : AY15
The long list is in BB from BB2:BB505
The column header "AL" is in AY1 and NL is in AZ1
View 2 Replies
View Related
Mar 26, 2013
currently using Excel 2007 with Windows Vista.
I currently have a worksheet where I want to input a date (G2) and a rank value (H2) ranging from 1 to 4. The header value (B1:E1) corresponding to the date (A2:A4) and the rank (B2:B4) should be returned to I2 (currently returns #NA).
Using formula: =INDEX($B$1:$E$1,MATCH(H2,INDEX($B$2:$E$4,MATCH(G2,$A$2:$A$4),)))
which I found under: Find row, find value, then return column heading
However, the above formula does not seem to work with my date order or recurring data values of 1 to 4 over the 3 rows.
The worksheet layout is as follows:
Date
A
B
C
D
Date
Rank
Header
[code]....
The return value under Header should be C.
I have reversed the order of the dates and put sequential numbers in B2:B4 as plug variables and the above formula will return the correct Header value but I need the formula to work with the current date order and repeating rank values of 1 to 4 in B2:B4. Do not wish to use VBA.
View 2 Replies
View Related
Nov 7, 2013
I use the following formula in excel 2007 (and it's working perfectly in 2007) but this formula does not work if I work with the same file in excel 2003 ...w why and what I have to adjust?
=IF(SUMPRODUCT(--(C5='sheet 2'!B:B);--(C13='sheet 2'!C:C);ROW(B:B))=0;"not found";INDEX('sheet 2'!D:D;SUMPRODUCT(--(C5='sheet 2'!B:B);--(C13='sheet 2'!C:C);ROW(B:B));1))
(I'm working with the dutch excel version so it might be that ";" must be ",")
View 6 Replies
View Related
May 10, 2014
Windows 7, Excel 2007. a formula to find the next lowest sequential number within a group.
Here's an example data set, which represents transactions by date and category:
Col A is a user entered Date which will not be in chronological order (unfortunately a sort is not an option)Col B is a user entered CategoryCol C is a formula that gives the chronological sort order based on the date.
The formula in C2:C9 is =COUNT($A$2:$A$9)-(RANK(A2,$A$2:$A$9)+COUNTIF($A$2:A2,A2)-1)+1Col D is a formula to determine if the row is the first chronological transaction within the Category
The array formula in D2:D9 is =IF(MIN(IF($B$2:$B$9=B2,$C$2:$C$9))=C2,"Y","")
This might not be a necessary column, but can be used as a helperCol E is where I would like a formula to give the next lowest value in Col C for the Category in Col B (the order number in Col C for the previous transaction within the same category).
I have entered in the values that the formula should return in Col E in the table below
A
B
C
D
E
[Code]...
If the current line is the first transaction of the category, the formula should return the value in Col C for that line (so E2 would equal the value in C2, which is 2).
So the first part of the formula could be straightforward, using the helper column:
=IF(D2="Y",C2,
Here's the tricky part: If it's not the first occurrence of the category, then it should return the value of Col C for previous transaction (or the next lowest number in Col C that is within the same category).
E3 would return 4, since the previous transaction for Category A was line 9
C3 = 6, and of all the values of Col C for Category A (2, 4, 6, 8), 4 is the next lowestE4 would return 6, since the previous transaction for Category A was line 3 C4 = 8, and of all the values of Col C for Category A (2, 4, 6, 8), 6 is the next lowestE5 would return 1, since it is the first transaction in Category BE6 would return 1, since the previous transaction for Category B was line 5 C6 = 5, and of all the values of Col C for Catebory B (1, 5, 7), 1 is the next lowestEtc.
I'm thinking I need an array formula to find the minimum value within the category, where that value is less than the value of Col C in the current line. Just not sure how to write that...
View 9 Replies
View Related
Apr 22, 2014
I have a sheet with Customer name in the first column and next 7 columns have unique serial numbers(alphanumeric) of the systems which that customer has purchased. In another sheet, i have the unique serial number in first column and I want to search this number in the 7 columns of other sheet and return the "Customer name" to this sheet. How do I do that ?
Vlookup searches only in 1 columns, so does match index. lookup didn't work as well.
View 1 Replies
View Related
Apr 12, 2013
I would like to write a formula to return a value from a column that relates to an array within which my lookup value exists. See the tables below:
ID
V1
V2
V3
105
27
3
149
[Code] .......
The tables above are on different sheets within a workbook. I would like to write a formula that returns the "ID" numbers from column "A" in the first table, based on values in columns B-D in that first table, into column "C" in the second table. For example, in this case, the ID number that corresponds with the value "12491" would be "109", since 12491 corresponds with 109 in the first table.
FYI, the "Value" numbers in the second table are calculated based on their rank (high-low) within the matrix in the first table.
I tried the INDEX-MATCH function, but it doesn't seem to work if I'm trying to find a value within a 2-D array- it only works if I'm looking in a single column.
View 2 Replies
View Related
Feb 8, 2008
I've got a database that is sorted by date...
I need a formula that will look for 2 criteria and once it finds those 2 matching criteria, I need it to return the information on that line that's in column 6, let's say.
so in one column I have the date, the next column I have the sales persons name, 4 columns over I have their order number.
In cell a1 of worksheet 1, I have a drop down ready that has all my sales persons listed. In cell a2 I will manually enter the date that I need to reference.
I need the formula to then look for cell a1 and a2 in worksheet 2 where I have my spreadsheet with the info I mentioned above, match that criterium in worksheet 2, and return the info in column 6.
View 9 Replies
View Related
Sep 3, 2009
I would imagine that the title of this thread made little sense, so let me try to explain:
Sheet A has columns A-C populated with data. Column D needs to search Sheet B columns A-C for a match. (ie, see if the data on sheet A is also on sheet B) if this is true, then it needs to return the value of the LAST column of the matched row in Sheet B.
So, if Sheet A R1 A-C is 1,2,3. Sheet A R1 D needs to search Sheet B for 1,2,3. and when it finds it, (for example on row 9) return the value of the last column of Sheet B row 9. The issue is, the last value could be in column R or S or AA, there is just no way of knowing.
View 9 Replies
View Related
Dec 18, 2008
I'm looking for a formula (VBA I'm assuming) that will help me create a unique customer ID out of data that my website generates in order to import records into my accounting system.
I have a current list of customers in CSV format with the columns: CustomerID, CustomerName, CustomerZip
Each customer in our accounting system is assigned a unique,7 digit CustomerID in the format of XXX#### where XXX are the first 3 letters of their last name, and #### is a 4 digit number (with leading 0's) to create unique ID's for customer with the same first 3 characters of their last name. SAMPLE LIST:
SCH0001, Lindsey Schubert, 75230
SCH0002, Thomas Schoembs, 53132
ADA0001, Samantha Adams, 28205
...
What I'd like to do is pass the formula 3 parameters (Cust_First_Name, Cust_Last_Name, Zip) and have it parse the .CSV file and either return an existing customer's current ID or generate the appropriate new, unique ID, making sure in increase the 4 digit # accordingly and insert leading 0's if necessary.
Another caveat, if possible to work with, is the ability to also pass the formula another range of cells to append to the end of the .CSV file's data for comparison reasons. There are times when I'll bulk-import orders (or we receive numerous in the same batch) and the potential exists to have two customers that would have the same CustomerID created using JUST the .CSV data. Ie. If we use the example above and have new customers of Steve Schwab and Julie Schwitzer - we'd end up incorrectly assigning them both SCH0003, where if we'd read Steve Schwab's newly created info and customer ID of SCH0003, then Schwitzer would correctly be assigned SCH0004.
View 3 Replies
View Related
