Keep Comments Column Correct After Refreshing Data Columns?

Dec 16, 2012

I have an worksheet that refreshes every day from a .csv report. I would like to be able to have a comments column at the end that users type in. The problem is how to keep the comment next to the row it was on before the refresh.

When it refreshes there will be new rows, rows that are no longer there and all in a different order.

There is an Order Number column that can be used as an ID column.

View 3 Replies


ADVERTISEMENT

Get A Correct Average Of Each Supervisor From The Data Breakdown In Columns

Dec 26, 2006

How do I get a correct average of each supervisor from the data breakdown in columns f:g?

I was thinking an if statement like =if(a2:a10,"x" --then I am not sure how to say average of f:2:f10). Would that be on the right track?

*
ABCDEFG
1*****knowledgable*friendly*
2john*mike*dave*bill*lisa*5*4*
3john*mike***lisa*4*3*
4john***bill*lisa*3*2*
5*mike*dave*bill*lisa*2*4*
6john**dave*bill*lisa*1*4*
7*mike*dave**lisa*1*3*
8john*mike*dave*bill**2*5*
9john*mike*dave*bill**3*3*
10john*mike*dave**lisa*4*5*

View 9 Replies View Related

Check Cells Are Correct Format And Contain Correct Data

Jan 26, 2014

We receive about 20 sales files of several hundred lines of data each day from various agencies. I want to create a macro / VBA code which checks that the data submitted is correct so that we can upload it into our database without import errors and / or having to manually check each line of data.

I envisage something like an output report:

#####################
149 entries
Column A - Date - OK
Column B - Customer_Phone - Errors (Should be 11 digits)
Row 21 - Customer_Phone - Error (Not 11 digits)
Row 108 - Customer_Phone - Error (Contains letters)
Column C - Outcome - OK
Please correct and re-check.
#####################

View 4 Replies View Related

Enter Data To Correct Column Using Userform?

Jun 29, 2014

I have started a user form called submit stock. What i am trying to work out is if i can use this form to enter the data to the worksheet using the form to specific columns depending on the first choice.

View 8 Replies View Related

Excel 2010 :: Move Part Columns Of Data From 6 Columns To Form 1 Large Column In Column A?

Jan 31, 2013

I have a large spreadsheet converted from pdf whose data still appears in A4 reading format.

I need to move part columns of data from 6 columns to form 1 large column in column A.

For example, move range B8 to B76 beneath range A8 to A76 and range C8 to C76 beneath that etc, page by page working through all 270 pages !

Also need to delete unnecessary 'page headers' throughout as in rows 2-6

View 2 Replies View Related

Pivot Table - Adding Data And Refreshing

Oct 17, 2011

Its been ages since I used a pivot table and I cant remember much.

I did one last week - lets pretend on the range A1, B100.

Ive since added some extra info into the range and it now goes from A1, B110.

I go to my pivot table and hit "!" and it updates. How can I be certain its included everything from my range? Is there a way I can check the pivot table to see where the range is from?

View 3 Replies View Related

Refreshing Actual Data Source Of Pivot Table

Dec 31, 2013

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.

View 1 Replies View Related

Refreshing Pivot Table Based On Cell Data

Mar 4, 2014

I'm looking to update a pivot table based on some info in a cell but I keep getting an error message.

View 7 Replies View Related

Stopping Cells From Automatically Refreshing After External Data Refreshes

Jan 30, 2010

I have 2 Excel documents.The first document is named "data" and it extracts data from off the Internet every 24 hours. The second document, named "database", is for storing this data. Every 24 hours, I run a macro which imports the data from "data" into a new row in "database", along with the date the macro was run.

My problem is this: Whenever I refresh the external data in "data", my "database" document also refreshes itself instantaneously. My question is this: Is there a way to "cut the connection" between the two documents so that, when "data" is refreshed, the data in "database" DOES NOT also refresh itself automatically?

View 3 Replies View Related

Excel 2011 :: Pivot Table Not Refreshing With Data Dynamically

Oct 7, 2011

Named my data range using this formula:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

I was hoping that my pivot table would refresh with the new data that I added on sheet 1 but it is not working and yes I have refreshed my pivot table. The new data is not capturing.

Is this the correct formula for Mac Excel 2011?

View 1 Replies View Related

Comments On Column G Based On Information On Column A

Apr 23, 2008

Am trying to put comments on column G based on information on column A. Column A contains a grade eg CDEF picked up from another sheet using vlookup. This grade is suppossed to be the same, but I have cases where I add a different grade say GHIJ to fill up my 99 required grades.Column G is for comments such as "Transfered" (which at the moment am entering manually)How can I use a formular so that each time I transfer from another grade other that the one intended it returns a comment "tranfered" on column G? Is it also possible to indicate the cell from which vlookup picked that grade on the same comment column G.

View 9 Replies View Related

Convert Column B As Multiple Column Titles And Move Data In Column C Into New Columns?

Jun 30, 2014

I have a excelsheet that looks like this:

Column A | Column B | Column C
Los Angeles | Fire Dept | 3
Los Angeles | Health Services | 12
New York | Fire Dept | 8
New York | Health Services | 22
New York | Internal Services | 100
New York | Public Works | 7
Chicago | Health Services | 15
Chicago | Public Works | 56
Chicago | Social Services | 4

And I am trying to make it look like this:

Fire Dept
Health Services
Internal Services
Public Works
Social Services

Los Angeles
3
12

New York
8
22
100
7

Chicago

15

56
4

View 8 Replies View Related

How To Change Auto Correct Words In Certain Columns

Jul 21, 2013

For example, on column X, if I type 20, bob or bobafett it type 2.0 instead but on column I if I type bob, it will type 3.0

they would all be edited manually

or even better, no matter where I type it on excel, it would go in the right column, like bob X, then it would change to 2.0 and go directly in the X column

View 4 Replies View Related

Combine Similar Products And Put Into Correct Columns

Jun 21, 2007

Try this on for size...

On the MSP sheet is a list of products with a weeknumber and quantity.

I need to combine products that match in the same week and add their quantities.

Then I need to put the quantities into the VOLUME worksheet.

Any products that arent listed need to be added on the bottom.

View 14 Replies View Related

Adding Comments To Row / Column

Mar 8, 2012

I have the following code which deletes the specified value from each cell in column B that contains that value, (note it only deletes the value from that column and not all in the row).

Code:
Sub delete1()
Dim lr As Long, i As Long
With Sheets("Database")
lr = .Range("B" & Rows.Count).End(xlUp).Row
For i = lr To 2 Step -1
If .Range("B" & i).Value = 1 Then .Range("B" & i).ClearContents
Next i
End With
End Sub

Now what I need is to adapt it slightly so that it also adds a comment to the cell in Column I and the current date in Column L, (on each row where the deleted value was).

The comment would be something like "old data archived" and the date in any format, preferably dd/mm/yyyy.

View 9 Replies View Related

One Column For Three Comments Fields

Jun 15, 2014

Need one column to combine three columns I have a in a spreadsheet.

Column A - Comments
I have a comment called Duplicate or blanks

Column B - Comments 2
I have a comment called Loss Fund or blanks

Column C - Comments 3
I have a comment of Y or N

I just a want formulae which put the following in one column
Where it is Duplicate in Column A
Where it is Loss Fund in Column B
Where it is Y in Column C

So I have one field for all comments.

View 8 Replies View Related

Extract Comments To Adjacent Column

Feb 21, 2008

I have a substantial schedule that has been completed by a 3rd party who has used the comment function for important data. Can anyone suggest how i can utilise a macro to extract all of the comments into the adjacent column?

The comments are currently against cells in column E and i'd like to extract them to blank cells in a newly inserted column F.

View 9 Replies View Related

Countif Formula: Added The Correct Amount In The Correct Cells

Feb 15, 2010

I'm trying to create a formula that will added the correct amount in the correct cells, I have create a dunny sheet in trying to achieve this. If Cell B8:B11 = ABS or Dum that any points won should be added to Cell L8:L11 right now its adding it into K8:K11. If Cell B8:B11 = is Blank any player points should be added to cells K8:K11. I'm using this formula throughout cells K8:K28 =IF(J8>J25,1,IF(AND(J8<>0,J8=J25),0.5,0)) Any thing in red is incorrect anything in blue is what I'm trying to achieve.

View 4 Replies View Related

Edit Comments Not Working For Certain Cells In A Column

Aug 28, 2009

I try to edit comments in cells of a certain column and nothing happens. I am able to edit other comments throughout the spreadsheet, but when I select "edit comments" for these particular cells, no edit box is displayed for editing. It worked fine a couple of weeks ago.

View 11 Replies View Related

Sumproduct- Counts The Correct Amount But Not With The Correct Dates

Jul 3, 2008

I have a table with 3 columns of dates and then a column with Set # that I
feel in the box #.

I need to see how many items processed for each set per day.

Example:
[url]

The problem is that it counts the correct amount but not with the correct
dates.
The formula that I use is:
=SUMPRODUCT(--($I$3:$I$8<>"")*(($C$3:$C$8=39601)+AND($E$3:$E$8=39601)+AND($G$3:$G$8=39601)))

View 14 Replies View Related

Formula To Correct The Numbers In The Next Column

Nov 3, 2008

I have a column with numbers ranging from -5 to 250. I want to write a formula to correct the numbers in the next column. If the number is less than 0, I want to replace it with 0,if the number is greater than 150 i want to replace it with 150 and if the number is in the 0-150 range it should be kept the same. Is there a way to use inequalities in a formula to do this??

View 2 Replies View Related

Sumif If The Column Is In The Correct Period

Jan 24, 2007

I have a set of costs in column c (more that shown below) that have an indicator - for example 0 or 30 (there are more).

I need to populate rows 18 and 19 with the sumif - i have other tables where it is a straighforward sumif as the periods align, here though I need to check that the periods (rows 4 & 16) are checked and then the right data entered underneath.

I can't find a way to combine the sumif, with an if statement checking that the period is correct too? ...

View 9 Replies View Related

Use Column To Autofill New Column With The Correct Value?

Apr 14, 2014

I have a colum full of durations in 00:00:00 format. I need a forumla to look at these durations, count the seconds and then multiply those seconds by a predfined rate. In effect giving me a second colum full of costs.

I have attached a screenshot f the data, and the rate should be 0.0073p (.73 of a penny)

View 5 Replies View Related

Calculate Sum Of A Column If Month And Year Is Correct

Jun 19, 2014

So yesterday I created a thread [URL] ..... that would clear a specific content if a date (or rather a day) matched the criteria.

Today I'm continuing with that document and need to sum the columns if the date in a row is the correct month and year.

Ex. Row 1 contains the date "yyyy-mm-dd" and row 2-5 contains empty cells or the value x. The x values are all random placed.

So, I want to sum all the "x" for February 2014.

I'd rather use a formula here than a macro/VBA-code but anything will do. I have tried myself with =sumifs and =sumproduct but with no success.

See attached file for example and for my =sumproduct formula.

Excelforumexample1.xlsx‎

View 5 Replies View Related

Sum Macro With IF Formula - Enter Correct Value Into Column

Dec 8, 2011

I want to create a Macro that uses IF statements to enter the CORRECT VALUE into COLUMN “Q” in the ACTIVE WORKSHEET. I am providing an example of what the data set looks like at the very bottom of this post. I want to use a Macro as oppose to Formula in the worksheet because I want to turn the Macro into an Excel Add-In.

I want the Macro to do the following THREE THINGS:

1. IF the Value in COLUMN L is “0” THEN enter “n/a” into COLUMN “Q”
2. IF the Value in COLUMN L is “2” THEN enter “n/a” into COLUMN “Q”
3. IF the Value in COLUMN L is “1” THEN use a formula that looks like this:

= O8 + ( ( O8 / P8 ) * (First SUM the HOURS in COLUMN O for all the ROWS that have the SAME VALUES in COLUMN C and H and a “2” in COLUMN L and then MULTIPLY that Result against those rows with a “1” in COLUMN L whose COLUMNS C and H values match up exactly with those of the Summed Hours). The RESULT of the FORMULA would be entered into COLUMN “Q”. Once you look at the example at the bottom of this post it will definitely start making sense. I highlighted rows 8 – 11 for you in red in the data set at the bottom of this post just to center the attention on the rows I am talking about.

Here is an example of how the formula will work:

I will use ROW 8 from the data set below as an example. From the dataset I know CELL O8 equals 10 and CELL P8 equals 76. Now I will SUM the HOURS in COLUMN O ROWS 10 and 11 for ALL ROWS that have the SAME VALUES in COLUMN C and H in this case the values are “Times” and “PM” and contain a “2” in COLUMN L which happens to equal 3, then that 3 should only apply to those rows with a “1” in COLUMN L whose COLUMNS C and H values match up exactly with those of the Summed Hours. So now I plug that into the formula: =10+((10/76)*(3)) and MY RESULT which will go into COLUMN “Q” IS 10.395.

Here the DATA SET:

Col A Col B Col C Col D Col E Col F Col G Col H Col I Col J Col K Col L Col M Col N Col O Col P Col Q Col R Col S Col T
Row 1 B ID Name Org Div T Number Model Make S Function E Function Type Description P ID OG Hours Sum EA P Hours Sum P S Hours EQ

This is what is happening in Column Q Explanation of Formula

Row 2
Times
Cont
2
28 28 n/a

n/a If formula populates Column Q cells with "n/a" if Column L cells contain a "0" or "2".

Row 3
Times
Cont
1
404 1194 413.4740369

O3+((O3/P3)*28) You get the Sum "28" by adding all the "P Hours" in Column O that match these 3 values:
1. The "Org" value of "Times" in Column C
2. The "S Function" value "Cont" in Column H
3. The "P ID" value "2" in Column L

[Code] ...........

View 9 Replies View Related

Military Time Wont Total Correct In A Column

Aug 20, 2008

B. start time C. end time D. Total time (military time)
Example: 2300 - 0300 = 4 hrs worked.
=IF(C19-B19>0,C19-B19,1+C19-B19)

I have a row for every date the hors is worked.

I cant seem to get the total hours to calculate correctly at the bottom of column D for a total hours worked in a particular pay period. It calculates a total up to 24hrs then reverts to zero. I have tried [h]:mm =SUM(XX:XX) and just about everything else I can think of and cant get it to calculate a correct total over 24 hrs.

View 5 Replies View Related

UDF Needed To Get Correct Data

Jul 3, 2014

I need identifying correct title and corresponding company name. For this I need a UDF which looks for first occurrence of title having event or marketing or meeting word in data and if found the remove other titles and company names already separated with "/".

Sample Data
Producer, Target BTC@Periscope/Events Manager, Recognition Events@Minneapolis Park & Recreation Board/Event Producer@Events by JLS

Required Result
Events Manager, Recognition Events@Minneapolis Park & Recreation Board

Sample Data 2
Sales Associate@Teavana/Event Assistant@City of Saint Paul

Required Result 2
Event Assistant@City of Saint Paul

Sample Data 3
Sales and Catering Manager@Bunker Hills Golf Course at Town & Country Caterers/Marketing Coordinator@Town & Country Caterers

Required Result 3
Marketing Coordinator@Town & Country Caterers

View 3 Replies View Related

Convert One Column Of Data To 257 Columns Of Data

Mar 11, 2008

I have exported a text file from a universe database and need to import it into excel.

Below is an eg of one section. The text file contains approx 257 of these, all one below the other.

Between each set of dotted lines is one user profile (not data all shown in eg) .

Each one (user) is NOT the same amount of lines(rows) deep and only the dotted lines separate each user.

A simple import places all data into one very long column.

I need a way to place each user in there own column.

I have user names as column headings the labels of the values below as row headings. I cannot change this.

Manually entering the data took a very long time and i need to update this workbook regularly.

In the text file, " the variable......: value " (eg UserCode.....: XYZ) sets are all lined up so that using a text editor, with column mode, I can delete all the data headings/labels up to the space after the colon.
This leaves me with 7000 rows of values (eg XYZ) one column wide and various numbers of rows deep per user.

Is there a way to import the data and seperate each user via the dotted lines or other so that each users data is contained in consecutive columns. 1 user profile per column.

The standard import utility allows for column placement (delimted, fixed width). Can this be done on rows, sort of flip it 90 degrees and use the dotted lines as column markers. Just guessing now. Any and all assistance would be most appreciated.

Another issue (not as important but still helpful) is that the "procudures barred" section of user profile can also be one row(line) or serveral. Problem is each Name...:Value set must be on one row(line) for everything to match in the work book.

Using custom macros in my text editor i moved all the 'proc. barred' data to a single row. Alot of manual editting was involved and any ideas on this area would also be muchly appreciated.

One EG user profile (not shown, but all the colons line up) ....

View 9 Replies View Related

IF Comparison Function Not Returning Correct Data

Jan 24, 2014

I'm trying to compare three formula results and return the largest number. Ex.:

2217922

The first three numbers are formula results and the last is the following formula:

=IF(O5>P5,O5,IF(P5>Q5,P5,Q5))

Unfortunately, with the list below, you can see the last number (the comparison formula) isn't always the greater of the three:

2217922
2017920
01799
01399
01399
01299
01299
01299
01299
01199
01199
01099
0088
0077
0077

I've also tried other formulas, with no success:

=LARGE(O5:Q5,1)
=MAX(O5:Q5)
=IF(MAX(O5:Q5)=O5,O5,IF(MAX(O5:Q5)=P5,P5,IF(MAX(O5:Q5)=Q5,Q5,"Error")))

What's odd is that if I create the formulas above independent of the specific formula data (O5, P5, and Q5) and just type in numbers, those formulas do exactly what they should. Can I not compare data returned though formula calculations?

View 12 Replies View Related

SumProduct Formula Not Gathering Correct Data

Dec 17, 2009

I have a what I believe to be a consistant formula on lines S12-S18 for gathering the sumproducts for dates on column O. Only problem is that the sumproduct formula is not working on line S18 correctly. I keep looking it over and I can't figure out why it is not returning the correct amount for that cell.

Here is the formula I am using:

=SUMPRODUCT((TEXT($O$12:$O$500,"dddd")=R18)*1)

Please see attached line S18 for the error I am getting.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved