Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Reorganize Cells

I didn't find any solution yet, concerning reorganization of cells in an excel sheet. To sum up, I created a macro that is able to reorganize a 2*5 table (E4:F8) into a row (H4:Q4), but I would like to apply this macro to any 2*5 table that I select (not only E4:F8, but also E9:F13 for example...) because I have a sheet with hundreds of these 2*5 tables. Here is the script of my macro, how can I adapt it so that it works on cells I select manually and not only on the E4:F8 cells?

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Reorganize Data
Is there any easy method to tranform the table from Tabel 1 to 2?


19/6/2009C43.12 ....

View Replies!   View Related
Import And Reorganize Text File
I have this text file that gets sent to me, and i want to be able to re-organize the horible set up (see Import tab) into the proper format (see Reorganized tab) using Macros. There are many variables, such as the number of part numbers, the number of problems can all vary. As well as the number of claims. I don't even know where to begin. See attached file as a sample.

View Replies!   View Related
Reorganize Spreadsheet To Comply With Vendors Inventory System
Basically We have a column with our part numbers and a column with the vehicle applications which contains the years, make, model, and engine.

Most of our part numbers have multiple applications that they work for.

The vendors system only uploads unique part numbers, so I need to find an easy way, if posable, to combine all the applications that use the same part number in one cell next to it's specific part number.

I don't know if I should be messing with formulas, pivot tables, VBA...?

View Replies!   View Related
Values In Each Cells(A) Represented Back At Cells(B) But No Repetition If Some Cells(A) Contains Same Value
I did my search, but cant find and knows what key search to look/type for...

If i have data A1 through A10, such as 1 1 2 2 2 2 3 3 3 3

How can i get column B1 through B3 as 1 2 3 ?

View Replies!   View Related
Date Cells In Project Plan To Change Based On Other Cells Including Other Date Cells
This is a project plan with tasks and dates. Column A is the activity number. (Example 1, 2, 3" etc). Column B is the task (Ex. "Complete Report"). Column C is number of days required to complete the task. Column D is the dependency column. (Ex. Cell D2 =1 in other words Task 2 is dependent on task 1). Column E is the date.

I would like to have a seperate start date cell and a go live date cell.

The objective is to enter a start date, and have each column E date increase based on the number of days entered in Column C. If a task is dependent on another and I change the number of days in Column C I need the dependent task to change the same amount of days.

View Replies!   View Related
Countif Statement: Count The Number Of Cells That Have A Value Greater Than 0 In A Range Of Cells
How do I count the number of cells that have a value greater than 0 in a range of cells?

View Replies!   View Related
Forcing Cells To ALWAYS Find MIN And MAXIMUM Values From A Specific Range Of Cells
I'm working on a project for my company. We make plastic tanks and for quality control we want to start recording the thickness of the tanks in different areas/zones of each tank.

Attached to this message is an Excel sheet that I've been working on. From "Sheet 1", it records inputted thicknesses into WorkSheet "1098". On the top of "1098", it shows all of the recordings, and just below that are the "10 Most Recent Entries".

Right below the "10 Most Recent Entries", there are formulas to calculate the Min and Max Values. Whenever a new entry is recorded, the selected cells for the Min and Max formulas change. Is there a way to force the cells to always stay the same?

View Replies!   View Related
Conditional Formatting: Cells Filled By Red Until The User Enters Text In Those Cells
Is there a way to set up a conditional format for several cells so that the cells are filled in with red until the user enters text in those cells??

View Replies!   View Related
2003 - Merging Four Adjacent Cells With Text And/or Blank Cells
I am trying to merge text, in four adjacent cells in the same row (say cells A1,A2,A3 and A4), into a single cell (say cell A5).

I would like a comma or full stop and then a space between each item merged (cell A5).

The text to be merged may appear in any one of the four cells (cells A1 to A4). Those cells without text are blank.

Only where all 4 cells are blank, will I need cell 'A5' to indicate this.

The formula needs to be relative as I will need to copy the formula down the spreadsheet so that it applies to additional rows.

View Replies!   View Related
Lock Cells Based On Value Of Other Cells - Code Simplification Query
I'm using the code below to lock certain cells depending on the value of other cells. The code below deals with one line of my spreadsheet only and as the spreadsheet comprises 38 data entry rows I've repeated this code 38 times in the worksheet module with the appropriate changes to row numbers.

It works, but causes much screen flickering and "thinking". I'm new to vba so no doubt I've made this code too extensive or lengthy or whatever (or just plain wrong).

Can anyone assist with suggestions on how to simplify the code and/or help with code to handle all 38 lines without repeating the routine 38 times?

View Replies!   View Related
Color Cells Based On Adjacent Cells Being Empty & Add Text
Im trying to make my life a bit easier, by adding a few macros and formulas to the spreadsheet (Everything was done completely manually before I got here!!!).
What I would like to do is take two columns, which contain a start and end time for work shifts, and colour them GREEN once I have entered a name in the Worker column (Along side the two with the time), and also to fill a cell with a Yes or a No. Im aware of auto conditioning, and Ive tried to have a play to get this to work, but I just cant work it out.
I have posted a link to an image which shows what I want. I hope I've explained it well enough!

View Replies!   View Related
Range(cells(),cells()) From 2 Different Workbooks, Run Time Error 1004
I've been trying to copy values from one range to another, and the ranges need to be dynamic. Normally I use a combination of the 'range' and 'cells' properties, like this:

View Replies!   View Related
Merge Date From Adjacent Cells Inbetween Current Cells
I have 2 columns of data and want to be able to merge as follows

4123567 123.45
4125467 900.56
4356456 456.32

need to get result of


View Replies!   View Related
Fill Empty Column Cells With Reference To Adjacent Cells
I would like a macro that when run, finds empty cells in a column within the used range and fills them with the same formula in the other cells in the same column but relative to the row.

I have a basic understanding of VBA so if someone can set me on the right track i'll have a go myself as i appreciate this would take a while to write out from scratch.

View Replies!   View Related
Cells Not Being Populated With Exact Same Formula As Previous Working Cells
formula using if's and vlookup's to populate a calendar with x's for day a person is gone. however the last couple of people that are gone are not being marked on the calendar even though i use the exact same formula (except for cell numbers and such) for previous, working entries. Here is my formula

View Replies!   View Related
Lock Empty Cells In Range Based On Other Cells Content
I am trying to lock the unused cells in 32, 2 column by 7 row named ranges, based on whether or not two cells, above each range are equal or less than each other. In other words while one of the cells is less than or equal to the second cell all cells in the range below should be unlocked, as soon as that condition is no longer true the blank cells need to be locked.

I am trying to use this in the Workbook_Sheetcalculate so that the macro will run automatically.

View Replies!   View Related
Count Unique Cells Based On Ajacent Cells Having Values
I have the following data in 1 of the tabs.


I want a unique count of sequences in a different for that class only if that particular row in 'A' or 'B' is populated. The result set should be as follows:

Can this be achieved through a formula?

View Replies!   View Related
Copy Cells Where Range Address Resides In Cells Of Another Sheet
Tried to write a Macro to Range Cells in a sheet, setting the range values from a another cell. I have encounted a Run-Time error as select method of Range class failed. Below is the Macro.

Sub Macro1()
Dim r1 As Range, r2 As Range, myMultiAreaRange As Range
Set r1 = Range("J4")
Set r2 = Range("K4")
Set myMultiAreaRange = Union(r1, r2)
Range(r1, r2).Select
End Sub

Have the Ranges been set incorrectly

View Replies!   View Related
Clear Contents Of All Unlocked Cells (many Are Merged Cells
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.

View Replies!   View Related
SumIf Function To Sum Cells When Other Cells Begin With Certain Characters
I want to use the SumIf function to sum cells when other cells begin with certain characters.

I've toyed with a few ideas of how this could work, but i don't know how to specify that the cells need to begin with certain characters. The cells that would be the criteria and the ones that would be summed come out of an Oracle database (and i have no control over the way they're pulled out - yet) so the beginning characters are connected to extremely unique information, so i dont want that to be included in the if part, for obvious reasons.

View Replies!   View Related
Fill Cells Color In Worbook Based On Other Cells
how can I automatically fill cells with certain colors based on the value of the cell. (i.e. I want to search an entire workbook and fill cells with values between 80 and 99 green, 60 to 79 yellow and 0 to 59 red.)

View Replies!   View Related
Find Value In Cells, Merge Found Cells To One Column
I need to mine a huge spreadsheet of about 30,000 cells and make columns from specific words. As in.

I need to search for all words "table" even if the word is "My_table" or "aTable" and suck them into one column. I have to do this one several words.

Let me know if you have the solution. I am trying to write something but I am unsure of the syntax at this level.

View Replies!   View Related
How Do You Protect Cells On Condition Of Other Cells Having Data Inputted
way of protecting an array of cells on the condition that information is entered in another array of cells or vice versa. So, put simply, if data goes into a1 then a2 becomes protected (or vice versa).

View Replies!   View Related
Show Contents Of Vertical Cells In Horizontal Cells
I am referring to the post made by NBVC at, Try: =IF($A1="",INDIRECT("A"&ROW()-ROW($A$2)+COLUMN(B1)),"") in B2, copied across and down

I have a similar case but there are no empty rows between lists, what would be the formula to get the same results? (The transposed address should appear next to each first line of each block).

Earlier, I posted at and I got brilliant answers, I also would like to get benefit of the above mentioned formula but in cases when there are no empty rows between lists.

View Replies!   View Related
Shading Cells If Values Exist In Same Cells On Another Sheet
We have a simple holidays workbook, with seperate sheet tabs for each member of staff in the department to note down there holidays in a calendar.

The 1st row (from B1 and onwards) contains the day numbers of the month, ie. 1 - 31
The 1st column (A2 downwards) shows the 12 months of the year.
The holidays are then marked in the relevant cell matching the dates required. The codes we use are F (full day), A (morning), and P (afternoon). A countif adds up all the occurences of the various codes and totals the ammount of used/remaining days.
Hopefully that drew a picture.

The problem is, each sheet only shows the holidays marked down for that person. There is no way of checking for a "clash" (ie. 1 member of staff off on the same day as another) without flicking to the other sheets and checking yourself.

how can I get a cell shading to automatically show up on the sheets, if there is a holiday noted down (ie a value, A, P or F) in one of the other sheets?

View Replies!   View Related
Formatting Cells Dependent On Other Cells Relation To That Cell.
i have just read the thread started by scabertrain regarding "if" conditonal formating, but mines slightly different and i cant make head nor tale of the fomula shown.

So... Monthly budgeting... using a projected outgoing (A1) and an actual outgoing (A2) i want the colour "A2" to change depending on whether i have spent over the budgeted amount, green being underspent, orange for on target and red for over spending....


I predicted (A1) that i was going to spend 50 on a night out, but my actual spending (A2) was 100, i would like A2 to turn to red.

View Replies!   View Related
Highlight Cells On A Form So That A User Knows Which Cells To Fill Out
1. I want to highlight cells on a form so that a user knows which cells to fill out.

2. When they print the form I do not want the cell shading to print.

View Replies!   View Related
Hyperlink All Worksheet2 A Cells To Worksheet1 A Cells According To Their Row Number
I am trying to hyperlink all Worksheet2 A cells to worksheet1 A cells according to their row number. I have tried to create a Hyperlink in A1 and drag it down but what happens is that hyperlink links to same cell adn doesnt change cell number whilst dragging.

View Replies!   View Related
How Do You Select A Range Of Cells On Another Worksheet Using The Cells Property
I seem to be going round and round in circles with this, but I'm sure it should be easy.

I'm just trying to select a range of cells in Sheet2 of my workbook.
I've tried many different bits of code, including:

Dim namesTotal As Integer

namesTotal = 2500

ActiveSheet.Range(Cells(1, 1), Cells(namesTotal, 8)).Select

(According to the Microsoft website, this is supposed to be the way to do it?)

View Replies!   View Related
Highlighting A Range Of Cells Dependant On Data In Any Of Those Cells
On the attached sheet I am trying to indicate that enough data has been entered by highlighting the entire column. Any 3 cells in Rows 7-13 and 18-36 will be filled in with any of the values from cells O6-O11 (hidden). When the total = 6 i would like to highlight the entire column to indicate it has been completed.

The aim is two fold: to ensure that the correct number of points is allocated in each race, and secondly to indicate which column is the next to be filled in (as human error sometimes misses the column and adds the points to either the previous or next columns). You could call it idiot proofing the sheet. At present i have the cells in Row 37 conditional formatted to show this, but would much prefer the entire column to highlight.

View Replies!   View Related
Fill Cells Based On Corresponding Cells Matching 3 Criteria
I am working on a spreadsheet for a shoe company. I have separate columns for the size, model, color, and item number of a shoe. I get everything except for the item number from a written document; I then have to find the item number for the shoe from another excell document called the Master List.

I was hoping there would be a way to have Excell auto-fill the item number for me. For example, if a shoe is a Red, Athens (the shoe model),size 12, its item number (which can be a pain to find) listed in the row of the Master List is aaabbb. So I want to just enter in the size, color and model number, and have Excell find the item number for me, and fill it in.

I have enclosed an example. Sheet 1 is the sheet I would be working on. Sheet 2 is a portion of the Item master list, which is actually 50k lines.

View Replies!   View Related
Color Cells Based On Multiple Conditions Of Different Cells
Im trying to change color in cell B9 according to different conditions in Cell C9 (than on b10 according to c10 and so long until necessary - probably will be around 2000 lines). Since there are more than 3 conditions and in two cases condition depends on the color of the C Column, I can not use conditional formatting. Ive searched and found similar forums here, but since Im ignorant in VBA code, I couldnt manage to make adjustments. So if you can help me with the code, I would really appreciate it!

Condition and Results required would be:

IF column C = S than on Column B = color cell light blue with white border
IF column C = P than on Column B = color cell Green with white border
IF column C = A than on Column B = color cell Yellow with white border
IF column C = L than on Column B = color cell Red with white border
IF column C = C than on Column B = color cell Dark Blue with white border
IF column C = than on Column B = color cell white
IF column C = V and has white background than on Column B = color cell white
IF column C = V and has a Dark Grey background than on Column B = color cell Dark Grey

I dont know if the last two are feasible.

View Replies!   View Related
Format Cells Based Upon Being Outside Scope Of Calculation Of 2 Cells
I am comparing a set of common variables across 2 sets of independent financial data. When comparing a variable across the 2 sets e.g. profit I want to colour a neighbouring cell based on the size of the difference. For example if the difference between the two values is within plus or minus 1 make it green, plus or minus 5 red etc.

View Replies!   View Related
Splitting Separated String Data In Cells To New Cells
I have pricelists tables with 300 and more rows and about 50 columns. Three letter codes in first column cells are in strings separated by separator (,), I need to split this strings so that each three letter code would be in its own cell in first column, at same time all other cells in same row as original string data should be copied to rows near appropriate 3 letter codes. In attached xls file I have made small example how data looks before and how it should look after applying macro. Table should not move to other place on worksheet or on new worksheet, in attached example I moved it just to show what kind of form result should have.

View Replies!   View Related
Under Solver, The Changing Cells Box Only Alllow 200 Adjustable Cells
Under Solver, the changing cells box only alllow 200 adjustable cells. I had problem that my adjustable cells is more than 200. Is there anyway I can increase it ? maybe using VB.

View Replies!   View Related
Highlight/Flag Formula Cells Referencing Other Cells
I am trying to write a macro which highlights all the cells with any reference to other cell. The macro should highlight a cell with formula like = 3 * A25 but not highlight a cell with following formula. = 3 * 0.4535566. 'Hasformula' property is not useful here as it can't distinguish between above examples.

View Replies!   View Related
Auto-populate Cells With Data From Other Cells
My sheet goes from monday to friday on the coulombs. On the rows i have various data sections for each day. I would like the data on the last day (any day after monday) that there is data for a sheet to input that data into the monday slot when the "master date" is changed on the cheese sheet. The idea here being that the script will take the last entered data for a given sheet, and put it into the monday coulomb on the same sheet when the date is changed. This is kind of hard to explain so if you need clarification let me know. Attached is the sheet i'm working with.

View Replies!   View Related
Adding Up Values In Nth Cells With Text In The Cells In Between
i have a problem adding cells every 5th cell for example i have


this adds every 5th row in this range and it does it right as long as there is no text in the cells in between

it works fine when it looks like this


but it does not work when it looks like this


when this happens it appears as #VALUE, meaning an error but i don't understand why if i'm just telling it to look at every 5th row and those specific rows have no text in them

i have a spreadsheet with text in between the cells that need to be added so i need a formula that only adds up the cells with numbers on it, to be specific the numbers are either ones or zeroes.....

View Replies!   View Related
Compare Values From Cells To Shapes To Cells
In the attached excel file I have two macros that create hyperlinks:

1. BalloonToNumber - Creates hyperlinks from "Oval" shapes on sheet "Op60_1" to sheet1 (column D).

2. NumberToBalloon - Creates hyperlink scheme from sheet1 (column D) to sheet "Op60_1" "Oval" shapes.

In Q #1 my macro seems to skip some shapes on sheet "OP60_1" and I simply can not figure out why.

In Q #2 my macro creates hyperlinks to shapes that do not exist and hyperlinks to numbers that are not a "100%" match, IE: 182 and 82 would share the same hyperlink?

If you open the workbook and use the hyperlinks on sheet1 they will take you to sheet "Op60_1" and shape hyperlinks from sheet "Op60_1" take you to sheet1. On sheet "Op60_1" there are some red arrows indicating the shapes that get skipped when running the "BalloonToNumber" macro.
Shape color changes as the hyperlinks are selected from sheet1.....

View Replies!   View Related
Highlight Cells If Match Another Sheet Cells
I have a lottery pool in sheet 1 in which i have the names of the people and their number picks. In sheet 2 i have the lottery results.

All i want is to be able to highlite(adding a backround color in the cell) to the matching numbers in sheet 1.

View Replies!   View Related
Highlighting Cells Based On Cells Values In VBA
I need to highlight a cell when its value exceeds parameters based on the production line it comes from. If the line is K11, then i need this cell to highlight when its value is either < 0 or greater than 221. If the line is K21, than it needs to highlight when its value is <0 or greater than 474.

View Replies!   View Related
Ingoring Empty Cells When Adding Cells
I want to add across columns, which is not so difficult but I have to "ignore" the blank cells because of the way my formula works.

I'm not sure of the correct mathematical term for the addition I have to do but here is my example:

in this row are the values

1 2 3 4 5

In the row below I want to add the numbers so the result is

1 3 6 10 15

basically, a cell is always adding itself to the result of the sum from the previous cells. This formula I can handle, (=A2+B1 then autofill the results by dragging the formula across the empty cells) but when there are blank cells between values I get the error message! value. How can I write a formula which will ignore the empty cells?

View Replies!   View Related
Format Setting Is The Same In All The Cells But Only Certain Rows/cells'
my excel setting... I'm not able to see some of the numbers (in currency/accounting) in excel file, it shows as many "#" sign, and it's not due to the column width. The same file can be seen in other computer.

The format setting is the same in all the cells but only certain rows/cells' numbers appear like "normal".

View Replies!   View Related
Copy And Paste In Other Cells While Typing In Different Cells
I have to enter info daily in column H. What I wanted to do is as I finish entering info in cell H3, infomation in row 2(cell A2:E2) is copied and pasted down to row 3 (A3:E3) and will continue about 100 rows down daily.

View Replies!   View Related
Calculating Cells Used Based On Another Cells Data
I have a bit of an odd issue I need to solve. I use an Excel spreadsheet to track daily income for the store over a months' period. I have each day set to give me a rough estimate of profitability for that day. I also want to create a cell that tells me how much we've made so far, based on the number of business days so far in the current month. For example:

Day 1, $1500, $800 in expenses (monthly expenses averaged over the month, plus labor costs for the day), so a total of $700 profit.

Day 2, $1700, $749 in expenses (same as before), so a total of $951 profit.

The formula I'm using to calculate that isn't an issue, but I have the same formula for every single day, and each day has it's own cell with the estimated profitability for that particular day. In two other cells, I put the total number of business days in that month (in this case, 26), and in another cell I put the current number of business days we've had this month (in this case, 4), which is used partially to calculate the other cells. I want to use them to create another cell to contain the estimated profitability based solely on the number of business days out of the month we've had.

So E3 through E33 contain the individual days, I want to calculate - since we've only had 4 business days - what E3 through E7 would total. When I change it to 5 business days, i want it to calculate E3 through E8. So if we've had 4 business days, it only calculates the totals from the first 4 cells, if 5 business days, then the first 5 cells, etc, etc.

I can sort of picture it in my head, but I can't seem to find the necessary equation for it. It may require a macro, but I'd rather use a formula if possible. I'm not a big fan of macros.

View Replies!   View Related
Variable # Of Cells That Need Formula Matching # Of Cells
I am new to Excel macros and programming, and am running into a problem that I can't figure out how to solve. I have read through the forum and tried various examples, but I am still not getting the result I am looking for.

I have a spreadsheet that has a variable number of cells in-between blank rows (the data can be 1 to 6 rows). I need to sum up the rows of two columns and see if they equal 0 or if there is a difference. If there is a difference, I want to make the cell red. The exact problem I am having is I can't make it check the # of rows and run the formula based on that number of rows. I am attaching an example of my raw data followed by an example of what I am looking for as the end result.

View Replies!   View Related
Copy Cells And Range Of Cells To New Worksheet
I have an excel workbook that I have created to use in the creation of purchase orders. I have a spot for the vendor in cell D10 and my items are in the range of B17 to N17.

What I would like to have is a macro that I can assign to a button that does the following.

I have a worksheet called "Purchase Order" that has all the information in it.

When you click on a button named "Process", it will take everything entered into B17 - N28 and copy it to a worksheet named "PO# History" starting in cell B2 and moving down. Each time I click on "Process" I need the information in the range above to get entered into the next empty line on the "PO# History" worksheet.

This way I can maintain a list of items purchased and then play with them in a pivot table/graph and such.

Also, What I need it to do also is when process is clicked, the macro needs to pull the vendor name from cell D10 and put it in column A on "PO# History" for each row it adds from B17-N28.

View Replies!   View Related
VBA To Clear Contents Of Cells And Keep Some Cells Locked
I have written this code to clear the contents of certain cells, lock the content of others and protect the sheet again it works on sheet1 but not on sheet 7. This is suppose to happen when the Print button on my sheet is clicked.

View Replies!   View Related
Calculating Sum Of Cells Only Where Adjacent Cells Are Blank

I am constantly editing this (we currently have over 100 accounts) and therefore the totals are changing.I have a formula for Total but I need formulas for the other two, based on when the cells in columns F and J are blank or have dates in them: For active, the total is the sum of all numbers in column M but only when there is a date in column F and a BLANK in column J. For yet to enter, the total is the sum of all the numbers in column M but only when both column F and column J are blank. At the moment, my accounts run from row 6 to row 142, with the first line of totals in row 145, however this is constantly expanding.

View Replies!   View Related
Linking Cells By Looking Up Part Of Link In Other Cells
I'm wondering if there is a way of linking to cells by looking up part of the link in another cell. E.g.In Sheet1, cell A1, i have some data that I want link to from Sheet2. Normally I'd do that by using "=Sheet1!A1" in the cell in Sheet2.

But I'd like to lookup the "A1" part of the link from two different cells in Sheet 2. Something like: =Sheet1!(Text from B1 in Sheet2 AND Text from A2 in Sheet2)
or: =Sheet1!(Sheet2!B1&Sheet2!A2). So that if Sheet2!B1 would contain an "A", and Sheet2!A2 would contain a "1" the resulting link would be the correct "=Sheet1!A1"

View Replies!   View Related
Copyright 2005-08, All rights reserved