I need to find a forumula that will enable me to transfer values from a table within the worksheet. Something along the lines of if I13 = A16 then need to enter the number that appears in A17 into cell 019, if I13=B16 enter number in B17 into 019. I also need the forumula to include if the figure in 113 is less than 3 then need to enter 0 into 019.
View 14 RepliesI am trying to track inventory over 160 days in a spreadsheet. Every week I update a table that shows the number of skus and dollar amount that are over 160 days in a table. I have a ton of formulas that will automatically populate once I put in the new weekly data on a different spreadsheet. I want to track the weekly data so what I would really like to be able to do is paste the actual numbers and not the formulas into another excel spreadsheet so I can track the progress over time. Is there a way to just paste the actual numerical number instead of the formula itself?
View 2 Replies View RelatedI have created a spreadsheet which uses a User Form to input data and then give it a reference number (this bit works fine).
I am now trying to use a User Form to enter a reference number which will then be searched across my spreadsheet, I then want all the entered data relevant to that reference number to appear in a User Form along with additional fields for entry of additional info.
In this User Form, the original data could be modified but more importantly, the remaining data added before then being placed into the main spreadsheet.
This is a booking in system where the person presents to a receptionist and the initial data is entered. Once that person has been dealt with, the remaining details regarding their visit will need to be inputted, hence this request.
check the table in the attachment.
I only want the top row / filters (A1 - H1) to be displayed on Table 1 but keep the functionality of it.
Is it somehow possible to hide all the information below A1-H1 or put it on another table and let Table 1 refer to it?
The table is mostly in German but it shouldn't matter.
Look at the Picture Below I Want that the Cell that marked with Red Arrow Returns a "Rank" Value in the Table, Which is a "Blaster" Rank. And I want That Cell Also Automatically Changed When "Blaster" is Changed to "A.A.Z Gonz" or etc. What Functions that i have to choose, INDEX? XL.jpg
View 6 Replies View RelatedI am not sure how would I go about finding the last data entry in a table and using formula to extract information. Attached excel 2007 file to better understand. Need formulas for cell B20 and E20. You can use column A as a reference if it is easier as this column is counting the number of entries and the next value will simply be a +1 to the last value entered in column A(or 75 in our case).
For Cell B20: Need to calculate the days difference between todays date (Cell A20) and the last entry date(in this case B5, this will change as new data will be entered). A subtraction of two dates would suffice - my problem is that I do not know how to find the last entry on the table and use it. You have to remember that table gets updated and new entry will be added so then cell B20 should give the difference between A20 and the last date entered.
I have a table on sheet 2 consisting of 120 rows and 8 columns, on sheet 1 I have a combo box and have used INDEX to display the values for each row that I select. what I want to be able to do is edit the INDEX data on sheet 1 and then have a button that will replace the old value in the table on sheet 2 with the new value.
View 5 Replies View Related1. Below I have a sheet stock, which show the maximum qty stock should be by code and size
Sheet Stock:
*ABC1CodeSizeQty Max Should Be
21250011273125003113412500499512500580612500614471250073308756049997560913210826021491182603180128
26041761382601143014978026220159780273701697802838017978029310189780302901997803117020756081992175601250
2. Here is a Sheet sold Article
Sheet Sold Article:
*ABC1CodeSizeQty Sold2125001123125003841250062257560436756092378260278826011999780261610978028271197803013129780312913756014
3. Here is pivot table from sheet sold article, which show the qty sold by code and size
PT-Qty Sold: 1
*ABC1
2
3Sum Of Qty
4Code
[Code]....
4. But I want to get additional information in column F form sheet stock that we always keep max stock of each sold article as shown below.
PT-Qty Sold: 2
*ABCEF1
Qty Max Should Be2
3Sum Of Qty
4Code
Size
Total
[Code]....
Is it possible to get information above in column F by using Formula VLOOKUP in column F or any other formula, without modifying any data layout?
Name
Rock
POP
HIP POP
Alex
1
Jimmy
1
1
TOM
1
So this what I want the Macro to do, example the above table shows peoples name and the type of music they like.
So I want the Macro to extract the information into "sheet 2" of the excel as follows.
This is how sheet 2 would look like.
Alex
1 Rock
Jimmy
1 POP 1 HIP POP
TOM
1 POP
Note how for Jimmy who had 1 for pop and 1 for hip pop, is in the same cell.
Basically I have two groups of data
Group 1 is machine info and has
Serial Number Build Month Commission Month
Group 2 is warranty info and has
Serial Number Service Claim Value of Service Claim
What I need to do is for all machines built in a given month count the number of claims within a given number of months after commissioning and the total value of the claims.
Eg
If I have a machine serial number abc123 built in jan 07 and commissioned mar 07 I need to search the table of data in group 2 for the number of claims between mar 07 and may 07 and also the total value of those claims
I need to have a cell (Total) but I need to be able to enter the information which all the subtotals are inputted into that area and will be the subtotals of the total. I have two areas that I will need to keep a running total of for each job #.
I'm not sure how to find out how to do this as I'm not that advanced with excel.
I have some data that I have created a report from. However it doesn't show a "true" picture for the stat we want. I have a pivot table set up with data showing the hours worked for an individual. What I am wanting to do would be to cross reference the data but not sure how to compare data. I have provided some make shift data to see if it is even feasible. The end goal is to get a true picture of P.P.H The data we are able to get is in a horrible format. I would eventually like to automate this in VBA but will work on the later. I thought about doing a vlookup but the is no real matching data for all employees. Some of the names are wrong in one system and there is no matching emp ID.
View 3 Replies View RelatedWhat I would like a macro to do is take information from cells A1 to P1 on sheet1 and add them to the table in sheet2 and then I can save the workbook. The information in A1 to P1 is taken from other cells around the workbook. Once the information is in sheet 2 I can delete or change the informtion in sheet 1 without it affecting the copied information in sheet 2.
The next time I open the workbook I can enter the information in cells A1 to P1 on sheet1 then run the macro again and will then take the information and add it to the bottom of the table in sheet2 underneath the previous information that had been added - obviously the source of this information will now be deleted.
I will need to do this again and again building up the table in sheet2. The table will also use columns A to P like in sheet1.
I use the GetObjects function to gain access to the properties of a mailbox and want to fill out a table with some detail information. The code works fine until I have a mailbox which name has an apostrophe included.
Set o = getobjects("winMgmts:!\VS1
ootMicrosoftExchangeV2:Exchange_Mailbox.LegacyDN='/O=TEST/OU=TEST/cn=Recipients/cn=co'brien',MailboxGUID='{59A40254-9F12-4502-9A03-3FB5CB0BBD38}',ServerName='VS1',StorageGroupName='sg1',StoreName='mb1'")
I highlighted the section that causes the problem. The string within the string is encapsulated in apostrophes, hence the apostrophe in the mailbox name co'brien confuses the code and raises an error. Microsoft provides a solution by using escape characters, but that seems to work only in vbscript, not in vba. Here is a link to Microsoft's solution:
http://www.microsoft.com/technet/scr...4/hey1217.mspx
I tried some workarounds that are being used for a similiar problem with SQL statements as well, but again without success - these are the versions I tried:
co/'brien
co'brien
co''brien
Is there a way to edit my database located on sheet 1 using the advanced filter (output) on sheet 2?
I have a gigantic database and I want to filter it down to the rows I need to edit.. so I used advanced filter to extract the rows I need on to another sheet. But if I edit the rows on sheet 2 how do I makes those changes reflect on sheet 1 (the full database)
What I have is a list of selected employees, with the site they work at next to them.
What I need is a macro which looks at the site they work at, references it with an address table in a separate worksheet and then pastes the site address along with the employee's name onto a thrid worksheet which will be used to print post labels.
>Look at which site the employee works at (Sheet 1, D:D)
>Find the address of said site (Sheet 3)
>Paste the relevant address along with the employees name (Sheet 1, B:B) into a separate worksheet.
This will be done weekly, and I need the macro to either only work on the latest's weeks table, or to just work with the current active selection. Any better way to layout my table.
I have a table of information with location codes as the column headers. Each location has from 1 to 6 codes associated with it.
I want to create a new table that displays the information by location instead of code, i.e. adding all of the codes for a location into one column for that location.
I'm hoping the example will make this clearer.
On the main page, I am trying to add in the wa column all of the columns in the raw page that have a code associated with wa as the header.
To make this more complex, I can't use vba on this one.
the only thing I've got so far is a very long, very complex formula that adds together numbers generated from index/matching each entry in the second table.
something like this.
(edit changing 1:1 to $1:$1)
=IF(VLOOKUP(B$1,lookup1,2,FALSE)=0,0,INDEX(sample.xls!main_table,ROW(),MATCH(VLOOKUP(B$1,lookup1,2,FALSE),raw!$1:$1,0)))+IF(VLOOKUP(B$1,lookup1,3,FALSE)=0,0,INDEX(sample.xls!main_table,ROW(),MATCH(VLOOKUP(B$1,lookup1,3,FALSE),raw!$1:$1,0)))+IF(VLOOKUP(B$1,lookup1,4,FALSE)=0,0,INDEX(sample.xls!main_table,ROW(),MATCH(VLOOKUP(B$1,lookup1,4,FALSE),raw!$1:$1,0)))+IF(VLOOKUP(B$1,lookup1,5,FALSE)=0,0,INDEX(sample.xls!main_table,ROW(),MATCH(VLOOKUP(B$1,lookup1,5,FALSE),raw!$1:$1,0)))+IF(VLOOKUP(B$1,lookup1,6,FALSE)=0,0,INDEX(sample.xls!main_table,ROW(),MATCH(VLOOKUP(B$1,lookup1,6,FALSE),raw!$1:$1,0)))
It works, but its horribly ugly, and if the number of locations goes higher (we could be looking at going to ten location codes for one of our locations) then I'll actually exeed the 1000 characters per formula limit!
I'm using a code to search some information in a pivot table and copy the value in the cells with offset(0,1) and is very very slow, I tryed another method with the using of Find but isn't working: error message: missing object in the with cycle.
Here is the working code:
Dim DataFine, DataInizio, UltimaRiga, Gg As Date
Dim NomeMacchina, Plant As String
Dim Cl As Object
Dim Pr As String
Application.ScreenUpdating = False
[Code] .......
Here is the code who doesn't work
For i = 6 To 500
Giorno = Sheets("OEE03").Cells(i, 2)
With Sheets("01")
Pr = Range("A5:A500").Find(Giorno).Offset(0, 1).Value
Sheets("OEE03").Cells(i, 9).Value = Pr
End With
Next
if I use this code on a normal sheet it works but when I try to use it on a Pivot table fields give me always the error message.
I'm having a bit of a block with an index/match formula that I am trying to create for the attached spreadsheet. i.e. I need to populate cell J3 with the info the corresponding letter contained in the table to the far left. The numbers of reference to match are the 'zones' in H3 and J2.
View 2 Replies View RelatedI am building a service for a company using software provided. The software provided will export the information into an excel sheet.
I am creating an A/B testing for 2 different areas of the site. I will be doing a daily/weekly and monthly figure reading. There will be 2 excel sheets, daily/weekly and monthly. I will need to put them into 1.
I want to somehow, import these 2 excel sheets so it automatically pulls the information I require into the relevant cells.
From there, I will create a percentage of what. Is this possible to do via excel or will I require other software to happen?
=SUMIF($A$2:$A$9,F2,OFFSET($C$2:$C$9,1,0))
It works great.
The issue is that the information (time) that I import into excel doesn't show as information if there is a zero in front the amount of hours. I can physically see the information but have trouble using it. For instance if I add
(A1) 05:25:25
(A2) 10:25:25
(A3) 02:25:25
If I total/SUM this my total is 10:25:25. It will not recognize anything under 10 with a first digit as a zero. So for me to add this I use:
=SUMPRODUCT(B3:B25+0)
This also works great. Now on to my issue.
I am using the 1st formula to grab data i.e. (A1). It will not recognize that as anything. It comes up as zero. I can't figure out what to add to the 1st formula for it to recognize the info.
I have a timesheet worksheet that a previous employee created and I need to alter a formula. Unfortunately, I can no longer contact them.
Anyways, I need to alter an "if" formula that is associated with a drop down list. As of now, the drop down list is used for a 2 week period ending on a specific date. When the date is selected, the calendar automatically aligns the proper dates to the days of the week. I believe it is able to do this with an "if" formula that is used by the numbered days of the month. For example; if I select January 14th, 2012 as the 2 week period ending date,
On the 14th the "if" formula shows as this:
=IF(ISBLANK($P$2)," ",$P$2+1-1)
On the 13th, it shows as this:
=IF(ISBLANK($P$2)," ",$P$2-1)
I noticed a trend here with the last digits 2+1-1 and 2-1, so i attempted to go with this trend and add numbers as the dates went on, but it did not work.
I need to change this so that instead of a 2 week ending period, it is for a full month. So, if I were to select January 2012 or any month, all the days of the month would align correctly with their days of the week.
I have a excel file which shows some information about mobile numbers but i don't know from where that excel file get that information one formula have some reference of sheet2 but there is no sheet2 in excel file. how this excel file get information?
download excel file
http://www.geocities.com/mirag2001/celldecoder.zip
is possible to display the formula and value of the variables in a formula in a cell? e.g. if I enter '=A3+A5, the formula will appear in the cell. However, can I also have the valu of A3 and A5 in the same cell with the displayed formula?
View 5 Replies View RelatedI have been looking for a forumla that will put an 'and' between the last two bits of information.
Joe Bloggs
Peter Smith
Matt Jones
I have list of names in individual cells like above. I have figured out a formula that will put them into a single line (Joe Bloggs, Peter Smith, Matt Jones). But what I need is a formula that will put it into single line and insert an 'and' ALWAYS between the last two people. So it should show like (Joe Bloggs, Peter Smith and Matt Jones) but if I remove Matt Jones it should now display as (Joe Bloggs and Peter Smith).
I have a spreadsheet designed for softball schedule for an entire league and am trying to come up with a way to count certain information so that I can figure out if the schedule is balanced. What I'd like is for a way to sum the number of games each team plays on diamond 3 or 4 and to sum number of games at 6:30/7:30/8:30
View 2 Replies View RelatedI am using the vlookup function accross two tabs. . . we'll call them TAB1 and TAB2. On TAB1 I have all my base information which I am referencing from, TAB2 is the sheet where i am using the vlookup formula to extract the information i need from TAB1.
Using cell B2 as an example from TAB2:
I used a vlookup formula in cell B2 and got the information from TAB1. The vlookup formula pulled, correctly, cell F17 from TAB1.
Instead of the formula reading "=vlookup(...)". I would like the cell to simply read =F17.
I need help with a formula so that I can create a single chart or (more likely) multiple charts for each of the choices in column C.
Attached is a sample of my spreadsheet.
I want to be able to break out each of the "Br#"(A) associated with the "BUM"(C) and graph each of the choices in column D (Run Time).
I am sure I can create the chart I need once I get the formula created to pull the data I am trying to combine. I hope that I have explained this well enough.
I need to pull information from a cell that is full of text and numbers and get it to creat a formular.
More information in the spreadsheet example
I have a Sheet1 that contains 900 rows of information 14 columns wide (C7:P7 are headers). Column M has an important date. The information is constantly changing.
On Sheet2, I want to display the upcoming dates, in numerical order for each row that's within 5 days of today's date. After the date has passed, it no longer needs to display on Sheet2.