Over 1 million tech questions and answers.

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

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

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?

RELEVANCY SCORE 200
Preferred Solution: Solved: MS Excel / Filling a cell with YES based on data from another sheet

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: 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 127.6

Hi folks, not sure if this is possible or not but I'll try to explain what I'd like to do.

On the first sheet, I have a simple sheet to be filled in daily. The date is changed, and then daily pick figures and hours picked are updated by the relevant department.

I then have to manually put all of the data into the second sheet, which acts as a log of previous pick figures.

Is there a way the second sheet can automatically be filled in when the date is changed and new figures are put into sheet 1?

I hope I've explained this well enough, I'm trying to teach myself excel as I'm going along! Thanks.

Edit - I've had to delete peoples names' for obvious reasons, initials in sheet 2 will correspond to people in sheet 1.
 

Read other answers
RELEVANCY SCORE 105.6

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 104.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 104.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 100.8

Hi Everybody

When I import data (numbers) from a web site to excel, I get the + sign in the upper left corner of each cell.
How do I remove these + signs ??

Thank You

Read other answers
RELEVANCY SCORE 99.6

Hi All,

Firstly, thanks to everyone reading, and greater thanks to those replying!

Here is my best attempt at explaining my problem...

In Sheet 1 I have data of "invoice number, "name", "invoice date","status", "amount", and some other headings. Everything is straight forward, except "status" will be either "outstanding", "paid" or "donation".

The headings are on the top horizontally, and the data is all vertical from that.

On Sheet 2, I need to return all the rows (the entire row's data), but only for those for which the status is "paid", and then on sheet 3 I need to return all the rows (the entire row) for those in which the status is "outstanding", and then the same applies for "donation" on sheet 4.

Currently I am using this very simple formula, =IF(Invoices!D3="outstanding",Invoices!A3,"") and then =IF(Invoices!D3="outstanding",Invoices!B3,"") in the next field, and the formulas go on like that en every cell to the right for each column on sheet1.

I have been using this just to get by, but as you can well imagine it is not very elegant, and since the rows on sheet 1 keep on being added downwards, I keep on having to copy my formulas down on sheets 2, 3 and 4, or I have to copy them down to like row 2000 just to keep ahead.

Is there a more elligant way of doing this - I have looked at vlookup, but unless ... Read more

A:Excel - Return Data on seperate sheets based on value in first sheet

Read other 6 answers
RELEVANCY SCORE 95.6

I would like to know a method to solve an otherwise repetative task in Excel for me.

In my Excel spreadsheet, I have many columns relating to customer orders etc. Each customer (finite number (~40)) has its name (customer1, and customer2 etc) input to a cell, I then must fill out the customer account number (ac001, ac002 etc). The customer name and number already exist on another sheet.

How can I automate the process of when I input the customer name, excel then fills in the customer number in the next cell?

Many thanks
Dave
 

A:Excel: Conditional Cell Input Based on Entered Data

Depending on how your Customer data is arranged, look at either Dlookup or Vlookup.
But Access is better for Customer databases.
 

Read other 3 answers
RELEVANCY SCORE 93.6

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 87.2

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 86

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 84.4

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 82.4

I have multiple sheets in my workbook and I need to refer to certain cells and cell ranges in other them. I’m having trouble trying to create a formula that would allow me to look up/do the following:

For example:

If cell A1 in current sheet ‘Results’ = cell B1 in another sheet ‘Data’ then
include (these are ID reference numbers and I need the IDs in Results column A to match the IDs in Data column B)
AND
If the information in Data X1:X100 equals 1, 2, 3 or 11, then return “Blue”
in Results column C (corresponding to the right ID number) ;
If the information in Data X1:X100 in equals 4, 5, 6, 7 or 12, then return
“Red” in Results column C (corresponding to the right ID number) ;
If the information in Data X1:X100 in the other tab equals, 8, 9, 10, or 13,
then return “Green” in Results column C (corresponding to the right ID number)

E.g.

Results Sheet
A - ID B - Date C - Colour
1440 5th March
1677 8th May
1589 1st April

Data Sheet
B - ID X – Colour Code
1440 3
1667 7
1589 13

What I need to appear in Results Column C would be “Blue” (as the code is 3) for any reference to ID 1440, “Red” for any reference to ID 1667 (as the code is 7) and “Green” for any reference to ID 1589 (as the code is 13).

I’m not very confident in Excel (I have Excel 2007) and any help
would be much appreciated! I hope what I am looking for is reasonably clear.
 

A:Solved: Excel - looking up ref codes in another sheet & returning data

Read other 11 answers
RELEVANCY SCORE 82.4

Hi,

I have an application called BT Billing Analyst. This application has an option to open a report in Excel.

When I used Excel 2007 the report data opened in sheet 1 of an Excel workbook and some information on the report was put on sheet 2.

I recently upgraded to Excel 2013 and now when I open the report in Excel the report data is populated on sheet one but after that the first 25 lines get overwritten by the information that should go to sheet 2.

Is there any way I can fix this?

Thanks
Paul
 

A:Excel - Sheet 2 data getting written to sheet 1

You may have to go to the BT Billing vendor to resolve this. Perhaps an updated version is available. 2007 is xml based like 2013 so I am surprised there is a conflict. But I don't think there is anything in Excel you can do to resolve.
 

Read other 2 answers
RELEVANCY SCORE 82

I am trying to write a macro that looks at each row in sheet A, finds the last cell in each row, does a compare to the cell to the left in the same row on the same sheet and based on the result enters a value in the same cell address in sheet B.

I have the majority of it written, but can not seem to get the part where the active cell from sheet a becomes the active cell from sheet B.

Thanks
Lori
 

A:EXCEL VBA -same cell diff sheet

OK, for a workbook with 2 sheets:

Sheet1 has values 1, 2, 3, 4 in A11, and values 5, 6, 7 in A2:C2.

If you run the line below with Sheet1!A1 selected, it'll put 1 in Sheet2!D1.

If you run it with Sheet1!A2 selected, it'll put 5 in Sheet2!C2.

Ideally you should upload a workbook containing some sample data & the code you have already, so that we're clear on what you're aiming for.

Sheets("Sheet2").Range(Cells(ActiveCell.Row, 256).End(xlToLeft).Address) = ActiveCell
EDIT: forgot to say welcome to TSG.
 

Read other 2 answers
RELEVANCY SCORE 82

Dumb Excel question:

How do I link to another cell on a different sheet of the workbook?
 

A:Excel: link to cell on different sheet

no question is a dumb question, you just select the cell where you want your information displayed, input '=' and go to the other sheet or workbook and select the cell you wish to refer to. good luck!
 

Read other 3 answers
RELEVANCY SCORE 80.8

Hi All,This is probably pretty easy but I cant seem to find what Im looking for on any other forums. I have a master sheet that I am keeping a list of names, personal information and a seperate colum for each skill set the individaul can perform. Basically what I want, is if there is a Yes in a particular colum for a specific skill set, it will copy all of their information onto a seperate sheet. If the cell is blank then no action needed. The other sheet will then create a new list of all the individuals with the same skill set but keep the master sheet as is. I also need this rule to work for each colum, and each colum to correspond to its own sheet. This means that for individuals with multiple skill sets, they will be listed on each sheet.Not sure if this makes any difference (and Im assuming it doesnt, but I am using a mac with office 2008)Hope that makes sense and that someone can help.Thanks in advance for your input.
 

A:Copy a row if data in cell matches and paste to new sheet

Taka a lokk at this post
http://forums.techguy.org/business-applications/959076-find-copy-paste-macro-excel.html
The macro will need to be editted but it does more or less anwser your needs
 

Read other 1 answers
RELEVANCY SCORE 80.8

Hi, Im looking for some code examples so all users can all update an excel spreadsheet simultaneously (make changes/edit etc). Im having a little bit of difficulty finding documentation on this (how to's).

1. Each User would have their own copy of the original document
2. When a user makes a change, the form will update the master document in another directory.
3. This is to alleviate the problem of having multiple users only having access to the document, one user at a time.

Thanks for any good links/tips etc..

-t
 

A:Solved: need to multithread excel sheet so users can update/add data simultaneously

Hi tjamnz,

Why not use a shared memory approach with one instance of the excel spreadsheet, and locks for each of the fields in the spreadsheet? Treat the spreadsheet essentially as a matrix data structure and lock each cell or (row, column) pair such that each user upon opening the excel spreadsheet accesses it with a different thread which is created when each user "opens" the spreadsheet - i.e. allow multiple reads, single synchronized writes using locks which each user's thread must acquire to update the cell (row, column) and release for another user to be able to refresh that cell with the latest update to it.

Give it more thought along those lines. Perhaps there is a simpler way to express this.

-- Tom
 

Read other 2 answers
RELEVANCY SCORE 80.4

I get sent a spreadsheet, which has loads of data in, which is used, but i want to add a new sheet into the workbook called say "ratio", based on a sheet I can create "sales "and uses one of the existing sheets "customers" sent to me. with the minimum work

So i have attached an example of the one sheet I get sent (remember this is in a workbook with lots of other sheets and data - which needs to be kept) "customer"

I then have a sheet of sales with a similar layout

column A will have some of the same names in my sales sheet
so I update my sales into a "sales" sheet for the latest week, and copy the sheet into the workbook i have been sent

i now want a new sheet created which will
look at the names in columnA of "sales" and where they match the name in columnA of "customers " then on a new sheet called "ratio"do a calculation for the rows that have the same dates in

customer/Sales

I need to update and repeat this process every week.

Any help please
 

A:excel creating a new sheet based on results from two sheets

Read other 12 answers
RELEVANCY SCORE 80

I'd like the macro to perform an automatic transfer of multiple cell data to a different multiple cells in a different sheet as new info is entered daily. If in Sheet 'JAN' C32:C90 is equal to "Cash Transaction," transfer the cell data from Sheet 'JAN' (for example) A40, B40, E40, and F40 over to Sheet 'Cash Acct' A9, B9, C9, D9. I need data transferred to the next blank/available row in Sheet 'Cash Account'. The available lines in Sheet 'Cash Acct' begin at line 9 and end at line 145.
Why won't this code work when I paste it into the 'JAN' VB Edit Window?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastRow As Integer
For Each Cell In Sheets("JAN").Range("C32:C90")
If Cell.Value <> "Cash Transaction" Then Exit Sub
Next Cell
LastRow = Sheets("Cash Acct") _
.Range("A145").End(xlUp).Row + 1
If LastRow < 9 Then LastRow = 9
If LastRow > 145 Then
x = MsgBox("Can't copy beyond Row 145.")
Exit Sub
End If
Sheets("Cash Acct").Cells(LastRow, 1).Value _
= Sheets("JAN").Range("A9").Value
Sheets("Cash Acct").Cells(LastRow, 2).Value _
= Sheets("JAN").Range("B9").Value
Sheets("Cash Acct").Cells(LastRow, 3).Value _
= Sheets("JAN").Range("E9").Value
Sheets("Cash Acct").Cells(LastRow, 4).Value _
= Sheets("JAN").Range... Read more

A:Macro auto transfer data to different sheet based on condition

Hi and welcome to TSG Forums!

What do you mean by "won't work"? Not doing anything at all? Or doing something but not what is expected?
Anyway, my suggestions to you are:

1. Run this code:

Code:
Sub test()
MsgBox Application.EnableEvents
End Sub

If it returns False, then all Event Handler subroutines are disabled, so the code you wrote is not executed at all. Default value of EnableEvents is True, and it should reset at each restart of Excel.

2. Place a breakpoint at this code line:

Code:
For Each Cell In Sheets("JAN").Range("C32:C90")
Placing a breakpoint: set the cursor on the desired lint then press F9.
When the code is executed for the next time, it will stop at the breakpoint. Hit F8 repeatedly to maintain a step-by-step code execution. This way you'll see what is happening really.

3. Declare all your variables.

Code:
Dim LastRow As Long, Cell As Range, x As Long
4. Variable "x" is, actually, not needed.

Code:
use
MsgBox "Can't copy beyond Row 145."
instead of
x = MsgBox("Can't copy beyond Row 145.")

A simple rule which might have confused you: When calling a function as if it was a procedure, you don't use parentheses. E.g.
myFunction Argument1, Argument2, Argument3
vs.
Variable = myFunction(Argument1, Argument2, Argument3)

5. In the code module of Sheet 'JAN' you don't need to write it out explicitly, you can refer to it a Me, or leave reference altogether. ... Read more

Read other 2 answers
RELEVANCY SCORE 80

Hello,
I sometimes use this bit of code to force Excel to open a workbook on Sheet1, cell A1:

Private Sub: Workbook_Open()
Worksheets("Sheet1").Activate
Range("A1").Select
End Sub
This works nicely, but if I had a chart selected in another sheet when I last closed the workbook, when I come to open that sheet, it remains selected. If I wanted to perform a print it then prints the selected item - the chart - to the full size of the paper and misses any other info around it.

I currently have about 100 charts in 10 different Excel workbooks, so I have to be careful to ensure that every chart is unselected when I close the workbooks down.

So:
1) How do I amend the code above to select cell A1 on every worksheet?

2) Can this still be done if the names of the worksheets are different - i.e. does Excel somehow still identify the left-most worksheet as Sheet1 even if it has a different name?

3) And what if the number of worksheets per workbook differs? In some workbooks I have 2 worksheets and in others I have 10 - can I use a standard bit of code that won't error if it runs out of sheets.

Thanky,

Gram123
 

A:Excel code to open at specific cell and sheet

Read other 9 answers
RELEVANCY SCORE 79.6

Hi:
Using excel 2010
I have a master list of items,product code, and price.
I would like to be able to use a separate worksheet and be able to use a drop down menu/list to select and add individually to my worksheet.
Example, there mmight be 1000 items in my master list and I might like to select/import as such 11 items into my worksheet.
Any suggestions?
 

A:Solved: Importing selected rows from Excel sheet one to Excel sheet two

Read other 6 answers
RELEVANCY SCORE 79.2

Hi, I am having a problem with an Excel sheet - when trying to add a comment to a cell, the view seems to shift upwards, so suddenly I am looking at a completely different set of cells, but still the comment box of the cell I was editing, and am them required to scroll back down to find that cell. There are 1012 rows of information in the sheet and probably several hundred comments.

Any help would be much appreciated
Thanks
 

A:Excel: adding comment to cell causes sheet to shift upwards

Check the comment's alignmnet? This is new to me.
Which version of excel are you using?
 

Read other 3 answers
RELEVANCY SCORE 78.4

Hi,

I want to add manually data\formulas to my excel sheet and I get a popup error message:

please see the attached file.

ps: I unprotected the sheet.

Where do I set the flag to allow to add data to my cell. It doesn't allow me to add in some cells. Why is that?.. Some work and some dont..

Thank you
 

A:Solved: error adding data to excel cell.

The cell is "protected" with Data Validation. Activate the cell and go to Data/Validation and either delete the Settings or click the Error Alert tab and set the Style to Warnng or Information.
 

Read other 2 answers
RELEVANCY SCORE 78.4

I have an Excel 2007 spreadsheet with 9,000+ entries. Column F contains text data (50-60 characters), which will include a 10 character serial number in the format "xxnnnnnnnn". The first two characters will always be alpha and the next eight will always be numeric.

I need to find each of these 10 character strings and copy them to column G of the same row. I do not want to do this manually if at all possible. Is there an excel formula that can do this type of function?

Your assistance is very much appreciated.

Mark
 

A:Solved: Excel - Find data, Copy to new cell

Read other 16 answers
RELEVANCY SCORE 78.4

Hi all,
I am having problems with code.
What I want to do is add a check mark to the right of an integer in a cell when I double click on the cell. I want to repeat the check mark addition indefinitely after the integer (integer, check mark, check mark, check mark, check mark, etc). The check mark I want is the uppercase letter "P" in Windings2 font.
I want to do this in any cell in Column C (3) that already contains any integer between 1 and 1000.
I am basing this on code that I already used in another post that I had: Excel Input box question, posted on July 2. http://forums.techguy.org/business-applications/1102607-excel-inputbox-question.html#post8727301
What I'm having problems doing is defining the integers using Dim code. I want to define "check" as any integer between 1 and 1000. I think once I get the syntax right, the code (hopefully) will work.
Any help would be greatly appreciated.
Thanks!
 

A:Solved: Excel DoubleClick to add additional data to a cell

Hi

try this and see if it suits....
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Target.Row < 1000 And Target.Column = 3 Then
Cancel = True
If Target.Value > 1 And Target.Value < 1000 Or Right(Target.Value, 1) = "P" Then
ActiveCell.FormulaR1C1 = ActiveCell & "P"
Selection.Font.Name = "Calibri"
For i = 1 To Len(ActiveCell)
If Mid(ActiveCell, i, 1) = "P" Then
With ActiveCell.Characters(Start:=i, Length:=1).Font
.Name = "Wingdings 2"
.FontStyle = "Bold"
End With
End If
Next i
End If
End If
End Sub

 

Read other 3 answers
RELEVANCY SCORE 78.4

I work with Excel 2007. If under column A, I have 900 names of Restaurants, and under each name of restaurant, is a one-line address of the restaurant, what should be done so that the address can be moved to the cell to the right of the restaurant.

For example

This is the original data:

Column A
AAA Restaurant
AAA address
BBB Restaurant
BBB address
CCC Restaurant
CCC address
This is the desired result

Column A Column B
AAA Restaurant AAA address
BBB Restaurant BBB address
CCC Restaurant CCC address

Thanks is advance
 

A:Solved: Excel 2007 Move Data to Another Cell

Read other 7 answers
RELEVANCY SCORE 78

Hi there,

I don't have enough experience in VBA excel or word, and would you mind helping for the following:
Q1
The below macro code add row below the last row. And Would you mind telling me what is the code that can add row for word document table between the last row and the row before the last?

ActiveDocument.Tables(1).Rows.Add

Q2:
My second question is related to export/import between two different programs or files ( Excel and word document)
Would you mind providing me the proper code for
Wordtable.copy
Workbook.xls - sheet1.past speical value
Suppose the word tables excel workbookD sheet 1 are as follow

Table 1 in word document
--------------------
Cell 11 - Cell 12
Cell 12 - Cell 22
--------------------
Table 2 in Word document
------------------------------------
Cell 11 - Cell 12 - Cell 13
Cell 21 - Cell 22 - Cell 23
------------------------------------

WorkbookD.xls – Sheet1
Rows Column
A B C D
1
2 wt1.Cell 11 WT2.Cell 11 WT2.Cell 12 WT2.Cell 13
3 WT2.Cell 21 WT2.Cell 22 WT2.Cell 23

How I write a macro to make the value or text in Cell 11. in word table 1 to be copied and past in Cell A2 in sheet1 of WorkbookD.xls
and the value or text of the range from Cell 11 to Cell23 of table 2 or word.doc to be copied and past in sheet1 of WorkbookD.xls from range B23 for n rows in table1 and table 2 of word.doc.

Thanks

Hamdy"
 

Read other answers
RELEVANCY SCORE 78

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 78

Hi,
I have an employee list excel file (vacation) including the details about empno, emp name, last vacation, next vacation, etc in sheet 1.
Now, I would like to write a macro where I will enter the name of the month in a dailog box and the macro should display the list as in sheet 2 of the attached file.

Windows 7 with MS Excel 7.

Kindly help me, its urgent.
 

A:Solved: Excel Macro to display data based on month

I've made a quick macro that does as you requested. It is not "smart" at all so if you change where the data is located (by adding a column for example) it will no longer work. It will handle more rows of data no problem. See the attached.
 

Read other 2 answers
RELEVANCY SCORE 78

Hello experts

I have an excel document with many sheets starting with sheet2

*** column_headers "enquiry#" in B6 and query_expiry_date in E6 (of every sheet)

What I am looking to do is, using sheet1 (which is unused at the moment)
dynamically return 3 pieces of information (in 3 columns, A,B,C for example)
based on expiry dates.

ie: if the expiry_date<today() then return sheet#, enquiry# and the date that expired.
this is for each enquiry and each sheet and if I can sort them in order of expiry date, that will be owsome.
Can that be done using lookup? or maybe better in VBA? a push in right direction or example
of code snippet is well appreciated.
Thank you
 

A:Solved: excel(VBA) returns data based on expiry date

Read other 12 answers
RELEVANCY SCORE 77.6

Thanks for reading, first of all.

I am not the most savvy person when it comes to programming. I can manage my way around formulas pretty well, but when it comes to VBA and macros I am utterly lost. I believe I need a macro to do what I want, but I have no clue where to begin. I have searched forums but what I need is so complicated that I don't know where to look to find it so I thought I would ask for help.

I have a spreadsheet which tracks projects I am working on. Right now the sheet I am working on is "2013" for example. The sheet has several columns, but the only columns of significance to this macro are:

Account Name
Requested Date
Urgency
Due Date
Completed Date
Request
Receive
Complete

Account and Requested Date are currently manual entries.
Urgency is a data validation dropdown with: NORMAL, RUSH and OTHER.
Due Date is a formula which calculates a due date based on the Urgency selected. (Except Other, in which case I override the Due Date manually.)
Completed Date is also a manual entry.
The 3 stages are data validations which default to a blank cell and then have a Square Root sign (which looks like a checkmark) as the only other option.

Now that I have explained the source data, allow me to go into detail what I would like this to do.
I would like to populate the first sheet of the spreadsheet with seven lists. These lists would, ideally, tell me at what stage each project is at (by account and due date). The lists I have set up are in seven bo... Read more

Read other answers
RELEVANCY SCORE 77.2

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
 

Read other answers
RELEVANCY SCORE 77.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 77.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 77.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 77.2

I am fairly new to VBA and this is my first post so please be patient with me. I require the assistance of other vba programmers in coding the following:

1. The user selects a Report ID from the first List box "Report ID's", clicks the "Add Single" button, then clicks the "Print Report" button. - (I have coded this)

2. The code for the print report button needs to take the report id from the "print report" List box and looks in the "Report ID" column from the "Reports" sheet for a match. Once found, it grabs the corresponding compilation no. for that Report ID.

3. It then uses that compilation ID no. to search in the "Compilations" worksheet for a match; it then grabs the corresponding Keyword for that compilation ID/no.
4. Lastly it uses the keyword to search for a match in both "Keywords1" and "Keywords2" columns from the "Exhibits" worksheet. Once a match has been found in any of the columns, it then grabs that entire row/exhibit and prints it out in it out in a word document, with correct formatting, Labels and headings, for the user to edit.

I would appreciate any assistance that can be given and strongly emphasise that i am not looking for someone to code everything for me, instead to take patience and go through a process step by step if possible.

Thankyou to any users who provide assistance, your patience and time taken to help has been duely noted and much ap... Read more

A:Solved: Create a report in word based on excel spreadsheet data

Read other 16 answers
RELEVANCY SCORE 77.2

It's difficult to describe my situation in words, so I'll just show you what I'm trying to do.

I have a table that looks like this:

A, B, C
Name, Hours, OT hours
Jane, 166, 0
Jack, 163, 17
Mike, 168, 0
Dot, 167, 24
Erin, 164, 8
Carmen 158,0

and I want to create a list that will have 2 rows if there are OT hours, and 1 if there are not.

A, B
Jane, 166
Jack, 163
Jack,17
Mike,168
Dot, 167
Dot, 24
Erin, 164
Erin,8
Carmen, 158

I can't use macros or VBA. It has to be formula driven. I can use a helper column or two. The starting table will change depending on who charged hours where. I've been trying to use IF and OFFSET and ROWS to do it, and it works until there's 2 consecutive rows that have OT hours.

Just to clarify, I just need to know how to get column A listed properly elsewhere in the spreadsheet - I can derive Column B using vlookup.

Thanks so much!
 

A:Solved: MS Excel: create list based on data in a simple table

2 Formulas - 1 for finding the name, the other for finding the appropriate value.
Note - must start on row 3 or greater.

Names (mine start in cell G3): =IF(G2<>"",IF(G1=G2,INDEX($A$2:$C$7,MATCH(G2,$A$2:$A$7,FALSE)+1,1),IF(VLOOKUP(G2,$A$2:$C$7,3,FALSE)>0,G2,INDEX($A$2:$C$7,MATCH(G2,$A$2:$A$7,FALSE)+1,1))),$A$2)

Hours (mine start in cell H3): =IF(G3=G2,VLOOKUP(G3,$A$2:$C$7,3,FALSE),VLOOKUP(G3,$A$2:$C$7,2,FALSE))
 

Read other 3 answers
RELEVANCY SCORE 76.8

I have an issue if certain cells are blank (not sure if they would be null, blank, empty) to have a macro to input "0" in the cell instead. When I look at the format in excel it's under general.

I currently have the following and it puts nothing in the cell. (or seems to be nothing in the cell) I can manually enter a "0"(zero) in the cell and it shows.....

Sub Addifblank49()
Sheets("BPS").Select
Range("c16").Select
Var = Selection.Value
If IsNull(Range("C16").Select) Then Var = "0"
End Sub
I've tried using val instead of var and it won't let me. I'm not real familiar with VBA so any help would be awesome!!
 

A:Solved: Excel Macro - enter in data in cell if Null/Empty

Read other 10 answers
RELEVANCY SCORE 76.8

Hoping to get some help on this question please. I have reviewed the forum and found a similar post, but the sample data is more predictable than mine and the Macro posted doesn't work on my data. See post "Excel Macro to Move data in Rows to column" 28-Aug-2008, 05:47 AM".

On my data, I would like to be able to take the yellow row, and move the data up to the previous row starting in column "R". The rows I would like on the same line, all begin with a date. I don't know how to make a looping Macro that will identify the "date lines", and take the "second date line", and move it to the "first date line's" row.

Any help would be appreciated.
I have attached some sample data. (I hope that's okay?!)

Thank you!!!!
Christine

PS - The range on data varies.
 

A:Solved: Excel Macro to sort data by date, then move to new cell

Christine,

this macro below works well with the sample data.
Code:
Sub sorting()
Dim Rng As Range, c As Range, i As Long
Set Rng = Range("A:A").SpecialCells(xlCellTypeConstants, xlNumbers)
For Each c In Rng
i = (i + 1) Mod 2
If i = 0 Then
With c.Resize(, 15)
.Copy .Offset(-1, 17)
.ClearContents
End With
End If
Next
End Sub

How it works:
1. Takes all those cells in column A of the active sheet that contain numbers. (Dates are actually numbers, only formatted to look like dates.)
2. In a loop, it takes every second cell of the set of cells selected in Step #1.
3. Takes a 15 cells wide region starting with the cell selected in Step #2, and copies it to the region starting with the cell positioned one row up and 17 columns to the right.
4. Deletes the content of the original 15 cells wide region.

Caution:
1. If there are any numbers in column A besides dates, then the algorithm is wrong and the macro will fail.
2. If the dataset in the second date line is wider than 15 cells wide then the rest will remain in place. But the macro can be easily updated, just change the Resize line.
3. Don't run it twice on the same worksheet.
4. Always make a backup copy of your data before running the macro, because it is not Undo-able.

Jimmy
 

Read other 3 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 76.4

Im wondering if it is possible to do this before i start trying...

I recieve an email containing an excel sheet containing several peices of info such as:
Name
Surname
ID
Etc Etc... Now when i open this.. i need the information posted into specified columns in a new excel document where it will have
NAME | SURNAME | ID |
-------------------------------------------
STEVE NORTH 01874
Any help given appreciated.
 

A:Excel - Auto export data into specified excel sheet

Read other 6 answers
RELEVANCY SCORE 76.4

Hi all,

I am trying to develop a cost calculator based on several drop down lists. I have used the following formula:

=AVERAGEIFS('Scope Of Work'!I:I,'Scope Of Work'!C:C,C6,'Scope Of Work'!F:F,'Cost Calculation'!D6,'Scope Of Work'!G:G,'Cost Calculation'!E6)

Cells C6, D6 and E6 in the Cost Calculation Tab are all drop down menus based on data validation. The formula works when all 3 cells have a selection value but I also want it to calculate if only one or two of the drop down lists have been populated, any ideas?

Thanks,

Mark.
 

A:Solved: Excel 2007 - Dynamic formula based on Data Validation List

Read other 11 answers
RELEVANCY SCORE 76.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 76.4

Hi guys,
I need to write a macro that is connected to many buttons, one for each row in spreadsheet, ask for a value to subtract, error check and (if ok) subtract from a corresponding value in column P.

When the button on a left hand side is clicked:
a dialog button should pop-up prompting a user to input the number of pieces to subtract from the corresponding cell (row based on the row of the button, column P)
error check the input number (show error if the value is smaller than 0 or larger than the corresponding value) ... I can write the error check by myself, so no need to do this step
subtract the number from the corresponding cell (i.e. user clicked a button located in cell A9, input 9,24 pieces in a dialog box and there are 15,97 pieces in P9 => the pieces in P9 should change to 6,73)
Thank you for helping me out!
Hope, the description is not very confusing.

Michal
 

A:Excel VBA: Subtract from a corresponding cell based on button placement

Read other 9 answers
RELEVANCY SCORE 76.4

Hello

I need to do the following in VBA:

If cell in column C contains the value GBP, change cell S from that row from 13 to 23
or
If a cell column C contains the value USD change cell S from that row from 13 to 33

exception cell: C1 --> contains the title of the column

How do I do this?
 

A:Excel 2003 replace macro based on value in other cell

Read other 6 answers