Interpolation Given A Variable Table Location & Location Of Data Within
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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Functions On Imported Data That Can Change Location In Table
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 Replies!
View Related
Insert Row At Variable Location
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 Replies!
View Related
Store Cell Location In Variable
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 Replies!
View Related
Create New Folders In Variable Location
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 Replies!
View Related
Circular Reference - Value Of That Variable Changes Depending On The Location Of The Cell
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 Replies!
View Related
Create A New Table That Displays The Information By Location Instead Of Code
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 Replies!
View Related
How To Get The Original Location Of A Data
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 Replies!
View Related
Move Data To A New Location
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 Replies!
View Related
Copy Data From Named File And Location
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 Replies!
View Related
Copy Data From User Input Location
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 Replies!
View Related
Get User To Choose Location Of Linked Data
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 Replies!
View Related
Extract Data From A Specific Location In A Text File
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 Replies!
View Related
Create Macro To Chart Data With Location As Object In Active Sheet
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 Replies!
View Related
Return To Last Location
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 Replies!
View Related
Defined The Location
If Worksheets(FirstSheet).Cells(Counter, ColtoMove) = MoveIf Then 'i didn't add the THEN PORTION becasue it errors at the above statement. the problem is that when i go to the defined location (Worksheets(FirstSheet).Cells(Counter, ColtoMove)) it shows the correct/or same PO number as when i put the cursor over the "MoveIF"....this leads me to think that it should not error. Could it be a correct statement to say how the MoveIf is defined? I have added a list of all of the potential values that can cycle through this statement. how these entries could cause a problem in the above code? I am trying to keep it simple (outside of the long list below that is) PO Number 3840 4247 4260 17467 21012 38125 90495 132284 133291 133453 133840 135496 135816 4500016105 0026974 0027060................
View Replies!
View Related
Saving In Wrong Location
I have the following code which saves my worksheet with a name extracted from particular cells: Public Sub SaveAsMaximoWO() ThisFile = Range("AC5").Value ThisFile2 = Range("E3").Value saveName = ThisFile & " - " & ThisFile2 ActiveWorkbook.SaveAs Filename:=saveName End Sub This (for reasons I don't understand) saves the file to My Documents by default. What I would like it to do is save to another folder within my documents as default.
View Replies!
View Related
Change The Folder Location
which part of this macro I need to edit to make it open a folder I want? Sub OpenFolderRequest() YesNo = MsgBox("Would you like to open the folder to see" _ & vbCr & "which files are currently there?", vbYesNo + vbQuestion, "Open Folder?") Select Case YesNo Case vbYes myval = Shell("c:winntexplorer.exe c:winnt", 1) Case vbNo End Select End Sub I have tried changing the (c:winntexplorer.exe c:winnt) to the location name of the folder I want to open (S:Stock ControlSTOCK CONTROLOrder Confirmation)but it doesn’t seem to work.
View Replies!
View Related
Copy A Picture From On Location To Another On The Same HD
First of all, I would like to thank all Excel Forum Moderators and Members especially Andy Pope for the great effort they exert to help all members. Lately, I viewed the thread available on the following link and I admired the great perfect work and codes provided by Andy Pope so much, http://www.excelforum.com/excel-prog...-userform.html My request is similar to the points discussed in the above mentioned thread, but, it is a little bit different .. Instead of inserting the picture to the worksheet, I want the picture to be copied from one folder on the HD to another folder called (Employees photos), which is located in workbook's path. In other words, I need a code to copy one jpg picture from one location to another.
View Replies!
View Related
Default File Location
I have had my server removed that I had my default file location, when I go in to change any of my options the first thing it does is go look for this file location and brings back an error stating it cannot find the server. I cannot find anywhere else that I could change it.
View Replies!
View Related
Formulas In A Central Location
Is it possible to have the formula written in one remote cell, so that any modification done in this formula is reflected on all places where its refereced to? z26 : Formula A A1 : Formula A A2 : Formula A A5 : Formula A A6 : Formula A A7 : Formula A A9 : Formula A A11 : Formula A in this example i store the formula in z26 and variety of cells in A are using it (not all of them)Suppose if i change anything in z26. I want it reflected in all the cells that are using this formula?
View Replies!
View Related
Repeating Code In A Different Location
I have some code that copies and pastes data from one cell into a range. The one cell is B4, and the range is A6:A10. This same action must be done every 30 lines (so the next time the cell is B34 and the range is A36:A40). What do I need to do to make this action repeat/loop every 30 lines?
View Replies!
View Related
Moving Rows To A Specified Location
I am trying to do work on a sheet and am getting very frustrated. I have a list of several different data entries that contain a row of information. I need to have the excel sheet move these rows to a different sheet in a specified location. For example, in the first column of the data I have the list of commodities, (sugar, cotton, yen, euro, and copper). Then in the second column I have the price and then the quantity. I want to move them automatically from sheet 1 (where they are entered) to a specific location on Sheet 2. I want Cotton to be in A2, Sugar to be in A20, Yen to be in A30, Euro to be in A40 and copper to be in A50. I would like the data to be cut from sheet one and inserted into sheet 2 so that the sheet can move them every day.
View Replies!
View Related
Changing Chart Location
Sub autograph() Dim x As Long, t As Long, y As Long, z As Long y = 3 z = 5 x = 4 While x < 609 With Worksheets("cabernet (2)") t = Cells(x, 1).Value Charts.Add ActiveChart.ChartType = xlLineMarkers ActiveChart.SetSourceData Source:=Sheets("cabernet (2)").Range("B" & y & ":H" & z), PlotBy:=xlRows ActiveChart.Location where:=xlLocationAsObject, Name:="Cabernet (2)" ActiveWindow.Visible = False ActiveChart.HasTitle = True ActiveChart.charttitle.Text = " " & t Windows("complete Favorite Genes.xls").Activate y = y + 3 z = z + 3 x = x + 3 End With Wend End Sub This time, however, instead of having a slight idea of what I need to do, I don't have the slightest of where to start. I need to move all of these charts into another sheet, sheet1, and i'd like to offset them or do something so that they aren't all right on top of each other. I'd be more specific but I don't really know exactly what you can or can't do.
View Replies!
View Related
Finding The Location Of A Cell
i am trying to find the location of the current cell and use the information to print it to screen. For example, if the cell A1 is selected, i need to get the location A1 and use this value for a vlookup. is there a method that returns the current cell location "A1"?? activecell.address
View Replies!
View Related
Amending Chart Location And Size
I have craeted a chart in VBA, which sits in the middle of the page . I have attempted to move it to a new location with the following code ActiveChart.Parent. Name = "MyChart" ActiveSheet.ChartObjects("MyChart").IncrementLeft -237.75 ActiveSheet.ChartObjects("MyChart").IncrementTop 32.25 ActiveSheet.ChartObjects("MyChart").ScaleHeight 0.96, msoFalse, msoScaleFromTopLeft ActiveSheet.ChartObjects("MyChart").ScaleWidth 1.17, msoFalse, msoScaleFromTopLeft which I adapted from a recorded macro, but which fails in execution, error message indicates that the Object does not support the property or Method.
View Replies!
View Related
Find The Location Of A Duplicate Value In Array
Is there a good way to find out after the exists returns True in which place in the array the Z is located in ? ... With CreateObject("Scripting.Dictionary") .CompareMode = vbTextCompare Z = a(i, ii) & ";" & a(i, ii + 1) If .exists(Z) Then msgbox "The array location for existing Z value is ..."... ...
View Replies!
View Related
Move/Cut Cell To Another Location
I would like to how I can move the words "Total List Price" over to column "C" rather than have it displayed in column "A". If someone can please advise me as to what part of the code I must change in order to have these changes take place Private Sub CommandButton1_Click() Selection.SpecialCells(xlCellTypeFormulas, 16).Select Selection.ClearContents Range("A8").Select Selection. Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(6, 9), _ Replace:=False, PageBreaks:=False, SummaryBelowData:=True Application.Goto Reference:="models2" ....................
View Replies!
View Related
Insert Rows At Specifice Location
I need a macro that will look in column B of a worksheet for the value "Grp2" and begin inserting rows underneath that value based on a variable that I will pass to it. The variable will be how many rows to insert. For example if value "Grp2" occurs in cell B19,beginning at cell B20 I want to insert a specified number of rows. ******** ******************** ************************************************************************>Microsoft Excel - Test2.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutJ19= ABCDEFGHI19XLRTGrp2CODERTSCFLTROASMLTGDOP*20*Begin*insert*rows*here*******21*********22*********23*********24*********25********326XLRTGrp3CODERTSCFLTROASMLTGDOP*27*********Test* [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View Replies!
View Related
Reference A File Location In A Formula
if this is possible, but is there anyway to reference a file location in a formula using another cell? EG: Cell A2 (Text) = C:Folder1Folder2Folder3Sheet v1.2.xls Cell B2 = =A2'tab1'!$A$1 So, in cell B2 it will use the file location in cell A2. I need this because the values will be updated in Sheet v1.2 and version controlled (so if it changes to 1.3 all you have to do is change cell A2).
View Replies!
View Related
Saving Workbook In VBA And Location
I am using this code in my ThisWorkbook, but it keeps coming back as an error for ActiveWorkbook.SaveAs Filename:=ThisFile Private Sub Workbook_BeforeClose(Cancel As Boolean) 'Saves file as what is in cell "C7" to specified network location - variant below Dim Location, ThisFile As String Location = "\Office-pcpublicCustomers" ThisFile = Range("C7") & Format(Now(), "dd/mm/yy") ThisFile = Location + ThisFile ActiveWorkbook.SaveAs Filename:=ThisFile End Sub
View Replies!
View Related
Advanced Filter Copy To Another Location
I seem to be having an odd problem with Advanced Filter. When I use the Copy to Another Location option I get an error that: "The extract range has an illegal or missing field name". As far as I can tell, there are no blank or missing fields. I did originally have a DATE field but I have renamed this in case it was a "reserved" name but still have the problem. When I used the filter in place it works correctly. I am trying to use Advance Filter as opposed to lots of looping, copying and pasting and this is the only snag. I suppose that I could filter in place then copy/paste the results to the other sheet but I'd like to see where I may be going wrong.
View Replies!
View Related
Disable In-book Location Saving
I save a workbook, the location where I were last (ex cell Z42) is also saved and upon re-opening it again, it opens the sheet to the last location (Z42). So how could I make the workbook and all its worksheets to open at a defined location. ie. A1 for instance.
View Replies!
View Related
Generate A Folder Within A Specific Location
I am trying to generate a folder within a specific location (For now lets call it C:Jobs) that will be named as per the adjacent cell (Column H on attached) Obviously if the folder already exists I want the code to stop. But this code will have to generate a different folder for each row within the spreadsheet.
View Replies!
View Related
Trusted Location - How To Save To And Access
managed to get a piece of code working to create unique sequential numbers for purchase orders, but only by "enabling all macros" which is apparently not recommended and could conceivably, as I understand it, leave a PC vunerable to viriuses contained in other imported files. Please bear with me as I'm very new to anything other than basic Excel functions; macros were, until last week, something I didn't even know existed, let alone how to use them. Anyway, having got my macro working, I understand that the best thing to do is put it in a "trusted location" from where it will work automatically without requiring operator input (whilst still maintaining high overall security), rather than "enabling all macros", but I have a couple of issues with this. Firstly, having created a folder in "my documents" as per the instructions, how do I save the macro there? What format should it be in? Surely not a word document? (as you can gather I am still really in the dark about all this). If not, what? Secondly, how do I direct Excel to access and run the macro from this "trusted location" folder, rather than the already existing "module1"?
View Replies!
View Related
Change GetOpenFilename Default Location
When I use the GetOpenFilename() method, it seems to default to the particular users My Documents. Is there a way to force it to default elsewhere like a share drive, or the users desktop? In the same line of thought, if I have a file named TestFile.xls. and I want to try to open it from the users desktop if they have it, how can I do this, since their desktop location is different than mine?
View Replies!
View Related
Save To Specific Location As Cell Name
I've been searching for ages trying to work out how to do this but have so far only managed to confuse myself. i have office 2007 and I have found some code to convert an excel sheet to PDF, however I need it to saveas the contents of cell (e6) and save to a location on our network drives (C:TEMP).
View Replies!
View Related
|
TRACKER
