I am doing an export of info from one system and running an excel spreadsheet to manipulate the data. The problem is..That the exported data sometimes will export info into combined cells. Look at attachment
This is what I need...
I need the ability to search all the info on the POC tab and pull it into the info tab, by searching on a system and returning the POC info..
I have tried the vslookup formula and it will return the first row not the second row which is where the info is that I need (shown Highlighted) =VLOOKUP(A4,POC!A2:b50,2,FALSE)
My merged cells are not purely aesthetical - each merged cell (covering 6 rows in total, beginning at A3:A8) has colums and rows to the right for other details/formulae relating to that specific merged cell (which contains a customer account name).
The list I'm trying to reference is on a seperate tab - 'Projects' starting in cell A4. This list will have project names added to it, so I need my merged cells to reference the list, and subsequently pulling through all new additions to the list, rather than having to add them on a 2nd time.
Merge a cell to the cell below it. Now merge the cell below the merged cell to the cell below it. Make one more merged cell. Now put text in each one like this "excel", "hates", "me". Now click on a different cell somewhere else on the page and go to menu Data --> Validation. Choose "List" and then in the source click the little button to the right and then highlight your three cells. Now you have a drop down menu in the somewhere else cell.
But when you go to the dropdown menu it has blank spaces in between the entries (because of the merged rows). If you do the same thing but the list is with cells that are not merged to the rows below then it doesn't have the blank rows. If you are only dealing with cells that are merged in twos it is okay because it is spaced all pretty and everything. The problem is when the cells are not merged evenly so everything looks uneven.
how to get rid of the apparent blank entries in the drop down menu?
I'm trying to create an excel spreadsheet to manage personnel and various ongoing projects, what I'm trying to do is create a custom box with a selection of options (5) that will automaticaly fill a selected range of cells with a colour and 'merged' text that was selected from the custom box.
I am having a list consisting of two columns. Column A describes a "product" and the currency directly below. In column B i have the quantity of the product and below the value. Now i have products in USD and EUR and would like to only sum up the quantity of all products which are in USD. A simple example is attached and i calculated the target valua manually. The values i would like to sum are market in red.
I have 2 lists, A and B. The lists were Obtained by different methods. However the information is now static and It seems that they have compatible information and format. But when I cut and paste A to B to make a single list, and I try to delete duplicate rows in the single list, I'm unable to do so. What can I do? Attached are the lists.
ID Responsibility Criteria One Criteria Two Criteria Three Numbers Functional Column
ID1 Marc lorem lorem lorem 1 x
I want to build a little report on another worksheet that goes over the table and counts cells (i.e. data records) based on the entries in the last column (i.e., if the corresponding data-record has an "x" in that column, then it should be counted).
As every data record also carries a numeric value in the "Numbers" column, I wanted to do this with COUNT formula combined with an IF-formula.
The formula to count the corresponding data records that match the criteria then looks something like this:
However, this always counts all cells in the "Numbers" column. I want the formula only to return the range of "Numbers" cells, that belong to the matched data records.
I have a report I am attempting to populate with data from a pivot table in another worksheet. Column A holds all the reference numbers (primary key), column B contains various start dates, and I want column C to contain all the payments made since the start date for each reference number.
The source data is a pivot table with Row = Reference number, column = transaction date, values = transaction amounts. This is an extremely large table, as I'm processing data from almost 1,200 cases, which each have around 20 payments spread over the last year, on completely random days. What I would like to do is build a formula in my report which looks up the records for the reference number from column A, and then adds up all the payments which have been made after the date in column B (and ignore any payments in the table which are before that date).
And to make things more complicated:
if an error is generated, it needs to return as 0, not #N/AThe report has the dates in UK format dd/mm/yyyy, but the pivot table has the dates in SQL format: yyyy-mm-ddThe pivot table is connected to a SQL database via ODBC and has to refresh every time it is opened.
I am trying to create a summary Calendar that would display all the names of the people who are off on a given day. I have a grid where I display each day of the month and a person in the group has their own column. If they are out of the office for any reason their name appears in that row. In the big Summary Calendar cell for each day of the month I want to look across all the people columns for that day and then display the names of the people who are not going to be in the office. Can I do this by a formula or will using VBA work best.
I wish to copy a merged cell (3 cells) based on if only 1 of 3 cells to the right contain "X". if the top cell does not contain "X" than the merged cell is not copied. Also, is therea more elegant to copy 3 columns at a time rather than do one at a time as my code shows:
Sub CopyICUCAPU() ' ' CopyICUCAPU Macro ' Dim i As Integer
Column A's rows are merged according to column B. E.g.
Row 1-7 (merged) Ron
Row 1: New Jersey Row 2: New Delhi Row 3: New Jersey Row 4: New York Row 5: New York Row 6: New Jersey Row 7: New York
My result should be like:
Row 1: Ron
Row 1: New York
and similarly for other rows also.
PS: The no. of rows merged for column A can be different.
Also the keyword that should be present in Column B should be according to hierarchy, New York, New Delhi and then New Jersey i.e. first the macro should check for New York, if its present (in 1st 7 rows according to example given) then New York should be written in Coulmn B, if New York is not present then it should check for New Delhi and New Delhi should be written in Column B and if New York and New Delhi both are not present then it should check for New Jersey.
There are only 3 keywords which need to be checked i.e. New York, New Delhi and New Jersey.
If these 3 keywords are not present then the macro should not do anything.
I'm trying to iterate through a range of merged cells, but each cell in the merged row is being iterated through and I want it to go from row to row. This what I have, but the subscript goes out of range since is looking at the cells within the merged rows.
I am looking for a code that will clear all of my unlocked cell in sheet 1. That is not a problem but since many of the cells are merged I know it keeps throwing me an error saying cannot change contents of merged cells or something like that. Does anyone know how to get around this without unmerging the cells. I saw a code to unmerge all of the cells on a sheet but I really don't want to do this as I already have worked around most of my problems with the merged cells.
i have a merged cell which runs across a multiple columns (but a single row). i would like to insert a column in the middle of this merged cell, but whenever i click on the column header to insert a column, the entire merged range is selected.
is there a way to change the excel setting to avoid this?
I have a spradsheet that I've formatted using merged cells. The spreadsheet has several columns. Into these columns I must enter raw data extracted from our SAP data base. The extraction worksheet has no merged cells. So bottom line, I have a column of say, 30 non-merged cells and I want to paste that column into my formatted spreadsheet that has 30 merged cells of thre cells a piece (90 cells total).
So far I have been unable to do this. If I've attached it correctly, the sample attached
Im having problems trying to sort through data and linking certain data. I will post example below:
Main 1 Main 2 Main 3 Sublevel 1-4
This is just one part of a row of the workbook but shows the Main data which is made up of 4 different sub levels on the right. I merged 4 cells for the main data so that all the cells lined up but I'm still having problems with being able to sort and link the files together so that when I filter, they all do it together. The example didn't paste well.
Book1 and Book2 are workbooks that I have modified in order to protect private information.
Book1 will have 11,000 records (my example Book1 has only 100). I need to rearrange Book1 such that it looks like Book2. Book2 has 20 complete records from Book1 combined into one single row, and my example Book2 has populated 3 rows only (3 rows x 20 records, making 60 records now appear on 3 rows only).
Macro for getting Book1 to Book2? 11,000 records in Book1 will take a lot of hours to transform into Book2 unless a macro can do the job for me.
I have a sheet where i want to delete duplicate rows where column A and column B combined are equal, i.e. range(Ax:Bx) where x is the current row. I am using the macro below but cant seem to get it working as I keep getting a type mismatch error and Im not sure why.
i have a problem to sort merged cells in the file attached. i tried via macro but could not reached a good solution. I have 30 plans inside the sheet and even manually is too hard to do it because i use some "=" to not type again every number since some are the same but when i try to sort the lines became diferent values.
I now see why alot of you dont like merged cell's. What i am trying to do is Cut and paste a range that contains merged cells. A kit can be created using the Button on the sheet. The cells in column G are merged down to the last kit item. On Row A of the kit the 1st cell contains data validation once the Delete kit cell is re-entered it removes the selected kit from the sheet. Then will select all kits below the (now deleated one) and cut the range, and paste it to where the old kit was located.
When the kit's are pasted into the new location there are issues with the merged cells in column G. Is there a way to fix this issue, or know of another approach to keeping the same layout.
I have a spreadsheet that has merged cells on that is typed into.
I have a Macro that pulls the information into another sheet and then it tries to clear the cells for the next person to fill in. The issue I am having is that i cant clear the merged cell. I have tried the following code