Convert Some Data From MLS To Make It Easier To Work?

Feb 7, 2014

I'm trying to convert some data from MLS to make it easier to work with statistically. The raw data regarding Bath count downloads from MLS in 2 columns; 1 column for Full Baths & 1 column for Half Baths (see Raw Data.jpg). I want to concatenate the baths to one column and, if there is a 1/2 bath, tack ".5" to the end of the full bath count.

The problem with my formula is that if it concatenates the ".5" to the full bath count number, it converts the format to text (see Current Formula.jpg). I know I can go through later and convert it back to number format but is there a formula I could use that would keep it in number format?

View 3 Replies


ADVERTISEMENT

Figure Out How The Best Setup Could Be Done To Make This Easier For Pivot Table Analysis

Jan 10, 2010

I am tasked with putting together an Excel Spreadsheet to analyze our accounts. There are over 80 items for the accounts - about 20 Income and about 60 expenses. These are for various years - actually the last 5 years - by months.

So, I am trying to figure out how the best setup could be done to make this easier for pivot table analysis.

So I have 12 "rows" for each account item for each year.

Maybe I should wait for comments and/or questions before going further.

Just need to figure out how to set up the workbook/worksheet.

View 11 Replies View Related

Make To Display (0), Instead Of (-0,02) When I Just Do Other Work Than Cad Work

Feb 13, 2010

In column J, Cad Hrs. How to make to display (0), instead of (-0,02) when I just do other work than Cad Work? I need to separate type of work according to price hour, overtime, and so on.

View 2 Replies View Related

Make Macro To Work On PDF?

Jul 1, 2014

I've got a PDF file that I was hoping to convert into an Excel sheet, but it doesn't look like that's possible, xince I'm working with only Adobe Reader, not Acrobat. Anyway, I need to compare 1 column of this report to another report that IS in Excel. Is there a way to have a macro compare an Excel file with a PDF??

View 2 Replies View Related

INDIRECT : How Do I Make It Work

Jul 2, 2008

The following formula gives me the error message #REF!

=INDEX(INDIRECT("R7"),MATCH(V6,D8:D11,1),11)

The problem I believe is in the INDIRECT("R7") as the following formula works

=INDEX(Well_AA_09,MATCH(V6,D8:D11),1),11)

The content of cell R7 is the text Well_AA_09 which is the name of a dynamic range I have created and pasted from within VBA into cell R7.

View 9 Replies View Related

Make Macro Work Conditionally?

Sep 13, 2013

I have a fairly simple macro (below). I want it to do one more thing.....not run if the filters have not been changed. I would like to prevent the error that occurs if someone tries to run it and there are no filters active.

Private Sub AutoFilter_Remove()

Sheets("Data").Select
ActiveSheet.Unprotect "password"
ActiveSheet.ShowAllData

[Code].....

View 9 Replies View Related

How To Make IF Work On Formulas And Not Values

Nov 18, 2013

I've developed a spreadsheet that shows what errors people made in their spreadsheets. I compare cell A1 in spreadsheet 1 (original) against cell A1 in spreadsheet 2 (final), in spreadsheet 3. Spreadsheet 3 shows a "-" if the cell they wrote was OK, and shows the new, correct value if they made an error, as follows:

=IF('Final'!A5='Original'!A5, "-",'Final'!A5)

The problem is, this only works if I take the original spreadsheets, which contain formulae in each cell, and paste them into a new spreadsheet using paste values.

Is it possible for the IF function to search the formula in a cell, and not the value?

View 1 Replies View Related

Make A Circular Reference Work

Jan 10, 2010

if i enter a value in 1 cell and want it to increase by a percentage how can this be done without having a sum column etc

View 9 Replies View Related

Make Macro Work In Many Rows

Jan 31, 2007

I'm having some difficulty with this one. I have found several posts on how to make a macro work through several rows, but cannot seem to figure out how to apply it to my macro.

Right now I'm having to write the macro for every line and it's a bear! Here's what I'm working with right now and it's ridiculous (I've actually got through line 51, but I'll only post a few here, for your sanity!)- there has to be a better way:

If Range("C9") = "" Then
Range("T9").Formula = "=IF(K9>I9,IF((I9-H9)>=180,180,(I9-H9)),IF((I9-H9)+(K9-J9)>=180,IF((K9-J9)>=180,0,180-(K9-J9)),(I9-H9)))"
Range("U9").Formula = "=IF(K9<I9,IF((K9-J9)>=180,180,(K9-J9)),IF((I9-H9)+(K9-J9)>=180,IF((I9-H9)>=180,0,180-(I9-H9)),(K9-J9)))"
Else
Range("T9").Formula = "=IF((T8+U8)>=180,0,(IF(K9>I9,IF((I9-H9)>=180,180,(I9-H9)),IF((I9-H9)+(K9-J9)>=180,IF((K9-J9)>=180,0,180-(K9-J9)),(I9-H9)))))"
Range("U9").Formula = "=IF((T8+U8)>=180,0,(IF(K9<I9,IF((K9-J9)>=180,180,(K9-J9)),IF((I9-H9)+(K9-J9)>=180,IF((I9-H9)>=180,0,180-(I9-H9)),(K9-J9)))))"
End If............................

View 4 Replies View Related

How To Make This Macro Work For Auto Range

May 7, 2008

I have generated macro, all I want is when this macro runs on different work sheets it should automatically select the active range based upon column A and copy the formula in the inserted new column B, please see the macro which will make it clear. Basically whenever it is copying a value or a formula it should be for the active range.

View 11 Replies View Related

How Can I Make A Macro Work To Hidden Sheets?

Aug 21, 2009

OR can I? I get a "Run Time Error '1004' Select Method of Worksheet Class Failed"

Is there anyway around this?

Basically all the sheets are hidden from the user except for the Entry sheet. They input there data on there and then they press a send command button which then needs to copy that information to a hidden sheet. This is where the problem occurs.. Thought maybe their was a way around that or do I need to unhide the sheets during the macro and then rehide them again?

View 13 Replies View Related

How To Make Excel Template Work On Only 1 System

Mar 9, 2014

Is it possible to make excel template work only on 1 computer by giving any vba password (on lines of license number).

Aim is to ensure it works on only client computer, even if template is shared outside it becomes useless.(We often see clients audit team getting access to templates using it for thier purpose & distributing).

In same spirit what are best practices one can take like protecting formulas with password etc

View 3 Replies View Related

Macro To Make Pivot Table Won't Work Twice

Aug 14, 2008

Our system can create an SLK file showing customer open orders. It is ALWAYS in the exact same format. I created a macro to open this file, manipulate the data (convert text to numbers, convert text date to actual date, and a few other things) and then make a pivot table.

When I try to run it it gets hung up on the pivot table and the debug highlights the BOLD below:

Columns("F:L").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"SOLIST!C6:C12").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select

The RowField, ColumnField, and Page Fields are correct.

The QTY is the data that goes in the field.

View 9 Replies View Related

Change Y/n To 1 Or 2 If That Is Easier (1=yes, 2=no)

Nov 24, 2008

I have a column of numbers. (column U)

I have a column with Yes or No (Y/N) (column R)

I want to total up numbers in column U that have a Y in column R.

I can change y/n to 1 or 2 if that is easier (1=yes, 2=no)

View 2 Replies View Related

Simple Button Suddenly Make Excel Crash On Saving Then Replace With New One And Work?

Mar 26, 2014

So i got a few macros on a worksheet and one of them is simply :

Sub Pr_Removefilter()
ActiveSheet.ShowAllData
End Sub

Ive worked on the excel a couple of times, everything fine. Then it started crashing upon saving (after changes made that had no possible connection to the macro, on another sheet). After a few painfull trials and errors i located the problem to be this macro button. I erased it, created a new one with the EXACT macro and it works. Why?..

View 1 Replies View Related

Easier Way To Shorten Up Part Of Code That Is Repetitive With Array

Jun 3, 2013

I have a macro that works but was wondering if there is an easier way to shorten up the part of the code that is very repetitive with an array or ? Nothing I have tried works except the code below.

Code:
Sub ClearAndReset()
Dim WS1 As Worksheet
Set WS1 = ActiveWorkbook.Worksheets("Stocks")
WS1.Unprotect
Application.EnableEvents = False
With WS1

[code]....

View 3 Replies View Related

Userform, Labels Equals Range In Workbok, Easier Code

May 24, 2006

I have over 500 labels in a userform and each labels content is gathered from an workbok like this:

Private Sub UserForm_Initialize()
CommandButton3.SetFocus
Application. ScreenUpdating = False
Workbooks.Open "V:allaBeredningSemesterstänging Medleverantörer och Verkstäder.xls"
Sheets("2006").Select
' Sheets(Year(TODAY())).Select
LabelA1 = Range("A1")...............

I have shortened the code and I haven't yet written all labes. The ... is supposed to mean "from" and "to". I don't want to write over 500 lines of the label codes.
So what is an easier way to write it?

View 7 Replies View Related

Make Userform Show Data From Spreadsheet And Add Delete Or Make New Entry

Jan 24, 2014

I have a spreadsheet on sheet 1 with a list of customers and their information. So on column A I have the customer number (i.e. k968, e37, p528,...), on column B i have the customer's name, on column C the street's name, on Column D the house number, on column E the zip code and finally the city on column F.

Right now there are around 600 customers in this list.

I have made a userform with a combobox in which I want to select an existing customer (pulled from the spreadsheet). On the same userform I have textboxes (customer number, name, street, number, zip, city). When I select a customer in the combobox, I want this customer's info to show up in the textboxes. I want to be able to change the info and hit Next to store the changes in the spreadsheet. When I do not select a customer from the combobox, I want to add new info in the textboxes and hit Next to store this info as a new customer. The userform also has a delete button. Then I select a customer in the combobox, this customer (and it's info) should be deleted from the spreadsheet when i hit Delete. So the spreadsheet is variable in length.

View 5 Replies View Related

How To Make Function That Works On One Line Work On Multiple Line

May 19, 2014

I have this function that works on line 3 and if the conditions are met, the result is 1

=SUMPRODUCT(--(IfColor(B3,$A$76)*(SUMPRODUCT(--(D3D4)))))

Here how it works, if B3 is the same color as the reference cell $A$76 and D3 is different than D4 then the result is 1

I would like this function to work from line 3 to line 60 and return the total of lines where the conditions are met. I'm thinking of a =COUNTIF function but can't get something to work. If there is a simpler way, it's even better. The IfColor is a function I wrote in VBA,

View 9 Replies View Related

Convert Layout Of Excel Table To Make It Suitable For Pivot Table

Mar 27, 2012

I have a very large excel data file, which I want to analyse using pivot tables. The problem is that while most of the columns are headed with the variable name (e.g. country) and have the list of variables displayed under that heading for each observation (e.g. Italy), the years are spread across the columns - i.e. the heading for column X is not "Year", but is 2003, with the next column being 2004, etc.

Is there a quick way I can re-arrange the data so that the layout is consistent and so that I can use it for pivot tables? I have way too many observations to do this by hand.

View 1 Replies View Related

Switch Statement Is Too Long For VBA. To Make The Switch Statement Work Over Two Lines

Jan 6, 2009

I have created a very long switch statement, which is too long to be placed in one row in VBA. I have attempted to put a space and underscore at the end of one line and continue the statement on the row below by placing a comma at the start of the second line. VBA will accept my efforts, but when I run the statement in the immediate window, the following error appears.

"Invalid procedure call or argument"

I understand that there are certain rules where I can split a switch statement onto two lines, yet I do not know what they may be.

View 9 Replies View Related

Copy A Formula Across Several Work Sheet And Have The Formula Always Take Data From Previous Work Sheet

Jan 2, 2009

I am want to copy a formula across several work sheet and have the formula always take data from previous work sheet.

2) I am working with this formula =C12+INDIRECT((MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)-1)&"!"&"C12")

and it comes from this thread http://www.excelforum.com/excel-gene...orksheets.html. I have included a worksheet attachment that has explanations

View 2 Replies View Related

Work Out A Formula For My Spreadsheet Which I Use To Work Out Cutting Lists For Timber Frames

Jan 11, 2009

i need to work out a formula for my spreadsheet which I use to work out cutting lists for timber frames. I need it to work out if the width of a job is for eg 2400mm i need to work out how many timber studs I need so the space between each stud is between 400mm and 500mm and this will need to work for a range of different sizes of frames. I have it written at the moment and it just devides the width by 400 and gives me a amount of studs but it would work much better if it could space them between 400 & 500.

View 4 Replies View Related

Work Accurately With Times To Calculate The Work Progress Of The People In The Workshop

Mar 3, 2009

In a project i am compiling i need to work accurately with times to calculate the work progress of the people in the workshop thus....here goes....

I have in work book #1 (7) sheets mon to fri + complete week + a sheet where all job numbers are collected.

From monday to friday the workmen log their times as a start time and a end time. This has to be then calculated to a total hours:mins spent per job, wich in turn then has to be calculated to a total hours:mins spent per day. And the on the complete week sheet recalculated as a total time worked per week.

View 9 Replies View Related

Work With Data

Nov 15, 2006

I get the following sent to me on a daily basis and I would like to format it so that I can utilize it.

I get this:

GROFKCTotalCalls:3
TotalOrders:0
808-5600Calls:3
UNI1Orders:0

GROHJBTotalCalls:436
TotalOrders:75
3-Nov644-6200Calls:225
UNI1Orders:42
3-Nov646-7979Calls:76
UNI2Orders:15
3-Nov700-2900Calls:89
UNI4Orders:12

What I need is this:

Phone Calls Orders
808-5600 3 0
644-6200 225 42
646-7979 76 15
700-2900 89 12

How would you work with the data I get to produce what I need??? I know a macro can format the data, but maybe there is something else I can utilize that I'm not aware of.

View 9 Replies View Related

How To Get Data From Web And Make Sure That Existing Table Is Updated With New Data

Feb 12, 2014

getting data externally from web and also keeping the previous data prior to the refresh.

My external table/data has 10 rows reporting values per day (10 days data table).

I can link the table to my sheet, however what I can not do is to create an offlinek, year to date table in the sheet which captures the rolling 10 days (everytime it is refreshed) without losing the prior days.

For ex:

External bank Data

Day Ratio
02/05/2014 0.15%
02/04/2014 0.13%
02/03/2014 0.14%
01-31-2014 0.23%
01-30-2014 0.16%
01-29-2014 0.16%
01-28-2014 0.18%
01-27-2014 0.19%
01-24-2014 0.19%
01-23-2014 0.21%

Above table changes on a daily basis, only reporting the last ten days.

How can I capture this data on a rolling basis, i.e. in a year to date format, so that everytime I refresh, the internal table gets updated with the new data.

View 3 Replies View Related

How To Compare Data From 2 Sheets And Put Into A New Work Sheet

Feb 28, 2009

In sheet one I have data as follows ...

View 7 Replies View Related

Vlookup That Pulls Data From Another Work Sheet

Dec 1, 2006

i have created a Vlookup that pulls data from another work sheet, it all works fine apart from this one thing....

On a work sheet i have a lot of hyperlinks that work fine, but as soon as they get pulled over using a Vlookup, the name of the hyperlink comes accross for example "link 1" but you can not click on it to take you to the link like you can on the original one.

View 14 Replies View Related

Macro For Selective Data Validation - Which Does Not Work

Jan 17, 2013

lately I have promised to program a little macro which looked easy for me to do but on which I have now spend a considerable amount of time without finding the problem why it is not running. I am working under Windows XP with Excel 2010, here is what i was trying to do:

In a worksheet there are two columns G and H. In both columns it shall be possible to select a number of possible entries from a list. For column G this is done via data validation, no problem. Now, the choices which are there in column H shall depend on the entries made in the appropriate row of column G. Column G thus functions as an overarching category. If I choose "Tree" in G, H would give me branches, leaves, apples. If I choose "car" the choices would be wheel, engine, gasoline etc. This shall be done by a Macro:

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cat1 As Range
Dim cat2 As Range
Dim x As Integer
Set cat1 = Range("G:G") 'Task Group
Set cat2 = Range("H:H") 'Task Category

[code].....

When running, it gives me Run time error 1004: Method "Range" of object '_Worksheet' failed. Debug highlights the row

Code:

With Range(Cells(ActiveCell.Row, 8))

View 2 Replies View Related

Macro To Work On Increasing/Descreasing Data

Sep 14, 2006

I have a spreadsheet with a worksheet for each month, so as a new month begins I add a new worksheet using a macro

Each worksheet has 5 columns:

A = Vendor, B = Date, C = Debits, D = Credits, E = Balance

Row 30 contains the totals for columns C, D, & E, cell A30 contains the text Totals
The problem I have is occaisionally extra rows are added so the totals may not be in row 30

Is there any way that the macro can be changed so that it looks for the word Total in column A and then reads the contents of the corresponding cell E? to transfer that total to cell E2 on the next worksheet

View 9 Replies View Related







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