I am trying to move a group of cell containing formulas from one sheet to another, I have tried copy & paste/ ctrl & alt / paste special but for some reason the initial cell references of the formulas are not updated.
View 3 RepliesI am running Excel 2007 on Windows Vista Business 32 bit. Recently I have noticed that if I enter a formula into an empty, unsused cell, it is recognized as a formula. If I modify that formula, it is then recognized as text and does not work as a formula. The only way I can get the cell to recognize a formula is to delete the cell and start over. This same scenario does not occur on previously stored workbooks. I have checked all of the flags that I know about, including the Options function.
View 3 Replies View RelatedI have an excel work book with 6 tabs. I would like to have Excel move an entire row from one tab to another tab (removing the row and inserting it in the other tab). I.e. Example I have a tab with items that are marked as "Open Actions" so if I were to change the drop down to close. Excel would move that entire row of actions to the tab with the "closed actions" and insert into the next available row. Now if someone were to come back at a later date say no it should be reopened than I would change the drop down to open and excel would move that row back to the open actions tab into next available row. I tried a PIVOT table and no good I played with few macro and not.
View 5 Replies View RelatedAbout half of the time when I move the contents of a cell I get the message "The Clipboard cannot be emptied. Another program might be using the Clipboard"
View 14 Replies View RelatedStarting on Monday and ending on Friday and then excluding the weekend dates beginning again on Monday. I would like to be able to store a value within a seperate cell.
EX:- Cell A1 contains the value of Cell B1,(B1 being Monday's value) but when the date changes so the value of Cell A1 should become the value of Cell B2 (Tuesday's value) and when the date changes again the value of Cell A1 should become the value of Cell B3,(Wednesday's value and so on). This should happen until Friday and then the calculation should evaluate the two days of the weekend and do nothing before moving on to calculating Monday's value and displaying in Cell A1 the appropiate value.
I have a excel spread with a range of A1:H64. The spreadsheet is designed for the user to enter information into the appropriate cells, i.e. user ID in cell D4; name in cell D5; and date in cell F5. My question is instead of the user scrolling around and finding the cells that require input is there a way to set the spreadsheet where the user tabs and/or uses the enter key to move around and go only to the cells that require input and not to the ones that are locked and protected?
View 9 Replies View Relatedit is possible to move a row of cells with (names in each cell) to a column. Basically, I want the list of names to go from top to bottom rather than left to right.
From this (each name is in separate cells)
John Jones Bob Smith Jane Morgan
to this:
John Jones
Bob Smith
Jane Morgan
I have a merged cell area, several columns wide and 2 rows deep.
I know that pressing enter before reaching the end of the first row moves to the next cell below. Is there any method, either key combination or whatever, of moving to the next row within the merged cell?
following code is supposed to check whether cell contains a value and based on that either display or hide a shape. The last couple of lines are moving it. What I would need is to check all cells in a row (not only the first one) and if lets say first 1,2,3...10... cells contain a value move the shape all the way to the end of that cell and so on.
VB:
Sub Worksheet()
If Sheets("Text").Range("z6") <> 0 Then
With ActiveSheet.Shapes("Rectangle 8").Fill
[Code].....
is there an option to allow cells under a pivot to keep the same distance under the pivot table when it grows larger or gets smaller?
View 1 Replies View RelatedI need to identify values which are less than 0 and move those cells to an adjacent cell.
Acct Desc Amt
2100 Acct1 -10
2101 Acct2 10
The -10 cell will move one cell to the right. I have an imported list with gl accounts, descriptions and amounts and want to have the negative values moved to a new column.
I have a list of diagnostic procedures, some of which are for the same patient. I need a macro that does the following: move every second procedure (a couple of cells per row) of one patient number (column B) next to (11 cells to the right and 1 row up) the first procedure. But only if there's less than 3 months between the procedures (dates are in column G) and if both procedures were on the same side of the body (left or right, column L)
Is there a macro that can do this for me?
Is there a macro that just moves a couple of cells in a row 11 cells to the right and 1 row up?
I have been given a spreadsheet to fix as the user says the charts/graphs aren't adding the new data.
But when I looked closer at the workbook the whole thing just seemed a little wrong to me
Please see the workbook attached.
All the data is entered into the sheet 'Data Entry'.
Then on the 'Data' sheet it should pull thru some key values but half of it isn't done and it isn't very smart.
I have created a 'TEST Data' sheet so I could start messing with it.
I was going to pull the data thru from 'Data Entry' into the 'TEST Data' sheet using a Hlookup to make sure it worked before i messd with the actual 'Data' sheet.
The problem I am having is moving/copying the Hlookup across.
On the 'TEST data' sheet the Hlookup is as follows and is correct:
=HLOOKUP($A$5,'Data Entry'!$C$4:$M$472,2,FALSE)
But the next set of (Week 2) is not in the 2nd or even 3rd row of the array, its in row 11......... thats 9 rows down from the original.
Moving on the data i want to bring back is always 9 more than the last so the Hlookups should look like:
=HLOOKUP($A$5,'Data Entry'!$C$4:$M$472,11,FALSE)
=HLOOKUP($A$5,'Data Entry'!$C$4:$M$472,20,FALSE)
=HLOOKUP($A$5,'Data Entry'!$C$4:$M$472,29,FALSE)
etc etc
Can any of you guys out there help on how I can do this or it may not even be a Hlookup but something even smarter!!
I have 2 sheet in Excel. Sheet1 I rename main page and Sheet2 I rename database. In my main page I use tab key to be moving from within 3 cells (B5,B6,B7) that are different to include data. How the data may be stored in sheet database. When we fill data in main page how to evacuate data to fill second data.
View 1 Replies View Related I have a Rental Form that i'm working on, and to tidy things up when I print it out, I would like to move a cells data down 1 row if that cell is empty. Here's what I have:
cell b8 - First and Last Name
cell b9 - Address1
cell B10 - Address2
cell b11 - City, State, Zip
cell 12 - Phone #
etc.
Not too many people have an address2, so when that field is empty, I'd like to move Address1's data down to it's position (address2). If address2 has data in it, leave address1 where it is. Simple right? This moving would happen when the command button is hit and the form data goes to sheet 1 which works fine.
I have a column of data that contains various blank cells where no data was measured. In the adjacent column I want to take the moving average of the last 4 data points including the most recent entry. My problem is i do not know how to handle blank cells where there was no data. I need it to average the last four in the column where data acutally exists. I am ok with using helper cells if needed and I am not worried about the first four results at this time.
Example data
A..................B
15
50
25
20................55
Blank............55
30................31.25
35................27.5
blank............27.5
blank............27.5
15................25
10................22.5
15................18.75
40................20
blank.............20
I am trying to design a spreadsheet for planning a project schedule. Down the left are the dates, across the top are the resource names and in the content are the activities being carried out.
Each activity I have is given a two letter prefix, e.g. TD = Training Delivery. I then have conditional formatting applied to each cell to change the cell colour based on the two letter prefix. In all there are six conditional formatting rules.
Now, my problem is that I need this sheet to be really fluid. I want to be able to drag and drop blocks of activities to a buffer area at the side and then drag them into another part of the schedule. However, when you drag and drop (effectively cut and paste) the activities around the conditional formatting moves with the cut content, whereas I want it to remain where it was. I guess what I'm after is a way of dragging and dropping values only.
The best I've come up with so far is an onChange macro that reapplies the conditional formatting to the relevant part of my spreadsheet, but I can't believe this is the most elegant solution. Is there perhaps a way of triggering a macro onPaste?
I've got this large data set that I need to edit. My problem is, the clients name and address are contained with in one cell eg, John Smith, Alba Road, Bigtown. Is there some way of transferring this address into three different cells foe filtering?
View 2 Replies View RelatedA spreadsheet that can be used to enter the hours worked by staff each week and then calculate their Gross pay, Tax and Net Pay. THe basic spreadsheet has been created, however it will require aq macro to copy the Week ending, Hours worked, gross pay, tax and net pay columns to the first empty cell in row 1, clear the entries in the week ending and hours worked columns that have just been created and then hide the give columns to the left. which show the data for the previous week.
I know this sounds confusing but ive supplied the file to help you understand. The problem is that we can get the macro to hide the previous five entries and copy 5 new ones in their place but it only does this once. Just about our whole class is having trouble with this as we're not very advanced in macros.
i have a row, A2-A7
there is data in each cell
can i make it so i move any of the cells up or down a row or wherever and they will all move in a unit as if they were merged? i dont want to merge though because then that will screw up the data?
How to make a formula with references to certain cells, so I can sort the sheet but keep the formula referring to the same cells as before the sorting?
In the example I illustrated the problem: when I sort the data, the formula refers to the same cells which have now different values, so the calculation is not right anymore.
I have a worksheet that I will be allowing other to access so that I can get them to input certain data. I will be protecting the sheet to avoid having them inadvertantly overwrite any of my formulae. So, I have a matrix on the left for them to input data into with calculations to the right that they cannot change. Can I prevent them from moving cells within the input matrix
View 1 Replies View RelatedI am looking to remove all blanks from a table I have and move all the data left. Right now the data is by date, and some dates are blank for some players (it is a basketball sheet) and filled for others, but I want to see it by game. So I have what is in the first table below, and I want it to look like what is in the second.
2-22-2014
2-21-2014
2-20-2014
2-19-2014
2-18-2014
[code].....
I need to remove numbers from a string of text and put them into new cells in Excel Starter 2010. There are two different values which need moving. The first is in parentheses and the second follows the parentheses.
Here's a few examples of what I mean:
Original text
RED008 - Wickaman and Hoodlum bandwidth (0.06Gb @ 2.00) 0.12
MFR005EP - Various Artists bandwidth (0.19Gb @ 2.00) 0.38
RAZORS010 Future Cut bandwidth (0.01Gb @ 2.00) 0.01
would like to split into...
Cell 1
RED008 - Wickaman and Hoodlum bandwidth
MFR005EP - Various Artists bandwidth
RAZORS010 Future Cut bandwidth
Cell 2
(0.06Gb @ 2.00)
(0.19Gb @ 2.00)
(0.01Gb @ 2.00)
Cell 3
0.12
0.38
0.01
I'm trying to work out how to take all cells with data from multiple columns and stack them in a single column.
Here's the history...
I have multiple part numbers in single cells in column A. I perform a text-to-columns function. The resulting part numbers spread across multiple columns (say, B through K). Now I need to get all the part numbers, in their own cells, stacked in column A for one continuous list of single cell part numbers.
Is there a VBA option for cutting only the data cells from Column B-K and pasting the data at the bottom of column A while avioding blank cells?
I have one excel file sheet in which we enter client data and another page in the same file that formats the data. Each new row is another client.
It is a simple formula in the data formatting sheet ='data sheet'A23 for example.
The problem is when we add client data above A23, say 2 rows, the format data page will follow the original cell and now say ='data sheet'A25.
How do I keep the cell as ='data sheet'A23 no matter what I do the original page?
Have developed a form for my associates and want them to be able to move to the cellls I want them to fill data in to by merely hitting the TAB or ENTER key vs. having to move the cursor to each cell requiring them to input data.
Want cursor to move in a specific cell order. You would think you could merely say 1M, 2C, etc.
To monitor fast moving decimal values in the even numbered cells( e.g. F2 to F 30 ) of a column, ignoring blank cells, and if two of cells values simultaneously fall below a certain value e.g. 2.04 True if not False.
Macro to clear cells with numbers but no cells with formulas with in this macro:
Dim i As Long
i = Range("E3")
If i > 0 Then
' Copy range
Range(Cells(6, 10 + i), Cells(500, 17)).Copy
Range(Cells(6, 10), Cells(500, 17)).Select
' Paste special
ActiveSheet.PasteSpecial Format:=2, Link:=1, _
DisplayAsIcon:=False, IconFileName:=False
' Clear i columns on the right
Range(Cells(6, 18 - i), Cells(500, 17)).ClearContents
End If
End Sub
The range is where the cells with numbers need to be cleared but not the ones with formulas.
the cells"A1" value = DISLCM864244984921MW#Q195FR "
how can splited the cell value just "864244984921" = "B1"
Have a huge data needs to count the 12 digi of numbers.