I am trying to find the code that will help me look in column A in sheet 2 and match it to Col B in sheet 1. If match is found it will then Bold all the values in that row.
I have attached the excel file
How to count the bolded cells In an excel sheet using the formula
View 1 Replies View Relatedi have two sheets, one to display results (Reults tab) & the other tab containing the data (Data tab)
what i am trying to do is some how create a search function and have a forumula which contains a LIKE function that looks up the data table
RANGE = Data!A2:K255
the search needs to lookup the primary column Data!B2:B255 ... if any results are found .. show them on the results tab.. and if multiple results are found, display those as well.. (in either instance, the whole row of information in respect to the results need to be dislayed and hopefully no duplicates are found .. eg, Data!A:K of a hit)
is there a formula that can achieve this? oh, the search is TEXT based and there should be no empty cells within the dataset
after some MASSIVE googling, i have stumbled accross this
B1 = Search box (txt field)
A6 (which will be a hidden column) contains =MATCH($B$1,Data!A2:A255,0). this formula provides the first instance of the result and provides the row number
A7 contains =MATCH($B$1,OFFSET(Data!$A$1,A6+1,0,8-(A6+1),1),0)+A6.
this is supposed to look for the next row number which contains a match and provide that row number
and througout my other columns, i have
B6=OFFSET(Data!$A$1,A6,1)
B7=OFFSET(Data!$A$1,A6,2)
B8=OFFSET(Data!$A$1,A6,3)
and so on
2 things i cannot recitify..
1, the match has to be EXACT ... unfortunately i cannot use exact .. needs to be LIKE .. eg, i cant use the search word "boat" as the range of data has "boats"
2, it comes up with multile .. irrelevent results.
see attached workbook. I want VBA to insert an index/match forumla on sheet 1 to lookup a value from sheet 2. I don't want it to specify a range though. I want VBA to look to see if there is data above and to the left of the cell and if it is true insert the index/match formula. Then it won't matter what row or column I put the headings in.
View 2 Replies View RelatedI am merging columns. Col B has all text in bold. Col C has all text in regular font. When I use ASAP to merge, the merged column comes out all bold. When I use a formula to merge the merged column comes out all regular. What I want is for the merged column to contain the bold text from Col B in bold, and the regular text from Col C in regular.
What I want is...
This is for service plan B: Take all data and transer to new server.
What I am getting is...
This is for service plan B:Take all data and transer to new server.
OR
This is for service plan B:Take all data and transer to new server.
I want to be able to paste the large amount of data I get each day into the first sheet in my workbook ("data dump" sheet), the data will be formatted in the way i have used in the example workbook.
I then would like to be able to just have to enter 2 values into columns A and B of "1st output" sheet (Label in A, and Item in B), then have it look up the data that corresponds to those 2 values that are both in the same row in the "data dump" sheet, and have it copy and paste all of the relevant data in that row into the row in the "1st output" sheet.
The data I would want excel to find and place for me in the first sheet is highlighted in yellow
As a way of keeping track of this every day, I then need the data from the "1st output" sheet, to be fed into the "2nd output" sheet. This sheet would have all of the peoples' names already in it, but would just need to look in the "1st output" sheet for references to these names. If one of the names pops up, I'd need the date shown in the row of that instance to be placed after their name. This will allow me to keep a log of how many times their name gets flagged up, as well as the dates on which it happens. Again, the data I'd need excel to look up in the "2nd output" sheet is highlighted in yellow.
If there was a way of having the 2nd sheet add new names it finds on the 1st output sheet, that aren't already referenced on the 2nd output sheet in column A, that would be amazingly handy if it could then enter that name into column A and start a new row for them
Example file :
Attachment 334122
Alternative link [URL]
In my workbook I have multiple sheets but I'm attaching a very simple workbook to demonstrate what I'm trying to accomplish. In my "Lookup" tab/sheet. I want to have known Latitude and Longitude data that will exist in columns A&B. Columns C & D will have address numbers and Street Name. I would like my lookup formula to find the longitude and latitude data from my "lookup" sheet, when the matching address information is typed in, in my 2009 sheet. I have to keep the street numerics and street name separate on this worksheet as well. I believe I'll need two separate lookup formulas as I need these formulas to start in cell G4 & H4 in my "GeoCoding1" sheet. Is it possible to have four columns of data to be viewed in a lookup formula? I tried this formula in cell G4 (GeoCoding1 sheet)
View 3 Replies View RelatedI want to be able to paste the large amount of data I get each day into the first sheet in my workbook ("data dump" sheet), the data will be formatted in the way i have used in the example workbook.
I then would like to be able to just have to enter 2 values into columns A and B of "1st output" sheet (Label in A, and Item in B), then have it look up the data that corresponds to those 2 values that are both in the same row in the "data dump" sheet, and have it place all of the relevant data in that row into the row in the "1st output" sheet.
The data I would want excel to find and place for me in the first sheet is highlighted in yellow
As a way of keeping track of this every day, I then need the data from the "1st output" sheet, to be fed into the "2nd output" sheet. This sheet would have all of the peoples' names already in it, but would just need to look in the "1st output" sheet for references to these names. If one of the names pops up, I'd need the date shown in the row of that instance to be placed after their name. This will allow me to keep a log of how many times their name gets flagged up, as well as the dates on which it happens. Again, the data I'd need excel to look up in the "2nd output" sheet is highlighted in yellow.
If there was a way of having the 2nd sheet add new names it finds on the 1st output sheet, that aren't already referenced on the 2nd output sheet in column A, that would be amazingly handy if it could then enter that name into column A and start a new row for them
Example file : ExampleExcel.xls
Alternative link : [URL] .....
I want to do a loop where you can copy say A3 worksheet 1 then add another sheet naming the work sheet "A3" then copying A3 worksheet 1 to A1 "A3". After that looping to A4 to a new work sheet naming the work sheet "A4"copying the value to A1 "A4", etc...
Is there a simply way of doing this loop? I can probably fit my other coding into the structure.
I have a sheet of unstructured data, data that cannot be easily turned into structured data.
What I would like to do is find a way to search across the sheet for a value and return the value that is found one cell to the right.
I cannot use, as far as I am aware, VLOOKUP or HLOOKUP as I have no idea in which column or row they are to be found. INDEX and MATCH, I can't get to work.
I have sheet 2 with a list of numbers/text in column A and column B.
I need a code that will look on sheet 1 to see if both the numbers are in a specified range and highlight the row if they are not the example may explain better.
As you can see on sheet 1 the word Test in C has in AB the words Today, Yesterday and Friday but because Tomorrow is next to Today on sheet 2 it is coloured in because it is not in the range according to column C.
The word Test2 in C has Monday and Tuesday in AB and because they are next to each other on sheet 2 that does not need to be coloured.
Finally Test3 has July and August within the range but no September according to sheet 2 so needs colouring in.
And so on down the file which is about 20000 rows.
Sheet2
AB1TodayTomorrow2Monday Tuesday3August September
Sheet1
CAB1TESTTODAY2TESTYESTERDAY3TESTFRIDAY4TEST2MONDAY5TEST2TUESDAY6TEST3JULY7TEST3AUGUST
I have some experience with excel, but until now have not ventured into VBA and macros.
I have a workbook which will have the following sheets:
1.Absence Summary sheet - Summarises data from each employee's individual sheet.
2. Template Sheet - A sheet formatted as an absence record sheet, but without data.
3. Individual employee Absence record sheets - Based on the Template sheet.
I have read with interest the various posts and help files on User Forms & Macros, but have got a bit stuck.
My Aim: ....
I am trying to construct a way to return a set of values from multiple sheets onto one overview sheet, based on just changing a week number in one cell. I have attached a basic form sheet.
In the "results" sheet I would like to change the week number 1, 2, etc and with that change, return the values in C9, C11, F11, J11, M11 to refer to the worksheet of that week number
I have a long list written twice in 2 worksheets worksheet A has a list with some of the numbers repeated. worksheet B has the same list (none repeated) and another list with new numbers beside it. What I need is to take the new numbers from worksheet B and put them next to their correlating number in worksheet A. With many of the numbers being repeated I need something to identify and repeat the new #. I'd copy and paste and drag etc. but there's about 21,000 numbers to go through.
View 3 Replies View RelatedWorksheet #1:
Column "A" going down (starting at A1 to A5) I have the numbers 1,2,3,4,5 entered in each cell...
Worksheet #2:
In cell A1 is the number "1"
In cell A2 is the number "7"
I want a formula in cell B1 (WS#2) that looks for the number in cell A1 (WS#2) in the range of cells A1:A5 on Worksheet #1, and if it finds the value of A1 (WS#2) in that range of cells on Worksheet #1, it returns the letter Y... if not it returns the letter N
So my result on Worksheet #2 should be...
Cell B1 shows the letter Y
Cell B2 shows the letter N
I've tried to find the solution and the options I've tried I just can't get the code or syntax correct...
I have a workbook with 11 sheets in, 10 data sheets, and a summary
On sheet call "CB", I want to select any cell, then, via VBA, on sheet "Summary", in cells b4 to b14, where a4 to a14 have the sheet names, I want to reference the cell I selected, using the Indirect function....
something like
Code:
SelCell = Activecell.Value
Range("b4").Select
Activecell.FormulaR1C1 = "=INDIRECT(""'""&R[0]C[-1]&""'!SelCell"")"
I know it's incorrect.
Formula is on Sheet1 and table array is on Sheet1
but the lookup value is on Sheet2 in Cell B15
Below does not work, either does anything I have tried.
=VLOOKUP(Sheet2!B15,B138:E161,4,FALSE)
I have 2 sheets in my workbook "Master Sheet" and "Weekly". The "Master Sheet" contains Parcel Numbers and the groups they belong to, this information will rarely change. The "Weekly" sheet contains data that is pulled from a report weekly. It has 4 columns, the "Parcel Number", the "Parcel Name" the "Availability" of that parcel, and the "Group" the parcel belongs to.
I want to create a macro assigned to a button then when pressed looks at the number in the "Weekly" sheet and searches for it in the "Master Sheet" and populates the corresponding "Group" cell in the "Weekly" sheet with the value it finds in the "Master Sheet". I have attached a sheet that shows an example of what I need.
Is there a way to get the data in the last row of a sheet and show it in another workbook? And also maybe the 2nd last row?
How to make the table treat like a database and gives the cost for each day alone.
depeanding on prices sheet list
( For more please see th file ..)
I am writing the code for a VLOOKUP in VBA..I was using the .Formula = "=VLOOKUP(LookupValue, LookupRange , Column No, 0 )"
But, the problem is that the LookupRange is to be done from different sheets everyday as the name of this sheet is going to be like 16th May,17th May etc.
The common thing is that this sheet is the adjacent sheet next to the one in which we are trying to get the VLOOKUP work...so what solution can i use.
I am trying to find a function that will scan down a column to find a particular criteria. I would like all the cells that meet this criteria to have their entire row pulled through to a new sheet.
For Example, In the attached spreadsheet, I would like everyone in sheet 1 to have their details pulled through to sheet 2 if they are in class 1, sheet 3 if they are in class 2, and sheet 4 if they are in class 3.
What I a trying to do is I have a sheet with 3 pages and am using excel auto complete to change data in my cells to save typing out every time. for example if I type 22.. in sheet 1 cell a1 and tab away, it replaces it with an address and post code stored in auto complete. is it possible to store my info in another sheet, if it matches the 22.. in say sheet 3 replace it with the text in the next cell to the right
View 5 Replies View RelatedI have a spreadsheet that has a resource table, project stage table and an approx 50 different project sheets. (The 50 sheets are duplicated layouts, just different project names)
Based on the data on the first two sheets I would like to populate the individual project sheet.
For example:
1.In the individual project sheet there are 5 stages in each quarter.
2.The project stage table sheet tells you want stage the individual project is in for the relevant quarters.
3.The resource table sheet tells you how many resources are required for that stage.
Based on this information, I would like to populate the individual project sheet with the information.
E.g. If the project is in the 1st stage, it would then go to the resource table and take number of resources allocated for that stage and populate the “relevant” field in the individual project sheet with the correct value.
I've attached the spreadsheet to hopefully better illustrate this.
We use an accounting purchasing program that stores it's data in Access format. I have an Excel sheet that does a query to that data source and returns inventory part numbers, descriptions and pricing.
This file is about 3mb. Using this file as a data source (was trying to make it portable so the sales people can take the data with them) I created a quoting sheet, where they can lookup parts and have all the info pull into the quote so they can give customers a quote with fairly current info. At first I had my search routine copy and paste in links to the info, but that created a problem when the sales person wants to open an old quote to see what they gave someone. So then I had it just paste values, which works fine except when the quote has expired and the sales person wants to keep all the same parts but wants current pricing. I was thinking something like index(match) but VBA code wouldn't let me do that. I was hoping to write code for a button that they can click and it fetches current info.
I am trying to copy onto sheet1 Colum FH date from sheet2 Colum E by matching Colum I on sheet1 with Colum B on sheet2. I would like to know if its possible.
View 3 Replies View RelatedI want to be able to copy a name from one sheet (Available Players), paste it to a cell in another sheet (Round 1 through Round 20). The cell that will be copied is fixed but the place where it will be pasted will be different and may be on a different sheet.
also i would like to change the color of the copied cell to "greyed" out or cut if it can not be greyed out. I have created a button and put in a macro that i created but have been having problems with it, generic 1004 errors that i can not figure out. i am attaching the document.
I want to create a macro button that can create copy, insert, paste and rename the new sheet in next month's name, like if the active sheet's name is January, I want to copy the whole sheet of January, insert new sheet, paste the new sheet and rename the new sheet to next month like February?
Also rename the new sheet (February) cell B3 the same as new sheet's name (February)
So if month of February is near end, the macro button in February will create the same way as Jan did which means the next sheet will be named March and so on.
I need to start a list in cell a8 on sheet1. I need it to find and list multiple results vertically. It will lookup what is in cell a1 on sheet1. The table of info is on sheet2 from a1 to b44. Column a on sheet2 has the values of what is in column a on sheet1 and column b is what I need returned to the cell with the formula.
View 3 Replies View RelatedI've been stuck with this issue for a few days now. I'm trying to populate another field with information already on the sheet. The data is demo data.
I've tried =VLOOKUP(C2,D55:E55,2,0) however it only returned 0