On one sheet I have an array of 4 cols. It looks like this:
123 Chris 12345 Jones
123 Chris 23456 Pete
123 Chris 34567 John
On the main sheet there are also 4 cols. In cell A1 I enter a number, in A2 I have a VLookup to get the corresponding name for the number entered in A1, which works fine, I type 123 in A1 and get Chris in A2.
In a perfect world I would like A3 to become a dropdown with 12345, 23456 and 34567 so the user could then select one of those, and by doing so, A4's VLookup would give Jones, Pete or John.
I don't think a dropdown can be populated this way - am I stuck using VBA?
I have a calendar (tab March in the attached file) where I can indicate the delivery package of tab List. The problem that if the delivery happened twice a day, only the first one will be indicated. If there is any way to indicate both deliveries?
Trying to do a double lookup, or a conditional lookup. Attached spreadsheet will provide much more clarity. I have multiple data sets within a universal file, each data set can contain the same constituant therefore I would like to lookup/vlookup a value only if the constituant is part of the data set I want to pull from.
In attached sheet: if cells in Col A= "F", then vookup("RDT",(array would be data set "F" (I think)),return value in col 2 (assuming vlookup is in col B), t/f)
I have a table of data with two rows of unqiue identifiying information. I am trying to develop a formula that will allow me to do a vlookup twice, once for each row of unique identifying information to retrive the value in same row for the third column. For example:
apple red 50 aaple blue 75 aaple yellow 80 apple green 90 grape red 12 grape blue 17 grape yellow 45 grape green 41
So, I am trying to do a lookup where I can say what is the value in the third column for apple and red. Or, what is the value in the third column for grape and green.
I have a spreadsheet of mail types with totals. What I am trying to do is create a formula in the highlighted cells in the second sheet that will look at the Account ID Row for a certain value (in this case '45265637'), then look for all the month (in this case April) then add all the figures for this month in the LETTERS row, with the cell below it doing the one for PACKAGES.
I am exhausted from trying to get this one done. The reason that I need to this is that is I have an Excel system readout of 821 different ACCOUNT IDs and their volumes for everyday from 1st April 08 - 30th June 08 and I need a forumula that will extract this automatically as I don't fancy manually cell referencing 45,000-50,000 cells!
I have a table that shows a row of dates, a row of campaigns, and then a row of values I need to reference. I need to pull the values for a specific date and corresponding campaign.
For example, I need to pull the value for campaign 'notset' on 6/16/09.
I tried the following, but it would only work on the first and last campaign: ...
I'm curious if there's a way to use the Vlookup function to compare TWO values in TWO cells with two values in two OTHER cells (same row though) and return one value from another column. Something like
Vlookup(A2 & C2, Sheet2!A1:C400, 3, FALSE & FALSE)....? Does that make sense?
Say the values in A2 and C2 are in A24 and B24 respectively in Sheet2. Can it return C24 for me?
I have two spreadsheets, one with master file with original data and one that needs to pull in the original data. My issue is all the data to match off of is in the same column, and a number of other files link to the master file, plus it is used externally, so I cannot alter it, and I would rather not create a mock/copy file. Is there a formula that can look for 2 different items within the same column? Would prefer not to use VBA, but if that's the only option I'll take it. I am using Excel 2003.
Here is an example of the setup - I would need the formula to reference off the two different items/categories in the column, so lookup off the 'St. Louis' and following that, lookup off the produce items.
Is it possible to enter double inequations? Excel returns "false" even it is obviously true. If I type =1<2<3 the result is "false".
I can get the correct result using =and(1<2,2<3), but that makes it longer, and if I have more formulas inside instead of numbers I'll have to repeat the middle one, it will take the place, but only in oreder to compare it again.
I have a list with fractions such as 25 1/4, 33 5/16, 20 15/16, etc. i have them formatted as FRACTIONS 2 DIGITS 21/25. Manny of those have double spacing ex: 25 1/2, So i am wondering if theres a way to take the extra spacing off the ones that have double.
I am attempting to create some code that has 2 loops in it.
THE SITUATION:
I will have 2 ranges (eg B54:B100, and B106:B149 - the problem is I will arrive at these ranges by code such as: Range(Selection, Selection.End(xlDown).End(xlDown)).Select)).
WHAT I THEN REQUIRE
I need to have some code that will look through each cell in the second range and if the cell contents is found in the first range then the entire row from the second range will be deleted.
Have a quick one, trying to figure out a simple expenses/income/saving tracking table for my wife and in-laws; have figured out most of it. Will use pivot to extract most of the data, but on a summary sheet I need double condition sums (or at least I guess so...). For example I have "amount" column (which is expanditure) and date column (have used function to extract month and year for other purposes to separate columns), how can I calculate multiple costs (all the costs/expenditures in a month) that have taken place in eg. april 2008 only/ since I want to display it on a separate sheet as a summary overview...here's the link to imageshack, dunno if it's gonna work:
[url]
Have tried something like: =SUMPRODUCT(month="4")+SUM(amount) but it sums up the entire column...
or this one: =SUMPRODUCT(ISNUMBER(MATCH(4,month,0)+SUM(amount))) I know I miss a bunch on the last one
xxxxxxxxxx1-jan | 2-Jan | 3-Jan ------------------------------------------------- Location 1 Bob Smith ----B2--- C2 ---- D2 Frank Thomas-B3---- C3---- D3 Location 2 Bill Clinton----ETC ALL George Clinton
And another sheet, a similar list, (just names and randomly sorted) but populated with the values I want,
xxxxxxxxxxx1-jan | 2-Jan | 3-Jan ------------------------------------------------- Bob Smith --- 2 -----5------ 1 Frank Thomas 3----- 1 ------2 Bill Clinton ----4----- 5 ------2 George Clinton 4----- 4 ----- 4
I want to check the name in A2 on the first sheet against all of column A on the second sheet, then find the number value for the correct day for that name.
How can I write a formula that will look up these values for me? I looked at OFFSET but unfortunately I don't have a lot of excel experience (yet)
If I have two stock codes the same in column A (not necessarily one below the other), I only want to count one of the occurrences per fund. So I would like to avoid counting the duplicate records for funds C and D. The desired outcome for the total holdings of LKOH should be 6 and not 8.
I am trying to lookup the value that corresponds to three sets of data. The formula I have been using is but I am still getting #NA. I’ve attached the spreadsheet
I am trying to write some questions on cells of Sheet1 of a workbook. Then I want to write the answers on cells of Sheet2 of the same workbook. I want the user to be able to double click the question-cell on Sheet1 and be taken to the respective answer on Sheet2.
I have a spreadsheet that just uses the basic "SUM" function. This morning I went to use it and the function does not work after I change a number within the field of that function. If, however, I go into the SUM function and double click, then hit enter, it does the new calculation.
I have a excel sheet with a macro set up. On the main sheet is all the information with expiry dates. Now each entry according to the expiry date goes into another sheet (Monthly Sheets) in the same document. However if you add to the main list and press go it re-sorts the list and duplicates are put into the monthly sheet. (Hope that makes Sense)
I have a timesheet which for certain days of the year calculates double-time. What i would like to do is have a formula that gives double time for a 24 hour period. I have a formula setup with a start time and end time for my double time, but it wont stop at midnight.
I have this list of all rangenames in my workbookin column A:A
For some reason I want to doubleclick on the rangename in the list to activate this range.
The reason is that I want more smooth activation-prosess; The Excel Range List is too long to practical use as a direct lookup, so I want to activate directly from my own list (where i have added definitions to the range names.
I have tried to build a hyperlink formula, but failed. Maybe the macro below can be modified to do the job, but I am stuck here as well.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim RangeName Application.ScreenUpdating = False If Target.Count > 1 Then Exit Sub If Not Intersect(Target, Range("A:A")) Is Nothing Then Cancel = True
I am trying to create a VBA code that will search through a column"A" and if it finds a calue that C1 then color it, when it finishes searching columnA then loop again and search for items that match C2
Here's what I have so far with no success
Sub ColorItems() Dim i As Integer Dim j As Integer Range("C1").Select For i = 1 To Selection.CurrentRegion.Rows.Count For j = 1 To ActiveCell.Offset(0, -2).CurrentRegion.Rows.Count If ActiveCell.Offset(0, -1) = ActiveCell.Value Then With ActiveCell.Offset(0, -2).Interior .ColorIndex = 6 .Pattern = xlSolid End With
I have a spreadsheet with two drop downs, one lets say has country and the other city. When I select a country in the country drop down, I want to be able to only select cities in that country.
I used a method that I found in an earlier post, where I define the name of list of cities of a country to that country and then use the indirect function in city dropdown down validation. The prolem is I can still get mismatched city and country because when I change country, I still have the city from the previous selection untill I drop down and select the city again.
I would like the city to show blank when I change the country until I select a city from that country.
I have in "column A" an accountnumber and in "column D" a lastname. The lastname can appear more than once and the accountnumber also. Though I want combobox1 on userform1 to contain the lastnames. It's possible to contain a double lastname as long as the accountnumber isn't the same.
For instance: accountnumber: Lastname: 1234 Johnson 7890 Johnson
here Johnson will be insert twice into the combobox.
accountnumber: Lastname: 1234 Johnson 1234 Johnson
I seem to have broken something!. Whenever I double-click to open an Excel file (.xls files are associated correctly), Excel opens, but nothing happens. That status bar says "Ready," but my workbook never appears.
If I do a File-> Open within Excel, then I am able to open the workbook. I have no idea what is going on. This occurs for all Excel files whether they contain macros or not.
