Interpolation Given A Variable Table Location & Location Of Data Within

Oct 6, 2009

I am trying to develop a spreadsheet that will calculate a cost based on a matrix. I am attaching a sample of the calculation created so far. The end result is in cell M13 and is highlighted in yellow. I kind of layed the formula out in a few different cells, so hopefully it would be easy to follow.

simplify this process with maybe another formula that I might not be aware of, or maybe show me how to get this done in VB code. I think VB code would be the correct way to go just not sure.

View 6 Replies


ADVERTISEMENT

Functions On Imported Data That Can Change Location In Table

Feb 7, 2007

I am trying to achieve can not be based on a range of rows or cells it must as this data is imported from a forecasting application and the location of the targets may change.

Perhaps an offset to the current selection can be used some how, but have a look-see if you know where Im coming from.

'I have used this to find a target in a data range.

Dim r1 As range

Public Sub FindDataIn Range(r As Range, target As Variant)

Set r1 = r.Find(target)
If r1 Is Nothing then
Msg Box target & " was not found"
Else
r1.Select
End If
End Sub

'Then I inserted and Named this procedure to find the data on various assumptions or targets - SUCH AS THE VALUE "14306".

FindDataInRange ActiveSheet.Range (A1:A226), "14306"
Selection.EntireRow.Select
Selection.Copy
Selection.Insert Shift:=x1Down
Selection.EntireRow.ClearContents

Then I write this again for another Target such as 14307 and it repeats

The result is that it finds the target cell I get an empty row above the two rows that contain that target. (LET ME EXPLAIN WHAT I MEAN BY TWO ROWS)

The thing is there are two rows containing 14306 in that range and the range is sorted ascending so that they are positioned one under the other. Each row has different forecast totals beside this number because one is an export SKU and one is a Domestic.

What I want to do is combine the two rows as one with one row of forecast totals for the number rather than two.

Like this:
14306big Widget Domestic 26 89 (This is combined as a new row)

Instead of this:
14306big Widget Export 12 14 (These 2 rows are then deleted)
14306big Widget Domestic 14 75

Also the Forecast totals run across 12 columns (one for each month) and then there is a column for year totals of each row that needs to remain the same.

Most important this here is this cannot be based on a range it must as this data is imported from a forecasting application and the location of the targets may change.

View 9 Replies View Related

Insert Row At Variable Location

Dec 3, 2009

I had a quick question for something that should be simple but I can't figure it out. I have a column of sorted values, it goes

A
A
A
A
A
B
B
B
B
B

and I want to insert a row to separate the A's from the B's. But there will be a random number of A's. I can figure out how to count where the A's end. But I can't figure out how to insert a row. Any suggestions? I tried the two below but they don't seem to work. The for section is counting the A's, and it works, it's what comes next that's giving me a headache!

View 2 Replies View Related

HLOOKUP With Variable Range Location

Aug 28, 2013

I have an issue with the below code:

VB:
LastRow1 = ActiveSheet.UsedRange.Rows.count
ActiveSheet.Range("R1:R" & LastRow1).FormulaR1C1 = _
"=IF(RC[-1]<>"""",HLOOKUP(RC[-1],Sheet2!R60:R61,2,FALSE),"""")"

The issue I have is that in sheet2 the HLOOKUP range i want to lookup will not always be in R60:R61 these rows vary depending on the amount of data above this range... is there a way of incorporating some sort of

VB : LastRow = ActiveSheet.Range("B" & Rows.count).End(xlUp).row + 1

To locate the bottom of the data and the start of the HLOOKUP range?? if is there some way of re-coding it?

View 3 Replies View Related

Store Cell Location In Variable

Jul 13, 2006

way to store a cell's location to variables.

Something like:

int a, b
Cell(a, b) = ActiveCell

I'm currently working with a fairly large worksheet, and I'm using Cells. Find to look for a specific cell. Then I want to Filter that column, but I can't figure out what column Selection.AutoFilter Field:=? should be.

View 6 Replies View Related

Create New Folders In Variable Location

Oct 17, 2006

I would like to create new empty folders from the list in column B.

The number of folders will vary depending on how many entry in column B.

I need the folders to be created in an existing folder in the "current directory" called "Shop_Drawings".

The following code may be able to be modified.

Sub CreateFolders()
Dim MyFile As String
Dim sDir As String
Dim rng As Range

Set rng = Sheets("Matdata").Range("B2")
While rng.Value <> ""
MyFile = rng.Text

sDir = "CurDirShop_Drawings" & MyFile
''above is where I am having trouble...don't know the correct syntax''
MkDir sDir


Set rng = rng.Offset(1)
Wend
End Sub

I have attached a sample workbook.

I have been getting by with code which requires changing the destination in the module whenever making folders in different diectories or drives.

View 6 Replies View Related

Circular Reference - Value Of That Variable Changes Depending On The Location Of The Cell

Mar 16, 2009

Excel 2003 > Attached is a small model of what I am trying to accomplish. Cells B2 and B4 contain the same formula a formula that calls a simple function. The function has a variable passed to it and the value of that variable changes depending on the location of the cell. Now, see the function in Module1 it is called CellCalc. If the variable passed = Jim then value = 3. If the variable passed = Jack then value = 8. That is straightforward.

Note also that when Sheet1 is activated, I calculate the cells from left to right and top to bottom that is important. Here is the challenge. If B2 > 1 then I want to add B2 to B4 and set B2 to 1. I can set B4 properly but I cannot reset B2. You can see my 2 attempts that are commented out. Is there some way of accomplishing this or am I simply stuck in a circular reference?

The alternative to this is to write a function that operates externally on these cells. That will work for sure but then the values of the calculations will overwrite the functions in those cells, thereby taking away the dynamic nature of this application.

View 4 Replies View Related

Excel 2013 :: Macro - Paste To Column Cell Location Based On Variable?

Jun 16, 2014

I have data in E6-E67 on Sheet 1. Based on the date in A2 on that sheet, I need to paste to a column in Sheet 2. In excel, I am able to get the cell location through vlookup and get the correct column number/cell reference. When it gets to the paste location, I am stumped on how to format that line of code? Do I need sometime of variable? I tried to use the address/lookup code but it does not work.

I have excel 2013.

View 4 Replies View Related

Find Out Row And Column Of Location Of Hlookup Table?

May 4, 2014

After I run Hlookup on a product, I would like two variables, Row and Col, to assign the actual row and column of the spreadsheet to the location in the table. I have a small worksheet/program that I have attached, what I am trying to do. The program works, but my code is very long for the little bit of information.

Sales.xlsm‎

View 2 Replies View Related

Get Cursor Location In A List Object Table

May 19, 2014

I know how to get the row number of the cursor in a spreadhseet (Activecell.Row), but how do I get the cursor location in a list object so that I can then insert a new row for the user at that point?

BTW, I am using tables (ListObjects) because when a row is inserted, all of my formulas are automatically inserted.

View 1 Replies View Related

Assigning Headers To Table According To File Name And Location

Aug 12, 2014

I need to assign headers to a table according to the file name and location. I have attached the example spreadsheet. Sheet1 contains the table with the data and Sheet2 assigns the headers to each file and location. The code is skipping headers and I can not figure it out.

Macro Example.xlsm‎

View 4 Replies View Related

List Folder Location And Filenames Into A Table And Color?

Apr 14, 2014

i want to use the "Browse for Folder" to select the folder where the files is in eg. E:My DocsGlobal and list the folder location and filenames to table (column I:J), and color them according to every folder location.

I would like the "Browse for Folder" windows to be able to select multiple files instead of just one file a time.

View 1 Replies View Related

Create A New Table That Displays The Information By Location Instead Of Code

Sep 7, 2007

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!

View 9 Replies View Related

How To Get The Original Location Of A Data

Dec 2, 2008

I have a range b2:g37 filled with numbers. I use max function to get the max number in this range in cell C41. For example, the number is 20400. Now I want to know where the 20400 is originally located in the spreadsheet (ie. in column B, C, D, ...? in row 2, 3, 4, ...?).

View 9 Replies View Related

Move Data To A New Location

Sep 27, 2009

I have a spreadsheet that I need to "relocate" data in. I need to take all of the narratives and want to move it to the far right so that it shows up in column "Q" I was hoping to be able to insert it and fill down, can this be done? ...

View 9 Replies View Related

Finding A Cell In A Variable Location Based On The Contents Of The Cell

May 2, 2012

I am creating a macro to automate data analysis for work, but I've become stuck.

I need to find the coordinate of the first cell in a column with word "reserved" or "extended" contained in the cell, and then assign the row number of that coordinate as a variable to use for moving data around. Basically, I want something like VLOOKUP, but instead of returning a value, I want it to return the coordinate.

View 1 Replies View Related

Data [Advanced] [Copy To Another Location]

Jul 20, 2009

When I use Data, Advanced, Copy to another location the records that are copied are not unqiue to the Criteria range? If my criteria range is Cape it extracts records with Cape AND records with Cape Town. I only want to extract records with Cape?

View 3 Replies View Related

Counting Cross Location Data?

Mar 5, 2013

I have a list of data as follows:

Employee Location
John Florida
John New York
Jill Maine
Jack Maryland

I would like to determine if an employee works across locations. My complete list has 550 names in it, this is just a subset of the data I am looking at. Above, John works in 2 locations, however Jill & Jack work at a single location. I am looking to differentiate cross locational versus single location employees.

View 4 Replies View Related

Copy Data From User Input Location

Mar 18, 2009

I'm using a worksheet as a surveying program. I need to be able to enter a cell reference in my "input cell", ie "=A11". Then in cell the the right of the input cell I need "=B11" to be entered. And in the cell to the right of that, I need "=C11", and to the right of the I need "=I11". I can get so far working with one cell at a time.

If I enter "=A11" in T3, in T4 I can enter: =IF(T3=A11,B11,IF(T3=A12,B12,IF(T3=A13,B13..and so on. But 8 conditions is all I can enter before I get a message saying that the formula uses more levels of nesting than are allowed in the current file format. And I need to be able to enter at least 50 conditions in 3 adjacent cells.

View 6 Replies View Related

Copying Data Irrespective Of Folder Location

Jun 4, 2012

I have a folder "Macro" in the below location in my PC:

C:UserskkumarDesktopMy WorksMacro

There are 4 excel files in this folder:
Sales.xls, Quantity.xls, Forecast.xls and Macro.xls.
Macro.xls has 3 sheets: Sales, Quantity & Forecast.

I want a macro which will pull all data in:
Sheet1 of Sales.xls to "Sales" sheet of Macro.xls
Sheet1 of Quantity.xls to "Quantity" sheet of Macro.xls
Sheet1 of Forecast.xls to "Forecast" sheet of Macro.xls

Also one additional requirement is if I copy the Macro folder to some other location in my PC the macro should still work.

View 3 Replies View Related

Copy Data From Named File And Location

May 27, 2006

I am trying to set up a file name and path in a cell, and then use this from a number of other cells but with a cell location added to it. So for example:

cell A1 contains a file name "c:mydirectoryexcelfile.xls"

cell A2 needs to contain the contents of the cell at location K12 (for example) from the file referred to in A1
cell A3 needs to contain the contents of the cell at location K13 (for example) from the file referred to in A1

This is to save having to put the filename and cell reference in all of the of cells. I would then do the same thing with another file in column B of this file and the same with column C etc.

View 2 Replies View Related

Get User To Choose Location Of Linked Data

Sep 18, 2006

I've have a spreadsheet where 4 cells are linked to another workbook via a vlookup.
the problem i have is that a lot of users can update this external book, or it can be saved as a seperate spreadsheet somewhere else on the network. If it was up2 me i would have them only update the one sheet, but as it stands its not. So what i want to be able to do is put some code onto a button on the sheet, from here i want the: Application. GetOpenFileName

method to open....but from here i want them to be able to pick the cells where the data is situated. Any clues..... i can get as far as them selecting a workbook. Do you think i will need to create another userform?? Maybe RefEdit? I'm not sure.

View 3 Replies View Related

Transfer Data From Userform To Specific Location Not Next Empty Row

Mar 14, 2014

I have a User form with a combo box that is populated with numbers (1 thru 50) and four text boxes for first name, last name, email & cell number.

It all works fine. However: I would like it to transfer the data to (Sheet3) in numerical order... In other words, If the user picks number 5 his data would be entered in the fifth row.(or sixth counting header). Or if he chooses number 37, his info would be entered into row 37 (38 with header) of (Sheet3)

It currently populates the next empty row.

My code is below, How would I modify it to accomplish this?

Code:
Private Sub EnterButton_Click()

'Populates GetNumber Combo Box

Dim w As Worksheet, x As Long
Set w = Sheets("Sheet2")
x = w.Columns(19).Find(Me.GetNum.Value, lookat:=xlWhole, LookIn:=xlValues).Row
w.Range("S" & x).Delete

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

View 2 Replies View Related

Extract Data From A Specific Location In A Text File

Aug 7, 2006

I have been trying to work this out by looking at other posts (mostly concerning Binary Access) but can't figure it! The source text files I am using can vary in length from 4,000 characters to well over 100,000 characters. However the data I am looking for always starts 40 characters from the end of the file and is 10 characters long! I need my macro to pick out this data and store it as a string (so it can be added to an array and exported to a worksheet later)

View 2 Replies View Related

Copy Data From Multiple Workbooks To Single Workbook In Another Location Using VBA

Jun 10, 2014

I have a folder which contains multiple 'Customer' workbooks (example attachment 'Customer_001'). Each workbook has a filename unique to the customer (Customer_001, Customer_002, Customer_117 etc). The workbooks contain a single sheet with customer information and answers to questions. These 'Customer' workbooks are automatically saved into a folder once the customer completes a Userform and clicks 'save'. Potentially, there could be 100's of customers' workbooks saved in the folder, each with their own unique filename.

I also have a 'Master' sheet saved in a different folder (example attachment 'Master'). The 'Master' workbook has multiple sheets named 'Department 1' and 'Department 2'. The purpose of the master sheet is to consolidate all information from the individual customers' workbooks.

Specifically, I would like a command button on the 'Master' workbook to execute the following tasks...

1. Copy the data from range A3:F3 from each of the 'Customer' workbooks held in the folder.
2. Paste the data into the next blank row on the 'Department 1' sheet in the 'Master' workbook.

3. Copy the data from range A7:F7 from each of the 'Customer' workbooks held in the folder.
4. Paste the data into the next blank row on the 'Department 2' sheet in the 'Master' workbook.

5. Save the 'Master' workbook.
6. Delete all 'Customer' workbooks in the folder.

View 3 Replies View Related

Create Macro To Chart Data With Location As Object In Active Sheet

Oct 11, 2006

I have been trying to create a macro in excel to chart a selection of data and to output the chart on the active sheet where the data was taken (as opposed to a named sheet). So basically, I have about 300 worksheets with data, and I would like to have a button on each page that automatically charts that data when clicked, and outputs the chart to the page where the macro was clicked. However, I have not been able to figure out a relative reference that will allow me to make the LocationasObject reference simply the ActiveSheet as opposed to a specifically named sheet. See my code below, which references an output to a worksheet called "Charts". Right now, all of my charts are outputting to the sheet called "Charts", as opposed to the active sheet.

Sub ConsDiscChart()
ActiveCell.Offset(29, 11).Range("A1").Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1:B1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Offset(0, -1).Range("A1:C24").Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.Location Where:=xlLocationAsObject, Name:="Charts"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub

View 5 Replies View Related

Excel 2007 :: Copy Column Of Data To New Location Ignoring Blank Cells

Apr 30, 2010

I am trying to create a formula which will look at the data in columns A - I (50 rows) and copy this data to columns K - S but ignoring any blank cells. Thereby consolidating the data in the upper rows with no spaces.

View 10 Replies View Related

Copy Individual Data Values In One Column To Single Cell Location On Multiple Sheets?

Jan 24, 2014

I am trying to come up with the most efficient way to copy data to multiple sheets within the same Excel workbook. The original data exists within one column on a summary sheet (could have up to 500 individual entries). I want to copy each individual entry to a unique sheet (that already exists), but in the exact same cell location within each sheet. I would only want to copy the original data value and not any formatting. Is there an efficient way to do this?

In my example spreadsheet, the original data is on the SUMMARY sheet. Sheets A through J would be the target sheets, with cell B2 as the target location for each of those sheets. My example shows the result of a manual copy paste value process, but I am hoping to automate that.

View 14 Replies View Related

Find Row Number Of Value From Table For Interpolation?

Feb 13, 2014

which function to use to find the row number of a value for interpolation.If I have a table of 5rows and 5 cols, how to find those rows of those 2 numbers between which my interpolation should be done.

X Y
2.2 3.45
2.9 4.56
3.3 6.2
3.7 7
4 8.1

Now I want to interpolate for 3.1. So how to find the row numbers of 2.9 and 3.3

View 1 Replies View Related

Return To Last Location

Jul 6, 2007

How do I write a VB Script to take the user back to where they came from.

Example:

User is on Sheet 1 and clicks a macro that takes him to Sheet 10.
I need a macro that is on Sheet 10 that takes the user back to Sheet 1.
BUT if the User is on Sheet 5 and clicks on a macro that take him to Sheet 10
I need that same macro on Sheet 10 to take him back to Sheet 5.

View 14 Replies View Related







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