Copying Information From Table But Only Number Shows Up Not Actual Formula?
Aug 13, 2013
I 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?
I have set up a simple form in excel, whereby people use drop down menus to select the appropriate information (as all data inputted needs to be entered in exactly the same way to allow sorting, counting etc) so I thought drop downs were best. The trouble is the data is carried through to my excel database (on another worksheet) were it is shown in numeric values dependant on how far down the drop down list the select item appears (ie "Fareham" is third on the list, so shows in the database as "3").
I'd very much like my database to be easier to read and actually show Fareham etc, rather than be populated with lots of numbers. I initially tried using IF function (ie =IF(C3=3,"Fareham") but unfortunately two of my drop down lists are too long for this (with 25 and 33 entries respectively).
I am looking for a way to show the character location number of a text file, possibly in the first row or a macro that I can run at any given location that will give me the location # I am currently viewing.
Currently, when I open the file I can see the character # at the opening screen (see attached file) but they disappear when it actually converts. I would like to be able to keep the character location ruler once the file is opened in Excel so I don't have to manually count.
We have a Shared workbook on our Server and about 5 users work at the same time every day. I want to understand something, If I open it I can see all the information in it, all the rows and so on, but if the same workbook is opened by another user he will see other information, the rows aren't the same as in my case. Why does this happen ? I would like the second users to see the same information as I do.
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.
There is pivot table for some reason the last column (YEAccts), which sums all the other columns values is exluding the 1st column for some reason. This "YEAccts" shows up fine in the pivot table "field list". And I followed the source for this and it's a table in a different tab that pulls data from an access query connection.
The name of the column that is summing up the rest of the columns is "YEAccts", but for the life of me I can't seem to find where this column is in the source table tab or even in the access query where the data is being pulled from.
How to find this "YEAccts" column? Also, why would it exclude not summing up the data in the first column?
I'm creating a basic form or templateusing Excel 2010 where the user will fill out information (See attached screenshot) I'd like to incorporate two a check boxes, that when ticked, change the display of the second table, either adding, removing or splitting of of the current columns in to two?
Is this, or any variation of, possible using Excel 2010.
I want for my set of data. The attached .xls is pretty straight forward: the first column is a list of people (identified by their customer number) and the second column is the URL they visited.
Since many people went to multiple pages, there are dupes between the two columns, but all of the rows are unique. What i am looking for is a table that shows as a header the URL (just one) and then the list of people that went to that URL under the header. So it's really just one column of information. It seems like a perfect task for a pivot table.
I've attached a simplified worksheet which tracks the calls of a team of salespeople along with a pivot table showing their results for Jan-Mar. I've also included a summary of their planned calls for the period. Ideally what I'd like to do is have the pivot table show actual calls, planned calls and variance for each salesperson and each month as seen on A37-J42.
I have weekly budget report (for the entire year) that I put in a Pivot Table with approx 20 different row labels. The actual revenue numbers come only once a month (several different reports make up the actual revenue numbers). I need to put the actual numbers on the same row as the budget numbers and I can't figure it out.
For example, for the first three months of the year, I have the pivot table showing everything perfectly. In February, I get January's actual revenue numbers that now "replace" the budget numbers for January. If this was a regular table, I could easily overwrite these numbers, but with the pivot table, I can't get these two fields to line up. For now, I have the Actual Revenue numbers on the Rows above the Budget numbers for their respective months/quarters.
I am trying to insert a blank row into a table (created using the table function in Excel 2010) without the copying the formulas. Every time I insert a row the the formula is copied.
I need to find a number (1, 2 or 3) and I am looking in two different cells for it. Now, Cell 1 might have the information; Cell 2 might have the information or BOTH Cells A AND B might have the information, or either cell (or both) might have different information. I can't do an IF/OR because I don't know which cell will have it, nor can I do an IF/AND for the same reason. I can't assume that they will both have the information -- so I can't just use a solution that includes a "divided by."
Trying to create a basic pivot table from a CSV file for our users. What I've done in the past is open my CSV and then take the option Insert > Pivot Table to create the pivot table from the open CSV/worksheet. My question here is .... how can I save this .XLS and refresh the CSV data within? Or do I need to use an external data source? I don't want to have to use any drivers. I'd love to be able to just point to a CSV or TXT file on our server and that be that.
For something else I have used Web Query strings to point to .php scripts that send back data in XML format. Is this an option?
Again, the goal is for me to create a pivot table and just have our user's open it up and have current/refreshed data within. We can run jobs overnight or on-demand that update the data source.
to copying a formula from one column to the next adjacent column and also incrementing the 2 numeric values inside the formula. Please see attached sample.
One value increments with each new column whereas the other increments with each new row. Please see attached sample.
And in the last row there is the average formula which must copy the new column and average the new column.
I tried to create a Macro in attached but it did not work with formulas.
Down loading data I receive dates as a number that indicates YYMMDD (ex. 130603 is 03 JUN 13). Is there an easy way to convert these number strings to the actual date indicated other than typing it in?
I need a lookup table. I have a table that shows what the sun is doing for each day of the year. Now I need to input longitude and time zone, add or subtract if the sun is fast or slow on that day
I need it to work off one formula not make 365 copies
I want to show the field name (i.e Surname) before the value is shown in the Pivot Table. for Example if the Field is Surname and the value is 'Smith', I want to show - Surname Smith
I have attached my workbook, it is a pretty standard pivot accross 3 sheets but it is only showing values instead of text, how can I change this? I have tried everything.
I am doing a vlookup on a cell range where the value I'm trying to lookup (a date) exists, but it's not an actual number in the cell...it's a reference to another cell with that value (somewhere completely different).
So, I'm trying to vlookup(date(1/1/2011),A1:A12,2,false) to get the B column value.
A B 1/1/2011 # 2/1/2011 # ... 12/1/2011 #
However, the A column is not the actual date. It is a reference to another cell somewhere completely different that has the actual date 1/1/2011.
When I do a vlookup trying to find 1/1/2011, it can't see it there unless I overwrite the reference in A1 (for instance) with the actual date.
I am trying to create a golf table that shows how many games are played how many points are given for 1st 2nd 3rd and so on we give 10 pts for 9 for 2nd and so on......
I have a column A with date formatted as dd/mm/yyyy. I then have a column B which is =A, and I reformat the date formats to a custom format DDDD which gives me the day of the week that the date relates to.
I then try and write an if statement
e.g. =If(b1="Friday",1,0)
B1 always references the date which is stored as 40118, when I need it to reference the actual formatted value that I see in the cell e.g. Friday?
I want to create a macro that will take information from one worksheet to another. The problem is that the first worksheet has an autofilter and i do not know how copy information only from the cells that were filtered with the correct information without copying it manually.
Say, for example, I have a spreadsheet as follows:
0 1 1 2 3 2 0 1 2 1 2 1 2 3 3 1 0 1
Is there a way to count the number of times a specific number shows up and have excel post it to a different cell? I need to know how many times a certain number came up for a specific row, and how many times.