V Look Up: Find User Number 1 And The Total For Weeks 1, 2, 3 And 4
Mar 13, 2009
I have a large spreadsheet with various data. On the first column I have a list of user ids, this is a unique id to each user. In each row there are data there are varies other data. I want to find a unique id and then add up the figures from week1, week2, week3 and week4.
So lets say I want to find user number 1 and the total for weeks 1, 2, 3 and 4, how would I do that using an excel formula? I have given an example below but I have simplified to spreadsheet.
I have two workbooks, (Invoice.xls) and (Inventory.xls) both using sheet1. When I fill out an invoice, with lets say 10 items on sheet1 column B, I need a macro to find those items in workbook "Inventory" sheet1 column A. When the item is found, I need the qty I entered on the invoice workbook "Invoice" sheet1 column C (same row as the item number) to be subtracted from the current total for that item in workbook "Inventory" sheet1 column C. I would like to run the code from the workbook "Invoice" before I do a "Save As". I have found other Vb code on this site that seems very close to what I need, but I just don't know enough to make the changes necessary.
I need to find the total number of rows down to the next blank cell (and then perform a function based on that number).
I'm using:
CountA(A1,xlDown) Situation: I have a raw data import - each record is anywhere from 2 to 9 rows, and I need to move each row in that group into a column.
I would like to use something like:
totalRows = Application.WorksheetFunctions.CountA(Range("A1, xlDown")) If totalRows = 4 Then ActiveCell.Offset(1, 0).Range("A1").Select Selection.Cut ActiveCell.Offset(-1, 1).Range("A1").Select ActiveSheet.Paste etc.
Have been going round and round on this! I have a set of data that shows sales dollars by weeks. Currently, that data is in a Pivot table. I'd like to have a macro that selects the weeks (1-52) based on my input. For example. If I want to see weeks 5-52, I want to be able to input "5-52" somewhere and have a macro run that will select weeks 5-52 in the pivot table instead of having to go in and manually select each.
Any method to determine the number of instances a value ("4" for example) appears next to a specific value ("x" for example). I don't have a workbook; I'm just looking for any formula that can search for specific values and find the total number of instances that an adjoining cell contains data.
Trying to think of a formula that will tell me the number weeks stock I have,would like to put the formula in h6 and copy across giving me the weeks stock
I am trying to create a table that will show the number of times a registration number has occurred in the last 6 weeks.
I can get it to display the values if I manually enter the rows that are in the last 6 weeks but I would like it to do it for me so other people can view the data without having to change values etc.
so Basically I have the date an entry was made in column B and the registration numbers are in column M. I have tried various combinations of COUNTIFS, SUM(IF and DCOUNT but to no avail! It either returns a 0 or an error.
I would like to display a number as weeks and days. For example the number 8 needs to be displayed as "1w 1d" or the number 14 as "2w 0d"
I am able to use a formula such as =INT((C3)/7)&"w "&(((C3)/7)-INT(((C3)/7)))*7&"d" but I wish to preserve the formatting of a number so that I can add 2 or more numbers together.
I am hoping that there is a way of doing what I want using the Custom Category in the Format Cells dialogue box.
I want to add a numeric number eg: 4 to a date format eg: 15/08/2007 so that it calculates 4 WEEKS from 15/08/2007 and returns the CORRECT date in a date fomat itself. How do i do this through a VB code ?
This may not be the best way to do this, but I don't know Macros or Pivot Tables.
I am looking for a way with formulas to do the following: Within a workbook the 1st sheet is the data entry. In another sheet that will total data from the data sheet is where I want to be able to total columns of data, depending on what is entered in one specific column:
Example: Data Sheet, E2:E2999 is a unit number selcted by pull down tab entry. G2:G2999 in the same sheet is where the data is.
Q: What formula would allow to total the data on the Total Sheet depending on what unit number is selected in column E on the Data Sheet and the data amount in column D from Data Sheet?
I have a spread sheet I have set up to calculate sales results, I have a column for each week in the quarter with the date at the top and a different page for each individual. I want to be able to enter a number to show how many weeks we have had and the spreadsheet to give me a cumulitive amount for the cells up to that date.
For example if I was to enter 1 in a cell A1 I would want the sum t work out just C3 for example.
If I enter 3 in A1 I would want the sum to be C3+C4+C5 wich is the three cells.
If I enter 6 in A1 it would be C3+C4+C5+C6+C7+C8 etc.
I'm sure this can be done but I don't think I have the formula correct. What I am trying to do is add each day's total while displaying a positive number if the total exceeds 6000.
I am trying to create a simple set up where the operator can enter a starting date and the number of weeks they want this event to occur. This then produces a column of dates from the starting date to the end date for the number of week entered.
number for item detail level from category standpoint. For example, in the category sheet has total number per category and per month and in the item detail sheet has a list of item number with category. I want a formula to recognize which item belongs to which category then use the total number of category by month to multiply with % of total in column D from Item Detail Spreadsheet.
I am looking for a simple formula that would look at column A2-A100 and calculate the whole number for the number of the week I am on. So in cell K22, I would like it to add up the A column and spit out a number 4, then on Monday when I add a new record I would like it to automatically update to a 5; indicating the 5th week I have been tracking the data. This number is needed to calculate the average amount in a work week accurately and automatically in cells K20 & K21. New weeks start on Monday and end on Friday. No data will ever have a date of a Saturday or Sunday. I have colored the cells currently to indicate what the number should be. There is 4 alternating colors now. The color coding is just for reference and will not be used going forward. Data will be entered weekly indefinately.
When setting up a Direct Debit There are 3 options, the 7th, 17th and 27th. I've put these into a drop down list. I need this list to be slightly dynamic. I must allow 10 working days from the date of the arrangment before I can apply the DD.
To explain further. If someone were to want to set up a DD and spoke to me today. If they then requested the DD be taken on the 7th, I wouldn't be able to set it up until the 7th of December as 10 working days are required. I'd like the list to reflect this. I can do it in OO with a simple if / switch but excel is a strange creature and I'm not sure how to approach the problem.
From that I think I also need to calculate the number of weeks remaining in the current financial year. I think I should be able to do this by using the Today() function and some maths.
Is it possible to do the following with a formula in Excel...
I have a list of users with the dates they first logged into a system and the date they last logged in. I'm trying to group them together into segments so I can analyse them using a Pivot table and chart to see how often different groups are using the system, e.g. New customers in December, November, October, etc.
The trouble I am having is trying to convert the different human-readable text strings into a consistent number of weeks from today's date, e.g.
USER FIRST LOGGED IN LAST LOGGED IN user 1 2 years 20 weeks 54 sec ago user 2 44 min 7 sec 1 min 37 sec ago user 3 49 weeks 2 days 17 min 3 sec ago user 4 5 weeks 2 days 33 min 32 sec ago user 5 38 min 9 sec 38 min 9 sec ago user 6 5 weeks 3 days 1 hour 7 min ago user 7 2 hours 17 min 2 hours 11 min ago user 8 45 seconds ago 45 seconds ago
Is there a formula I can use to convert these human readble text strings into a number of weeks elapsed since today's date?
I have a button that a user clicks to print the info they entered. I need some code to add a running count to a cell (say, H2) every time the user clicks that button and runs the print macro.
I have a column with Cells that will sat True or False, the amount of rows will be different every time, I need to work out what the total percentage of True cells compared to the total number of cells. How would this be achieved.
Take a look at the attachment file. Those highlighted in yellow are entered by the user. What is the formula to calculate the End date in (A6) after the user has entered the start date (A2) & the number of weeks (A4)?
I have a column of names, and I want to be able to count all the instances of each name, as each instance represents a sale of a product.
Countif(Sales!B:B,"Dave") works, counting all the instances of Dave.
But if I have all the names in column A, and try to have column B give the results (from another WS), as in: =COUNTIF(Sales!B:B,'Best Customers'!A1), I get a "0" as the result. Yet XL help says countif can be used as =COUNTIF(A2:A5,A4). where A4 holds the value to search for.
While we are exploring this, is there a good way to look in a column, get every different instance of the names, and output them into another column?
I need a formula that will scan column A (Code)total the like items (also) add column B (Qty) if there is a number greater than 1. Then add the price ($) together and divide by the sum of A&B.
In other words find the average price for the total of each item..
In my header I am trying to show the current page number and the total number of pages in the workbook. So if I have 10 pages in my workbook, page 5 would read "5 of 10".
I have this in my header "&[Page] of &[Pages]". All worksheets with 1 page read "1 of 1" and worksheets with 2 pages read "1 of 2" on the first page and "2 of 2" on the second page.
I need a formula where by i can get just number with total in front from e.g cell D2 =148513 Total and E2 should result just 148513 and leave the rest blank which does not have total.
Layout
148513 148513 148513 Total 1578972 1578972 1578972 1578972 Total
Formula that can calculate % of total for individual items. For example,
Item NoCategorySales% of total 1234Bed150 5678Bed100 1547mattress52 58658mattress188 12356bed12 8954mattress185
I need to find out each item % of total per their own category. For example item 1234 should equal 150/(Total Bed) to get % of total per category and item 1547 should equal 52/(Total of mattress)
I have a number I would like to find that is made up of a number of these cells. Lets say that number is 205, and as such the only possible values that could make that total are 1,100, and 104.
Is there a formula I can run that will allow me to find this number without manually figuring it out.
