Importing Formula / Code From One Cell To Another To Affect Multiple Cells?
Mar 5, 2014
The idea is to select an item i one cell, which in return will affect the following cells with the nutritional values. So far so good. I would then like, if a given amount is entered, it calculates the values.
A food item is typed in Cell A. The following cells will Index Match a table (on a different sheet), importing calculation formulas based on the food items nutritional value (Cells C - G). If an amount is typed in Cell B (i.e. 100 grams) the following cells will then calculate the correct nutritional values.
i learned to make an INDEX MATCH function, so that when i type "Gulerod" or something like that, the following cells import information from the Table tab... When the table contains fixed values, this works just fine. However - i tried to make the table (the cells) with calculation formulas (i.e. =SUM(4*B3))... this means the values in the tables are 0 untill a value is typed in B3 (in this example).
... however when i do the Index Match in my main tab, only the "0"'s are imported, and no matter what value i enter in B3 in the main tab, the following cells remains with the fixed value 0.
... so my question is - is it even possible to have the Index Match function import the calculation formula from the table and have it work in the main tab as well? Or is there any other way to make this work in Excel? Basically - to have the main sheet find the relevant data to use for calculation on a different tab/sheet, and then do the actually calculations on the main tab/sheet.
View 5 Replies
ADVERTISEMENT
Sep 7, 2009
I have a rather simple problem (tough for me!). I am setting up a food log. I would like to have one column with common food items that I have in a drop down list. The list would be about 20+ things. The other columns would have relevant nutritional info such as calories, fat, carbs, etc.
The problem is how do I make the values in the other columns change depending on which food item I pick from the drop down list?
For example, if I choose bread from the list, how would I get the columns for carbs and calories to change accordingly.
The IF function would be too hard because I couldn't nest 20+ IF functions in the formula for each cell. Also, the drop down list would change as my diet changes, possibly reaching 50+ things.
Also, I would like to manually enter a serving size that would alter the nutritional values in each column (for example 0.5*Calories or Carbs).
It seems so simple in my head, but I can't figure out how to get it to work.
View 6 Replies
View Related
Feb 9, 2012
I have many formulas on my (inherited) spreadsheet in row 4 through 10000 Then in row 2 there are sumproduct and subtotal formulas
When I hide and show rows only the formulas in row 2 need to be recalculated. Yet, it recalculates ALL formulas
Is there any way to control which formulas get recalculated or is it locked to recalculate all of them
It really slows down the process
View 1 Replies
View Related
Sep 12, 2007
Is it possible to use the marquee scrolling text affect in a cell in excel?
I want to try and make my sheet a bit slicker and this would look great.
View 11 Replies
View Related
Jan 26, 2014
How do I get a drop down (gender list) to affect the data in another cell? ie when male is selected this automatically affects another cell - data 13.5 and when female is selected as gender the cell data is 12.5.
View 3 Replies
View Related
Aug 26, 2013
I am using the code below to combine the rang of cell values into 1 cell. with this code I need to select the range of cells manually and run the code then repeat it on the next row manually.
What I need is automatically find the range from selected cell till last cell in the row and run the cod for this range then do the same on next row until last row of the sheet.
Code:
Sub JoinAndMerge()
Dim outputText As String[code]....
View 1 Replies
View Related
Sep 16, 2013
A little background on what i am doing. I have a spreadsheet that tracks when i have blown the dust out of our computers. I have set up conditional formatting so that the text turns red after 300 days and the cell turns yellow after 600 days. However, some computers are in high dust environments. I would like the spreadsheet to tell me to blow the dust out of these sooner. see the attached sheet.
dust 1.xlsx
View 2 Replies
View Related
Apr 18, 2008
I'm trying to set a cell on one sheet to be equal to the product of two cells on another sheet. The problem is that one of the cells on the other page is dependent on the variable T. This is what I've got and it's giving me errors
Worksheets("Output").Range("K14").Formula = "Worksheets("Calcs").Range("D17")*worksheets("Calcs").Range("D17").Offset(10+T,0)"
When I record a macro it gives me this, but again, I need the last cell in terms of "T"
ActiveCell.FormulaR1C1 = "=Calcs!R[3]C[-7]*Calcs!R[14]C[-7]"
View 3 Replies
View Related
Nov 7, 2013
I want to create a string of 0's and 1's in the same cell in excel.
I used the function =randbetween(0,1) to generate 1's and 0's. Just wondered if there is a way to repeat this multiple times in same cell so it looks like 1 0 1 0 0 1 etc... as an example. Or a way to merge cells?
View 3 Replies
View Related
Jun 27, 2013
I have a workbook with a button that will run a macro to open files and import sheets based on if the user checked the box. Everything works perfectly EXCEPT for the importing of the sheets from newly opened workbook.
Here is what I want the code to do:
1. if the box is checked then open the file
2. Copy (import) the first sheet in the newly opened workbook and put it before the 1st sheet.
3. Close the workbook that the sheet was copied from and do not save changes
4. active the workbook that has the newly imported sheet.
And here is what i have thus far:
Code:
Sub OpenFiles()
Dim Folderpath As String
Dim cell As Range
Dim r, LRow As Single
Dim Dbook As Workbook
Set Dbook = ActiveWorkbook
[code]....
After stepping through it, the problem is here:
Code:
Workbooks.Open Filename:=Folderpath & Range("B" & r).Value
Source = Range("B" & r)
Sheets(1).Copy Before:=Workbooks("CompletionWorksheet.xlsm").Sheets(1)
'How the hell do I close this ^ workbook
ActiveWorkbook.Close savechanges:=False 'This closes the workbook where the sheet was copied to
Windows("CompletionWorksheet.xlsm").Activate
Not sure what to do.
View 3 Replies
View Related
Jul 3, 2007
with code in which I import data from the web via a query then try to use end(xlUp).row to find the last row of the query. Right now, the code continues while the worksheet still displays "Getting Data...", so the last row always comes out as row 1. After the data is finished importing, however, the last row is most definitely not 1.
If I put a break point after the query and wait for the data to finish importing, then let the code finish, it runs perfectly. But how can I have the code automatically wait to move on until the data is finished importing?
View 9 Replies
View Related
Feb 27, 2008
I have part of macro to import a txt file into excel but it is not working as it should. It is giving me a message "This file is not a recognizable format" but its just a normal txt file. Also after it gives me this warning it will open in excel and with every new txt file it gives me this warning and after it opens it.
The code I am using is:
Sub su()
Dim oWbk As Workbook
Dim sFil As String
Dim sPath As String
sPath = "C:Historical" 'location of files
ChDir sPath
sFil = Dir("*.txt") 'change or add formats
Do While sFil "" 'will start LOOP until all files in folder sPath have been looped through Set oWbk = Workbooks.Open(sPath & "" & sFil) 'opens the file
Workbooks.OpenText (sPath & "" & sFil), Comma:=True, DataType:=xlDelimited
' do something
sFil = Dir
Loop ' End of LOOP
End Sub
In this line it gives me Runtime error 1004 Method 'Opentext' of object workbook failed:
Workbooks.OpenText (sPath & "" & sFil), Comma:=True, DataType:=xlDelimited
View 9 Replies
View Related
Aug 24, 2012
So I have a sheet with 600 or so rows of data on a sheet called "Agent_Summary". On another sheet in the same workbook I have a list 20 peoples names. I am trying to find a way to look at the 600 or so lines on the Agent_Summary in column A and see if the name in that cell matches any of the 20 names from the "Team" Sheet. If the name on Agent_Summary is not found on the "Team" sheet then it clears the contents of that cell and moves on. Then it comes back and Deletes the blank rows. I can make it look for any 1 of the 20 names and the code works fine. How do I turn it into something that will look for any of the 20 names?
Here is my code to look for 1 of the 20 names that works great.
Code:
Sub Clean_up_Agent_Summary()
Dim Cell As Range
Dim R As Long
Dim RE As Object
Dim Rng As Range
Set Rng = Worksheets("Agent_Summary").Range("A5:A600")
Set RE = CreateObject("VBScript.RegExp")
[Code]...
"Worksheets("Team").Range("A5")" is where my 1 name is and the rest are just below that. I am thinking some kind of an Array but I am stuck on getting that set up and working right.
View 6 Replies
View Related
Dec 3, 2012
I have a workbook that contains over 100 worksheets with stock data and price information. It uses a screenscraper to update the workbook each day with the latest day's price, and then exports each of these .csv files into a local directory. The macro for this is working. In effect it is generating a price history file automatically in my absence for me that can be read by charting software.
What I would like though is a macro to loopthrough and backfill missing price histories on each worksheet. I have a source of .csv price histories already, but would like to avoid having to cut and paste each of the 105 files manually, as it may become a regular occurence.
Each worksheet that requires backfilling has the stock code in the cell "A2", so that can be used to search for the filename Range("A2") & ".csv"
This is as far as I have got - however it results in a runtime error (91) Object variable or With block variable not set, pointing to this line:-
VB: Workbooks.Open Filename:="D:FinancialData SheetsSpreadsheetsPension" & Ws.Range("A2").Value & ".csv"
VB:
Sub BackFillData()
Dim Ws As Worksheet
For i = 1 To 105
'Sheets.Add
ActiveSheet.Name = "Fund" & i
[Code] ......
View 2 Replies
View Related
Jun 15, 2013
I am troubleshooting my macro that seems to cause a data shift with the Letter "F" when I import multiple CSV files into one spreadsheet. When I go outside of the macro and record a macro and import the CSV into a workbook it works perfectly fine. But there must be something in this code that is causing the shift:
VB:
Private Declare Function SetCurrentDirectoryA Lib _
"kernel32" (ByVal lpPathName As String) As Long
Public Function ChDirNet(szPath As String) As Boolean
Dim lReturn As Long
lReturn = SetCurrentDirectoryA(szPath)
[Code] ....
I have attached an excel workbook that shows how the macro imports vs. a regular import. Why "F" is causing a shift in the data.
Data.xlsx
View 3 Replies
View Related
May 6, 2009
I have an excel spread sheet with about 300 tabs. each sheet has the same column fields..I need to pull certain column fields ( the same fields ) out of each tab and export them to another spread sheet.
View 6 Replies
View Related
Feb 23, 2012
Im trying to import data from multiple worksheets. The data from these worksheets are scattered throughout the different work sheets.
I would like to extract the specific data from each worksheet into a master template that ive created. 80% of the worksheets templates match my master template.
Is there any way to do this without taking 1 week to complete?
View 1 Replies
View Related
Feb 18, 2013
i want to import data from multiple excel files (with same kind of data) into a master file with each import want to display name of the file from which the data is imported in last column on all rows. for example if file 1 has 10 rows with file name abs, after import the macro show display on all 10 rows in a blank column abc. then import second file and its name and so on.
View 3 Replies
View Related
Apr 16, 2008
This is a great bit of code (I found), to import multiple text files.
I have one small issue -
currently it imports with each file going to a new column, like this: text file1 A1:A4, txt file2 B1:B4, txt file3 C1:C4 etc etc
And eventually i risk running out of columns because I'm working with a lot of text files.
Can someone tell me what to do to modify this code so it imports like this? :
text file1 A1:D1, txt file2 A2:D2, txt file3 A3:D3 ETC ETC
.e.g. first file imports across row1, second file across row2 etc
Sub test2()
Dim myDir As String, fn As String, ff As Integer, txt As String
Dim delim As String, n As Long, b(), flg As Boolean, x, t As Integer
myDir = "\BP1MELIS001SHARED_DATAEVERYONESolutions IntranetPriceMasterlogs" '
View 9 Replies
View Related
Apr 19, 2007
Is there a way to automate the importing of macros into a list of files ? I have a number of files that I want to add some code to and was wondering if this could be automated as opposed to adding the code to each file manually as it's going to be a regular job.
I've written a routine to loop through the list of files and open them, I just want to know how, if possible, i can automatically import the required macros.
View 6 Replies
View Related
Nov 26, 2008
The tables given below located in different worksheets rather then doing autofilter(sheet3 hidden) is there any code or formula to sort them out.....
View 9 Replies
View Related
Nov 28, 2011
I want to import an excel file that has multple columns, however, the first column contains both the Group name and the dates. Is there a way to import this into a table to have each record populate the group to the corresponding date?
BASE Researching Duplicate
11/23/2011 21
E-Team General Pool
11/23/20112120
11/25/20112318
General Pool for SB Activations
11/23/2011429418
11/24/2011251125
11/25/2011485452
11/26/2011452410
11/27/2011415408
View 2 Replies
View Related
Nov 25, 2013
Couple of small queries that I have regarding importing data into excel from multiple webpages?
I am trying to automatically import data from this site, example page:
Greyhound Race and Breeding
The page numbers go up in increments of one, so the next one will be 3473265, 3473266, 3473267, etc
The problem is that the macro runs but nothing happens.
Further info -
I need this data to import all into one [very long!] sheet
Sub Macro1()
'
'Macro1 Macro
'
'For i = 3473264 To 3473265
With ActiveSheet.QueryTables.Add(Connection:= _
[Code] ,.........
View 6 Replies
View Related
Nov 22, 2006
I have about 1200 text files with data regarding different machines.These file contain a line called 'Validation date" and also the programe number on different line.
I need a macro which will extract this date for each program number and write in excel in two columns like program number and validation date.
I have tried with some of the macro help available on this site,but I have to do this one at a time.The time required doing this way is same as going in each text file and copy/pasting in excel.Is it possible to automate this process.
View 9 Replies
View Related
Nov 17, 2008
I know similar questions have been asked before, but I couldnt work out how it is done.
Basically I have several xls files with the same name, eg. "test" with an alternating number at the end. 1 - ....
each of these workbooks contain several sheets but all books are the same just with different information on it. each workbook has got a summary sheet in it, i want to import all summary sheets into a single workbook called summary. but each sheet should be imported as in individual sheet.
View 9 Replies
View Related
Apr 26, 2007
I am using excel 2003. I have more than 500 text file which are result of some numerical analysis. I have another bunches of the same file number. I can record and play around with macro.
What I want to do is
-copy selected cells from imported text file in excel and paste in new or in first opened excel file. I have recorded macro for one file including importing from text to excell, copy and paste the selected file (look the macro below). But I have more than 500 files and I want to do the macro the same thing for each file in one excell file. Do i have to make one macro for each file??, that takes much more time than manual import and copy paste. The cell position and range to copy is the same, but have to be pasted in new row (in one excel summery file). The file name of each text file is different and all are in the same folder.
Sub text_to_excel()
Workbooks.OpenText Filename:= _
"I:ResearchVALERI_germinationvaleri_slopevaleri_slope_COREL_DHPoutputDSCN2589.txt" _
, Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _ ..................
View 9 Replies
View Related
Jan 22, 2007
I have this source am using in a validation list.
=INDIRECT(SUBSTITUTE($A$2," ","_")) and the source points to A2 cell.
My problem is I need to expand this code to the next say 50-100 cells below and when I try to code the cell the next cell refers to the same A2 when I need it to refer to next corresponding cell (Eg A3)
=INDIRECT(SUBSTITUTE($A$2," ","_"))
what I need is below:
=========================================================
Column A Column B
A2 =INDIRECT(SUBSTITUTE($A$2," ","_"))
A3 =INDIRECT(SUBSTITUTE($A$3," ","_"))
A4 =INDIRECT(SUBSTITUTE($A$4," ","_"))
View 7 Replies
View Related
Jun 8, 2014
I am building a service for a company using software provided. The software provided will export the information into an excel sheet.
I am creating an A/B testing for 2 different areas of the site. I will be doing a daily/weekly and monthly figure reading. There will be 2 excel sheets, daily/weekly and monthly. I will need to put them into 1.
I want to somehow, import these 2 excel sheets so it automatically pulls the information I require into the relevant cells.
From there, I will create a percentage of what. Is this possible to do via excel or will I require other software to happen?
View 1 Replies
View Related
Sep 14, 2012
I have a folder in my D drive where I used to keep my Text files after downloading it from our support centeral. I want to Import all these Text files in one excel sheet in a way that data of each file got append in excel sheet below previous file data.I am trying to do this through FSO but unable to do the same.
View 1 Replies
View Related
Sep 28, 2009
I have thousands of Excel files, each with a generic names (i.e. 2009092812163503.xls)
Each of them contain a header with contains column titles like Company Name, Executive Contact, Address, etc and then a single row of data for a single company.
I want to be able to task excel to extract all the data in the second line and enter them all into a single spreadsheet for further work.
We're talking 30,000+ unique files here, what would be the best way to approach this?
View 10 Replies
View Related
