does it not like dates? i have columns of data and i wish to search it for each month and then print a months worth. here is part of the data. maybe vlookup does not like dates? i tried putting an index column on the left, 1,2,3,4... but it still would not pick up "Jun"
I am working on two worksheets, in different workbooks. They are both lists of customers and Worksheet 1 is 2500 rows & contains up to date data & Worksheet 2 is 20000+ & includes some that is outdated. Worksheet A data is all contained within worksheet B but I need to be able to update/compare them.
Each customer in worksheet A has two unique identifiers but in B there is only one. I want to be able to add in the missing unique identifier (numeric) from A to B, which will identify which customers are still active from worksheet B.
I have been trying to do a VLOOKUP so I can insert the relevant data from the cell from A to B but I'm getting a N/A error. My formula looks like this:
This formula works correctly, displaying the lookup value for K7. My query is between the"" I can place text to display when K7 is blank and this works correctly too. However I would like to place a formula in here. The formula is VLOOKUP(I7,Orig!A7:B35,COLUMNS(B7:B35)+1,0 i.e. the lookup value is now I7 and not K7 when K7 is blank.
I have tried the following and variations based on what I know but they return errors.
above is col A which has all codes and second is col D which has respective numbers. i want help with some macro which can combine (seperated with a comma) all the numbers in col D based on first three letters of each cell in col A.
see like for NDL, i will have 1100015657, 1100015658, 1100015659, 1100015600,1100015664.
I want to place this forumla "=IF(INT(B3/$U$1)=(B3/$U$1),1,0)" across my worksheet.
Now, I'm okay when dragging down as it works fine with the B3 changing to B4 and the U1 staying as U1.
However, I would like it so that when I drag it horizontally, the U1 becomes V1. I want it so that I can do this and it stays within the $. So when dragging sideways it becomes "=IF(INT(B3/$V$1)=(B3/$V$1),1,0)".
Need to trimming certain information contained in 1 cell and putting them each in a column. I could do left, mid & right but this time there is inconsistency in the content of the cell.
I've attached a file which will further give you an idea exactly what I want (formula to be in Column M to P).
In the attached work book I have 2 sheets named, Data Entering page-plan and Varaince Summary-in hours.
Data Entering page-plan - is used to enter data Varaince Summary-in hours. is used to retrieve data
In Varaince Summary-in hours. sheet in Cell A6 I have a drop down to select the job nos, according to the job no selected date, position no, and milling hours are filtered and displayed. I have written a index/match formula in G6 and have dragged it downwards since I dont know how many positions would be fetched in each job no selected. Is there any way not to display N/A in unused cells in Column G each time a job no is selected ?( embedding the function in a IF using ISNA or ISERROR would be inefficient as I feel since this sheet is going to house lot of data of about 30 sections, I have shown only one section here)
I have a spreadsheet that has a AGE column. The function was put in the top cell and after listing pulled down to automatically get the right cell indicators in the formule. I have 1000 rows on the spreadsheet and i should for see adleast a other 1000. The issue is as follow or better the question, how do i hide de #VALUE in de cells were there is no DOB yet? Since i put in the empthy rows in the DOB column / / i do get the #VALUE there where the DOB will come. Hoe can i solve this issue and make the additional rows still availeble for futher usage.
I've got a worksheet that deals with dates and time. Similar to a checkbook, I need to take the balance and subtract what's taken from that. But the writer copied this from another software program and it's in this format. How do I convert it to a time format? Or is that what I really need? Right now the taken and balance are both in a general format.
I just need to know on any of the given days what the balance of this person's time was in hours and minutes.
I have text with line breaks in a cell for example:
A1 =
Multiple App1 App2 App3 App4 App5
When I reference this cell for example in B1 (=A1) the line break list merges together and adds a square (LBC) where the line break is meant to be as follows:
Multiple LBC App1 LBC App2 LBC App3 LBC
Is there a formula that can look at cell A1 and split the lines out so that both A1 and B1 look identical not merged together?
I'm trying to copy a vlookup to an entire column, I want to look up a value in the cell to the left and compare it to a given table; but when I copy it down it up dates the cell value, but it moves the table down by one row at every row so it's not finding most of the values the further down I go.... what am I doing wrong. I doubt I'd have to re write the formulas in all the 2500 cells I need to look up.
I m trying to write a formula for my financial model. If anyone can take a stab at a solution. I'm trying to write a formula that will equally distribute revenue either over the next 1 month, 2 month or 3 month period depending on size of the deal.
Details: Sales will fit in 1 of 3 categories. Less than 25k; between 25k & 100k; greater than 100k.
- if under $25K, recognize in next month (month N+ 1) - $25K-100K, recognize in two equal parts in months N + 1 and N + 2 - over $100K, recognize in three equal parts over 3 months N + 1, N + 2, N + 3 ...
In the attached spreadsheet, I am trying to drag the formula from one column to the next but I need the same cells to be used in every column with the exception of one cell. For instance, the formula I want to use is in the cell highlighted yellow. In the next cell over (highlighted blue) I want the formula to use the same D and E cells with the only change being the first part of each (G). Both the blue and yellow cell represent what I want to do. For example, the next column (H) would have (H4+D4+E4) in the first part. Column I would have (I4+D4+E4), and so on.
The D and E columns will be in every equation for each column. The problem is that excel wants to use the next column over instead of keeping D and E in each. How do I drag these equations so that it just changes the first part and keeps the D and E columns the same?
There are about 200 rows with values, I need to copy the numbers frow row 1 that are 75,36 -6,73 30,74 ,then in row 5 they are 69,44 -8,28 36,3...etc.
So the point is I have to copy every 4-th row with values in a nearby column. Of course I've tried the Ctrl + left click for every 4 rows then copy/paste it, but I was wandering is there a faster way??
What VBA code will copy column A in Sheet 1 to column B in Sheet 3? The range of column A to be copied begins in row 2 and varies in length. Column A ends right before the last nonblank cell.
I want the row in one worksheet to contain the data in the column of another worksheet. For example, Column C in the "Agency" worksheet contains the acronyms for 57 agencies. I want populate ROW 3 in the "Child" worksheet with those acronyms without having to enter a unique "='AGENCY'!C..." formula in each cell of Row 3. How can I do this?
At the moment I have data in column A that I would like to move over to columns B, C, D, E, F, G and H. I need to do it for the whole sheet (60,000 rows), but the data isn't evenly spaced so I'm making a bit of a mess trying.
Here are a couple of examples of what I am trying to do.
Example 1: Data on the left Example 2: How I want the spreadhsheet to look Example 3: What happens when I copy and paste the row 3 columns b-H
I've been messing around with formulas like: if column A = the word CD, put the contents of the cell in this cell. I'm not that clever though! I know it can be done and it's annoying me not being able to do it.
every month a create two different spreadshets and I'm tyring to figure out what is the easiest or the best way to transfer the date from the sample2.xls to Sample1.xls (samples attached). Probably is an easy solution but haven't been able to find a solution on my own so I decided to ask for your expert opinion.
The data columns in both the workbooks are of same length,I just want to copy the corresponding columns from source to destination as per above referenced cells.
I am using a macro to copy a variable column of data from one file column A:4to another but what I want to do is copy the column apart from the last 2 cells. I am using:
I have a sheet which has multiple columns, i want copy the data from column Q to Column H depending on the filter that i'm using on column D. Below is the code, what am i doing wrong here
I'd like the following code to do is to go through each worksheet in the workbook and copy the value of the formula in cell S2 down the S column to the last row based off of a count of rows in column B. It's not working quite right and was hoping someone would be willing to correct it.
I have finished a subroutine that allows me to press a button to run Macro1 that will open a dialogue box to select FileB (that contains data I want to import). Right now, it will import that data (always B4:B20) from Workbook1 and insert it into Workbook2.
I would like to adjust this macro so that each time it is run the imported data won't replace into one column but instead be inserted into the next empty column. i.e. Right now the first run places it into B4:B20 in Workbook 2, and I want the 2nd, 3rd, etc times it's run to place the data in column C, then D, etc.
This is what I have so far, but it only does it for the first piece of data. I want all 16 entries.
I have a cell "A1" that changes values. Every time "A1" gets a new value I want to copy it the the cell below the last value in Column "B". Assume "B1" contains a column name.
I want to copy A1 to B2, then copy A2 to B3 when A1 gets a new value.
Then I want to copy A1 to B3 when A1 gets another new value and so on.
