Computer Support Forum

Excel: Checking values in multiple cells and transferring values to another worksheet

Question: Excel: Checking values in multiple cells and transferring values to another worksheet

This is the code i got so far it will go though and pick the users that have the appropriate status "A" and paid "No". I need it to then copy those users that are appropriate to the Mail Extract sheet. I think i am ment to have a Dim or something in there arn't I. The macro is under the Modual 1 Thingy... If you can help me please do so PLEASE. The data to be copyed is on the Band Members sheet.

=======================================================
Sub MailExtract01()

!!! I know i need some DIM stuff here too!!!

'If filtering
Sheets("Band Members").Select
Range("A3").Select
ActiveCell.Offset(0, 5).Range("A1").Select
For Each Cell In Range("Status")
If ActiveCell.Text = "A" And Selection.Offset(0, 2).Text = "No" Then

!!! This is where i need the copy code to go, I think !!!

End If
Next Cell
Sheets("Mail Extract").Select
Range("A1").Select
End Sub
=======================================================

I need the code to filter though every person and check if the status is “A” and the Paid is “No”. After the filtering I need every person with an “A” under Status and “No” under Paid to be copied to the “Mail Extract” worksheet. I have got all the code to filter though the cells but how do i get this to then copy the appropriate members to the "Mail Extract sheet. There is a code of the file i am working on attached to this post.

Any help will be great. The is for my Tafe course and i am stuck please HELP!

Thanks Techie_003

More replies
Relevance 100%
Preferred Solution: Excel: Checking values in multiple cells and transferring values to another worksheet

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

Relevance 176.03%

This is the code i got so far it will go though and pick the users that have the appropriate status "A" and paid "No". I need it to then copy those users that are appropriate to the Mail Extract sheet. I think i am ment to have a Dim or something in there arn't I. The macro is under the Modual 1 Thingy... If you can help me please do so PLEASE. The data to be copyed is on the Band Members sheet.

=======================================================
Sub MailExtract01()

!!! I know i need some DIM stuff here too!!!

'If filtering
Sheets("Band Members").Select
Range("A3").Select
ActiveCell.Offset(0, 5).Range("A1").Select
For Each Cell In Range("Status")
If ActiveCell.Text = "A" And Selection.Offset(0, 2).Text = "No" Then

!!! This is where i need the copy code to go, I think !!!

End If
Next Cell
Sheets("Mail Extract").Select
Range("A1").Select
End Sub
=======================================================

I need the code to filter though every person and check if the status is “A” and the Paid is “No”. After the filtering I need every person with an “A” under Status and “No” under Paid to be copied to the “Mail Extract” worksheet. I have got all the code to filter though the cells but how do i get this to then copy the appropriate members to the "Mail Extract sheet. There is a code of the file i am working on attached to this post.

Any help will b... Read more

Answer:Excel: Checking values in multiple cells and transferring values to another worksheet

16 more replies
Relevance 153.99%

Hello Everyone,
(This is kind of long but I didn't know how else to explain everything)

There are two parts to my question below.

I have a spreadsheet with roughly 40 columns

An Example of some of the columns would be:

Code:

B1 = Client Name E1 = Travel F1 = Backup G1 = PC Install H1 = Data Recovery | X1 = Total | AE1 = Summary
B2 = Tony Smith E2 = $70.00 F2 = $0.00 G2 = $95.00 H2 = $0.00 | X2 = $165.00 | AE2 = (See Below)
B3 = Tom Daniel E3 = $70.00 F3 = $0.00 G3 = $0.00 H3 = $725.00 | X3 = $795.00 | AE3 = (See Below)
B4 = Rene Little E4 = $70.00 F4 = $125.00 G4 = $95.00 H4 = $995.00 | X4 = $1285.00 | AE4 = (See Below)

Currently this spreadsheet is being analyzed to be used to track field technician jobs and figure everything out for accounting.

I have been asked to please make a cell with a list of the information above so that accounting can just copy and paste from that cell into quickbooks in order to send these people out a receipt instead of having to retype everything into quickbooks. This list has to be based upon whether or not there is a value greater than 0 within that cell then it will grab both the heading cell in the first row and the value of that cell it was checking and place it as a line item in another cell.

I know how to do half of what they are asking and if I was dealing with 1 column I can do the other half.

I know how to list items in 1 cell using the c... Read more

Answer:Excel: Checking values on multiple cells and transferring values to 1 cell

16 more replies
Relevance 97.73%

I have created a bonus plan for my team. It has 3 factors that could reduce the value. If all 3 factors = 100%, they get 100% of bonus. If any factors are less than 100%, then their bonus is reduced by the % of the lowest factor to give them their payout. Example1: If any factors are 0%, bonus is 0%, Example 2:If the factors are 100%, 75%, and 50%, then payout would be at 50% (the lowest factor). Below are the parameters of the 3 factors. Factor A = 100%, 75%, 50%,0%Factor B = 100%, 75%, 0%Factor C = 100%, 50%I hope this is clear enough. Thanks in advance for your help inautomating this recurring process for me if possible!

Answer:Need Excel to return value based on multiple cells & values

Your text doesn't match your math."If any factors are less than 100%, then their bonus is reduced by the % of the lowest factor to give them their payout. ""reduced by the % of the lowest factor" means that:If any factors are 0%, bonus is "reduced by" 0%I assume you mean that the bonus is "reduced to" the percentage of the lowest factor. That is the only way a Factor of 0% would equal a bonus of 0%.Assuming that you meant "reduced to", let's go from there.You didn't supply any information related to the layout of your data, so it's hard to be specific with a solution.Assuming that the percentage for each Factor is stored in a cell, you should be able to use the MIN function to determine the Bonus amount.For example, let's say you start with this layout:
A B C D E
1 Max Bonus Factor A Factor B Factor C Actual Bonus
2 $1,000 100% 75% 100% =A2*MIN(B2:D2)
The formula in E2 should return $750, which is 75% of the Max Bonus.Is that what you were looking for?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.message edited by DerbyDad03

3 more replies
Relevance 85.84%

Hey guys, I have a spredsheet with 3 seperate worksheets. One has a huge list of properties in a column, and the second worksheet has another huge list of properties in a column. I need to know how to compare column 1 (on worksheet 1) with column 2 (on worksheet 2) and then return values that have close or exact match.

E.g

Table 1 Table 2

Sky Hotel Moon Hotel
Golden palace hotel Golden palace hotel
Atrium Hotel Atrium Hotel
Beach Hotel Beach hotel

I need these values to be compared, and ones that match/or do not match to be listed on worksheet 3. E.G

Worksheet3:

Table 1 Table 2
Golden Palace Golden Palace
Atrium Hotel Atrium Hotel
Beach Hotel Beach Hotel

(notice how sky hotel has not been displayed here)

If Vlookup or something similar is the formulae to use, could you tell me how to use it, as I've tried but to no result.

Thanks guys!
 

Answer:Excel 2007 - comparing values in one worksheet to another

The simple answer is arrange the tables next to each other (1 in column A, 2 in column B), then use:

=A1=B1

in column C to return TRUE or FALSE.

However, I suspect it's more complicated than you're suggesting.
 

1 more replies
Relevance 84.68%

Hi. I'm building an employee schedule into Excel. I am using a seperate tab for each different areas of the business. In each tab I have a column that indicates if the shift is a "Custom Program". I would like to have a seperate tab that brings all "Custom Program" shifts together on one page, drawing from all the other tabs.

I know how to reference specific cells onto the new tab, and I know how to do conditional formatting within a tab to find and respond to a specific word, but that's not quite what I need.

-Is there a way to reference a particular value (every time the words "Custom Program" appear) from Sheet 1 and put it in Sheet 2?

-Once the "Custom Program" values are sent to Sheet 2, I need the rest of the data in those row to come with them. How do I do that?

I hope I've explained that somewhat clearly. Thanks for your help.
 

Answer:Excel: Referencing Values (Not cells)

9 more replies
Relevance 83.81%

Hi,

Im currently having trouble, i have some code to search for a value within 3 spreadsheets but what id like is for it to also return a value on the same row.

e.g
Dave 111111
Claire 112233

so instead of returning just dave, id like the number returned also.
 

Answer:Excel macro: Return values from 2 cells on same row

9 more replies
Relevance 83.81%

Hey,
I've got Microsoft Excel 2003. I am working on organising tons of data (words, not numerical) that's given to me in a bunch of spreadsheets and I need to consolidate all of them into one. The original spreasheets are not organised very well, so a writing of a macro isn't an option. Copy and paste it is. However, there are some sections where I need to combine the information in multiple cells into one.

Example:
cell1: John
cell2: is the
cell 3: manager of the
cell4: company.

Now it'd be really nice if there was some sort of way to copy cell1-4 and paste it into cell 5: John is the manager of the company. Anyone know a way possible? It's taking me forever since I have to go into the fx, not just copy/paste the cell.... not to mention that there are some instances where I have to combine 10+ cells into 1.

Thanks...
 

Answer:Excel help... any way to consolidate values in multi cells into one?

This problem has been around for a long time. I know that on anything pre-2k3 it was a little more difficult as copy/paste, of course, doesn't work exactly as you need it to in this instance.

If the cells are right next to each other, you might be able to just merge all the cells together. You can't do this in 2k as it will erase all but the first cell.

The other thing you could probably play around with is the concatenate function (at least, I think that what it is). You'll have to google for exact formulas but I think it's something like con(cell1, cell2, etc). It's been a while since I tried that. Downside to this is if you have a bunch of cells in a lot of random postions, you'll end up doing a lot more formula writing than actual copying.

Or, could you export the excel document as a word file? Then you could take out all the spaces / tabs with the replace function. Take a little time, but it'd probably work.
 

2 more replies
Relevance 82.94%

Hello,

A pretty inexperienced VBA'er here.

I have 2 named ranges:

- LastStatus = "J8:J500"
- CurrentStatus = "K8:K500"

What I would like to do is create a loop to copy the cells in CurrentStatus over to the LastStatus column if and only if there is a value in CurrentStatus (column K). If there is no value in column K and there is a value in column J, I do not want change the value in column J.

Any help would be greatly appreciated.

Brian

 

Answer:Excel - Conditional Copy / Paste (Same Worksheet) based on cell values

You don't need a loop.

Sub test()
Range("LastStatus").SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF(RC[1]<>"""",RC[1],"""")"
Range("LastStatus").Value = Range("LastStatus").Value
End Sub
 

1 more replies
Relevance 82.65%

I have a macro from MrExcel.com that parses each row from an Excel spreadsheet into a separate word document and saves each document to My Documents as "File-Row #.doc". For example, row 2 is saved as: File2.doc, row 3 as File3.doc, etc. to the last row with data. The macro starts on row 2 to skip the column headers.I would like to modify the macro to have the file name equal the concatenated values from the first two cells in each row (Columns A and B from row 2 to the last row with data).An example of a simplified version of the spreadsheet: A B C1 Date as Text Record # Record Status2 2012-05-28 APF-2012-1940 Closed3 2012-05-29 ICM-2012-1987 In Process 4 2012-06-02 PAT-2012-2317 Awaiting ClosureOnce the macro is run, the results from the above would be 3 word documents with the file names as below:Row 2 file name is: 2012-05-28 APF-2012-1940.docRow 3 file name is: 2012-05-29 ICM-2012-1987.docRow 4 file name is: 2012-06-02 PAT-2012-2317.docThe macro as it currently exists:Sub ControlWord() Dim appWD As Word.Application Set appWD = CreateObject("Word.Application.8") appWD.Visible = True Sheets("Data").Select FinalRow = Range("A9999").End(xlUp).Row For i = 2 To FinalRow Sheets("Data").Select Range("A" & i & ":N" & i).Copy Sheets("Template").Select Range("A" & i & ":N" & i).PasteSpecial Tr... Read more

Answer:Modify macro to use values in two Excel cells as file name

In the future, if you are going to post data or code in this forum, please click on the blue line at the end of this post and read the instructions on how to post example data and VBA code in this forum.As far as your current issue, this syntax is probably not converting to what you want it to be:Range ("A" & "B" & i)For i = 1, this would seen to VBA as:Range("AB1")I would try something like this within the loop:tmpFilename = Range("A" & i) & " " & Range("B" & i)...
..
...
appWD.ActiveDocument.SaveAs Filename:=tmpFilenameThis will build the Filename from the values in A1 and B1 with a space in between the 2 values.You should also be aware that you might run into problems with the dates in Column A.If VBA picks up the dates as 05/28/2012, then your code will fail because slashes can not be used in a filename.It will depend on how the default dates are set up on your system.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 82.65%

Hi,

Got a few questions which tie in to what i am doing so i'll try to explain as best i can.

I want to be able to have excel automatically arrange the order in which it displays the information based on the value or information in a cell. Eg i have a list of things i sell online and I track their performance using the spreadsheet were i have a sheet for every product type which are linked to a master snapshop sheet which has the prices, weight and other info about the items. The product name on this main sheet is hyperlinked to the sheet containing the product and in turn the data in that sheet is formulated back to the main snapshot the aim being as little data input as possible to calculate everything.
I would like to be able to have the snapshop mainsheet automatically put for example the best performing product at the top of the list. At the moment a have to select the cell and expand the data range but this is erratic as i don't think i do it properly. Sometimes it messes all the data up. I am not that good with excel so i am probably going to be told why don't you do this or that etc but that is ok if anyone has an idea.
Anyone able to help?
Apologies if its difficult to follow but i will give more info if requested.
If someone has a link to a webpage that can help would be great also.

Regards,
lee_1133
 

Answer:How do you Auto arrange cells in excel based on their values?

16 more replies
Relevance 82.07%

In sheet 1 Column A has current values, Column B has the new value for each entry.

Column A Column B
Old value New Value

Document 1 SOP-001
Document 2 SOP-002
Document 3 SOP-003

In sheet 3 has a list of entries
Column A

Document 1
Document 3
Document 3
Document 2
Document 1

What formula can I use to automatically replace the value SHEET3:ColumnA with the appropriate value from SHEET1:column B? The lists are quite long. Resulting in a list on sheet 3 that looks like this:
SOP-001
SOP-003
SOP-003
SOP-002
SOP-001
 

Answer:Solved: Excel-Substitute values from a list of new values

Hi bongiojf, welcome to TSG.

You can do this with a vlookup formula, assuming that the old values are in the leftmost column and that they are sorted alphabetically.

On sheet 3, in column B (or any blank column) use this formula for the first cell and then copy it down the rest of the column:

=VLOOKUP(A1,Sheet1!$A$1:$B$3,2)

Change the range $A$1:$B$3 to be the range of data you have on Sheet 1. The number 2 at the end of the formula indicates that the new values are in the second column from the left. If they are not, change the number accordingly. For example, if they are in column D, the value would be 4.

Once you have the new data in your blank column, you can copy the column and paste special/values over the existing data on sheet 3.

Hope that helps.
 

1 more replies
Relevance 81.78%

Hi All,I am running a windows batch file which will accept a single value as a parameter.The value is either 0 or 1.Inside the batch file i want to check if the parameter is not 0 or 1 then it will generate an error.It is something like this.IF <PARAMTER VALUE> NOT IN (0,1) THEN  GOTO :PARAMETER_ERRPlease let me know how to do this. I am using windows XP and windows 2003 server

Answer:Checking multiple number values in a windows BAT file

Maybe something like this?Code: [Select]@Echo Off
If [%1] EQU [] (Echo Param required & Goto End)
If %1 LSS 0 (Goto ParamError) Else If %1 GTR 1 (Goto ParamError)
Echo Param passed was %1
Goto End
:ParamError
Echo Wrong parameter passed. Must be 0 or 1
:End

2 more replies
Relevance 77.14%

i am uploading excel work book with 4 sheets, sheet no1 contains the record to be verifed/matched with the records in sheet no.2. we will match BTC_Name, BTC_Fname, Deg_RegNO of sheet1 with student name, father name, reg.no. of sheet no.2, if records of sheet no.1 are matched/presented in sheet no.2 then the whole row of sheet no.1 should be copied in to sheet no.3 (if matched display here) else other wise mismatched/ not presented records of sheet no.1 in sheet2 should be displayed (whole row) in sheet no.4 (not matched display here). i have shown sample values in sheet 3 and in sheet 4 taken from sheet 1.
thanx.
 

Answer:compare/match multiple column values in multiple excel sheet

11 more replies
Relevance 76.85%

Hello,I have an Excel spreadsheet with a column of hyperlinks to all of the photos on my hard drive. In this spreadsheet I have a column for key tags for each photo. I want to be able to search the key tags and isolate each row that contains the key tags, hyperlinks and additional data in the spreadsheet. Here is an example of the key tags column Events Baby Shower Lunch Feb 09 Kim's Baby ShowerRight now if I do the basic CRTL F search and I type in "Events Lunch" it will not find the above entry. Is there a Macro I can set up to continuously use that would put each matching entry in a new spreadsheet? Also I would like to be able to search for words that are not on the spreadsheet and produce a search with results, for example if I searched "Books Baby Shower" and no key tags in the entire spreadsheet had "Books" I would like to still see everything with "Baby" and "Shower" in the key tags column.any tips or better ideas would be greatly appreciated,Thanks.

Answer:Find multiple values in Excel

Instead of searching for Events Lunch, you can find the above entry with:Events * Lunchas long as the "Match entire cell contents" box is not checked.Instead of searching for Books Baby Shower you can find the above entry with:b? * showeras long as the "Match entire cell contents" box is not checked.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 76.85%

I have 3 lists, a master locker register, an employee list, and a resignee list.I have to update the master locker register referring to the resignee list. below is the example of a value in the master locker registerfloor | locker number |staff name| *employee ID*| process | line mgr| extension|2 s345 adam 43614985 SS mada 2589the resignation list is basically the same thing but the thing is someone else is editing it and i find it difficult that the person editing it puts in values all across (according to date) instead of just adding on to the list (which is much easier as i will be able to track the changes)i am also doing this very annoying manual cut and paste method. the value that i need to compare between the 2 is the employee ID. please help

Answer:Searching for multiple values in excel

First, please repost your data after reading the How To referenced in my signature line.Second, keep in mind that we can't see your spreadsheet from where we're sitting, so we have no idea what this means:"the person editing it puts in values all across (according to date) instead of just adding on to the list"Please explain, or better yet, post an example - after reading the How To.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 76.85%

I want to search multiple values in excel together and want them to be highlighted. E.g. I want to search the following 4 values together with a single command in an excel468.307482.3212482.3219504.34Pls help.ThanksMainak

Answer:Searching multiple values in excel

We need more details...Where are these values? In a single column? Anywhere on a specific sheet? Anywhere on multiple sheets? Etc?Do they all have to be present to be highlighted or if any one or more are found should it/they be highlighted?Are these the actual values or are they just examples? Will there always be four values or will the numbers of searched-for values vary?These are just some of examples of the types of details that we need in order to help us offer a usable solution. The more you tell us about what you are trying to do and how your spreadsheet/workbook are laid out, the better.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 75.98%

Hello,What I would like to do is to use the for example the Ctrl-F function in Excel to look for different words/values at once. Is this possible?Even better would be a macro that searches a certain worksheet for lets say 10-100 predefined words/values. If the word/value is found, the row in which the word/value appears should be copied to a second worksheet.Thanks in advance,E.

Answer:Find multiple words/values in Excel

I suggest that you try the code in a backup copy of your workbook since macros can not be undone.1 - Press Alt-F11 to open the VBA Editor2 - Click on the name of your workbook in the left hand column3 - Click on Insert...Module4 - Paste the code into the right hand pane that opens5 - You should see Blue, Green and Black text. If you see any Red, something's not right. Post back here describing what's Red.6 - To Run the code, click anywhere in the code to place the cursor in the code, then click the Green arrow in the tool bar.The code will either do what it is supposed to do or throw up an error. Post back with info on the error if that happens. How long it takes to run will depend how on much data it has to search through. When the cursor starts blinking in the code, it's done.You can also Single Step through the code by pressing F8. Each time you press F8, one line of code will be executed. This can help narrow down problems if they occur. You can single step for awhile and then click the Run arrow once you're satisfied that the code is doing what you want.In single step mode, you can switch back and forth between the workbook and the VBA Editor to see if lines are being copied. You can even size the VBA window so that you can see your workbook behind it to see if lines are being copied as expected.You can assign the macro to a button, a function key or a menu item to make it easier to run once you are sure it is working. Google something like assign macro to button for var... Read more

20 more replies
Relevance 75.98%

Hi,

I need the help of a guru. I'm an intermediate (NOT ADVANCED) user tasked with an assignment and I'm not sure of the best way to execute the idea. I have two spreadsheets FILLED with data. I have to take Manufacturer numbers from a column in one spreadsheet, and compare them to the the manufacturer numbers in a similar column of another spreadsheet to find matches. There are thousands of numbers! What is the best and quickest way to do this?! I would love to open List B and run a check for all the numbers from List A at the same time. Hope I'm making sense because I need a savior.

Please answer my question (in a user-friendly, step by step fashion if you would) and consider your good deed for the day done!

Thanks,

*Overwhelmed*
 

Answer:Excel - Search Multiple Values at Once (Overwhelmed)

Welcome to TSG dusk.

Look at the Vlookup function.
 

2 more replies
Relevance 75.98%

Not an expert so I need allot of help. In a production schedule I have various models to make with X number of units per model in a day. Using VLOOKUP I have a time value (production time) column for each model multiplied by its number of units, so I know the time it takes to produce each individual model in a day. I now need to include a PST (Production Standard Time) column for each model so workers can know at what time they should finish each of the models in the daily schedule. During our work hours (7:00am - 5:06pm) I have 3 lunch/rest breaks one @ 9:20 am(lasts 19min), 12:30 pm(lasts 29min) and 3:00 pm(lasts 9min) so I need to add to the PST that specific lunch/rest time if a model is finished during any of the lunch/rest periods, so I?ll assure the PST is accurate. How can achieve this using one single formula, considering the 3 criteria?

Criteria #1: IF((A1+B1)>=timevalue("9:20 am") but <=timevalue("12:30 pm"), (A1+B1)+time(0,19,0), (A1+B1))
Criteria #2: IF((A1+B1)>=timevalue("12:30 pm") but <=timevalue("3:00 pm"), (A1+B1)+time(0,29,0), (A1+B1))
Criteria #3: IF((A1+B1)>=timevalue("3:00 pm"), (A1+B1)+time(0,9,0), (A1+B1))

Since the values will be adding up as the day goes, it can occur, that the value (A1+B1) falls directly into the second criteria (eg. 1:37 pm), I need then for it to add the 29 minutes plus the 19 of the first criteria, and so on and so forth. Thanks so much in advance.
HEL
... Read more

More replies
Relevance 75.98%

Hi,Office 2010 Excel I have a table with code reference and value next to it I want to sum all the values with the same code reference.example:2 1003 702 202 200 5 1083 233The result should be: 100+20+200 = 320 thanks for the answers.

Answer:excel formulas lookup sum multiple values

Look up the SUMIF function in the Excel Help files.If you have any trouble figuring out how to use SUMIF, come on back and we'll see what else we can do to help you.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 75.98%

Hello.
I have a document with 4 columns, in column one there are codes, in column two there are names and in columns 3 and 4 are start and end dates.
There can be multiple codes for each name.
What I'd like to do is create a second worksheet with the names listed (once each) in column A, and have the corresponding codes and dates in the adjeacent cells, so I'd go from this:

Code____Name____Date1____Date 2
A1______ABC123___01/01/05__20/01/05
A2______ABC123___15/01/05__12/02/05
A1______DEF456___01/01/05__08/01/05
A5______DEF456___07/02/05__10/02/05
To this:

Name_____Code1___Date1____Date2____Code2___Date1_____Date2
ABC123_____A1_____01/01/05___20/01/05_____A2____15/01/05___12/02/05
DEF456_____A1_____01/01/05___08/01/05_____A5____07/02/05___10/02/05
A name may have between 0 and possibly 10 codes.
Is there a way to do this?
I've looked at VLOOKUPs, Pivot tables and Row Indexes, but can't seem to get it to work.

Any help is appreciated.

Gram
 

Answer:Excel 2002: Lookup multiple values

i think a pivot may do it but not maybe give you the order you have more a grouping
 

2 more replies
Relevance 75.98%

Hello I have long string that looks like that:"54665 - Name 6420, 5412, 5224, 4454, 4408 - SSL Acceleration - Upgrade from 500 CPS to 2,000 SSL CPS; 9210027 - Upgrade from 500 CPS to 2,000 "I wont to search for exist value 9210027 inside the string and copy it to anither column.Any function to do it ?Thank you!

Answer:Find multiple values in Excel string

Assuming that the parts numbers that are embedded in the string are always preceded by a "semicolon space"then try this:With your data in cell A1do a Text To Columns to split the string at the semi-coloninto three seperate cellsThis is for 2007:Highlight cell A1On the ribbon select the DATA tabSelect Text to Columnsselect the Delimited buttonClick NextFrom the list of Delimiters, check SemicolonClick NextClick FinishYou should now have three cells, A1, B1, C1with your seperated data and each cell should begin with your part number.Now in cell D1 enter the formula:=LEFT(A1,FIND(" ",TRIM(A1),1))&", "&LEFT(B1,FIND(" ",TRIM(B1),1))&", "&LEFT(C1,FIND(" ",TRIM(C1),1))In essence it is the same formula repeated three times and concatenates the three cells A1, B1, C1 together seperatedby a comma.See how that works for you.MIKEhttp://www.skeptic.com/

8 more replies
Relevance 75.98%

Is it possible to build several if statements that return multiple values if satisified to one cell in excel. For instance, I have the following Info in Excel:Cell A1: Coborrower Cell B1: John DoeCell A2: Principal Cell B2: Jane DoeCell A3: Coborrower Cell B3: John SmithCell A4: Coborrower Cell B4: Jane SmithCell A5: Guarantor Cell B5: Jack DoeIn Cell G6, I want to create an if statement that says if coborrower is found in cell A1, A2, A3, A4, or A5 then return the corresponding values of the B Column seperated by commas. For instance, in the scenario above I would want G6 to read: John Doe, John Smith, Jane Smith

Answer:If Statements Returning Multiple Values Excel

The result listed above does not have to be seperated by commas but would be preferred.

5 more replies
Relevance 75.98%

HiI am really badly stuck at this and am a rookie on excel. I am building portfolios based on Market values(MV) and then multiplying those MV's with returns calculated from Price Index (PI). Iv got approx 300 companies, out of which I have to make 6 portfolios for each year comprising of 20-30 companies out of those 300. The company names are in in the top row and the MV values follow in columns. same happens with the returns which is, as i said, function of the PI values, on a different sheet. So now i need to search for those portfolio companies one by one and then copy paste the MV values first and then repeat the whole process to get the return values from PI sheet, then multiply them together and find the total portfolio return. I have uploaded the file on zippyshare http://www66.zippyshare.com/v/35938...In this file you will see 2004 portfolio where i have actually made the first "BH" portfolio by doing what iv mentioned, when ull scroll down ull see another portfolio "BM" where the companies are listed. Is there an easier way to search through the 300 companies to retain the required companies and their corresponding yearly values and filter out the rest? and same could be done for returns?THANKS a million in advance

Answer:Finding multiple data values in excel

I'm not if this is what you are looking, so let me know...In 2004 Portfolio!B2 you have a value of 6013.42 which I assume came from MV 2004-2005!KF2.This formula will return that value from MV 2004-2005 using the INDEX and MATCH functions.The first thing I did was turn MV 2004-2005 into a Named Range (MyData). I then used the following formula to return 6013.42 into 2004 Portfolio!B2The MATCH functions will return the row_num of the value in 2004 Portfolio!A2 and column_num of the value in 2004 Portfolio!B1 based on where they are found in MyData (MV 2004-2005).It will then use those row_num and column_num values in the INDEX function to return the value found at that intersection.=INDEX(MyData,MATCH(A2,'MV 2004-2005'!A:A,0),MATCH(B1,'MV 2004-2005'!1:1,0))Hope that helps!Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 75.98%

Hello all. I have 2 spreadsheets which contain subscription information for 2 different products costing £21.58 and £5.96. I'm trying to merge this information so that if Mr A in spreadsheet 1 is shown as subscribing to Product B and the same person in spreadsheet 2 is shown as subscribing to Product C, an additional column shows in spreadsheet 1 so that it then appears as: Mr A - £21.58 - £5.96

If not, the columns would show Mr A - £21.58 - 0 (or blank) etc

The code for VLOOKUP is straight-forward but only returns the first row. I need it to reference every row and cross-reference the data.

I'm using Windows 7 and Excel 2010. I've attached a small example though the actual spreadsheet has around 3000 rows. I only need to do this once to get a single file up and running.

Any ideas

Many thanks

Nos
 

Answer:Solved: Comparing multiple values in excel

Sorry all. Now solved using a simple VLOOKUP and defined names with a fill-down. Doh!
 

1 more replies
Relevance 75.98%

I have an excel spreadsheet with multiple lines of data; and within one column I need it to find every line that has a specific word in it and move that row to another tab. I have multiple words to seach for. The below code is close, but it stops after it finds the first instance of the word in the list. What I would actually like it to do, instead of copying to another sheet is color that cell a particular color. Any assistance would be appreciated.
Sub GeneFinder()
Dim srchLen, gName, nxtRw As Integer
Dim g As Range
'Clear Sheet 2 and Copy Column Headings
Sheets(2).Cells.ClearContents
Sheets(1).Rows(1).Copy Destination:=Sheets(2).Rows(1)
'Determine length of Search Column from Sheet3
srchLen = Sheets(3).Range("A" & Rows.Count).End(xlUp).Row
'Loop through list in Sheet3, Column A. As each value is
'found in Sheet1, Column D, copy it top the next row in Sheet2
With Sheets(1).Columns("D")
For gName = 2 To srchLen
Set g = .Find(Sheets(3).Range("A" & gName), lookat:=xlWhole)
If Not g Is Nothing Then
nxtRw = Sheets(2).Range("D" & Rows.Count).End(xlUp).Row + 1
g.EntireRow.Copy Destination:=Sheets(2).Range("A" & nxtRw)
End If
Next
End With
End Submessage edited by Mloftin66

Answer:Find Multiple Words/Values In ExcEL

Does anyone have an answer for the above?

2 more replies
Relevance 75.98%

I run a file room and have 55,000 files listed in an excel sheet by rack number / shelf numberRACK FILE1 FILE2SHELF 2.2.1 00-DCV-115533 TP 01-DCV-121638SHELF 2.2.2 07-dcv-156148 07-DCV-156329 TPSHELF 2.2.3 08-DCV-161660 tp 08-CV-161663SHELF 2.3.1 08-DCV-166310 08-DCV-166316 TPSHELF 2.3.2 09-DCV-169134 09-DCV-169166When users request files (by using an excel sheet) I would like to be able to click a 'find' button and use values from the user provided excel sheet to find those files in the file room excel sheet.Here is a sample file sent by a user:FILE NUMBER REQUESTOR'S NAME09-DCV-169193 Erronda Harris 09-DCV-171620 Erronda Harris 09-DCV-173211 Erronda Harris

Answer:Search excel file for multiple values

A database may be a better tool.

6 more replies
Relevance 75.98%

I have similar kind of Excel report extracted from remedy tool having around 400-500 rows of records. My search string have list of 100's of server names. I want to find out the records having servers from search string. The required search operation would be on two columns.

Answer:How to search multiple list values from excel

It is not clear to me what you are trying to do.Please click on the blue line at the end of this post and read the instructions on how to format and post example data in this forum. Then post a short example of your data and your search string. Don't forget to use Column letters and Row numbers as shown in the example. Thanks!Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 75.4%

Hello,I have a list of about 4,000 lines of Reference codes that need to be looked up at once from a separate sheet and then highlighted. I have this code currently Option ExplicitSub DocFinder()Dim srchLen, DocName As IntegerDim g As Range'Determine length of Search Column from Sheet3 srchLen = Sheets(3).Range("A" & Rows.Count).End(xlUp).Row'Loop through list in Sheet3, Column A. As each value is'found in Sheet1, Column E, Highlight it With Sheets(1).Columns("E") For DocName = 1 To srchLen Set g = .Find(Sheets(3).Range("A" & DocName), lookat:=xlWhole) If Not g Is Nothing Then g.Interior.ColorIndex = 5 End If Next End WithEnd SubIf it looks familiar it is because I had modified it from a similar post. My problem is that it only finds the first hits and highlights it. I have many repeated reference codes that all need to be highlighted. I have tried implementing a for each but the program will then cease to run.Any help will do.Thanksmessage edited by JonaRey

Answer:Find multiple words/ values in excel and highlight them

Just for future reference, it would be easier for us to help you if you would post your complete macro, not just a small portion. When we want to test your code, we want to be able to Copy it directly into the VBA editor and run it. When you only post a section, we have to '"rebuild" it to get it to run. If you make us do too much work, we might get bored and walk away. ;-)As for your error, think about what g is. g is the object that gets set when the .Find method finds what it is searching for.As I explained in my previous post, your If statement is testing to see if g is Not Nothing. Only when g Is Not Nothing does it have properties such as .Row, .Column, .Value, etc.The code will only fall through to the Else section if g is Nothing and if g Is Nothing you can't reference its Row, you can't copy it, you can't do anything with it. After all, it's Nothing, and just like in real life, you can't do anything with Nothing. ;-)OK, so why was g set to Nothing? Because the current search string wasn't found. What is it that you are trying to copy? The unfound search string, right? So, don't try to copy g, copy the current search string which wasn't found.I'll be nice and tell you upfront that even if you try to copy your current search string, you are still going to get an error. Range(?A?) is not a valid destination Range. You have only referenced a Column without a Row. VBA will not like that. You also need to keep in mind that you have to increment that row each time you ... Read more

14 more replies
Relevance 75.4%

Hi I am currently using the code below to find multiple values and the copy/paste into another sheet. What I would like to know is if it is possible to add the now date to a column in sheet 1 for the value found in sheet 3?Option ExplicitSub BBIB()Dim srchLen, gName, nxtRw As IntegerDim g As Range'Clear Sheet 2 and Copy Column Headings Sheets(2).Cells.ClearContents Sheets(1).Rows(1).Copy Destination:=Sheets(2).Rows(1)'Determine length of Search Column from Sheet3 srchLen = Sheets(3).Range("A" & Rows.Count).End(xlUp).Row'Loop through list in Sheet3, Column A. As each value is'found in Sheet1, Column A, copy it top the next row in Sheet2 With Sheets(1).Columns("A") For gName = 2 To srchLen Set g = .Find(Sheets(3).Range("A" & gName), lookat:=xlWhole) If Not g Is Nothing Then nxtRw = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1 g.EntireRow.Copy Destination:=Sheets(2).Range("A" & nxtRw) End If Next End WithEnd SubAny help much appreciated.

Answer:Find multiple words/values in Excel and add date

Hi!Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum. Then please repost your code, as copied from the VBE, so that it is easier for us to read.Thanks!Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

4 more replies
Relevance 75.4%

Hi,
This is probably a very simple macro but programming isn't my strong point.
I need an Excel Macro that will be able to scan the first column of a worksheet row by row for certain document numbers i.e. "09-005", "09-1052", "586463" and highlight them. There is a substantial list of these.
I managed to figure out that I need a range and how to highlight, but am getting stuck on how to find the multiple document number values.
Any and all help is greatly appreciated
Thanks in advance
 

Answer:Solved: Highlight Multiple Values Excel Macro?

8 more replies
Relevance 75.4%

Thanks to everyone who helped me with that last one.

What I'm stuck on now is an array that brings up all of he rows that contain a certain policy number in column A.

So Say We have;

TL123 X B A 3C
TL456 C B E 4D
TL586 X B A D4
TL456 C B A 3C
TL892 C A E 4F
TL586 C A E 2B

And someone here say put the value TL586 in cell m6 they would get an answer of;

TL586 X B A D4
TL586 C A E 2B

The current sheet is approximately 500 rows by 30 columns and will be growing daily, so running this through IF Calculations is taking about 2 minutes to recalculate each time, so that isn't viable at the moment.

Any help appreciated,
 

Answer:Solved: Excel - How to return a multiple of matching values?

6 more replies
Relevance 75.4%

I want to Highlight Multiple Values with an Excel Macro. I want the macro to loop though and grab the list/range of values from another sheet and use it in the Array.

I want to avoid having to enter such a big list of values for the Array. I want to use something like "SearchVal() = Range("Lists!A2:A62").Value" but I get an "out of range" error. Here is my code now.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Sub Highlight()
Dim SearchVal() As Variant
SearchVal() = Array("ALM", "AXV", "AZZ", "BCT", "BHN", "CRE", "CSI", "CVM", "DCH", "DEK", "DER", "ESE", "GCV", "HCO", "HND", "HSI",
"IBO", "ILW", "KCM", "LEO", "LES", "LPA", "MCY", "MEX", "MHS", "MRM", "MSL", "NCL", "NTX", "NZI", "PHC", "PLA", "PPH", "PSE", "PUV",
"PVN", "PWL", "RKC", "RSN", "SCM", "SEL", "SGA", "SHC", "SRG", "SVL", "SYV", "TGC", "THA", "TSL", "VAV", "VID", "VTA", "WHE", "WHH",
"WIH", "WWJ&qu... Read more

Answer:Solved: Highlight Multiple Values with an Excel Macro

8 more replies
Relevance 75.4%

....A................. ........B ..........................C

1. AMDRG.SA ........AMDRG.CCM.......CTFACmpMkt='AMDRG.SA' + 'AMDRG.CTA';

I am not sure how good that is going to look once i post this, but here is a rundown of what it is.

Lets say those are 3 columns, A, B, and C. I would like to have a macro or script that will take what is in column B, which is also inside the text of column C, and change the text AMDRG.CCM to what is in column A(AMDRG.SA). So when i am done, the info in column C will look like this...
CTFACmpMkt='AMDRG.SA' + 'AMDRG.CTA'
Column A and column B will always line up with eachother, but column C will not always line up right beside the cells associated with what the replacement text is.

Thanks for any help that you can give!
 

Answer:Multiple find and replace cell values in excel

Can you post a sample workbook with a few more records?

Rollin
 

3 more replies
Relevance 105.37%

Hi,
I have frequently used an array formula like this to look up multiple records in a list in Excel:
{=INDEX($A$1:$B$7),SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2)}
It nicely retrieves all the rows where A10 is the value to be looked up.

However I have not had any success using the same type of formula with an OR statement:
{=INDEX($A$1:$B$7),SMALL(IF(OR($A$1:$A$7=$A$10,$A$1:$A$7=$A$11)),ROW($A$1:$A$7),ROW(1:1)),2)}

I want to retrieve all the records in a list where the value is what is as in A10 OR A11.

Does anyone know how I can achieve this?
Thanks!!!!!
Janie
 

Answer:Excel - looking up multiple records of multiple values

7 more replies
Relevance 105.37%

Hi There

I have run into an issue with excel that's a little beyond my amateur coding skills

I think this can be solved with some simple vbscript or nested excel formula

All i need to happen is to compare a list of names and one additional value to another list of names and add the missing value. Easier if i give an example of data

the columns are as follows

Column A Column B Column C Column D
Name Value Name Value
Bob, Test Full Bart, Test
Bill, Test Part Bob, Test
Ren, Test Non Bill, Test
Bart, Test Part Ren, Test

So in column A and B is the reference data, and you see Column C and D is where the data needs to be matched / copied to. The script / formula needs to in this case work its way down column C - look up the matching value in column A and then copy whats in column B next to the matching value to column D.

Example is with the above data it would first hit in column C - Bart, Test - it would then lookup cell A for Bart, Test - and copy whats in column B beside it which is "Part" to blank cell in column D

Then it would move on to Bob, Test in column C - it would then lookup cell A for Bob, Test - and copy whats in column B beside it which is "Full" to blank cell in column D

I have a massive list of 3800 names that gets emailed to in a jumbled order and may grow in cell numbers as well so i cant count on a simple A to Z sort to help out - with additional information that i need to marry up to my existing data. I need an easy ... Read more

Answer:Solved: Basic Excel Question - Comparing multiple values

This is what I think you need (See attached file) Look at the formulas in column D. The first formula can be copied down that column as many times as you require.

HTH
 

3 more replies
Relevance 105.37%

OK... Here's something I have been looking at for a while and getting myself all in a tizz...!!!

I have 2 worksheets within 1 workbook - see attached example. The first contains unique references (ABx in Column A) with associated processes in Column B. Each cell may contain more than one process - comma seperated. The second sheet contains unique references (XYx in Column A) which map back to ABx references in Column B (again, each cell can contain multiple mapping references - comma seperated).

e.g.
Ref - Process
AB1 - Create, Update
AB2 - Create, Read
AB3 - Delete, Update

Ref - Maps-to
XY1 - AB1, AB2
XY2 - AB1
XY3 - AB2

Now, what I am looking to do is within sheet 2 (containing the XY references mapped to AB references) is create a column which will display the corresponding processes without duplication.

e.g.
Ref - Maps-to - Process
XY1 - AB1, AB2 - Create, Update, Read
*Note that AB1 and AB2 both contain Create which should only be returned once.

I have tried using =VLOOKUP(B2,'Reference Sheet'!A:B,2) but this only uses the first reference to return results and therefore using the above as an example the result would not include "Read".

Any takers? Hope someone can help...

Thanks in advance!
 

Answer:Solved: Excel: Cross Reference multiple values in cell

16 more replies
Relevance 104.14%

Hello,
I've searched and have not been successful to find an answer to my issue.
I have a workbook with multiple spreadsheets. Each spreadsheet has a list of employess with tasks and hours.
I need a macro that will search for the employee name and add the hours spent working on a task.
I also need to search for the different tasks and add the total hours spent on the specific task.
Workbook contains multiple tabs (Summary, week1, week2, week3, etc)
Each worksheet has three columns and employee name may be in the first column multiple times with different tasks:
Employee Tasks Hours
Jason Dyer dishes 8
Joyce Brown lawn 12
Donald Steiner vacuum 16
Jacqueline Lowe dusting 4
Robert Jones mopping 2
Barbara Fritts trash 8
Lisa Stillman painting 10
Antionette Adkisson repair 6
Billy Barkley errands 14
Ian Grayson sweeping 18

And the Summary has:
Employee Total Hours
Jason Dyer
Joyce Brown
Donald Steiner
Jacqueline Lowe
Robert Jones
Barbara Fritts
Lisa Stillman
Antionette Adkisson
Billy Barkley
Ian Grayson
Task Total Hours
dishes
lawn
vacuum
dusting
mopping
trash
painting
repair
errands
sweeping

Any help will be appreciated!
 

Answer:Excel 2007 search and match macro, add values in multiple spreadsheets

7 more replies
Relevance 104.14%

Hi all,

This may be more complex than I think but I have searched hundreds of forum posts all over the place and while I've come close to finding a solution to this; nothing has quite described a way to do this...

Basically, I have a roster for staff (attached is a simple sample I've thrown together to show you what's on the rows / headers & sample contents...obviously the actual spreadsheet is much bigger and month-to-view on each sheet).

Let's assume the following:
- sheet 1 and sheet 2 have staff rosters on as per the example spreadsheet
- sheet 3 is where I want to display the search results
- the same name may/will be present on different dates and different shifts
- this is for me and not an 'end user' so it doesn't have to be in any way flash in its working or pretty!!!

I would like a search entry box on sheet 3 (let's say in cell A1).

I need the following results returned for EACH occurrence of the searched name (this is where it gets beyond me). These will be copied into sheet 3 let's say starting from cell A3 - I'll stick in a clearcontents on the range at the start of the sub as the results will be copied into an email and then can be cleared when I need a new search:

- Shift (always found in Column A of the sheet being searched)
- Job Number (always found in in Column B of the sheet being searched)
- Date of shift (always found in Row 1 of the sheet being searched)

So essentially it's a lookup / fin... Read more

Answer:VBA Search Excel Workbook - Multiple Values, Return Row & Column Header

16 more replies
Relevance 102.91%

G'day,

I have a Microsoft Excel 2003 file that contains two spread-sheets. In one, I have a list of clients and a column next to each client name that I want to display the amount of money made from that particular client. Then, on the second spread-sheet I have a list of all credit and debits relating to the various clients. So, we might have received $100.00 from a client (and that would be in the credit column next to the client name), and then we may have spent $50.00 on that client (and that would be in the debit column next to the client name). So, obviously, the total amount made from that client would reflect the credit minus the debit.

Anyway, what I need is for the cell on the first spread-sheet that says the total made for that client to look at the second spread-sheet, look for any row that has that client's name and then adds the credits and subtracts the debits, then leaving the total back on the first spread-sheet.

I hope that makes sense. I have attached a demonstration to help, and I have also done the formula for the first client to show you what outcome I am looking for (although the formula is not what I want because it doesn't automatically add all of the rows from the second spread-sheet that share that client name).

Anyway, I hope I've explained it well enough and if anyone can help me I'd really appreciate it!
 

Answer:Solved: Adding Selected Values Together Over Multiple Work-Sheets In Microsoft Excel

hi blujein,

Attached is a quick solution.

Copy the formula I have added to the Total Amounts spreadsheet in Col B, down in new rows as you add new clients.

lol
Hew
 

3 more replies
Relevance 102.09%

Hi, I was wondering if anyone could help with conditional formatting across 25 worksheets to capture duplicate values across all worksheets (not within the one worksheet they all need to be linked). I have to insert the serial number of the product in two columns(some valuable product need only one cell to insert the serial number, so i merged the two cells of the two column to get a single cell) and there can never be a duplicate within any of them, across all 25 sheets. I need it for the whole two nearest column(I have merged some of the cells in these columns), I have tried formatting one whole column (which works within that column) and then used paint to format to another worksheet but it did not work? Stuck any advice would be much appreciated.

Answer:To find the duplicate values across the worksheet

conditional formatting across 25 worksheetsTo use CF across worksheets the easiest way is to use a Named Range.I have to insert the serial number of the product in two columns(some valuable product need only one cell to insert the serial number, so i merged the two cells of the two column to get a single cell) and there can never be a duplicate Using Merged cells can be a cause of problems.I actually think what you need is possibly a VLOOKUP()You enter the serial number, do a VLOOKUP(), it reports back it the number already exists or not.We would need more specific info, IE Column Letters, Row Numbers, etc.to offer more help.And of course, there is always using a MACRO to get the data.MIKEhttp://www.skeptic.com/

18 more replies
Relevance 101.27%

Is there a way to set the value of a cell to the name of that particular worksheet with a function???

sorry about the consecutive posts, i'm working on a project for work.

thanks for any help!
 

Answer:setting cell values to worksheet names

Hello:

Try using the function =cell("filename") in any cell. It will return the complete path including the tab name of the spreadsheet.

I found this by going to help and looking up information functions.

Hope this helps.

ddcrab
 

3 more replies
Relevance 100.45%

Hello all,I have two questions, could you please help me:1. I have many cells with values like that:25pq568925tyu568925ter568925QD5689I need to change letters with numbers (every letter correspond to appropriate number like a=02, b=05, etc...), and I expect to have in results:2505645692556456892501256892558956892. Next one, I have two columns:| number | name || 29 | N1 || 56 | N1 || 45 | N1 || 58 | N2 || 89 | N2 || 55 | N3 || 65 | N3 || 47 | N3 || 56 | N4 || 10 | N4 |I need to SUM values from column "number" for N1, N2... from column "name".

Answer:Sum of values by criteria from other cells

I'm a tad confused by your examples. Some of the "input" values have 2 letters and some have 3, yet your outputs don't seem to match those configurations.Were those supposed to be one-for-one inputs/outputs?Is this a 1 time thing or will you have to repeat this conversion on a regualr basis?As far as your second question, review the Help files for the SUMIF function.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 100.45%

I have multiple sheets (12 months) in my workbook. I am on the sheet "February Earning 2010" (third page in my workbook) and I am in cell P8.I need a total amount to show in this cell for all of the previous earnings on the prior two sheets. So I need it to refer to cell C8 because that is where the contract number is located. Then I need it to look for the contract number on:"December Earnings 2009" and "January Earnings 2010". On each sheet, the contract numbers are listed in column C. The values to be added up is where are get really confused.The values could be in Q8, S8, U8, V8 or W8 of the row.Each time the contract number (in row C of each page) is found, I want to add the amounts in the cells (Q8, S8, U8, V8 or W8). What formula should I use for this?

Answer:Sum of values in various cells if criteria me

Hi,Two questions:1. Do you only add data from one row on the preceding worksheets - i.e. is there only one row with the required contract number (and it's amounts) or do you need to add up the results from several rows, each containing the required contract number.2. If you have to get data from columns Q, S, U, V or W, if the value required was in column S (say S8) are Q8, U8, V8 and W8 empty. If not how can you decide which column to use.Regards

12 more replies
Relevance 99.22%

Hello

I am working on a comparison macro that takes information from my system and compares it to information from a different system. The problem lies in the format the other system comes in. I have been able to clean it up as much as possible but i just can't figure out the last little step.

If I have 15 containers on a an order and I am missing all 15 I need 14 rows inserted and all the information copied from the first line entered into the other 14. I thought i had this down but what i didn't take into consideration was that there are times when i have an order for 1 and missing 1 if that is the case i don’t need anything done. And then there are times when i have a an order for 15 i have 7 and it gives the names of the containers so i need 8 lines entered and this time when the information is copied i don’t need the container names it has to be blank.

I am going to attach a spread sheet of what i am talking about. The first tab is what it looks like when it comes in and the second tab is what i need it to look like. I color coded it to make it somewhat easier to understand. The rows in red are the original data and the rows in yellow are the ones i need added. I have asked this on several different forums and have not received a response at all hopefully someone here can help me with this. if you need more information i will gladly provide.
 

Answer:Inserting Rows for values in certain cells

6 more replies
Relevance 99.22%

If I have an EXCEL which has:Column A mentioning illumination status as FL or BL or NLColumn B has sq ft areaI want that Column C should automatically give the value as mentioned below:If A=FL, then value in C should be B multiply by 10If A=BL, then value in C should be B multiply by 25If A=NL, then value in C should be B multiply by 10

Answer:Value Calculation based on values in other cells

Here is a formula:=IF(A1="FL",B1*10,IF(A1="BL",B1*25,IF(A1="NL",B1*10)))if A1 = FL it will multiply value in C by 10if A1 = BL it will multiply value in C by 25if A1 = NL it will multiply value in C by 10

4 more replies
Relevance 99.22%

I need to add many individual cells together that are formatted as iferror, return "-". I do not want to change the "-" to 0 because i don't want to see a thousand 0's, but that is the only way i can get the value in the cell i want

Answer:adding cells with text values

Perhaps it would help if you posted the formula you are using so that we can get a better idea of your issue.If you are trying to count text values, perhaps COUNTIF or COUNTA will get you what you are looking for.Bottom line is that I don't think we have enough information to give you a concrete answer.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 99.22%

For an assignment I have to create a spreadsheet with columns that have given values. I am lost on how to assign a word a value without using to separate cells. Here is part of the assignment as an example:Constants needs the following fields: Target Markup % ( give this the value of 102%), Target Price ( give this the value of $4.00 ) Base Costs needs the following fields: Qty on Hand (a numeric input field; the sum of the Warehouse On Hand), Cost Per Unit (a currency input field; the average of the warehouse Costs per Unit), Inventory Total Cost ( calculated as Qty On Hand times Cost Per Unit )

Answer:How to give values to cells with wording

Why don't you want to use separate cells?It reads to me that the "words" you are concerned with are nothing more than Column headings.The only other thing I can think of is possibly Named Ranges. Look that up in the Excel Help files or DAGS. Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 97.99%

hi thereI have the same problem with positive and exact negative value in a column which should be delete.for example consider the below sample:Column A123-123554-333-554145-145234I just want the below result:Column A-333234as you see just the values with no negative one, remained.how can I write a macro to do this for me?would you please help

Answer:delete rows with cells in a column with th same +and- values

I'm sorry I did not notice the instructions...I really appreciate your answer.you are all right. I forgot to change the D to B. in my real data the D column play the B role in this example.any way...I tested the below code and fortunately. it worked out...Sub Delete_Stuff()

Dim lastRow As Long, myRow As Long, findRange As Range, findRange1 As Long, goalrow As Long, mynewrow As Long
Range("A1").CurrentRegion.Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlGuess
lastRow = Cells(Rows.Count, 1).End(xlUp).Row

For myRow = lastRow To 2 Step -1

If Cells(myRow, 1) < 0 Then

Set findRange = Range("A1:A" & lastRow).Find(Abs(Cells(myRow, 1)), LookIn:=xlValues, LookAt:=xlWhole)


If Not findRange Is Nothing Then

goalrow = findRange.Row
For mynewrow = goalrow To (myRow - 1) Step 1
If (Cells(mynewrow, 4) = Cells(myRow, 4)) Then

Rows(myRow).Delete
Rows(mynewrow).Delete
Exit For
End If
Next mynewrow
End If

End If

Next myRow

End Sub
Again thank you very much

6 more replies
Relevance 97.99%

hi
i have a sheet with a few listboxes. when selecting an item from a listbox it populates various cells. that works well using vlookup. but now i want to have the same cells populated when selecting a different listbox. so in essence the values from listbox A gets cleared first when selecting an item from listbox B.
any ideas will help thank you.
 

More replies
Relevance 97.99%

I want to add up the values in individual cells in a large spreadsheet. The cells are dotted randomly across this spreadsheet and not in rows or columns. Is there an auto sum route to this addition
thanks Peter

Answer:adding values in individual cells inExcel

You will have to select each cell one by one.

1 more replies
Relevance 97.99%

i have this code to copy the values from a cell in column G in column C. but i have a problem because this code copy the formulas from cells,that i want is to copy only the values not the formulas.is possible to modify the code?Private Sub Worksheet_Change(ByVal Target As Range) Dim A As Range, intr As Range, r As Range Set A = Range("G:G") Set intr = Intersect(A, Target) If intr Is Nothing Then Exit Sub Application.EnableEvents = False For Each r In intr If r.Value <> "" Then r.Copy r.Offset(0, -4) End If Next r Application.EnableEvents = TrueEnd Submessage edited by zomis

Answer:vba code to copy only the values from cells not formulas

First, a posting tip: Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum so that it is easier for us to read. As far as your question, replace this:r.Copy r.Offset(0, -4)with this:
r.Copy
r.Offset(0, -4).PasteSpecial xlValuesClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

5 more replies
Relevance 97.99%

Hi,Been messing around with this for a while, and other than having many nested IF functions, I can not seem to make this easier. If any one has some ideas, I would be happy to be directed the right way. What I have is as followsCells B5, F5, J5, and N5 are the cells that I require to total the 3 highest valuesThe cells between these have values in them as well, otherwise I would use the array B5:N5If B5 = 5, F5 = 8, J5 = 2 N5 = 3 then my total would be 16If B6 = 4, F6 = 3, J6 = 2 N6 = 3 then my total would be 10If B7 = 4, F7 = 4, J7 = 4 N7 = 4 then my total would be 12If I were using an array then the formula would be as follows=SUM(LARGE(B5:N5,{1,2,3}))So from this I need to change the format from an array to a list of particular cells within the rowmessage edited by tonygibb

Answer:Total three largest values from a selection of cells

have you tried this:=SUM(LARGE((B5,F5,J5,N5),{1,2,3}))Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 97.99%

I am making an inventory spreadsheet and want the current inventory cell to go red when it goes below the min quantity(in another cell). I use conditional formating to change it red but don't know how to use it for the entire column as it just compares it against the one cell. I want it to compare it to each of their corresponding row.Any help would be appreciated

Answer:Changing color of cells comparing values

With your minimum quantity values in A1:A5 and your actual quantities in B1:B5,Select B1:B5 and use this as the formula for Conditional Formatting:=IF(B1<A1,TRUE,FALSE)It will update for each cell, becoming=IF(B2<A2,TRUE,FALSE), etc.Just make sure you don't use any $ which will lock in the Row numbers.

2 more replies
Relevance 97.99%

I need to concatenate a range of cells (approx. 50) across a row (for many rows in my spreadsheet) and before each cell value, insert the cell's header row value. The header value and cell value needs to be separated by [:]Each header/value pair needs to be separated by [;]Example: Color[:]Red[;]Size[:]Large[;]Length[:]LongVB preferred. Many thanks!

Answer:Concatenate range of cells with header values

Without a more detailed explanation of how your sheet is laid out there's no way for anyone to write any code that will do what you ask. We don't know which Rows/Columns to reference, how many cells you need to concatenate, etc.If you are going to post an example of your data, please click on the following line and read the instructions found via that link.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 97.99%

Hi,

I have been trying to figure out if it is possible to use a VBA function to place values and formulas into excel cells. In the past, I have done so using Macros, but for my particular application, I want to be able to do so from within a VBA function. Any ideas on how to do this?
 

Answer:Using a VBA function to place values or formulas into cells

Hi
This might give you some ideas.
Sub FormulaInCells()
Dim MyRange As Range
Dim Total As Integer

'Setup Range Pointer
Set MyRange = ActiveSheet.Range("b1:b10")

'Placing Formula's in Range
With MyRange
.Formula = "=int(rand()*10)"
End With

'Using Excel Functions on a Range
MsgBox ("Sum Data & Autofit")
Total = Application.Sum(MyRange)
Range("a11").Value = "Total"
Range("b11").Value = Total
Range("b1:b11").Columns.AutoFit

'Restore Worksheet
MsgBox "Restore Worksheet"
RestoreWorksheet
End Sub
 

1 more replies
Relevance 97.99%

how do i get excel to show 325,987,654 as 325million

Answer:I want to get excelt to show the values in cells in millions

You can do it with a formula, but it is not a very good option.I would advise using a Macro.Here is a formula that should give you what your looking for:This formula expects your number to be a regular dollar amount WITH decimal places, IE 1001.12and using zero zero, IE 1001.00 does not work because Excel will strip off the trailing zeros, so you end up with 1001=IF(LEN(A1)>=10,LEFT(A1,LEN(A1)-9)&"Million",IF(OR(LEN(A1)=9,LEN(A1)=8,LEN(A1)=7),LEFT(A1,LEN(A1)-6)&"Thousand",A1))If your numbers NEVER have decimal places, you can use this formula:=IF(LEN(A1)>=7,LEFT(A1,LEN(A1)-6)&"Million",IF(or(LEN(A1)=6,len(a1)=5,len(a1)=4),LEFT(A1,LEN(A1)-3)&"Thousand",))But I would really advise getting a Macro, there are several out on the web.MIKEhttp://www.skeptic.com/

2 more replies
Relevance 97.17%

Hi,

I'd like some help with a formula please. Have been working on this for hour and can't figure it out...

I need to compare the values of two cells, then multiply the lower value by 2.
For example:

............Column A.....Column B......Column C (this is what I'd like to calculate)
Row 6........50.............40...............80
Row 7........30.............30...............60
Row 8........25.............29...............50
etc...

This is what I'd like to say in the formula:
If A6 is lower than B6, then multiply A6 by 2. If B6 is lower than A6, then multiply B6 by 2. If A6 and B6 are the same, then multiply either by 2.

Any help is much appreciated.
Thank you in advance.
F. Jones
 

Answer:Solved: Compare values in two cells and multiply lower value by 2

In c1 try =min(a1:b1)*2
 

2 more replies
Relevance 97.17%

=SUM(LARGE(C20:F20:I20:L20,{1,2,3}))This work the worksheet as required, however the selected cells may number up to 50I have tried the following but know that I am missing something, but I can't see what=SUM(LARGE((C20,F20,I20,L20),ROW(INDIRECT("1:C2"))))C2 is a volatile number, subject to change given a specific value from elsewhere in the worksheet, so I could be looking for the total of the largest 2 values, or the total of the largest 12 values.message edited by tonygibb

Answer:Total three largest values from a selection of cells Part 2

=IF($C$2=1,SUM(LARGE((C19,F19,I19,L19),{1})),IF($C$2=2,SUM(LARGE((C19,F19,I19,L19),{1,2})),IF($C$2=3,SUM(LARGE((C19,F19,I19,L19),{1,2,3})),IF($C$2=4,SUM(LARGE((C19,F19,I19,L19),{1,2,3})),0))))Thank you so much for your reply, I understand your explanation, but the method of CTRL,Shift,Enter would tend for the end User to get confused.My intention is that they are to only to create the value for C2 and that the calculations take place without any other key motion.I have created the above nested IF function which will do what I require.I just have to amend it slightly to accommodate the number of values to be Sum totalledAgain thanks for your help

7 more replies
Relevance 97.17%

Using a macro, in cell range D2:D151, find values between 0 and 8 and if true display a message in cell F151 "No Jackpot this week". Can you help ?

Answer:how to find values in range of cells and display message

It's not clear to me what you are asking for.You say you want to search a range and find values (plural) between 0 and 8. Do you mean you are looking for at least 2 values any where in that range that meet that criteria or do all values in that range have to meet that criteria, or do mean something totally different?I'm also not sure why you want a macro, but that answer might depend on what you are trying to accomplish.Just as a shot in the dark, this formula will return your message as long as at least 2 cells in that range are greater than 0 but less than 8:=IF(COUNTIFS(D2:D151,">0",D2:D151,"<8")>1,"No Jackpot This Week","")Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

4 more replies
Relevance 96.35%

I want to insert into my database; 1 of the fields is a 'created' field. eg if its August then is I need to create a variable in vb with August in it. ANd Date onComm is Today's date (now).But the rest of the fields are from an linked xcel spread sheet. How do I code this? At present I have in SQL
INSERT INTO CommCustomer ( Accno, Contractno, DateonComm, Amount, CommDate )
SELECT [ Acc] AS Accno, Milcar.[Contract No ] AS Contractno, Now() AS DATEonComm, [ Rec] AS Amount, MonthName([DateonComm],True) AS CommDate
FROM Milcar
WHERE ((([ Rec])<>0));

But I get invalid procdure call; I actually want it in VB.

sql = "Insert INTO CommCustomer (Accno,CommMonth,Contractno,DateonComm,Amount)" & _
"SELECT "[ Acc], [Contract No ], [ Rec], " & _
" FROM Milcar " & _
" WHERE Rec <> 0 ;"

So my question is how do I insert 2 fields that are not from the select clause - I actually need to use values from them?
 

Answer:Insert with Values and values from a Select clause

6 more replies
Relevance 96.35%

'm making a spreadsheet that i want to be able to sort by date. The problem is, I have different levels of specificity for the dates - for some, we just have a year ("2000") or month and year ("December 2014") or a range ("2013 - 2014"). i want those to remain displayed as quoted in parenthesis, but I know I need to go down to mm/dd/yy to keep the sort right. So I'm okay with "2000" sorting as 1/1/2000 - but I can't figure out a way to have a different underlying value from what it actually sorts on, other than the very tedious different custom formatting for different types of dates. Ideally: I'd want to have these columns - "Date (Sort)" and "Date (Display)"...I want to be able to hide the sort column, but still have "Date (Display)" sort based on the values in the sort column. if there's a way to conditionally format for how it's displayed based on whether i enter month/day or just year, but still have it treated as a date in mm/dd/yyyy format for sorting - that would be even better.
Is there a way to do this?

More replies
Relevance 95.94%

I'm trying to do a simple 2 column merge in an Excel file. The idea is to put both first and last names (currently in 2 separate columns) into one column. There is more data in the worksheet, address, telelphone etc - in other columns.

When the name columns are selected, then FORMAT CELLS, then ALIGNMENT, MERGE CELLS, it comes back with -

"The selection contains multiple data values. Merging into one cell will keep the upper-left most data only"

There's text only in all the cells in both columns.

When I click on OK - like yea, do it anyway - both first and last name columns are blank - and there is no merged column.

I have also tried to use the CONCATENATE function and it works great for 1 cell at a time, but does not do entire columns...

Is there another step I've missed in this ?

Thanks...
 

More replies
Relevance 95.12%

G'day,

Is it possible to have a cell show the total calculation of all the values in an entire column?

I have a column that will display the quantity of particular items, but because I will be constantly adding to this column and it will be constantly growing, I need a way for the cell showing the total to calculate the entire column, not just a limited range. Is this possible?

Any help would be greatly appreciated!
 

Answer:Solved: Calculating Values In An Unlimited Range Of Cells In A Column In Microsoft Ex

6 more replies
Relevance 89.79%

Good morning,

I will try to explain this best to my knowledge. I have a worksheet that will work as a report. This report will be based on Contracts. In one file (Physical) I can have multiple contracts. All linked to the same file number.

Example if file number 1234-45678 as 2 contracts then my code will automatically add the information required, adding 2 contracts for file number 1234-45678.
Therefore my initial row 5 (Example only) will add a new row underneath and will add the following information:

A5: 1234-45678 B5: 002 C5: 1234-45678/001/HS
A6: 1234-45678 B6: 002 C6: 1234-45678/002/HS

So my column A will always be the same linking the 2 contracts. 1234-45678/001/HS and 1234-45678/002/HS belong to file number 1234-45678.

If I need to make an amendment, in house an amendment is to make modification to a contract. In my column B I will always have the amount of contracts. In the example above the amount for Contracts is 002.

If I make an amendment and the reason of the amendment is a Termination, therefore cancelling one contract to create another one, I need to be able to change all information in my Column B vs All information from Column A. So for Example:

If
A5: 1234-45678 B5: 002 C5: 1234-45678/001/HS
A6: 1234-45678 B6: 002 C6: 1234-45678/002/HS
And I need to make the above modification then I need to add another Contract but change all information from my Column B (Made in a userform, already establish). So the above example will become:

A5: 1234-45678 B... Read more

Answer:Add Values in column And change Values in another column vs .find match function

The code for Sheet5 ("Report") is the following:
Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Application.EnableEvents = False
On Error Resume Next

'This function will add a new row if the number is higher than 002
If Not Intersect(Target, Range("M5:M1000")) Is Nothing Then
NumRows = Target.Value - 1
For R = 1 To NumRows
Target.Offset(1, 0).EntireRow.Insert
Next R
End If

'This function will copy the information onto the next rows adding a /??? and /HS
'This will depend on the number that the user has inserted in "Contracts" in Column B
If Not Intersect(Target, Range("M5:M10000")) Is Nothing Then
On Error Resume Next
For i = 1 To Target ' stops the code looping
tmpArr = tmpArr & "," & i
Next i
With Target

With .Offset(, 1).Resize(.Value)
.NumberFormat = "000"
.Value = Application.Transpose(Split(Mid(tmpArr, 2), ","))
.Formula = "=A$" & .Row & "&""/""&TEXT(ROW()-" & .Row - 1 & ",""000""""/HS"""""")"
.Value = .Value
End With

.Offset(0, -12).Copy .Offset(0, -12).Resize(.Value)
... Read more

3 more replies
Relevance 89.79%

I want to look up criteria over multiple sheets in Excel and return all the values of a cell that match that criteria. Similar to a Vlookup but over an undefined number sheets and to return multiple values. Can anyone help?

Answer:A Vlookup over multiple sheets and returning multiple values

This code assumes that the headings shown above begin in A1 in all sheets.Paste the code into the Sheet Module for Sheet1 and then enter a tracking number in B2.Private Sub Worksheet_Change(ByVal Target As Range)
'Check to see if change was made to B2
If Target.Address = "$B$2" Then
'If Yes, disable events
Application.EnableEvents = False
'Store Track Number
trackNum = Target
'Find last row in Sheet1
endRow = Range("B" & Rows.Count).End(xlUp).Row
'Clear Sheet1
Range("A2:I" & endRow).ClearContents
'Loop through Sheets
For shtNum = 2 To Sheets.Count
'Find last row and begin search for Track number
lastRow = Sheets(shtNum).Range("A" & Rows.Count).End(xlUp).Row
With Sheets(shtNum).Range("A1:A" & lastRow)
Set t = .Find(trackNum)
If Not t Is Nothing Then
firstAddress = t.Address
Do
'For each Track number found, Copy data to next Row on Sheet1
nxtRow = Range("B" & Rows.Count).End(xlUp).Row + 1
Range("A" & nxtRow) = nxtRow - 1
.Range("A" & t.Row & ":D" & t.Row).Copy _
Destination:=Range("B" & nxtRow)
.Range("E" & t.Row).Copy _
Destination:=Range("G" & nxtRow)
.Range("F" & t.Row).Copy _
Destination:=Range("F" & nxtRow)
.Range("G" & t.Row & ":H" & t.Row).Copy _
Destination:=Range("H" & nxtRow)
Set t = .FindNext(t)
... Read more

14 more replies
Relevance 89.38%

Hello,

I need help with a formula. I need the formula to calculate totals from 10 worksheet.

Totals to come from C164 from each sheet to total in the summary worksheet D20. I can't seem to get it past the first worksheet. This is the formula that I have. The work sheet are named xx1-xx10

=Sum(xx1!c164,xx2!c164,xx3!c164,xx4!c164,xx5!c164,xx6!c164,xx7!c164,xx8!c164,xx9!c164,xx10!c164) IIs this possible?
Please and thank you!

Answer:Excel Formula using multiple worksheet to calculate into a summary worksheet.

Moving thread to the Office Forum for better results

2 more replies
Relevance 89.38%

I have two columns, district and customers. i want to list the customers based on the users district input. vlookup will only return the first customer entry per district...any ideas?

Answer:Looking up and returning multiple values

Hi,Have you tried using the filter function.Select the the column of cells with the district data (including heading row)From the Menu bar select Data - Filter and then AutoFilter.This will give you a drop-down list at the top of the column.Select a district from the drop-down and only rows containing that district name will be shown - and you will see all customers for that district.The visible data can be copied and pasted elsewhere if required.Regards

5 more replies
Relevance 89.38%

Hi, I have a spreadsheet full of data, 2 of the columns being "New" or "Old" and the other being products "OTN" and "OT7". I want to count the number of cells that are both "New" and "OTN" and "Old" and "OTN" (and the same for OT7. I have tried using the countif and sumif formulas and can't find any that help. Any suggestions? Thanks in advance!

Answer:Count if with multiple values

Hiyou need an array formulasupposing you have "Yes" written in the column that signifies affirmative. So a Yes in column New mean it was New and a Yes in OTN meant it was an OTN and suppose New was in column A Old in column B OTN in column C and OT7 in D and there are 123 rows of data.The number of New and OTN would be=SUM((A2:A123="Yes")*(C2:C123="Yes")*1)you enter this, and then in edit mode hold down shift and Ctrl and hit Enter and it would give you the number of New and OTNAndrew

4 more replies
Relevance 89.38%

Code:

#include <iostream.h>

using namespace std;

struct mystruct{
int x;
char y;
float z;
}example, example2;

mystruct myfunction(void);

int main(){
example2 = myfunction();
cout<<example2.x<<" "<<example2.y<<" "<<example2.z;
cout.flush();
cin.get();
cin.get();
return(0);
}
mystruct myfunction(void){
cout<<"Enter a integer: ";
cin>>example.x;
cout<<"Enter a char: ";
cin>>example.y;
cout<<"Enter a float: ";
cin>>example.z;
return example;
}

Is there a way to return multiple values in a function without having to define them in a structure and simply returning that?

Much appreciated. Thanks in advance.

-Penny
 

Answer:C++-Returning multiple values

9 more replies
Relevance 89.38%

I want to look up multiple values in a column and give back the same value.So if I search my coumn for A and B and C, I want it to return a value of X and then look up D and E and F and return a value of Y.Can anyoneplease help, I'm starting to confuse myself

Answer:Returning one value after looking up multiple values

I do not understand what you are trying to do.Please post an example of your data, both the input data and the desired output.Please click on the following line and read the instructions found via that link before posting your data. Thanks.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 89.38%

In Sheet1 here are almost 6000 account no. I want to give input multiples account numbers at a time and find those numbers in sheet1 and if it finds then marks the whole row so that i can understand which nos. are found.

Answer:Search Multiple values

OK, now we know what you want.If you need any help with that, feel free to ask.

4 more replies
Relevance 89.38%

I'm trying to create a macro to copy the last row of data from individual cells from one worksheet to another here is what I have so far.[]Sub UpdateLogWorksheet() Dim DataLogEcnWks As Worksheet Dim RptEcnCmpltWks As Worksheet Dim nextRow As Long Dim oCol As Long Dim rngC As Range Dim myCopy1 As String Dim myCell As Range 'cells to copy from DataLogEcn sheet - some contain formulas myCopy1 = "A2,C2,O2,CG2" Set rngC = Range(myCopy1) Set DataLogEcnWks = Worksheets("DataLogEcn") Set RptEcnCmpltWks = Worksheets("RptEcnCmplt") With RptEcnCmpltWks nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).row End With With DataLogEcnWks Set rngC = Range(myCopy1) For Each myCell In rngC.Cells RptEcnCmpltWks.Cells(nextRow, oCol).Value = myCell.Value oCol = oCol + 1 Next myCell End With End Sub{}

Answer:copy the last row of multiple cells to other worksheet

SolvedSub UpdateLogWorksheet()

Dim DataLogEcnWks As Worksheet
Dim ReportCompleteLogWks As Worksheet
Dim OpenEcnLogWks As Worksheet
Dim ReportOpenLogWks As Worksheet

Dim nextRow As Long
Dim oCol As Long

Dim rngC As Range
Dim rngD As Range
Dim myCopy1 As String
Dim myCopy2 As String
Dim myCell As Range

'cells to copy from DataLogEcn worksheet - some contain formulas
myCopy1 = "A2,C2,O2,CG2"

'cells to copy from OpenEcnLog worksheet - some contain formulas
myCopy2 = "A2,C2,E2,O2"

Set rngC = Worksheets("DataLogEcn").Range(myCopy1)
Set rngD = Worksheets("OpenEcnLog").Range(myCopy2)

Set DataLogEcnWks = Worksheets("DataLogEcn")
Set ReportCompleteLogWks = Worksheets("ReportCompleteLog")
Set OpenEcnLogWks = Worksheets("OpenEcnLog")
Set ReportOpenLogWks = Worksheets("ReportOpenLog")

With ReportCompleteLogWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).row

End With

With ReportOpenLogWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).row

End With

With DataLogEcnWks
Set rngC = Range(myCopy1)
oCol = 1
For Each myCell In rngC.Cells
ReportCompleteLogWks.Cells(nextRow, oCol).Value = myCell.Value
oCol = oCol + 1
Next myCell

End With

With OpenEcnLogWks
Set rngC = Range(myCopy2)
oCol = 1
For Each myCell In rngC.Cells
R... Read more

2 more replies
Relevance 89.38%

An appeal to any Excel wizzo's out there!I have a simple sum ie, a1:a10 in a cellIf that value is zero, how can I get it to leave it blank?

Answer:Zero Values in Excel

=IF(SUM(A1:A10)=0,"",SUM(A1:A10))

2 more replies
Relevance 88.15%

Hi,

I built a large macro a while back that I need to reduce because with the recent additions I have made, I got "Procedure too large" as an error upon execution. I've already condensed most of my coding to something more elegant (I keep learning new things every time). Now I'd like to reduce the following code :

Do Until ActiveCell.Column = LastCol + 1
If ActiveCell.Value = "resRespondent" Or ActiveCell.Value = "ResLastCallDate" Or ActiveCell.Value = "LastCallStartTime" Or ActiveCell.Value = "resCallCount" _ ''And it goes on like this for many more values
Then
ActiveCell.Offset(0, 1).Select
Else
ActiveCell.EntireColumn.Delete
LastCol = LastCol - 1
End If
Loop

This is actually a big chunk of code and I have many more of these chunks in my macro. The problem is always having to repeat "Or ActiveCell.Value = " before each test. Something along the following idea would be great:

If ActiveCell.Value = "this", "that", "or this", "or that" Then

Thanks,
Peatawn

Answer:VBA - Test cell for multiple values

You can use SELECT CASE instead of IF, which allows such multiple values when comparing the same variable to many constants:


Code:
SELECT CASE ActiveCell.Value
CASE "resRespondent", "ResLastCallDate", "LastCallStartTime", "resCallCount"
ActiveCell.Offset(0, 1).Select
CASE ELSE
ActiveCell.EntireColumn.Delete
LastCol = LastCol - 1
END SELECT
Which is functionally equivalent to your example. Try it, as I've not actually tested it.

2 more replies
Relevance 88.15%

I am preparing year end numbers for one of our clients. I have several different worksheets I am working on in one workbook. I have to take totals form one worksheet and add them to another to get final totals. My problem is that when I try to copy and paste the one set of totals, they are not the correct numbers. For instance: the total is 687,862.00, when I paste to the next worksheet is is 677,452.00. Does anyone know what I may be doing wrong, and what I need to do to fix this.

Answer:Need Formula To Sum Values From Multiple Sheets

Are you Pasting the Formula or are you Pasting the Values?From the Edit menu, select Paste SpecialThe Paste Special dialog box should appearYou can now select either to Paste just the ValuesorPaste the FormulaThe default behavior is to Paste the Formula.And just so you know, Excel questions are best asked in the Office Software Forum.MIKEhttp://www.skeptic.com/message edited by mmcconaghy

4 more replies
Relevance 88.15%

Hello, I am trying to create a macro that searches a sheet (Sheet 1, column AR) for a list of values found in column A of Sheet 3. After a match is found, I would like to compare the value from the row that has been found from column B in Sheet 3 to Column AP in Sheet 1. If it matches on both columns in the same row, I want it to copy the entire row from sheet 1 into sheet 2. So far my code will accomplish the first part, but I have no idea how to make it compare the value in the second column with the row that was found. Any help would be greatly appreciated! Here is my code: (I found most of the code in forums and then modified it a little bit)Sub valueFinder() Dim srchLen, gName, nxtRw As Integer Dim lastName As Range, ff As String 'Clear Sheet 2 and Copy Column Headings Sheets(2).Cells.ClearContents Sheets(1).Rows(1).Copy Destination:=Sheets(2).Rows(1) 'Determine length of Search Column from Sheet3 srchLen = Sheets(3).Range("A" & Rows.Count).End(xlUp).Row 'Loop through list in Sheet3, Column A. As each value is 'found in Sheet1, Column AR, copy it top the next row in Sheet2 With Sheets(1).Columns("AR") For gName = 2 To srchLen Set lastName = .Find(Sheets(3).Range("A" & gName), lookat:=xlWhole) If Not lastName Is Nothing Then ff = lastName.Address Do nxtRw = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1 ... Read more

Answer:Matching values In Multiple Columns with VBA

First, a posting tip:Please click on the following line and read the instructions on how to post VBA code in this forum in a manner that will make it easier for us to read:Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

6 more replies
Relevance 88.15%

I have an excel spreadsheet or access database with 3 columns: "ID#"; "Name"; "Case#"1 Jon Smith 1231 Jon Smith 3212 Jane Doe 456How can I run a formula or query to return all the case numbers associated with a particular person? For Example:Name CasesJon Smith 123, 321Jane Doe 456

Answer:Return multiple values for one person

I would like to clarify that I need the returned values (cases) to be contained within a single cell.Thank you!

7 more replies
Relevance 88.15%

Tech Support Guy System Info Utility version 1.0.0.4
OS Version: Microsoft Windows 7 Home Premium, Service Pack 1, 64 bit
Processor: AMD A6-4400M APU with Radeon(tm) HD Graphics, AMD64 Family 21 Model 16 Stepping 1
Processor Count: 2
RAM: 7650 Mb
Graphics Card: AMD Radeon HD 7600M Series, -2048 Mb
Hard Drives: C: 909 GB (840 GB Free); D: 21 GB (2 GB Free);
Motherboard: Hewlett-Packard, 18A7
Antivirus: Norton Internet Security, Enabled and Updated

The Uploaded File MULTIPLEMAX contains rows of numbers from 1 to 12 .
In many rows there are many matches eg In row 1 the number 6 is matched twice - in row 4 there two matches equal to 7 etc

All values in any row have equivalent values in another row in another table which can be determined using VLOOKUP or INDEX MATCH.
Unfortunately these formulae will only find ONE of the repeated matches and not all of them
I have googled this question on various forums but the answers only apply to columns of data and not rows as is the case in MULTIPLEMAX.
Is there any excel formula sequence which will find all these repeated matches .
Please advise.

Regards

FLYNNE
 

Answer:Finding Multiple Matches and their values

6 more replies
Relevance 88.15%

i'm trying to sum up time values (in hours and minutes). Ex: 14.48 15.21Total sum should be 30.09. Sum formula is considering minutes as 100 minutes and the result is 29.69. Can someone assist with the correct formula.Thanks,Ali

Answer:sum time values in excel

Try this:Convert your numbers to Time: 14. 48 > 14:4815.21 > 15:21Notice all I did was to replace the Dot with a ColonNow Excel knows it's dealing with TIME, not just numbers.Next you will need to Format your total cell as: [H]:MM:SSThe brackets are required.You will find the format under CustomSo your data should look like this:
A
1) 14:48
2) 15:21
3)
4) 30:09:00 <= Formula =SUM(A1:A2), formatted as [H]:MM:SS
MIKEhttp://www.skeptic.com/

5 more replies
Relevance 88.15%

Ms Excele.g A B C D1 Deal Date Value Date Diff. Type2 01/05/11 03/05/11 B2-A1 (2) Ready/Spot/Forward3I want in Column D2, if C2=0,"Read", or C2>=1 and C2<=3,"Spot", or C2>=4, "Forward"What would be the Formual

Answer:I have to Compare three values in Excel

Try this:if C2=0,"Read", or C2>=1 and C2<=3,"Spot", or C2>=4, "Forward"=IF(C2=0,"READ",IF(AND(C2>=1,C2<=3),"SPOT",IF(C2>=4,"FORWARD","")))See how that works.MIKEhttp://www.skeptic.com/

5 more replies
Relevance 88.15%

Hello,I am trying to add values related to individuals from one worksheet to another worksheet. Is there a way to add the scores from sheet No 1 on sheet number 2?(Worksheet 1)Name ScoreJohn 23Mike 76Jason 28John 99Mike 21Jason 18Worksheet #2Name Total ScoreJohn 99Mike 21Jason 18Your help will be greatly appreciated!

Answer:Adding Values in Excel

Hi,If your example data is in cells A1 to B6 on worksheet "Sheet1"andthe three names/scores on "Sheet2" are in cells A1 to B3then enter this formula in cell C1 on Sheet2:=SUMIF(Sheet1!$A$1:$A$6,A1,Sheet1!$B$1:$B$6)Drag the formula down a further two rows alongside the names/scores and this is what you get: A B C
1 John 99 122
2 Mike 21 97
3 Jason 18 46
If you wanted column C to include the scores on "Sheet2" then use this in C1:=SUMIF(Sheet1!$A$1:$A$6,A1,Sheet1!$B$1:$B$6)+B1I wasn't sure from your post if there were already scores on "Sheet2" as you showed, or if you just had a list of names and wanted the totals from "Sheet1". If so the first formula works, but start with it in cell B1Regards

3 more replies
Relevance 88.15%

I just can't get new data to display on this graph (see below):

[IMG]http://www.**************/imgbnc.php/628220c5ad14e56fcc9c18f3a30331450461e373d13e7498ffb7e5c39e8d57af6g.jpg[/IMG]

I enter the values in the table on the left, and they won't show up in the graph! This has just started happening, as you see, the first six sets of values are displaying fine.

Why?!

I have the correct cells selected, by the way.

Answer:Can't add values to Excel graph.

Well, I would've posted an image, but it just won't display, whatever I do.

Ah, it's just one of those days, ain't it...

4 more replies
Relevance 88.15%

Can anyone tell me how I can display negative values in a spreadsheet to display brackets. It sems to drop the brackets and change to red. I don't mind the red but I would like the brackets to be displayed. I have tried formatting cell to accounting and currency and get same result.Many thanksCilla

Answer:Excel - negative values

Use a custom format like0.00;(0.00)

1 more replies
Relevance 88.15%

Hi Everybody,

I'm trying to do the following thing.

I got an excel sheet with different data in it. Now I want to automatically show the last 5 filled in data per column.
Now I got it working for when each row is filled in (see attachment). But I also got columns where not every row has a value. Is it possible to get the 5 last filled in data of these columns also?

See the examples in the attachment.

Does anyone know how to solve this problem?

Thanks in advance,
Willem
 

Answer:Solved: Get last 5 values in Excel

8 more replies
Relevance 88.15%

hi all
I have 3 shapes on a worksheet that are assigned a macro each, which in turn shows a value in a cell when a shape is clicked (see code below)

Sub Double1p1()
Application.ScreenUpdating = False
ActiveSheet.Unprotect
Range("x3").Value = Range("x3").Value + 2 Assigned to shape 1
ActiveSheet.Protect
End Sub

Sub Single1p1()
Application.ScreenUpdating = False
ActiveSheet.Unprotect
Range("x4").Value = Range("x4").Value + 1 Assigned to shape 2
ActiveSheet.Protect
End Sub
Sub Treble1p1()
Application.ScreenUpdating = False
ActiveSheet.Unprotect
Range("x5").Value = Range("x5").Value + 3 Assigned to shape 3
ActiveSheet.Protect
End Sub

What i would like to do is add 3 option buttons,
if option button 1 is selected and shape 1 is clicked then the value for that shape is shown in cell X3,
if option button 2 is selected and shape 1 is clicked then the value is shown in cell Y3
if option button 3 is selected and shape 1 is clicked then the value is shown in cell Z3
the same to be done for the other shapes with the values being shown in cells X4,Y4,Z4,X5,Y5,Z5 Respectively,
what is the easiest way to achieve this,any help very much appreciated.
 

Answer:excel shapes and values

Are these "shapes" embedded in the worksheet or did you create a stand alone form? Have you already added the option buttons?

Rollin
 

3 more replies
Relevance 88.15%

I have rows with up to 5 separate values - ie SD, D, N, A & SA (responses to a questionaire) for each person responding to the survey. How do I sum all the responses (up to 100 to 20 questions) at the end of each row so that it tells me that for Q3 eg, there were 20xSD, 10xD, 35xN, etc?

Answer:excel sum 5 different alphabetical row values

Read up on the COUNTIF() function in the Excel Help files.You can string (concatenate) COUNTIF's and text strings together if you need all of the information in a single cell.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 88.15%

Hi
stuck on this one guys and girls. I have a spreadsheet, in which some values are >100 eg 116.3, 109.2 etc what I would like to do, is if the value is over 90 then the text to be blanked out so that it is not visible, but when the value is at <90 it shows normally.

cheers in advance

david

Answer:Excel - blanking values of certain value

you could use conditional formatting
and change the text to white - so it will not be seen - unless you click on the cell
if you want the cell to be completely blanked out , then you need VBA

Select the range
add a formula
=cell>=90
format white

other wise , perhaps upload a sample sheet , so we can help with specific formula

1 more replies
Relevance 88.15%

Hi,I have an Excel sheet with time values in column A (for example from 0s to 600s) and I would like to calculate the average value in column C of values in column B every 15s (from 0 to 15, 15 to 30...). The thing is that it can happen that I will only have 2 values in B between 0s and 10s (=2 rows) but maybe 15 values between 515s and 530s.Don't know if I am clear. Is there anyone who can help with that?Thanks:

Answer:Average between 2 values on Excel using VBA

re: "Don't know if I am clear."Not to me.Read the How To referenced in the next line and then post an example of your data along with the expected result.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 88.15%

heres an example of my sheet:

sheet1: actually much bigger
A--------B
£54-----Y
£76-----N Y paid
£23-----P N not paid
£55-----Y P pending
£97-----P
£33-----N

sheet2:

A---------------------B
total paid----------
total unpaid------ so i need to sum the values of paid/unpaid/pending individually and automatically
total pending----- as simply as possible as the sheet has a really long list
 

Answer:excel summing values

Closing duplicate.
 

1 more replies