Over 1 million tech questions and answers.

Solved: countif based on cell format, not value?

Q: Solved: countif based on cell format, not value?

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?

RELEVANCY SCORE 200
Preferred Solution: Solved: countif based on cell format, not value?

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.)

A: Solved: countif based on cell format, not value?

Read other 10 answers
RELEVANCY SCORE 76.8

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


 

A:Solved: Clearing Cell Contents Based off other Cell

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
 

Read other 2 answers
RELEVANCY SCORE 68.8

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.
 

A:countif based on values of 2 columns

Read other 12 answers
RELEVANCY SCORE 68.4

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.

Read other answers
RELEVANCY SCORE 68.4

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.

Read other answers
RELEVANCY SCORE 68.4

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
 

A:Excel - Copy paste cell into range based on another cell

Read other 12 answers
RELEVANCY SCORE 68

I have conditional format in row 5, as follows
Condition 1
=(F5="") and dragged across
Format is for No colour
Condition 2
=ISNUMBER(FIND("G",numForm)) and dragged across
Format is for Light green if condition met

Cell Format is set at General

I have two problems
1. Cell F5 produces 4 decimal places
2. If I change Cell format to Number , with 2 decimal places Conditional format of light green does not occur.

Is there a way to have a number cell format (custom or otherwise)to two decimal places and a conditional format that leaves the light green if a number is in F5. If no number cell to remain clear.

Any suggestions appreciated.
Pedro
 

A:Solved: A Conditional Format and cell format proble,

I think you should verify that the format in the conditional format is a number too
Check if there are no quotes arounf that conditional format conditions, this happens quite often
 

Read other 2 answers
RELEVANCY SCORE 67.6

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
 

A:Solved: Hide Columns based on value of cell

Read other 9 answers
RELEVANCY SCORE 67.6

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)
 

A:Solved: Emailing from Excel Based off Cell Value

Read other 16 answers
RELEVANCY SCORE 66.8

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?
 

A:Solved: sequential row numbering based on value in another column/cell

Read other 7 answers
RELEVANCY SCORE 66.8

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.
 

A:Solved: Set cell selection based on a variable offset?

Read other 6 answers
RELEVANCY SCORE 66.8

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).
 

Read other answers
RELEVANCY SCORE 66

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?
 

A:Solved: MS Excel / Filling a cell with YES based on data from another sheet

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.
 

Read other 3 answers
RELEVANCY SCORE 66

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,
 

A:Solved: excel - data returned based on the value of a specific cell

Read other 10 answers
RELEVANCY SCORE 65.2

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
 

A:Solved: VBA email excel workbook based on cell values using; If Then ElseIf Please he

Read other 16 answers
RELEVANCY SCORE 63.6

I have a sheet with 2 simple columns: Date and Price. I have imported the dates (##/##/####) and the prices ($###,###) by copy/pasting from the search results given to me by a niche database program I use. When the cells paste in, they all have the format "General".

When I try to format the "date" column into dates, it _does_ change the format as far as the cell is concerned, but the content of the cell doesn't adapt to the new format. For example, I have the date as 3/05/2001 and when I change it to a date format of MMM D, YYYY the content should change to March 5, 2001 but it doesn't. It is as if all the cells are forced to stay as text regardless of what the formatting is that I'm applying.

Same problem with the price column: if I change the format to include 2 decimal points, that format does apply to the cells, but the content of each cell remains without a decimal or anything following, as if the content is just text.

I have like 1000 rows in each column, and plan to do this analysis of the database's results frequently, so I'm hoping the answer isn't just to retype the data. There's got to be a way to copy/paste or export or something. Maybe I could copy/paste into notepad first to scrub out any formatting or locking from the niche database program?
 

A:Excel 2007 Cell Values Won't Take On Characteristics of Newly Applied Cell Format

Good news: Made some progress. In thinking that maybe each value had the textual single-quote forcing it to act like text, or maybe if I find/repaced all the dollar signs and commas that had been imported, I accidentally discovered that each and every value in my imported columns has a following space!

Bad news: Seems like Excel has a bug that thinks that if I say "Find=[singleSpace]" "Replace=[null]", then I should be given an error saying "Excel cannot find any data to replace". I think I'm doing the find/replace correctly because it worked on the dollar signs and commas.

Anybody know a workaround for the bug?
 

Read other 1 answers
RELEVANCY SCORE 62.4

I have a spreadsheet that has about 10,000 cells over 20 or so columns and depending on the result value in each cell they are coloured differently.

Is there any way to write a sum formula that will scan the data area and sum up each set of coloured cells?

The results to colour of cell are not banded, the colouration depends on the source of the original data and value.

When the source data is refreshed, it is a manual process to change the colours of the cells.

Thanks in advance for any help you can offer.

Regards

Ben
 

A:Solved: Excel sum by cell format

Here is one way to do it:
http://www.exceltip.com/st/Sum_by_color_using_VBA_in_Microsoft_Excel/517.html
here is a slightly different way:
http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm
 

Read other 3 answers
RELEVANCY SCORE 62.4

Hi all,
Currently I have a set of codes working in such a way that it copies one of the values (Value A) from another worksheet.
(From sheet A to sheet B).
On Sheet B though, I need another line which cites Value A. I got this part working, but the formats are wrong (eg suffix, separator, decimal points). This is difficult to explain, so I'll show what I have.
Code:
ws2.Activate
qprice = Cells(qlastrow, qpricecolumn)
Cells(qlastrow + 2, 2).Value = "Final Price = " & qprice
Cells(qlastrow, qpricecolumn) is the value that I need to cite.
The number format is "$" #,##0.00

For example
Cells(qlastrow+2,2) should display: Final Price = $1,000.00

What my code does now is only: Final Price = 1000

p/s: The format of the number in cells (qlastrow, qpricecolumn) is already correct. I'm using Excel 2010.

I hope someone can shed some light on me. THANKS!
 

A:Solved: Citing cell value with format

You're putting text into the cell you have formatted to a number and it cant do it.
Try something like this ="Final Price: " & DOLLAR(qprice)
 

Read other 3 answers
RELEVANCY SCORE 62.4

A little while ago a created a page in Excel for Mac 2011 (Version 14.1.20) where in one column if a value of a cell was less than 1000 the background of the cell was one colour and if the value was 1500, the background was a different colour to the previous value.

I can't for the life of me remember how I set these values.

Help please
 

A:Solved: Cell format problem

Try Conditional formatting

Pedro
 

Read other 2 answers
RELEVANCY SCORE 61.2

Hi
Is there a way to have conditional format change the alignment of a cell based on a condition

I see that I can change font color, fill, style, etc. but is there any way to change the alignment ???

I want to have any value greater than 0 align to the right in the cell.

If not, is there a way to have a macro look in a certain row (example: N6:N400) and do it when i open the worksheet

Mike
 

A:Solved: change cell alignment with conditional format

AFAIK you can't do it with conditional formatting
Attached is macro example
 

Read other 3 answers
RELEVANCY SCORE 59.6

Hi!

I need a cell to accept only data that meet the following criteria:
1. the first 9 characters should be composed of numbers only
2. the 10th character should either be a letter or the number zero (0).

If they don't meet both of the requirements, I need for an alert box to pop up informing them of the requirement they failed to meet.

How do I do this?

Is it possible to do this purely with an in-cell formula and formatting/data validation?
 

A:Solved: How do I format a cell to accept only data that meet specific requirements?

Read other 11 answers
RELEVANCY SCORE 59.2

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?
 

A:Solved: Excel if cell contains vlxp then put matching cell data in current cell

Read other 6 answers
RELEVANCY SCORE 58.4

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
 

A:validation based on another cell

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

Read other 1 answers
RELEVANCY SCORE 58.4

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.
 

A:VBA Deleting row based on cell value

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?
 

Read other 2 answers
RELEVANCY SCORE 57.6

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
 

Read other answers
RELEVANCY SCORE 57.6

Hello,

I'm trying to enter a formula that returns a value when a loan balance is paid off. I've attached a spreadsheet that shows the costs and revenues of a project and the corresponding loan balance. It's very basic, but I'm trying to see if there is any formula that will return the Month that corresponds to the loan being paid off. I've been trying to use an "if" formula and when the loan balance is <0, but i don't know how to do it with just one cell. Let me know if anyone has ever done anything like this.

Thanks,
Chet
 

Read other answers
RELEVANCY SCORE 57.6

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?
 

A:Solved: excel-replace content of cell-1 if cell-2 is > cell-1

Read other 8 answers
RELEVANCY SCORE 57.2

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
 

A:Q: draw value from a 2nd workbook, based on a variable cell ref in 1st wb

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
 

Read other 1 answers
RELEVANCY SCORE 57.2

Hello,

I am using Excel 2010 and trying to resolve an if statement... in the file attached I require the following:

I need 1 of 3 results for cell G2 based on the first 3 characters of cell B2

M15### would equal PAB
M22### would equal NSY
Non# would equal None

Thanks
 

A:Multiple IF Statement based on first 3 characters of cell

This was my attempt but not working:

=IF(LEFT(B2,3)="M15","PAB",if(LEFT(B2,3)="M22","NSY",if(B2,3)="Non","None")))
 

Read other 2 answers
RELEVANCY SCORE 57.2

Is there a way to insert a new worksheet based on whether or not a cell contains data and name that worksheet with the contents of the cell.
Ex: A1= Data
worksheet would be added and named Data
 

A:Insert Worksheet based on cell content

Read other 9 answers
RELEVANCY SCORE 57.2

Hi all,

I have a workbook containing 30 worksheets. The first sheet is named Master, while the other 29 worksheets are named with cities where we held branch offices (Los Angeles, New York, Chicago, Washington D.C., etc.)

I am trying to write a macro that will copy the contents of a row in the Master sheet to a particular worksheet based on the contents of the cell in that row that contain the location.

To illustrate:
Master sheet:
Columns are: Staff Name, Location, Project status

We have over 5000 staff.
I am trying to write a macro that will copy each staff's record to the worksheet corresponding to his/her location, depending on their project status.

So I am planning to use macro buttons labelled A, B or C (these are the values in the Project status column). So when I click on the button labelled A, the macro will go through all 5000 employees, and for those employees whole project status is "A", will copy that employee's record into a worksheet depending on his/her location.

For example:
Staff name, Location, Project Status
Joe Doe, Los Angeles, A
Jane Davis, New York, A
Mike Smith, Los Angeles, C

When I click on the macro for "A", it should copy Joe Doe's record into the worksheet labelled Los Angeles and copy Jane Davis' record into the worksheet labelled New York.

Thank you so much for your help. I've been searching all over the place for the command to use to reference the worksheet.
 

A:Excel - how to reference a worksheet based on value of a cell

For the love of PacMan, make sure you save your file BEFORE you try this. Try it on a copy of the file, even.

HTH,
Andy

Sub A_Status()
'select the list of staff names on Master sheet, then run
For Each Cell In Selection
If Cell.Offset(0, 2).Value = "A" Then
DestinationSheet = Cell.Offset(0, 1)
Cell.Resize(1, 3).Copy
Sheets(DestinationSheet).Select
'assumes each location sheet has a header row
Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Master").Select
End If
Next Cell
Application.CutCopyMode = xlCopy
End Sub
 

Read other 2 answers
RELEVANCY SCORE 57.2

Hi,

New here. I dug up a thread that Zack Barresse solved many years ago. I am looking to do the exact same thing. The link to the thread is below. My file is infinitely more complicated than what that user was asking for so I need a bit more help tuning the VBA. Link: http://forums.techguy.org/business-applications/710581-automatic-email-alerts-using-excel.html

Some specifics:

- I am using Outlook not Express
- Excel 2007
- All the functionality is complete for monitoring several live streams of securities data with several trade indicators.
- It is consolidated onto one sheet for manual monitoring (Picture below). Basically takes copious amounts of data and reduces it to just IF and AND functionality for the triggers for easy use from all the other sheets.
- The workbook will be open and running/refreshing on its own 24/7 as it is now.

I am a busy guy, I just need the VBA to automatically email me remotely when any of the 7 currency pairs causes a trigger when I am on the go. I can log trades from an app on my phone.

One other hurdle would be that if say (Using percentages to keep it simple) that a trigger would be if something reached as high as 80% to send the notification email. But where the system refreshes every 60 seconds it shouldn't send another notification each time it remains at or above 80%. Just the once. It may remain there for hours and that is a lot of emails.


Thoughts? and many many thanks in advance.
 

A:Excel - Auto Email based on cell value

Read other 10 answers
RELEVANCY SCORE 57.2

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
 

A:Move data from column to row based on value of cell?

Read other 6 answers
RELEVANCY SCORE 57.2

You must be bored with this question cos I've seen lots of them, however I can't seem to fathom out what to do..........

I've got a block of data within a spreadsheet 4 rows always in the same place.

How do I put into code:-

If column d43:e46 is blank delete rows 43 - 46 if not ignore it.

As I said I've tried a few things and it will delete the four rows regardless of them being empty of not.

Can someone help please.

Many thanks,

Moll
 

A:Deleting rows based on empty cell.

Is there anybody there? - Help!
 

Read other 2 answers
RELEVANCY SCORE 57.2

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.
 

A:Change Range Cell Color Based on Another

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
 

Read other 3 answers
RELEVANCY SCORE 57.2

Sounds like a simple thing but I’m having troubles finding an easy answer. Still searching ….

I want to create a catalogue for a customer of mine (basic – but might be more extensive later)

– showing a thumbnail picture and then a short product description. Then he can go into the doc and correct the descriptions etc.

Not sure if excel or word or other is the best place.

He has over 200 product pictures stored on disks and folders on hard drive. I have corresponding description in excel based on a code that is the photo name (+.jpg).

So, I have the product files name as HB1000.jpg and all I want to do is show the picture (say at size 100x100)

Ideally I’d like to build a reference based on the code and not have to insert each file.

Thoughts??

So far – tried creating a formula/reference in excel with “Code” HB1000 (Cell A2) and image cell contains “=c:\productimage\”$A2$”.jpg” - can not get the pic to display
 

A:How to display images in Excel based on cell ref

The answer is already on here, I posted it fairly recently, it requires VBA to do it well.
see this thread
http://forums.techguy.org/business-applications/723855-solved-insert-image-based-cell.html
 

Read other 2 answers
RELEVANCY SCORE 57.2

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.
 

A:How to hide rows in excell based on cell value

Read other 8 answers
RELEVANCY SCORE 57.2

I have a worksheet that contains a budget, some of the line items are identified as allowances in column C, by the "allow" designation. The description of the allowance is in column B.

I want to create an automatic table on a worksheet called "Allowances" which searches column C for "allow", and if found returns the description from column B in cell A and the amount of the allowance from column F, in cell B.

Any suggestions?

Thanks,
 

Read other answers
RELEVANCY SCORE 57.2

 The laptop immediately shuts off when I pull out the power connector.  I've tried the troubleshooting steps listed on HPs website with no change. Thanks.

Read other answers
RELEVANCY SCORE 57.2

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
 

Read other answers
RELEVANCY SCORE 57.2

I have a sheet with several hundred rows. Starting in Row 4 through the end of the sheet are mixed dates in column 1 and ALL rows from Row 4 through the end of the sheet hidden. I want to be able to type in a date in Cell A1 and have all rows that have that date in Column 1, Row 4 through the end of the sheet to become unhidden. Secondly, I would like to type some other code like "999" or something in Cell A1 to have ALL rows become unhidden. Thirdly, I would like to either type another code or just delete anything in Cell A1 and have ALL rows from Row 4 through the end of the sheet to become hidden again.
Tech Support Guy System Info Utility version 1.0.0.4
OS Version: Microsoft Windows 7 Professional, Service Pack 1, 64 bit
Processor: Intel(R) Core(TM)2 Duo CPU T6600 @ 2.20GHz, Intel64 Family 6 Model 23 Stepping 10
Processor Count: 2
RAM: 3963 Mb
Graphics Card: Mobile Intel(R) 4 Series Express Chipset Family, 1853 Mb
Hard Drives: C: 453 GB (298 GB Free); F: 931 GB (710 GB Free);
Motherboard: TOSHIBA, Portable PC
Antivirus: Webroot SecureAnywhere, Enabled and Updated
 

Read other answers
RELEVANCY SCORE 56.4

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.
 

Read other answers
RELEVANCY SCORE 56.4

Hi All,

My name is Diego.

Can anyone send me code to automatically send me an email when the date listed in "column J" is the same date as today. Also, it needs to email only once and even if I am not running excel or at my computer. I want to use Microsoft Outlook and use the ClickYes program as well if this helps that was talked about by Zack Barresse in

http://forums.techguy.org/business-a...s-using-2.html
Essentially I have to be reminded of a reapplication for specific state licensures on healthcare courses I provide. I don't want to forget which courses I have to reapply for so I need to have a program that will look at a date which I have in column J and then email me to remind me of this.

BTW - I am using Outlook 2007 and Excel 2007 on Vista.

Thanks. I appreciate your help! Also, extra points and praise for the person who solves this problem!
 

A:Automatic Email from Excel based on Date in Cell

Read other 16 answers
RELEVANCY SCORE 56.4

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
 

A:Excel VBA : Hide rows based on cell input.

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.
 

Read other 1 answers
RELEVANCY SCORE 56.4

Hey all,

I'm new and have no experience with Excel vba coding. Hopefully someone here can help me out.

My excel sheet keeps a list of Email addresses on column B (with duplicate email addresses), and their particulars from column C (Item price, purchase date, etc) onwards.

I need the vba to email multiple recipients (those with the "notification" field marked as yes) with their purchasing details in it. It should also prevent multiple emails to the same email address.

Thanks in advance,
Leon

PS.
Using Excel/Outlook 2010
 

A:Emailing multiple recipients from Excel Based off Cell Value

Read other 9 answers
RELEVANCY SCORE 56.4

Hello,

I have seen posts similar to my requests but have been unable to put together a code to do everything I need it to do as I am very new to VBA coding.

I am looking for a VBA code that will generate an email when the date in a cell of a column is 7 days or less from the current date. The email will be sent to a recipient listed in another cell column and cc my coworker. I also need a code to change the data in another cell to "Approve, Email Sent" when the same cell date is between 7 and 1 days from the current date and "Expired, Email Sent" when the date equals the current date so that an email will only send if this cell does not equal "Approved, Email Sent" or "Expired, Email Sent" so that emails are not repeatedly sent every time the spreadsheet is opened. I plan on running task scheduler to open the spreadsheet every Monday so the code will run weekly at minimum.

Any help is greatly appreciated, thank you!
 

Read other answers