I have a Date in Cell A1 Page 1 and I need a formula to put in Cell A2 on Page 1 that if there is a "matching" Date in any cell in Row B1-B7 of Page 2 that the data in the corresponding row C1-C7 on Page 2 will be placed back on Page 1 Cell A2.
View 9 RepliesI have tab1 with (master list with contact name) and tab2 (list with no conact name)
i want to have formula or macro that llnks the contact persons name with the company name, address, city, and state.
i know copy and paste--but thats not what will work..this is an example worksheet, the real verson will have thousand of line of data and i would like to creat a link or formula..
Can this problem work in excel or is this more on an access promblem? in where i need to build a database...
Master list example 1.xlsx
I want to create a sheet where the weather is updated automatically. for example,
CITY TEMPERATURE PERCIPATATION
Toronto -9 20% rain
Montreal -20 40% rain
thats what it should look like or similar. The problem is I want to have a formula for the temperature where it gets an update from the internet. So everytime I open or refresh this sheet, it would change to whatever the website is.
I am trying to create a formula for a work report and I am afraid I am a bit of a novice with the more complex formulas. Here is what I am trying to do: Sheet 1 has Sales order numbers that duplicate based on the sales lines. Sheet 2 has single lines of sales order numbers and their assigned PO number. I would like to have Sheet 3 where it combines all data and duplicates and fills in the PO number to match all of the sales order numbers that repeat on the other sheet. I hope this makes sense. I can't seem to figure out if I need a VLookup or If or Match formula (or all of the above).
View 1 Replies View RelatedI have a workbook with three sheets. The first is a list of employees with their managers. The second is the data about those employees (who they sold product to, and how much, on what date). The third is the PivotTable with the employees' sales. I have the Customer name in the Row field, and the amount in the Data field. I have two page fields - the Manager's name and the Employee's name. The example that I'm using so as not to violate my NDA is a Northwind knockoff. That's why the names probably sound familiar.
What I want to do is have the second pivottable page field (Employee) change, so that when I choose a manager's name, only the employees who report to that manager show up in that page field. I have a named range called "allEmp" (all employees), one called "buchanan" (all the employees who report to Steven Buchanan), one called "fuller" (all the employees who report to Andrew Fuller). (File attached, btw).
Private Sub Worksheet_Calculate()
Dim mgrRange As Range
mgrRange = Range("allEmp")
Range("$b$1").Select
Select Case ActiveCell.Value
Case "Andrew Fuller"
ActiveWorkbook.PivotTable("EmpSales").PivotFields("Employee").CurrentPage = Range("fuller")
Case "Steven Buchanan"
ActiveWorkbook.PivotTable("EmpSales").PivotFields("Employee").CurrentPage = Range("buchanan")
Case "Susan Erickson"................
I have an Excel file that use an internal link from one worksheet to the next. When I click on the link, I'd like it to go directly to a print preview page so I can print that one page instead just switching to the other worksheet. Is this possible?
View 3 Replies View RelatedI have a page that is protected so formulas are not changed mistakenly, and have check boxes on the paged that link to cells and display True of False based on the checking of the box or not. If I leave the page unprotected I risk the True or False being changed. Is there a lay to protect those cell from the user but still change when the box is checked or not. The checkboxes are Form Control type.
View 6 Replies View RelatedI have two worksheets, one with detail monthly information and one with YTD information. So let's say the three numbers I want to capture in the YTD sheet are in columns B, G and I on the monthly sheet. January's data might be in B5, G5 and I5. February's data is in B12, G12 and I12 and so on.
On the YTD sheet in cell C2 I link to Monthly!B5 and in C3 I link to Monthly!B12 so cells C2 to C13 on the YTD sheet show the monthly totals from column B on the detail sheet. On the YTD sheet, cells C20 to C31 show the monthly totals from column G on the monthly sheet, so cells G5, G12, etc. And finally, cells C40 to C51 on the YTD sheet show monthly totals form column I on the monthly sheet.
In the past I've always created all these links manually. After creating the links in C2 to C13 on the YTD sheet, is there a way to use a formula in C21 that uses the link in C2 to create a link for G5?
I have an items list on page 2. On page 1 I want to create a drop down menu in C2 that references the list on page 2. Then when I choose an item, I want the additional information in cells D2 and E2 to come in automatically. The list on page 2 will be expanding daily, and when I expand it I can simply insert a new row so I correctly alphabatize it instead of constantly having to resort it.
With that said....
1. How do you put in the drop down box that references information on a different sheet?
2. What is the best way to reference the information in my ever expanding situation: Index/Match, Vlookup, Lookup, or something I havent mentioned?
I am looking for a formula that will take data from one page and graph it on another page. The data I am trying to graph is arranged like so:
A B
1 q 2009.05.01
2 w 2009.05.02
3 w 2009.05.01
4 q 2009.05.01
I am looking for something that will look at column A and if the answer is "q" and then look at column B and if the answer is "2009.05.01" Excel will take that and count it in a specific cell on another page. In the case above, I want Excel to give me the answer of "2" in a designated cell.
Assume that calculation is set to automatic and not manual, then is every formula on a page updated when any value on the page is changed, or only when the values in the cells pertaining to a particular formula change?
For instance, if cells a1-a10 have values and in a11 is a sum a1-a10 formula, and in b1-b10 are values and a sum b1-b10 formula in b11, if I change a value in range b1-b10 but do not change any values in a1-a10,then as well as the formula in b11 updating will the formula in a11 recalculate or does excel keep track of which cells have changed and thus is aware that the formula in a11 does not need to be recalculated?
When I view a sheet under Page Break Preview, it shows the Page numbers in the centre of the Page. While I am aware that it would not print the page number I was wondering if there is an option to remove/hide the page numbers.
View 3 Replies View RelatedI would like to fill in a a form on page /sheet one and have the same info on every sheet that follows is it possible?
View 3 Replies View RelatedI have attached a spreadsheet and I am trying to capture the info in lines 2,7,12,17 and return the info into column d,e,f,g
The info in these columns at present has been manually entered but I am sure it could be automated.
OOL Roster Final 18-31Aug14.xlsx
With my limited knowledge I am having trouble constructing formula to break the following info that I receive frequently from my credit card company into 3 columns - Date, Details, Value
21 Apr 2007 SAINSBURYS PETROL BURPHAM £10.00
I am looking for a formula or Macro if its the only way which does the following:
I have a set of locations in the below table which also has exactly the same name as a tab in the same workbook. So for example I have 1 tab called "summary" which contains the below table and than another tab called "London453" & another tab called "New York45" & another tab called "Paris3232" & last tab called "Vancouver43"
In the "Reconcilation needed" column I would like a forumula/macro to start in Cell B2 and look at the cell next to it in Location column [Cell A2] and then go to the corresponding tab in the workbook and if there is a value in cell G22 in that tab, than I would like the formula/macro to take the first 7 charactors of Location value otherwise put nothing.
Example :
in tab "London453" there was a value in cell G22 --> hence 7 charactors "London4" in below table
No value in Cell G22 in the "New York45" tab --> hence nothing in below table
Value in "Paris3232" tab in cell G22 -> hence "Paris32"
No value in "Vancouver43" tab -> hence nothing
Location
Reconcilation needed?
London453[CellA2]
London4 [Cell B2]
New York45
Paris3232
Paris32
Vancouver43
I need a formula that will take the value of one cell and put it in another cell other than the one in which I am typing the formula. I can't think of how to do this.
I have two columns, A & B (no headers), of information. The columns are not identical, and column B has empty cells scattered throughout the column. For each empty cell in column B, I want to copy the same row of information into it from column A, as if I'm just scooting the information in column A that's on the same row as the empty cells in column B.
This is simple with a cut/paste, but when there's thirty empty cells, cutting and pasting takes time. (What's really frustrating is that I've figured out a few formulas that will copy the information from column A but all the information gets pasted in the column in which I'm creating the formula (say, column F, for example). THAT doesn't help me because the empty cells in column b are still empty!)
In column A I have Product Codes, They start at A3 and go down to A169.
I need to have a formula which i can drag across from CQ3 to DH3 which corresponds in the following manner.
CQ3 = A4
CR3 = A5
CS3 = A6
etc etc.
I then want to be able to copy the formula into another place, eg CQ20 = A21, CR20 = A22 etc
I have attached a screen shot.
Untitled-1000.jpg
I have a formula in column J that is the following:
=IF(I541="",IF(ISNUMBER(SEARCH(LEFT(G541,4)&"*"&RIGHT(G541,4),H541)),G541,""),"")
See screenshot:
I need to replace "G542" in the formula above. For example, in cell J541 shown above, I need it to output "1996-1999|2000-2001" instead of "1996-2001" I need the output to include LEFT(G541,4)&"*"&RIGHT(G541,4)
i have a list of names which also contain e mails addresses after the names, they are displayed as (In column B onmy spreedsheet)
Armani Stevens/GB/companyname/GB@soso
what i need from the above is just the name (up tp the first backslash)
so i would need Armani Stevens and disregard the rest.
What i would then need to do is to take the name and then see if the names is in the list which is situated in column A,
so to sum
once i have Armani Stevens extracted from Coulmn B i would want to see if this name is in Column A
Names are obviously of all different lengths and there is always a space after the first name and surname
I’m working on quite huge spreadsheets. The same formula will be used on more than 30 sheets.
So far if I have to change the formula in one cell I have to go to each sheet and change it manually (which is quite boring and time consuming).
I’m wondering if it’s possible to reference all the formulas on the sheet to one sheet (which some kind of a master sheet with all the formulas).
I can copy the formula with the “Past Special” function but the function is not updating if I change the formula on the main sheet.
So is it possible to link it and that it’ll be updated automatically when I’m changing the formula on the main sheet?
I would like for the result of a formula to stay in the cell even after the first cell changes.
in cell G1 I use this formula, if(C1=D10,A1,""), I would like result of formaula to remain in G1 even after C1 is changed.
I've created a spreadsheet that I use to calculate weights of steel members for my job for estimate purposes. I'm trying to figure out how to save some time by not having to enter/copy/paste formulas every time I do takeoff for an estimate.
The bold green cells in column K are always linked to the yellow "Part Heading" above in Column A. As I do my estimates, I copy and paste a "Part Heading" with a "PL/Member" formula below for the next piece I'm working on. Every time I paste a new "Part Heading" with a "PL/Member" formula, I must change the formula in Column K to link to the next cell up in column A with a value present. In the screen shot, cell K/90-K/96 are linked to cell A/89. Cell K/83-K/87 are dependent cell A/82; K/74-K/80 are dependent on cell A/73, and so forth. Since each "Part" varies on total number of "Members", I can't seem to write a formula that will stick when copying and pasting.
My question is: Is there a way that I can automatically link each cell in Column K to the next cell up in Column A with a value present?
Within my spreadsheet all the formulae are sums of cell references from 7 other documents. I now don't need one of the documents and therefore want to remove just that one link only from the formula. I would do it manually but I have hundreds to change! I have tried breaking the link to that one document but that removes the entire formula.
View 2 Replies View RelatedI have a formula in the target workbook in cell C3 =-'data.xls!'np2011'
However, when UI copy the formula to D3 , the formula remains as =-'data.xls!'np2011'
2011 after np is the year. The names in the source workbook have been named np2011, np2010, np2009 etc
np2011 is a range name in the source workbook. When I copy the formula to d3, it should change to -'data.xls!'np2010'
In the target workbook I have the years in cells C1 to L1. How can I get the formula to change when copying/ Alternatively can one concatenate the range name to C1 for eg such as ="-'data.xls!'np&c1&"' I have tried to do this, but cannot get it to work.
I created a summary page for some reports I do and I'm having trouble keeping them linked together as my original data has rows deleted and inserted. There are 30 spreadsheets worth of information and I have them linked to a summary file. Each month I insert a new tab to each spreadsheet. I set up all of the formulas last month but see a problem this month when I copy and past the formulas to the new tab. On the 30 spreadsheets my data moves up and down so the row referenced in the summary formula is wrong. I am looking at having to type "=, click on original spreadsheet, select cell, enter, repeat" all over again. I pull 5 numbers from each sheet, and have multiple formulas to double check the math so I would be repeating this process over 300 times.
After all that, my question is...Is there a formula I can use that will pull this for me without needing to be edited each month. One idea I thought would work is to tell the formula to equal the first cell in Column B that is in red text and I could just change the font color on the orginial spreadsheet. But, I don't know how to make a formula say that or if excel can even do that.
How do I get rid of the link??? My formula seems like it is not working because it has a link which I cannot find anywhere.
dog.xlsx
Screen shot 2014-01-05 at 2.38.08 AM.jpg
It's a new year, which means new tables and lists. I have a list of 450 People with a reference to their own individual file. They all receive a new file at the start of the new year that I Need to be able to reference in a new list. Here's an example
Joe Bloggs has a cell next to his Name referencing
[Code] .....
In 2014 Joe will have 'Bloggs 2014.xls' as will 449 of his friends, each named individually. The only part that changes from 2013 to 2014 is the date in the Name of the file. Can I somehow just Change that one part of the link without having to manually relink everything?
I have one list in one sheet that is source for the userform and combobox used on another sheet. With userform I make a choice and data is entered in one cell.
But is it possible to compare the choice that I make in combobox with the list on the first sheet and then make a simple link between these two cells, so that cell in the second sheet is always showing what is in the cell on the first sheet.
Reason for this is that items on the list on the first sheet sometimes change and I need to update the second sheet automatically.
Private Sub cmdOK_Click()
With Sheets("Presentation").Range("V18:V32")
Set c = . Find(cbochange.Value, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
ActiveCell.Offset(0, 2).Formula = firstAddress
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Unload Me
End Sub
I've been having some problems with an excel page whereby I need to know the underlying formula used but can't actually click or edit any of the cells other than 3.
The page is designed for you to enter three variables which are then used to calculate a transport rate, I need to know how they calculated the transport rate but I can't click on any of the cells other than the 3 that they let me (so can't see the formula used).
[URL]