I am trying to create a formula by comparing data in columns on a separate tab.
Basically, I want to take the value of one cell in the primary tab, find the matching value in a column on another tab, then once it is matched, pull in the data from another cell in the second tab on the same row of the matched data.
I have a summary page that includes the titles for each tab within the excel 2003 workbook. I want to use the names of the tabs in the summary page and create it into a formula to lookup fixed cells within the various tabs. Sorry for not uploading an excel doc but I was at work earlier and the thread did not load for some reason, so I am reposting it.
View 3 Replies View RelatedI am trying to create a master list in a new sheet based on the data contained in 4 separate tabs. The data is Username, Display Name, Company. Unfortunately some users have accounts in 2 of the companies (with the same username) and I need these to default to Company A.
The output need to be username, Display Name, Company.
I am trying to take data from one spreadsheet and do a couple of things. First, i need to create a tab for each column and name the tab after the name in row 1. (this code i already have, it is below)
Second, and here is where i need help, i want to put the tab name and corresponding data (all in the same column on the first sheet) onto each tab. Essentially i am just divying up each column on my master sheet into its own tab so that i can do further data analysis.
Sub GenWStabnames2()
'Kemper Ohlmeyer based on code from David McRitchie
Dim cell As Range
Dim newName As String, xx As String
Err.Description = ""
On Error Resume Next
'--cells with numbers, including dates, will be ignored,
For Each cell In Worksheets("Manager list").Range("fund.names")................
I am sure the answer to this is out there somewhere but I haven't been able to find it. Thank you in advance for the help - I just can't figure it out.
I have several identically formatted worksheets (for different departments) with macros that reorganize the data in each sheet. I also have a list of managers with their respective departments in this same workbook.
I want a macro that will save these department sheets (values and formats only) in new workbooks - the manager's name - according to the list. I also need the macros that reorganize the data to work without referencing the original file.
Is it even possible to create an IF/AND formula that draws data from multiple tabs?
For example,
(assume there are tabs named exactly the same as each of the data entries in column A)
IF A2 = "ARI" then D2 = ARI!H3. Is this possible?
I have attached an example workbook where I have three worksheets i.e. Data1, Data2 and Master Data all of which have the same data format in each column
I have data in the Data1, Data2 tabs and was wondering if it is possible to copy the data from both these tabs (using formulas) onto the Master Data bearing in mind the rows of data in the Data1, Data2 can vary on week to week basis.
Excel version 2010
Attached File : Data.xlsx‎
I have several tabs, 100 or so, and would like to have cost per West, South region etc onto a summary sheet. The summary must separate these costs per individual company per tab/worksheet. I'm looking for a formula, a macro or both to execute this work for me. The tab list grows every time i.e. new ones are set up all the time so the formula has to take this into consideration.
View 2 Replies View RelatedCan i create a chart in excel just using a formula - but not data?
View 4 Replies View RelatedI am inputting 5 variables in columns A through E:
Family (Y/N)
Adult (1/0)
Child (1/0)
Date of Birth
Gender
I am attempting to generate:
Age Males in families sorted into age brackets
Age Females in families sorted into age brackets
Age Males single sorted into age brackets
Age Females single sorted into age brackets
I did this rather simplistically generating 4 columns (F through I) and then using COUNTIF (although I know there is a better way than the lame way I used COUNTIF but that is not the point right now).
However, my question is, can I generate the data described above (located in D13:I22 on my spreadsheet) without going through the messy, cumbersome step of creating extra columns?
I have a workbook with roughly 100 tabs and a summary tab for the data within the 100 other tabs. on the summary tab I have column A with the name of each tab, i would like to copy a formula down column B that pulls the data from each tab on cell C5 for example, maybe even look up other info too, but that is another question.
What i need to know is can i write a formula that uses the tab named in column A and pulls the cell C5 from that named tab without having to manually change the name of each tab in the formula?
The basic formula would be ='17003.05'!C5 however since i have 100 tabs, i did not want to have to go in and edit each tab name.
I need a formula to add cells between tabs
View 5 Replies View RelatedI do have a file in excel with total 9 tabs. The last tab, has some formulas, which is taking data from tab 3 to 8 and calculates something. Tabs 3 to 8 are identical, (same data inputs from different departments). When I will distribute the file to some colleagues, they will need to add some tabs in between tabs 3 and 8, depending on their needs.
I want that in the formulas in the last tab 9, the data from the tabs entered additionally, is taken into consideration. How I can define the range of tabs, so that tab 9 takes then the data from all the tabs in that range, even if tabs are added in between?
I'm a bit over my head on this one. I want a formula that does the following: Look at the date I put in on the last tab and find the correct date on the other tabs. Using that date as the column I want it to return the correct row for the data.reference.
I am using the HLOOKUP function. I'm not even sure this is the right function. Ont the workbook attached I'm trying to get the data on the Totals tab to come from the Sept Wk 1 through Sept Wk 5 tabs. The formula I tried to use is on the Totals page C7.
I'm trying to come up with a Macro that once it see's the word "Rolls" in column M, I would like for it to go to the row below the word and divide the information on column K by 30
then for it to perform this formula for the next 17 rows and on the last row have the cell in gray color.
Then for it to keep doing this recursively down the column of the sheet and once finished to go to the next tab and do the same algorithm(there's like 40 tabs !!)
I need a formula that will allow me to look up data on different worksheets. I have 5 worksheets (1 summary, and 4 with raw data). The raw data tabs all have the exact same number of rows and columns but the data is from a different region. I want the user to be able to select from a drop-down menu which region they want summary data tab to pull from using a vlookup formula.
For example, I have five tabs in my workbook: Tab1) Summary Tab which needs to pull the data from the other four tabs, Tab2) named "West", Tab3) named "East", Tab4) named "South", Tab5) named "North". Using a drop-down list, I want to be able to select either West, East, South or North and have the vlookup formulas look at the corresponding tab for the data. So, in my example, if I select "North" from the drop-down menu, I want the vlookups to pull data from the "North" tab etc. I do not want to use PIVOT TABLES for this.
I have a workbook with 5 or more tabs. One of the tabs is a CONSOLIDATION of all the tabs put together. I have columns on the consolidation tab with the names of the individual tabs. To the left of these columns is a list of general ledger numbers with their respective names. For example:
East West NE South
6103256 –sales
6540000 -salary
510000-travel
I want a excel to look at the individual tabs, for this specific gl number and name and, if applicable, return a value. What formula would do this?
My columns are not showing up correctly. East,West etc are the columns. 6103256 - are the rows
I have been trying to get Excel (2007) to change the colour of tabs to match other tabs in the workbook
Using this code I get the colour code of the current tab
Code:
x = ActiveSheet.Tab.ColorIndex
But when I use this value in a procedure like this:
Code:
ActiveSheet.Tab.ColorIndex = x
The tab is a different colour!
I have a workbook with 30+ worksheets
named 23 Wed, 24 Thur, 25 Fri, etc etc
meaning April 23, 2014 Wednesday, April 24, 2014 Thursday, etc etc
The next project may start on May 19, 2014 Monday - so I have to rename each worksheet beginning with 19 Mon, etc etc
Renaming each worksheet manually takes a long time and is prone to error
I would like to just rename the first worksheet to start date of May 19, 2014 and have the rest renamed automatically.
I am autocreating tabs running through a loop and I want to input a formula into certain cells with the tab that was created name into the formula becuase I got to refrence it in my formula and the cell so now I need to figure out how to get that name into the formula without it saying E.
View 4 Replies View RelatedI need to create a formula using 2 different tabs within an excel workbook. I need to search column A on the first tab and column A on the second tab and if they match I need to compare column F on the first tab to column F on the second tab. (This column contains my staff names but the problem I am running into is on the first tab their names are listed first name last name and on the second tab they are listed last name, first name). If column A and F match I need to search column H on the first tab to column H on the second tab. If all columns match up to this point I need to take the data in column Q on the second tab and in put it into column K on the first tab. Is this even possible?
View 2 Replies View RelatedMy main data tab is collecting gallons pumped for a particular piece of equipment. I have a drop down box to populate the piece of equipment and VLookup to identify the unit number associated with the equipment. I would like to create a separate tab for each piece of equipment that will track the number of gallons pumped during the calendar year. Here is the format for the main tab:
DateBeginning Meter ReadEnding Meter Read GallonsEquipment IDEquipment Description
1/2/2014565443565625 18212006 ford f250
1/10/2014565625565675 5022006 Chevy 2500
1/11/2014565675565750 754Ford Taurus
1/12/2014565750565830 8012006 ford f250
1/13/2014565830565900 7012006 ford f250
1/14/2014565900566000 10012006 ford f250
1/15/2014566000566125 12512006 ford f250
1/16/2014566125566215 9012006 ford f250
Here is the format for each piece of equipment:
2006 Ford F250
Date Gallons
1/2/14 182
1/12/14 80
1/12/14 80
1/12/14 80
1/13/14 70
1/14/14 100
1/15/14 125
1/16/14 90
The formula I used in Cell A4 is =INDEX(Gasoline!A4:A23,MATCH(1,Gasoline!F4:F19,FALSE),1)
Cells A4 and B4 have the correct values. My problem comes in A5,A6 and B5,B6. These should not have a result because they are different pieces of equipment. Throughout the year, each piece of equipment is going be used on the main tab. How do I keep the individual tabs from picking up the same entry multiple times.
How can I get a vertical lookup or sumIF formula to check multiple tabs for a given value?
Or - is there a way to specify the tab? For instance, put "Tab A" or "Tab B" in Cell A1, and have the lookup formula reference the value of Cell A1.
I have a spreadsheet with multiple data tabs feeding a few summary tabs.
For this question I will deal with one summary tab and two data tabs, one data tab for sales and one data tab for service. On the summary tab, I want to look at vehicles sold during a certain time period-(looking to the sales tab). For each of the records in the sales tab that match the date range, I need to count the repair order activity on the service tab. The key between the summary and sales tab is the sales date, and the key between the sales and service tabs is the vehicle serial number.
I currently have multiple columns on the sales tab to perform the counts from the service tab, thus allowing me to pull the information to the summary tab. Problem is that the spreadsheet has over 2 million calculating cells and tends to take excessive amounts of time to recalc.
I always copy data from all tabs to a single tab. I am looking for a macro which does this and saves time. Criteria is Data starts from Col B to Q not further in all tabs, but the rows will vary in all tabs.
View 13 Replies View RelatedI'm quite proficient in Excel but am struggling here, would really appreciate any help
I basically have 80 tabs with IP addresses in Column A and then information in Columns B, C and D related to this.
What I want to do is bring all this together in one sheet
Possible?
The following is an example of the layout of the data that I have in a pivot table. What I need to do is find a vb code that will grab each store and its data and put them into either an individual pages or tabs. For example, if I have region 1 selected from something like a data validation list, the code will put stores 1 and 2 and their data from below into individual pages or tabs. Then if I select 2 from the list it will no longer show the results from region 1 and do the same for region 2.
region store apples oranges
1 1 3 4
1 2 3 3
2 3 4 4
2 4 5 3
I am trying to creat an IF formula for a spread sheet I am working on with information from Bloomberg. I am referencing sheet one to sheet two so I have that forumula in, but in some of the cells this symbol #N/A N.A. appears because some of the information is not available on Bloomberg. How can keep the referenc formula but also include a formula that basically says if #N/A/N.A. symbol appears to replace it with just NA.
View 9 Replies View RelatedI've only recently ventured into the VBA scripting and am finding it quite exciting. After doing some trivial stuff, I'm trying to work with websites. Currently, I'm trying to pull data off of a website which poses a form which has to be filled first. I've been successful in filling the form and clicking submit, through the code, but the site opens the results in a separate tab in IE. I am unable to make it go to that tab and copy-paste data from there.
Secondly, instead of just blindly copy pasting, it would be better if the data is pasted in a more readable format (as on the website). The following code is plainly copying and pasting the data as-is and hence rendering it unreadable:
VB:
.ExecWB 17, 0
.ExecWB 12, 2
.PasteSpecial Format:="Text", link:=False, DisplayAsIcon:=False
In the annual table tab, I want to be able to select a sales person or other field, and create a table that will pull data from all the tabs. For example, if I select the salesperson Kelly, I want it to pull all of the data from January, February, and March, and compile it into a table.
I would want the table to be dynamic also - I want to be able to quickly sum/average all of that salesperson's sales for all months.