I am tring to find a more efficient way of avoiding a double Vlookup function.
=IF(ISNA(VLOOKUP(A6,Sunday!$A$72:$AM$86,38,FALSE)),"",VLOOKUP(A6,Sunday!$A$72:$AM$86,38,FALSE))
I have read over this forum but still haven't understood any solutions.
A6 is a name.
The name may not be on the worksheet the function is looking up.
I am trying to avoid double counting two separate rows with the same columns. For example:
StockFund AFund BFund CFund DFund EFund FFund GCountDesired Count
LKOH111100044
LKOH001111042
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'm currently trying to vlookup the same data from 2 different sheets. Here is the code i've tried.
View 4 Replies View RelatedOn 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 double vlookup problem here.
Here's a simple illustration:
A B C D E F G
1 1 1 1 One
2 2 2 2 Two
4 4 3 3 Three
3 3 4 4 Four
5 5 5 5 Five
I need to match A,B with E,F.
If they match, I need to display the string in G in C.
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?
View 2 Replies View RelatedTrying 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!
(and I hope the images work!)
http://www.mediafire.com/imgbnc.php/...5942a7002g.jpg
http://www.mediafire.com/imgbnc.php/...a3b756c52g.jpg
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.
I've got a couple of formulas for doing in single step:
B A
A B
F D
A F
A #NUM!
D #NUM!
F #NUM!
D #NUM!
but I'm unable to avoid those #NUM!
I have a perfect formula
=IF(A2>1,A2/1.16,"")
It does exactly what I need , it does not calculate on empty rows , but it calculates on heading rows , how can I modify to avoid this.
See file attached , I don't want the value on b6 when I drag downwards.
07 09 value avoiding.xls
I have a sheet that requires me to press F9 each time I open it to re-calculate all cells. Why do I need to do this on this 1 sheet? A few months back it was fine and didn't require the extra attention.
View 2 Replies View RelatedAttached is a worksheet to show...
1 - D2- because the supplier has no address it shows up as "0" can you guys help me fix that to show nothing?
2 - Can you guys help me build a formula for E8? Right now I have it as $C8*$D8 it works fine until C or D is empty it gives me the #VALUE because I have a formula in D. Because of the #VALUE my formula in E14,15,16 does not work.
I am multiplying cells and copy down the column to make it easy.
Cell D17, has "=A17*B17" in it. This is fine when there are numbers to compute, but when there aren't, D17 will display "0". How can I have cell D17 remain empty if nothing is put into A17 and B17?
Let's say I have a sheet called Category. Column A contains code; column B contains description. Now I have another sheet in which either column F contains a category code or column G contains a category description. I want to look up the code in column F if the description is given or the description in column G if the code is given. I thought I might be able to do something like:
in F2: IF($G2<>"",INDEX(Category!A:A,MATCH($G2,Category!$B:$B,0)),"")
in G2: IF($F2<>"",INDEX(Category!B:B,MATCH($F2,Category!$A:$A,0)),"")
(and then copy the formulae down the rest of the columns of course). In other words, if the cells were both empty then there would be no common dependency; it would just set them both to empty. However, if I provided either value then it would overwrite the formula in that cell so there would be only one formula left and nothing to be circular dependent about.
However, it must be looking at the condition itself and seeing the two columns in common because it is coming up with a circular dependency. I thought I might be able to trick it by having IF(INDEX($G:$G,ROW())<>"",... but it still doesn't work.
This sort of situation must crop up from time to time. How do we get around it?
I have the formula =today()-g3 which works fine to let me know how many days a loan has been out. However when there is no date in column g i get #value! errors. Can anyone reccomend an alteration to the formula to avoid these errors showing up as they dont look great.
View 2 Replies View RelatedHow do I avoid using arrays? I am reaching Excel 2003's limit.
Our workbook has many worksheets, each with tables generated from queries that have multiple columns. On the first sheet we have arrays that refer to the other worksheets. Such as:
{=SUM(IF(('Prime Change Orders'!G$2:G$10=C17)*('Prime Change Orders'!D$2:D$10
I have a worksheet of Users with columns pertaining to first, middle, and last names. I want to allow duplicates in each column but not a completely duplicated record.
View 9 Replies View RelatedI have a simple spreadsheet that will be used by others for records management (rehabilitation case management). I have entered the code for most of the 'stuff' that I need to make the workbook easy and simple to use, and get the data back to head office each month.
Because the users will be emailing their records and I haven't yet worked out a way of sending just the "open cases", I need to keep the workbook/worksheets small (and to maintain good programming).
In column L, I have copied this formula from L2:L501:
=IF(ISBLANK($K2),"",VLOOKUP($K2,LOCSTAT!$A$1:$N$299,2,FALSE))
Column K is where the case-managed person works and so column L returns the geographical location value. Obviously it's blank until the case is opened by starting a new record and entering each field with data. For example, the user can select " Finance" from a validated drop-down list and the adjacent cell (to the right) will look up the value and return "Parramatta".
How do I translate the cell formula to some VBA?
I suspect that the static VLOOKUP range in the formula (LOCSTAT!$A$1:$N$299) will need to be dynamic range as well - I can follow the instructions for a single column dynamic range, but I am also getting lost working out how I can create a dynamic range for all data on a dedicated worksheet.
I'll be able to translate a solution to other cells as well
I have a workbook that multiple users access (read only) to do quick estimates.
This workbook has a "Print & Record" button/macro that does just that... it prints two copies of the estimate, then records/copies the basic data to another workbook (Retail Estimates Data.xls). The macro opens, pastes to, and closes this file in a fairly quick time frame, but it is conceivable that two (or more) users could be trying to do this at the same time.
What I need is some code to check if the Data file is open by another (read only status?), and if so give a message to that effect, and ask to try again, by which time the other user instance should have finished with the file.
Is it possible to inset a bank into a cell, and use that cell in a subsequent addition formula?
For example;
A1 contains 1
B1 contains 2
C1 contains =IF(A1=1,"",3)
D1 contains =A1+B1+C1
In the above case the cell D1 contains #VALUE and not 3, whereas if C1 were actually blank D1 would contain 3.
The reason I ask is that a blank is not the same value mathematically as 0. The latter is the number zero, whereas the former is the lack of any number.
I have a range, A1:A10 that I want to sum, min, and max. I want to ignore any text or #DIV/0! values in this range. I know how write the formulas except how to ignore the text and cell errors. Can someone steer me in the right direction?
View 5 Replies View RelatedI am combining the information from multiple files--one per state, for several states--into a single large table so it's more easily reviewed. Each file has multiple worksheets, named for categories. The category/worksheet names are standardized, but not all worksheets are found in every file.
There is some overlap of items from state to state, so my macro copies key fields from each worksheet and pastes them into the new table and adds a column on the left with the name of the category/worksheet each item came from. It then removes any duplicates to create a list of unique items. Across to the right, the table has two columns for each state, one to show if the item exists there, and the other the date it was added.
In the final step I am using lookup formulas to populate the states columns, using the category/worksheet name from the left-hand column to identify which worksheet to pull from. When a worksheet doesn't exist in a source file, however, this creates an invalid reference.
Is there any way to use VBA to identify which worksheets are in a file so I can use the results in an IF/THEN statement to bypass any lines that would create the invalid references?
I have some short code that imports data and account numbers that is "|" delimited. The problem I am having is that during the import, excel converts some of the account numbers to exponential numbers.
e.g. it converts 542690E24 to 5.4269E+29.
Is there a way to avoid this from occuring during the import? I tried to format the destination cells as '000000000' (all accounts are 9 digits) but it didn't work.
I have in my sheet from C13 to C350 many serial numbers which I manually type them in. These serials are 5 digit numbers, and I was just wondering if there's a function or formula that will not allow duplicate numbers in that column.
I have a problem with an array formula where I want to sum the values in every 7th row. The problem I have is I am receiving #VALUE! in my returing cell of the formula when there is text in the array. Is there a way I can avoid including the text in the array or maybe error checking using ISERROR and ISNUMBER.
The array formula I am entering is: [Code] .........
How can I avoid that an empty series shows up in the legend of a graph?
View 9 Replies View Related