Hi guys,

Can you please help me further expand the macro below? I need it to also look between the ranges of C24 and Z24 and hide the respective column. (i.e need some sort of loop)

i.e

if C24 is 0, then hide column C

if D24 is 0, then hide column D

etc...

Sub Hide_Column ()

If Range("H24").Value = 0 Then

Columns("H").EntireColumn.Hidden = True

Else

Columns("H").EntireColumn.Hidden = False

End If

End Sub

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link __ http://downloadreimage.com/download.php__. (This link will automatically start a download of Reimage that you can save to your computer.)

Hi Guys,

Apologies for repeating an old question, but I am trying to build my spreadsheet so that it auto-hides any rows were there is zero premium outstanding for a client.

I had gone through previous answers, and I had tried to lift and amend some VBA code to achieve this. However, somewhere within this code or the way I've implemented it, there is an error, because it's not showing of hiding any rows.

Can you please take a look at the attached and fix it (in order to hide all rows with a zero in column C), and maybe point out what I was doing wrong or omitting. I have anonymised my data.

Many Thanks

John

Apologies... the criteria column is actually F, not C, the "outstanding premium" column. The intention being to display only those rows where client still owe premium

Hi,

I am trying to hide rows in excel 2010 based on cell values in a certain column. I know that I have to use the VBA but I don't know how to do that kind of stuff. I know it should be simple to do but I don't know where to start. Let me know what you need in order to help me accomplish this task.

Hello to evrebody,

I have look into the answers and the solutions about "hide/unhide rows based on the cell data" but i am not abble to understand how its work and apply to my sheet.

On theattached sheet i explain what im looking for.

Pleaseo play it and hope to help me and give a solution please.

Thanks in advance.

Jose

Hi all, long time reader, first time poster. I searched around and have found similar queries to what I require however I haven't been able to adapt these solutions with any success so here we go....

I have a list of businesses in a spreadsheet with information such as name, phone number, e-mail, website, postcode etc. I would like a search box on the side, say in cell O2 where a user can input part or all of a postcode e.g. (LS19 or LS) and those that do not begin with LS would be hidden. The postcodes are in column G.

I'm trying to make this as simple and user-friendly as possible so employees can find businesses in a certain area with ease. Thanks in advance for any help and please feel free to ask questions.

Chris Needham

Seems nobody had the answer so I found a work around myself. Just in case anybody else wants to do a similar thing. Using auto filter is the easiest way.

Code:

[SIZE="3"]Sub Search()

Columns("G:G").Select

Selection.AutoFilter

ActiveSheet.Range("$G$1:$G$999").AutoFilter Field:=1, Criteria1:="*" & Range("O2").text & "*", _

Operator:=xlAnd

End Sub[/SIZE]

Where G is the column to search for and O2 is the users input data.

Guess this can be closed now, thankyou all for your help.

Hey all

New here and new to VBA so I apologize if this is the incorrect place to ask this question or if I'm not including enough information.

Basically I have two forms, one form that is a cost est. form and another form is a proposal form (client sees this one). I have the proposal form pulling information over from the cost est. form. What I would like to do is on the proposal form if rows c13:c277 are blank (no data pulled from the cost est. sheet) they will hide. But when I add data (a number) into the cost est. form the proposal form pulls that and unhides that row.

I've tried all kinds of VBA code and none seem to do what I want. I can get the rows to hide but then not unhide. I don't have code to show because I have deleted it when it didn't work. Any help would be greatly appreciated.

Hello,

I am attempting to clear 2 cells, based off the value of another. I am pretty sure the code is correct, because it works within another Macro. Thanks.

Code:

Sub REMOVE()

Dim p As Long

p = Cells(Rows.Count, "a").End(xlUp).Row

For i = 1 To p

Range("k2").Select

If InStr(1, Range("k" & p), "None") > 0 Then Range("L" & p) = "" And Range("M" & p) = ""

'If no Issue, Location/Obsevations should be blank

Next i

End Sub

hi

try this variation;

Sub REMOVE()

Dim p As Long

p = Cells(Rows.Count, "a").End(xlUp).Row

For i = 1 To p

If InStr(1, Range("k" & i), "None") > 0 Then

Range("L" & i) = ""

Range("M" & i) = ""

End If

'If no Issue, Location/Obsevations should be blank

Next i

End Sub

I want to hide a few columns on a spreadsheet after I run a macro. I'm familiar with making sheets visible and hiding them with a macro, but not sure I know how with columns. Any help.

Set the width to 0? That will effectivly hide them.

Hi Again

CodeLexicon gave me a code that worked very well in hiding column A only. Is it possible to make the macro hide all columns with all zeros?? Also I tried to change CodeLexicon's macro to do hide all rows whose columns have zeros but without success. I tried to make the code generic.

Attached is a workbook with the 2 macros. Test the macro that I did out and see that it only hides row A only.

Mario

I need to delete multiple columns within an Excel worksheet based on the headers in the first row. What would be the best way to perform this operation?

I have been playing around with the code below, but I feel as though there could be a better way.

Sub test()

Dim myArr As Variant

myArr = Array("Test1", "Test2", "Test3")

If Range("C1").Value = myArr Then

Columns("C").Delete shift:=xlToLeft

ElseIf Range("D1").Value = myArr Then

Columns("D").Delete shift:=xlToLeft

End If

End Sub

Hello,

I cant seem work out a solution for what I'm trying to do. I have an Excel workbook that has multiple sheets. On sheet 1 i want the data from cell "G3" to be copied onto sheet 2. But i want the location on sheet 2 to be based on whatever was entered into cell "D3" on sheet 1.

For example: Sheet 1, cell D3 I have the name John, in cell G3 i have 68. I want "68" to be pasted in sheet 2 in cell B26.

But if the name in Sheet 1 cell D3 is Suzie, then I want G3 to be pasted in Sheet 2 in cell D26. So I would need to identify the paste location for each person.

I want the data to paste to the next cell so that the next entry can be pasted below the last entry for that person (for John the first entry would go into cell B26, then the next entry would go into cell B27 and so on).

But i want it to be a specific range, i dont want data to be pasted past 20 cells (cell B45). If possible a message box could be created to let the user know that the max is reached.

I would appreciate anyone's help with this as i have been struggling for awhile to try to get this. Thank you

I have a sheet set up with the list with the description (text) in column B, and summary scores (numerical, percentage) in column D. I want to do a summary row at the top of the sheet that pulls the data from the B cells, based on the lowest 3 values in column D.

I plan on using the formula =SMALL(D7:D32,1) (with d7:d32 being the list of percentages), to figure out the lowest 3 values. But the formula just pulls the summary score, not the description. I want to pull the description into but I am at a loss.

I am using excel 2013 on windows 10. Any help would be appreciated.

I have a sheet set up with the list with the description (text) in column B, and summary scores (numerical, percentage) in column D. I want to do a summary row at the top of the sheet that pulls the data from the B cells, based on the lowest 3 values in column D.

I plan on using the formula =SMALL(D7:D32,1) (with d7:d32 being the list of percentages), to figure out the lowest 3 values. But the formula just pulls the summary score, not the description. I want to pull the description into but I am at a loss.

I am using excel 2013 on windows 10. Any help would be appreciated.

i am using a userform with a listbox that a user chooses a value, that value is then placed in sheet 15 at cell 29, what i would like is if the value chosen is 6 then rows c6 - c25 in sheet 15 are unhidden otherwise they remain hidden, i have managed to do this before using checkboxes but not via a userform, any ideas for code please

You need to trigger a macro which can be called from the form.

When a value is chosen, the macro triggered will be something like

To hide:

Range(Cells(6,1),Cells(25,1)).entirerow.Hidden = true

To Unhide:

Range(Cells(6, 1), Cells(25, 1)).EntireRow.Hidden = False

Hey all:

I'm have really no experience with coding, so I'm out of my depth trying to do this myself. Hopefully someone can help, so here goes!

Essentially, my excel sheet keeps track of dates of when files need to be renewed. They need to be renewed every 60 days. So I have an "IF" formula that tells me if it's time for renewal.

What do I need to do to get an email notification whenever a file comes up for renewal? I've attached a sample sheet.

Best,

Nick

PS.

Using Excel/Outlook 2010

ClickYes Pro is installed (don't know if it will be necessary or not)

Does anyone know a way to manipulate the countif formula to count how many cells are "green" or "red" (with varying text in each cell), rather than the value of the cells?

Can this be done in VB?

I need to provide seqential row numbers from 1 to N that relate to cells in another column when the cell value is greater than zero. For instance, if the column with data is column d and I have data greater than zero in cells d3, d4, d5, and d7 I'd like the row count in column c to read number 1 for c3, 2 for c4, 3 for c5, and 4 four c7. Cells c1 through c3 as well as c6 should remain blank. What formula do I use?

I know that this is probably a pretty trivial problem, but I can't seem to come up with the answer. I am trying to set the selection focus to a cell in a column, based on an offset variable. The following is a little macro that I created to experiment with. As written, it positions the selection focus on cell C6. But I can't find the way to set it to C6 + Offset. The only reason for the MsgBox statement is to prove that I passed the argument as expected.

Sub MvTo(ByVal Offset As Long)

Range("C6").Select

MsgBox "Offset " & Offset

End Sub

I thought I could use RnCn cell references and write something like Range("R6[+Offset]C3").Select, but I get an error return, so I'm missing something somewhere. Run-time error '1004', Method 'Range' of object "_Global' failed.

I am using Excel 2000.

Lets say I had in cell $A$1 the number "36", in row2 figures for monthly spend, in row 3 cumulative spend and in row 4 the month number(1,2,3,4..). What I want to do is substract the monthly spend from $A$1 months ago from my cululative figure in row 3 so I have a rolling cummulative hardware spend value of 36 months in this case. So in this case if I was to do it manually in cell AK3 I would have "=AJ3+AK2-A2" and drag it further right. So I add my monthly figure of spend each month to the cummulative figure in row three but also deduct the monthly spend from 3 years prior.

So in order to use the figure in $A$1 I could do something in row 3 like: starting from B3: "=if(B4>$A$1,A3+B2-[$A$1rows_further_back_thanB2],A3+B2). So when I get to month 37 in row 4 I deduct the figure in row 2 (in the formula in row 3 - the cummulative row) from 36 months prior. I hope this all makes sense. Can you help me please (see attached spreadsheet).

I have a sales report that shows number of leads by sales person and date. It's organized into a monthly calendar view (with dates running down instead of across) and I've used formulas so that I only need to change the year to autopopulate the dates to fall within the corresponding month. What I want to do is only show the actual business days in each month - so auto hide any Sunday or Saturday rows as well as the blank rows (just the rows that have formulas in column A, not the blank spacer rows) so that when I update for each year I don't have to manually hide the rows with no data. Can I do this with VBA code? I'm not sure if I've explained myself well, so please let me know if this doesn't make sense! I've attached my spreadsheet.

For Windows XP, Excel 2010

Thanks!

I have an excel sheet with two tables for customers to know which products the customer is using based on a Contract table.

The enclose sheet has two tables:

1. Customer

2. Contracts

All what I need is to fill the columns of "Product A", "Product B" and "Product C" of every customer looking at the table "Contracts" where the data is available based on the "Customer ID"

How can I do it?

I would just use a simple countifs formula to check if the customer ID and Product type match (ie the count of matches is greater than zero)

See attached.

i have a spreadsheet that updates with data from a number of different sources. i need to filter only the relevant data that i need. i'm almost there but need to find out how to pull 2 more pieces.

in the attached spreadsheet the cells i need are in column R and T. I need to know the corresponding "name" and "size" of the data that is in column S.

unfortnuately the actual values don't show up in the spreadsheet b/c they are live data feeds but the formulas written up until now are still there.

in a nutshell what i'm doing is getting 5 price feeds and i need to filter out which is the highest updated within the last 2 minutes. i actually have the only the price figured out in column S, but i also now which name and how big the size is related to that price.

your help is very appreciated.

regards,

Good day everyone.

Basically I need to have a formula in excel that includes auto hide and unhide of rows based on the result value. Attached is my sample exercise for quick reference. In this exercise, I want to hide automatically the rows under "REPORT OUTPUT" that contains "0" ZERO value. Basic guide: Once you enter value (from 1 to 5) in cell D3, report output will automatically calculate...... The missing condition in the formula is to automatically hide ZERO value.... Please help.

Thanks in advance.

I need a code that will allow the workbook to be emailed when Column A is populated by certian numbers. The numbers in column A corespond to particular email addreses. This is the code I've been working but it isn't functional.

Sub Email_Out()

If Worksheets("Sheet1").Range("A5:A200") = "190030001" Then

ActiveWorkbook.SendMail Recipients:=("[email protected]")

ElseIf Worksheets("Sheet1").Range("A5:A200") = "190450025" Then

ActiveWorkbook.SendMail Recipients:=("[email protected]")

End If

End Sub

All help is greatly appreciated!

Mikey

Read other 16 answers

I am trying to hide rows of my worksheet that show a date in the completed column.

The orders that are complete have a date in column F up to this point I highlight them green so I know they are completed but it would be nice if they highlighted green and were hidden when a date was entered into the completed column.

Can someone assist me with creating a macro that will do this?

I am very new to macros so please forgive my lack of knowledge.

I have attached a sample of what I am doing however inf has been changed.

I am using Excel 2007

Thank you,

Hi, welcome to the forum,

I put some simple code in the Sheet's vba

Make sure you allow macro's to be run

Just enter a date in the last row of column F for testing.

For the other green rows, just click in column F and update the date by retyping it

I have multiple (80 or so) sheets were I what to hide certain columns say I:N on all sheets not just the active sheet with out a very length code! Can any1 help.

Sub hidealltest()

Dim ws As Worksheet

For Each ws In Sheets

Columns("I:N").Select

Selection.EntireColumn.Hidden = True

Next

End Sub

It only works on active sheet but highlights all columns to be hidden in sheet 2 and 3 but fails to hide them..

I posted in wrong place by mistake then it got moved after I made a new 1 sorry

Here is a sample of the table that I am working with:

Year Warehouse Item# Period1 Period2 Period3 Period4 etc....

There are many more columns but my issue is that if there is a 0 or blanks for all columns from period 1 to period 12 then don't show that row. How do I get this to happen with a query?

In the first Criteria row for each period column enter

>0

Is there an easy way to only count instances where two columns have two different values?

Example: I want to count instances where Column A = "Test" AND Column B = "Evaluate". If either column is not equal to that value, it doesn't get counted, even if the other column is correct.

I found this code to hide certain columns when any cell in Column C is double clicked.

Code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Target.Column = 3 Then

Range("D:D").EntireColumn.Hidden = Not Range("D:D").EntireColumn.Hidden

Range("E:E").EntireColumn.Hidden = Not Range("E:E").EntireColumn.Hidden

Range("S:S").EntireColumn.Hidden = Not Range("S:S").EntireColumn.Hidden

End If

End Sub

What I would like is for code to hide columns if any cell in columns between A:AA(27 columns) is double clicked.

Thanks

Pedro

You edit the If ....

In this case If column B through column Z

If Target.Column > 1 and Target.Column < 27 Then

Just play with the If staemenet.

Hello

I am running win 7 and and I've noticed that the folder columns change based on the content of the folders. For example, when I'm looking at a folder with documents in it, I have the following columns:

Name

Date Modified

Type

Size

When I go into a folder with mp3s:

Name

Track number

Title

Contributing artists

Album

For pictures:

Name

size

type

date

date modified

I'd like to have all folders have the same columns no matter what files are in it. I've tried using Tools->folder options->view-> apply to folders button, but it does not fix the issue. Any way to change it for all folders?

Thanks

Hello Bandit, and welcome to Seven Forums.

This can help show you how to set a default folder view to be used in all folders in Windows 7.

Folder View - Set a Default for All Folders

Hope this helps,

Shawn

Greetings,

I am using MS Excel 2007.

My situation is as this

I run a report every week for some items and their value, I compare 2 conditions. Condition 1 gives the whole list with Column A as the name of the item and Column B with value of the item.

Condition 2 is for the same items but the problem is that when I extract the data from SAP it excludes the item name that has 0 or less value, so i don't get the whole list of items.

And both conditions go in the same woorkbook and sheet when I run the report (I copy them into one workbook. The report is for comparing between condition 1 and condition 2.

The problem is that I have to go through both lists of items and manually add the items that are missing in condition 2 and add a value of zero next to them.

I need a macro that compares the conditions and not only add the missing item name but also add zero to the cell next to the item added ( to the right)

Sorry for bothering... I must have put this in the wrong place

I solved the problem... Needed an iPod with some of the best of Yanni and a couple of cigarettes to come up with a solution

I was thinking that the "IF" formula is the solution but what I needed actually was advanced filtering.

Item name on Column A and value in Column B

Sorted both Conditions by Column A

Condition 1 has the full list of items.. and Condition 2 had a smaller list but needed to have all items and the missing ones needed to have 0 value in B.

I had a new workbook and copied Columns A & B from Condition 2 into the new workbook. Then went to condition 1 and copied column A only under the cells copied from Condition 2.

Used advanced filtering on Column A to show unique records only.

The result is a list of items with their values in column B untouched and the ones that were missing in Condition 2 had blank value.

Now I can run a macro to automate this process...

Sorry for having this put in the wrong place, and it seemed that I was able to solve this on my own after all.

Well the bright side is that there is a one in a million chance that someone out there need the same thing that I need and actually see a solution

Hi All,

I have a requirement. I have multiple values in a single cell. They have to be splitted to adjacent cells(next columns). The problem is there are no standard delimiters,there are diffferent delimiters in the same cell.

Example:

Input:

ColumnA

Row1: {Product} (Accumulator) Section Benefit, Period has invalid value : 24 Months

Output:

ColumnA ColumnB ColumnC ColumnD

Row1: {Product} (Accumulator) Section Benefit Period has invalid value 24 Months

Like this there are some 40000 rows.

Note: You can use that "(Accumulator)" as a delimiter. It ll be present in all the rows.

Thanks in advance.

Can you post a workbook with a few rows of sample data?

Rollin

I am trying to write a macro that compares cells in column A from sheet1 to cells from column A in sheet2 and if the values from sheet2 are not in sheet1 then I want to delete the entire row from Column A in sheet2.

for example

In sheet1, column A has a list of id no's (no duplicates) and in sheet2 Column A has list of id no's (with duplicates). I want to delete every row in Sheet2 Column A that is not in Sheet1 Column A.

Keep in mind that both sheets have several columns.

Also there is about 5000 rows in sheet 2

Any help would be greatly appreciated.

Thanks

Welcome to the board.

While I understand each part of your post (I think), some parts seem to contradict others. So here's what I did.

In Sheet1!A1:A5 I entered 1 -- 2 -- 3 -- 4 -- 5.

In Sheet1!A1:A6 I entered 1 -- 2 -- 6 -- 3 -- 4 -- 5.

Then I wrote some code that removes the third row from Sheet2, since it contains 6 in column A which is not in col A of Sheet1.

How the code works is:

(i) inserts a new column A on Sheet2

(ii) enters a formula in new column A Sheet2, e.g.:

=MATCH(B1,Sheet1!A:A,0)

which returns a number if there's a match in column A Sheet1, #N/A if not

(iii) deletes any rows on Sheet2 with #N/A in column A

(iv) deletes column A on Sheet2 (redundant).

Obviously if this isn't what you need don't use it, post more info instead.

HTH

Sub test()

Sheets("Sheet2").Range("A1").EntireColumn.Insert

x = Rows.Count

y = Sheets("Sheet2").Range("B" & x).End(xlUp).Row

Sheets("Sheet2").Range("A1").Resize.FormulaR1C1 = "=MATCH(RC[1],Sheet1!C,0)"

Sheets("Sheet2").Range("A1").Resize.SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete

Sheets("Sheet2").Columns(1).Delete

End Sub

In cell j, I have formula =IF(SUMPRODUCT(ISNUMBER(SEARCH("VLXP",K2:AB2))+0)>=1,"Yes","No") that returns yes or no if VLXP is contained in any cell K2 through AB2 and it works correctly. What I would really like to do is then put into cell j the entire matching cell content or if not found return n/a. Is there a way to accomplish this maybe with VBA?

Read other 6 answers

I have a fairly simple task. From Row 17 to row 51 and Row 96 to 163 , I want to delete the row if cells P and Q are empty.

From row 57 to row 94 I want to delete the row if cells B, C, P, and Q are empty

I'm not sure what I'm doing wrong here.

Sub CleanUp()

Dim endrow As Long

Dim x As Integer

Set endrow = Sheets("Work Order").Range("A17").End(xlUp).Row

For x = endrow To 17 Step -1

If Sheets("Work Order").Range("P" & x) = "" And Sheets("Work Order").Range("Q" & x) = "" Then Sheets("Work Order").Rows(x).EntireRow.delete

End If

Next x

Rows("167:180").Select

Selection.delete shift:=x1Up

End Sub

Click to expand...

If you see something glaringly wrong or know a quicker way to do this, I would be very thankful.

Here's an example work sheet. If the code works right in the example, it would delete rows 4, 5, 9, and 10.

Code:

Dim endrow As Long

Dim x As Long

For x = endrow To 29 Step -1

If Sheets("Sheet1").Range("P" & x).Value = "" And Sheets("Sheet1").Range("Q" & x).Value = "" Then

Sheets("Sheet1").Rows(x).EntireRow.Delete

End If

Next x

Why won't it remove the rows?

I have a spread sheet where I would like a message to pop up after I enter a value in cell G2 based on what the value of I2 is. like the following description:

In cell I2 I have "=(E2*F2*G2)/144" after I enter a value in cell G2 I want a message to come up if the value of I2/g2 is less than three and not equal to zero" Is this possible? If so could someone help me out with this?

thanx

This is the same as the challenge file but I added the code for this question

I have a table that is set up and formatted. The cells are counted across. I would like to quickly automatically hide the rows that total to 0 - instead of highlighting each one or group of lines and hiding them.

Trying to print only the rows with totals in the easiest way possible.

Thanks - attaching the spreadsheet sans names so you can see what i'm talking about.

Carla

Intel Corporation - Graphics Adapter WDDM1.1, Graphics Adapter WDDM1.2 - Intel(R) HD Graphics

Download size: 59,5 MB

You may need to restart your computer for this update to take effect.

Update type: Optional

Intel Corporation Graphics Adapter WDDM1.1, Graphics Adapter WDDM1.2 software update released in January, 2015

More information:

Information about hardware device drivers for Windows XP

Help and Support:

Product Solution Centers

hi everyone will have their opinion on this but i usually hide graphic updates cause it caused me problems before.

Read other 4 answersI want to hide the grid lines in an excel spreadsheet. I went to page setup, sheet, and the print gridline box is not checked, but when I print worksheet, the grid lines show. How do I get hide them?

Hello everyone.

I am trying to tweak a macro that was posted on this forum some time back, but I'm a bit of a N00B. The macro is from this thread that is now locked: https://forums.techguy.org/threads/...ue-date-reminder-based-on-excel-file.1129238/. What I am looking to do is generate two different emails whenever the value of a cell changes. All the data that needs to be part of the email is included in various cells. I've included sample data. If possible, could you provide code for generating the email and another for sending the email without opening Outlook (i.e., as soon as the value changes)? I've included sample data for your consideration. Thanks in advance for any help you can provide.

When Cell Value = In Progress

The email I'm hoping to generate (I've mapped it with the cell contents I'm hoping to pull) will be as follows when Column F changes to In Progress. The email addresses are in Column G.

Subject Line: "Web Request(s) Now In Progress"

Dear D2,

Your C2 B2 request has been received and is now in progress. Thank you!

My Signature

When Cell Value = Completed

The email I'm hoping to generate (I've mapped it with the cell contents I'm hoping to pull) will be as follows when Column F changes to Completed. The email addresses are in Column G.

Subject Line: "Web Request(s) Completed"

Dear D2,

Your C2 B2 request is complete and the changes are now live. Thank you!

My Signature

Hi All,

So I'm fairly competent in Excel in most things other than macros (which means it could be argued I know nothing about Excel...)

I'm looking for a way to automatically hide rows from a page. Essentially it's a progression tracking sheet that I've got, and I want things to stay on the sheet for only 5 days after the job is complete. I have a TODAY() function which I am using to determine whether or not it has been 5 days since completion.

Any advice would be more than appreciated and I thank you all in advance.

Tom

Bumping due to inactivity. Not sure if it's allowed but hey, I still haven't got an answer...

hi, i have 2-excel cells in the same sheet, both contain manually entered numbers; cell-2 changes frequently; if the existing entry in cell-1 is < than the new entry in cell-2, cell-1 should immediately reflect this new value. how do you create this formula?

i have 2 workbooks entitled [price] and [fruit] respectively

[price], sheet1!, cell A1 is a variable which can contains a cell address, eg $D$1

[fruit], sheet1!, contains information in cells as follows:

D1 = apples

D2 = bananas

D3 = cherries

in the [price] workbook, i want to have a formula in say, cell B1, which would use the cell reference in A1 to find the value in the [fruit] workbook.

so, if [price]sheet1!A1 = $D$1

then [price]sheet1!B1 = apples

note that the result "apples" was pulled from another workbook, [fruit]sheet1!$D$1.

if [price]sheet1!A1 = $D$2,

then B2 = bananas

and so on...

in the [price] workbook,

As A1 is a variable, i do not want to physically retype the value within A1, but rather draw reference to it inside of a formula. i hope this is clear as mud.

Is this possible?

Thanks,

markus

In Sheet1!B1 (of Price), use

=INDIRECT("[Fruit.xls]Sheet1!"&A1)

, where A1 contains the cell reference -- $D$1, $D$2, $D$whatever.

As explained yesterday, this (the INDIRECT function) will only work when both files are open ; to get it to work when the source file if closed, you need to download Laurent's add-in. Please do not start threads over.

Rgds,

Andy

What is the best way to change cell colors based on a cell color (Not a number)?

I have a condition where a cell may have green background (conditional formatting), and on the next update, a different cell may have the green background (same condition, but not the same cell). I would like several cells in that column to change to the same color when this happens, they will be different rows, but will be on the same row as the change occurs ... it is dynamic.

*********

Condition 1:

G8 background color is = green

Change adjoining cells to the right and down to the same color

(H8:K8 and G9:K12) The inclusive range would be (G8:K12)

********

Condition 2 on update:

G8 is back to normal but H8 is now green

Cells in column G are back to normal but adjoing cells to the right and down should now be green.

(I8:K8 and H9:K12) The inclusive range would be H8:K12)

*******

Here is an example of the conditions above.

Condition 1

Row G H I J K

8 20 22 33 32 35

9

10 24% 15% 22% 21% 25%

11 80 82 90 88 89

12 44% 45% 48% 47% 50%

Condition 2

Row

8 22 33 32 35 38

9

10 15% 22% 21% 25% 27%

11 82 90 88 89 90

12 45% 48% 47% 50% 52%

The cells I want to highlight will not be of the same value and there are formulae in every cell.

Thanks

I haven't got the hang of copying the cells from Excel to the post. sorry about the formatting.

In (nearly) simplest terms, and assuming from what you say that there'll NEVER be a time when G8 AND H8 are BOTH green, then

If Range("G8").Interior.ColorIndex = 10 Then

Range("G8:K12").Interior.ColorIndex = 10

ElseIf Range("H8").Interior.ColorIndex = 10 Then

Range("H8:K12").Interior.ColorIndex = 10

End If

You'll have to get back to us on what sheet event might fire this. & let us know if the actual cond formatting (a) blocks the code and/or (b) remains intact.

Rgds,

Andy

Dear Fellows

I am looking for help for a macro which can copy data to other sheet based on adjacent cell value which i define.

For example i have data attached in a sheet.

i want to copy data based on value present in E column to different sheets. if it is ES then all ES rows should be copied to sheet ES with roll number name father per and dept.

Same is for other departments, on different sheets. There will be 9 departments overall.

Looking for response

I have an Excel workbook with 400k rows Each set of rows contains a table name with the fields in the table. The number of fields is not the same for each table. For example:

COLUMN A COLUMNB

AV_ACTN_RSLT_VWACTN_RSLT_CDAV_ACTN_RSLT_VWDESCRAV_ACTN_RSLT_VWDESCRSHORTAV_ACTN_RSP_VWEMPLIDAV_ACTN_RSP_VWEXT_ORG_IDAV_ACTN_RSP_VWINSTITUTIONAV_ACTN_RSP_VWNAMEAV_ACTN_RSP_VWRSPL_ID

AMT_X_SAL_TYPEWCS_PLAN_DESCR20AMT_X_SAL_TYPEWCS_TOT_AMOUNT

I need to convert the field names so that they are in a row like this:

AMT_X_SAL_TYPEWCS_PLAN_DESCR20WCS_TOT_AMOUNT

Is there any way for me to do this programatically? I'm using the transpose feature but it is time consuming and I have 400k rows. So basically, I want something like if the field in column A row 1 matches row 2, move that field to the right of row 1 and so on until the table name changes.

Or maybe i should dump the spreadsheet in access and try there...

Thanks,

Amy