I have a list of items in column A of Sheet1 and the same list in column A of Sheet2. Both list will contain the same items, however not necessarily in the same order.
What I want the book to do is; when I click on an item in the list on Sheet1, it takes me to the same item in Sheet2.
Is this possible with VBA?
We have many spreadsheets in my lab. We have been using them for YEARS. Two days ago we ran into a bizarre problem. If we copy a formula and paste into adjoining cells we get the following
Init Values Formula copied & Pasted Results in B column
A column B Column
10 =A1* 10 100
15 =A2*10 100
20 =A3*10 100
If you copy Range("B1") and Paste it to Range("B2:B3") you get 100, 100, 100 NOT 100, 150, 200. If you click on range("B2") it says =A2*10 but the value 150 does not appear in the cell. If you manually type a formula into each line, the results are what one would expect. If I open a new workbook, copying and pasting works as always.
I would like to compare each cell in a spreadsheet to the cell on its right.
If the numeric value of a cell is less than the numeric value of cell on its right, I would like to highlight the cell on the right light red.
Maybe a better way of putting that is if the value of a cell is greater than the cell on its left, highlight it light red (The problem I foresee with this formulation is in the first row where there are no cells to the left).
Setup: I have 2 worksheets with between 8 and 9,000 rows on each
Column A in both worksheets Sheet1 and Sheet2 have an email address in them.
Not all addresses in sheet1 will be on sheet2 and visa versa
Column J on Sheet 2 contains a date
What I need: Column M on Sheet1 is empty
I need a formula to place into Column M on Sheet1 that will
Look at Sheet1:A
Locate the corresponding value on Sheet2:A
Pull the date from Sheet2:J same row into Sheet1:M
I have a "Generate Table of Contents" button that launches a macro. This macro builds the Table of Contents, creates (3) columns (Patient Name, Date of Pickup, and Frequency), finds all of the sheets in the work book (already renamed to a patient's name), sorts them alphabetically, and lists them under the Patient Name column.
This code works great - The next challenge:
On each sheet (which corresponds to a patient in the database), there is a cell that is filled in that states which day the patient picks up their medication. There is another cell that designates how often they come to pick it up.
I am at a loss as to how to direct the macro to: For each sheet, go to the specific cell, and then report it in the table of contents.
It somehow needs to bind the information together... meaning the patient name, date of pickup, and frequency must be displayed correctly each and every time on the same row.
I've been given the task of automating a spreadsheet to assess whether work has been done by each employee. Every employee has their own spreadsheet, where column A is a job code, and column H contains either y or n dentoting whether they have completed the task (y) or not (n).
The main spreadsheet is designed to show any outstadning tasks across everybody so it has a list of everybodies names in column A and then any incomplete tasks will be listed from columns b onwards next to the appropriate person. Here is what I have done at the moment.
I work for an IT company who build their own PC's. And we use a simple Excel 2003 spreadsheet to piece-together and price-up PC's for retail, I've attached an example of the spreadsheet for you to see. It uses basic formulas so we can see at-a-glance things like VAT figures, margin, profit etc. Each PC we sell is contained within one Excel file, and each PC has it's own Workbook tab.
The problem I want to solve is whenever we change a component or price, we have to do each system manually, which gets very repetetive and time consuming. What I'd like to be able to do is change the price of, let's say, the motherboard, and have the price be reflected across each of the PC's using that same motherboard on each seperate tab. This same system would apply when we change a product name, product ID etc.
if its possible to link contents of a cell to a comment.
IE - Whatever the cell contents in sheet 1 A5 would reflect in the comment in sheet 2 A5 Im happy if its coded that would work.
I have a restaurant and every evening the manager emails me a sales report. The names of these files are all based on the day's date so I know what the file name will be in advance (e.g. today is 20120109.xls and yesterday was 20120108.xls)
I have a master spreadsheet collating the monthly sales data. It links to the daily spreadsheets with links like this:
=IF(ISERROR(VLOOKUP($A5,'C:UsersSimonDocumentsPersonalTOSEADaily Sales[20120105.xlsx]Sheet1'!$B$6:$F$50,3,FALSE)),0,VLOOKUP($A5,'C:UsersSimonDocumentsPersonalTOSEADaily Sales[20120105.xlsx]Sheet1'!$B$6:$F$50,3,FALSE))
Each day I create a new column and drag the previous column's formulas over. I then do a find and replace to change the file name (20120105.xlsx in the example above) to the current day.
What I want to be able to do is change the filename reference in the formula above to a cell within the master spreadsheet that contains the date, so that when I drag over a new column it picks up the date from that cell and automatically uses it to create the filename in the link. The path for the file name remains the same.
I am trying to combine the contents of two cells depending on the contents of another, I have tried to use the If function but am coming up stuck!
I have provided a link to the example file below:
[URL]
I have a cell that a I want to get the results of a SUM to 20 cells in 20 worksheet out of 60 worksheets. When I enter my' =sum( shows up with serial_numbers. When I go to each sheet and select the cell I want (same in all worksheets) like in sheet 2 it shows my sheet name and cell. But when I go to my sheet 3 and select that cell it replaces sheet 2 with sheet 3 it does not keep adding the sheets with the cell numbers. When I select all sheet tabs it still only changes the sheet number in stead of running a range. I could type all the sheets names and cell but lots of work and I have a lot of this type of ranges to do.
View 2 Replies View Related“Pre” is where the user type information and these information would then shared throughout all the sheets. There are a total of 5 things that I need to be done… (Those indicated with numbers are the ones that need to be done).
1st thing: How to highlight the days for all the TERM BREAKS?
2nd thing: How to link the dates for the Public holidays to “P1_B1” and highlight it red. Without using conditional formatting because there maybe more than 3 public holidays.
3rd thing: Number of groups entered here would create rows of group according to the number of groups entered in “Pre”
4th thing: The number of classes entered here would create the number of rows for each particular subject. Eg. English has 3groups so there are a total of 3 rows.
5th thing: The number entered for the number of programmes would create new sheets with respective to the number entered there.
*I have to only enter no the “Pre” sheet and it will automatically link the information across the rest of the sheets without the need to press RUN. *
[URL]
What im trying to do is match columns A & B from AUDIT Sheet to Columns A & B in MASTER sheet. If they match then pull columns C, D & E from MASTER into AUDIT.
I build a tool in excel to make an analysis per industry (30 industries). I want to compare the results on the first sheet, as a summary. All the information is now scattered over the sheets, but what I want to do is when you change the ticker in column A, it automatically retrieves the information from on of the sheets for the specific company. I will try to make myself clear to attach a screenshot.
So if you change ticker in column A, I want to fill columns C:D:E, G:H:I, K:L:M, O:P:Q, etc automatically according to the company name.
I have a macro that copies a sheet and renames it:
Copies "Q114" and renames it to "Q214".
Q114 is defined as OLD in my VBA code
Q214 is defined as NEW in my VBA code.
I also have a third sheet, call it "results", which picks up cell values, ie cell A1 in "Q114", but now I want it to use cell A1 "Q214". The values hasn't been filled in, so I need a link between them:
[Code] ......
I need to link two text boxes on two different sheets. Is there a excel formula or macro to do this. I am entering text in textbox 1 on sheet1 and same text needs to show in textbox2 on sheet2.
View 4 Replies View RelatedOn sheet1, I have numbers and names. These names are also contained in Comments on sheet2, and i would like a function in Column M that matches the numbers with the names in the comments. Cell M1 I have manually filled for demonstration purposes.
Can this be done with a function or is this a VBA task?
I have a protected worksheet with 60 columns (20 sets of 3), and want to give my users the ability to hide the columns they're not using. There's a row of unprotected cells below the columns in question with data validation allowing a choice of either Show or Hide, and want my macro to act based on thier choices.
Since it's a large spreadsheet, I don't want to run 60 (or 20) different hide commands, if at all possible.
Is there a way to read the 60 different Show/Hide cells (or 20 range names) and join those into a single VB range so I can run the EntireColumn.hidden = True statement once?
spread sheet training matrix. The subjects to be trained on are plentiful so I have broken these down into different sheets. I produced this to my boss and the first thing he asked was "can we link the sheets so I can get information about one single employee" in other words I have a list of employees in the first column and this list is the same on each sheet, the headings on the sheets are different but instead of having a set of columns about forty headings wide I have split them down to ten headings on four sheets. The big question! Is it possible to print one sheet with one employee but all of the headings? When I say headings I mean the columns under the headings as well.
View 4 Replies View RelatedI have an excel file containing three sheets(1,2,3). And each sheet has 9 Columns (A,B,..,I). Now for the first sheet i have a functioning mask (userform) through which i can edit the columns live and add new rows.
Now i have a userform which is devided into three parts. The third and the last part is working fine which is linked to Sheet 1. Now i want the first part(on the top) of the form to be linked to Sheet 2 and the midpart to sheet 3.
In first and second part of the userform there is button (copy this to sheet 1 as new). This button should be coded in a way that if its clicked then the active entry (in sheet 2 or in Sheet 3) should be pasted at the end of the sheet 1 rows and the textboxes linked to sheet 1 should jump therer so that the new entry can be edited directrly in sheet 1.
The mask can be opened by the button in Sheet1 column A1! I am using 1280x1024 pixels for my grafikcard.
I am attaching two files. Excel file 2003 xls in zipped form and an image of the mask as jpg.
Is it possible to somehow link sheets in excel to different tables in Access so when I update the sheets in excel it can automatically update the tables in access?
Doesn't have to be on line but I want to avoid the continous copy paste or exporting and trying to automate this process.
I want to write a Formula or Macro :
To clear Second Leg score in First Leg of Cup match
in Attached file, I explained detailed what to do. [URL]
if u need to know anything about Football,
I have a spreadsheet with 60k lines. One of the columns contains barcodes as per the example below, with a suffix of -XXX-XXX where the XXX are numbers. I want to highlight all the cells where the -XX-XX are the same number (as in the example below -303-303) how I can do this with a formula as I've around 30 spreadsheets each with 60,000 lines I need to sort through?
2500071189898-303-302
2500071189898-303-303
2500071189898-303-304
2500071189898-303-305
Is there a formula that will enter the current date in a cell A1, when I enter text into the adjoining cell A2? I'm creating a log with notes and would like the current date to just appear when notes are entered in a cell.
View 9 Replies View RelatedI would like to link students for the 1st Nine Week list to the correct grade level as shown. I have attached a file.
View 2 Replies View RelatedI am very new to using excel and I need connecting the information between two sheets. I have a weekly schedule that I want to use for my employees. I want that information to automatically be moved to a daily schedule. I need to be able to see how many openers closers and mid-shifts I have and how many hours I am consuming from my budget. I want the shifts from the schedule to be moved to the daily schedules in order in chronological order (morning shifts first, then mid-day/closers). If at all possible how I would like to have the span of their shift to be highlighted in the cells on the daily schedule.I will attach what I have so far to provide as much info as possible.
example.xlsx
I have a excel workbook with One Master Sheet.
The data entered in the master sheet is automatically updated in the sub worksheets through direct links created . Each sub worksheet is a branch of organization. almost 100 branches are there.
The data is entered for all the branches in the Master Sheet at different periods continuously which is to be kept as such.
For example In first day 10th, 15th branch data may be entered in master sheet first and second row, which is getting automatically updated in the respective branch worksheets. But the problem is when the data is entered for any branch( for example 6th branch) in 5th row of Master sheet, in the respective 6th branch sheet also, it is updated only in 5th row only, leaving the first four rows blank. But i want the updation in the branch sheet continuously without any blank rows.
For updation i have used the simple direct link between the cells of Master worksheet and Branch sheets.
How to get the updation in branch worksheets while entering the data in Master sheet without any blank rows.
I have a sheet with 3 macros. It consists of two macros to produce results, and the third to bridge the two together where is all starts. I have built a userform in the sheet and am trying to get the variables in both sheets, to be user changeable and selectable via the userform.
In the attached data, the userform allows for the selection of two sheets, A and B. The user would select in A the sheet with data which is equivalent to Oval_An and in B the sheet with the data equivalent to Oval_DMA. The first macro, Find_75 runs, followed by the second macro, kTest, all working to produce results when hitting the Subtract button on the form. The Find_75 tolerance are also defined via the useform box, as well as kTest's compare tolerance.
Userform that people use. I need validating the users input into the form.
1. Need to validate that A1 only has 3 letters (Alpha)
2. Need to validate that A2 is not left blank
3. Need to validate A3 has only 10 digits
My state is that when the user hits the submit button in the user form the macro checks for these validations above and if everything is ok emails the sheet. However if the user havent inputed the data correctly a msg box will pop to let them know what is wrong. If thats the case i do not want the sheet emailed. below is the email code i'm using
HTML Code:Â
Sub SendMail()
' Copy the sheet(1)
ThisWorkbook.Sheets(1).Copy
' Send Email
.SendMail Recipients:=Array("email.address@email.com"), Subject:="Test" & Format(Date, "dd/mmm/yy")
.Close SaveChanges:=False
End With
End Sub
Have tried a few things but can't get it to work properly. I have attached the file I am working with because it is much easier to understand if you just look at it!
Basically, I have a single sheet with three columns:
Name, Post Code, Tenant
There are varying instances of 'Name' and each one has a different 'Tenant'.
What I need to do is merge the contents of the Tenants cells so that for each different 'Name' there is only 1 instance and then for Tenants all the different cells are merged i.e. "Tenant 1, Tenant2, Tenant3...etc"
I hope this makes sense, as I mentioned above it will be a lot clearer if you have a quick look at the attached spreadsheet.