Use Text In A Cell For A Formula As Cell Reference To Create Range
Mar 10, 2013
I have a list of headings and items and I have a set of formulas that work out depending on the heading what items are listed.
Say theres 10 items and the heading starts at C4 and that heading has 10 items, so it puts "C5" as text in G1 and "C15" as text in G2 so i now know my cell range of items
How can i use the text in those cells to put in a formula to call that as a range?
If I use the indirect formula it shows me the value of the cell, but im after using it to reference the cell
I'm new to Excel (4th day in class) and I can't find the answer to what I'm looking for on the net. Part of the problem is that I don't know what to call it, so I have a hard time looking it up.
Very simply put, let's say there are examples numbers 1,2,3,4 put in cells A1 to A4 (vertical) and more example number 50,60,70,80 are put in cells A6 to D6 (horizontal)
What one formula can be written in B1 that I can copy down to B4 that will make B1=A1+A6, and B2=A2+B6, and B3=A3+C6, and B4=A4+D6
I am wanting to convert a cell reference text "=$A$1" to an actual cell reference =$A$1
Manually I can go through each cell and click F2 + Enter and Excel automatically changes it.
I have tried recording a macro whereby I click through each cell with F2 + Enter but the VBA writes the actual formula "=$A$1" rather than the process. This does not work as the cell reference is variable.
I'm NOT wanting an external cell to convert it for me i.e. INDIRECT(CELL) because I am wanting to copy the answer to another independent spreadsheet
I'm NOT wanting to paste values i.e. return the answer from cell $A$1 because I want the cell reference to remain within the cell.
I have created a table which is an index of all the worksheets. Now i would like to create a formula using the index reference. I get the right result if i concatenate but the text doesn't turn in to an excel formula.
=CONCATENATE("=";A1;"!";"D4") A1 contains the worksheet name, if the worsheetname is "A" then i would like it to return a formula that is =Sheet1!D4
Let's say I have cell A1 on Sheet 1 of Book 1. I need this cell to be a range variable for a macro in Book Two, but cell A1 varies from time to time (A1 this time, A7 next time).
In Book Two, I write "A1" as the contents of cell C3, for example. How do I turn that string into a range variable for use, not the containing cell? Since the cell needs to manually changed each time I run the macro, having it as a text field somewhere on my Book 2 Sheet seems the only simple way to identify it but IS there another way? Selecting it as the active cell and using another subroutine defeats the use of the macro.
However, I want to use this same function in another place, without the limit set on the cells that will be cleared.
What I would like to do is send the formula the cell to start at (E14), offset that by one column, and then send a number of rows. With that, create the range to be cleared.
How do I change a formula cell reference based on another cell's reference? I'm building a schedule that looks to a task's trigger and adds days based on that relationship. All entries in column "A" will be text and all cells in "B" will be the simple formula "=A2" or "=A3". Due date is calculated by adding the value in "C" to the preceding date in column "D". In the spreadsheet below, the trigger for "Budget set" is "Specs written" with 3 days added to the previous due date.
________A________________B_____________C_________D 1 Task___________Trigger_____________Days_____Due Date 2 Design begins__Proj OK______________10____10-Jan 3 Specs written__Design begins (A2)____5____15-Jan (D2+C3) 4 Budget set_____Specs written (A3)____3____18-Jan (D3+C4)
If the trigger for A4, "Budget set", changed from A3 to A2, is there a way that the formula that determines the due date in D4 could read the trigger cell reference in B4 so that the value in the corresponding row in column "C" is added in the date column?
I'm basically looking for something almost like an inverse function to INDIRECT. This function would first look at a cell's formula as a text string, parse out the first valid cell reference in A1 format, and return that cell as a text string.
Detail: I have a spreadsheet with cells that point to other values. I would like to get only the row number from the first cell reference in the formula residing in a given cell. For example:
Suppose A1 has the formula =AL267. and A2 has the formula =SUM(AL94:AL235)
I would like a formula in B1 that returns the text string, "AL267" so that I would know this is the first reference.
Ideally it could be dragged down to B2 such that it returns the text string "AL94" (and not "AL235") because AL94 is the first cell reference in A2's
Currently I am copying the formulas after hitting ctl+` and pasting that text into a text editor, followed by text operations to manipulate the results into the desired values. Any solution that didn't involve going out to notepad.
A1: The War of Art by Steven Pressfield and Shawn Coyne (Nov 11, 2011) - Kindle eBook A2: Do the Work by Steven Pressfield (Apr 20, 2011) - Kindle eBook A3: How to Get the Raise You Want in 90 Days or Less: A Step-by-step Plan for Making It Happen by Kathy M. Barnes and Robyn Feldberg (Jan 30, 2009) - Kindle eBook (Note: This cell (A3) have "by" 2 times)
I want to extract text from RIGHT till the word "by"
So the result must be: B1: by Steven Pressfield and Shawn Coyne (Nov 11, 2011) - Kindle eBook B2: by Steven Pressfield (Apr 20, 2011) - Kindle eBook B3: by Kathy M. Barnes and Robyn Feldberg (Jan 30, 2009) - Kindle eBook
I am trying to create a budgeting spreadsheet that contains 26 worksheets that represent 2 week expense cycles. The worksheet will tally all the expenses in its 2 week cycle, compare them to a target budget, and calculate the remaining budget. I would like to roll the remaining budget over to the next 2 week budget cycle by having a cell refer to the remaining budget in the previous worksheet.
I am trying to accomplish this using the following VBA script. I get an error (Run-time error 1004: Application-defined or object-defined error) on the 3rd to last line of the code. I think the issue is with how I am constructing the wsReference variable. I have seen other codes do something similar (VBA - How to refer to a different worksheet cell) but I am not having any luck.
Sub Budget_Rollover() Dim i As Long ' Loop through all the worksheets, starting with the second sheet For i = 2 To Worksheets.Count Dim wsReference As String Dim wsName As String ' Define the name of the previous expense cycle worksheet
I have the following formula in cell L51 of all sheets calculating the volume depending on the monthly index that is chosen from the drop down menu in a particular sheet. =If(MIndex=0, SUM(D33:L50),If(MIndex=1,SUM(D34:L50),If(MIndex=2,SUM(D35:L50), 0))). I am getting the following message and I do not understand what it is about.
Microsoft Office Excel cannot calculate a formula. Cell references in the formula refer to the formula's result, creating a circular reference. Try one of the following
I need to make a cell display the contents of the cell immediately below it, regardless of if there are rows inserted at the referenced row or above or below it on the worksheet. ALSO I need to do this at several additional cell locations down the worksheet. Such as : A1 displays A2, A4 displays A5, A7 displays A8 etc.
Example: I want A1 to display whatever is in the cell immediately below it. In this case A2. I need to be able to insert several rows at row 2 and have whatever new value is now in the A2 position displayed in A1. After the rows are inserted I need the cell that was in position A4 to continue to display the contents of the cell below it.
I tried using the formula in A1 of: =INDIRECT("A2") and it works good except when I insert the rows my similar formulas below the area where the rows were inserted continue to refer to the specific original cell and not the one positioned now below it.
I have lets say 12 months of data. I have formulas that reference the latest 6 months. When I insert a new column to input a new month, how can I make the formulas include the new months without manually updating them.
EXAMPLE:
12 months of data exist in cells B3:M3 going from B3(oldest) to M3(newest). Formulas reference latest 6 months of data in cells H3:M3. When a new month hits, I insert a column after column M.I would like the formulas to now reference cells I3:N3 which is now the newest 6 months.
Here is a made up scenario which tries to emulate my query:
I have a spreadsheet with two tabs - one called Model and the other called Prod. In the Prod tab, I have the following text entered in single cell - A1
My name is I was born in I Live in
What I would like to do is add cell values from the Model tab at the end of each line. For example;
My name is JOE I was born in 1915 I Live in AUSTRALIA
I tried the following but it didn't work.
My name is Model!A1 I was born in Model!A2 I Live in Model!A3
I believe it didnt work was because the cell starts of with text rather than a formula
I have run an INDEX and MATCH formula to give me a resultant cell reference number. I now want to copy a cell to this specific cell reference using another formula.
For example the data I want to copy is in cell A1 and in cell A2 I have the result of the index formula (B1).
I know how to manually do a formula for this but wanting to know if there is a uniform formula that I can use that looks at the formula result of cell A2.
Ok, basically C3 is a dollar amount. The default total for C3 is $0.00. If C3 is $0.00 then this formula cell will display a blank cell. If there is any other amount in C3 then the formula C3-C2 will run.
I have 2 sheets. In the first sheet, I have 3 columns. The first column contains transaction codes. The 2nd column contains the destination code (like a mapping cell where the value of the 3rd column would go but in a different sheet/2nd sheet) and the 3rd column contains the values for it. Would it be possible to reference a destination in a cell itself? Or should I just code it individually? I wouldnt want to reference each data one by one because the total would be 480 reference cells. Is there an easier way?
Calculating the sheet takes forever because of the cell range. Sometime the sheet will have 10 rows other times it will have 10,000 rows, so I put the range N2:N65536. I already have a count of the number of rows in a cell. Can I refer to that cell in the range, for example: N2:N&B1 (where B1 has the number of rows)?
I tried it a ton of ways but can't get it to work.
I want to find the last used cell within a column. Then I want to write a formula to find the Min from the cells above (Row 1 is a Header, so I do not want to include it).
Like this: Column A has data in an undetermined amount of rows below. A1 is a header and I do not wish to include it in the formula. A2:A15 has numerical data.
I want A16 to have the formula =MIN(A2:A15)
Next time there may only be data in A2:A10 or maybe A2:25...
I have a range that I named. Let's say that range is called "RangeX". Let's say RangeX is defined by B2:E10
I have a series of formulas that give me the row and column numbers of the starting and ending cell of a subset range I want to create within RangeX. Let's say the starting cell is defined by (1,1) and the ending cell is defined by (3,4) within RangeX. Translated, the starting cell would be B2 and the ending cell would be E4.
How do I create a subset range using the starting cell (1,1) and (3,4) in VBA? I would like to use the row/column reference as RangeX itself could move around.
I've seen a lot of other posts on here regarding referencing a COUNTIF statement to a cell instead of a value (like this: =COUNTIF(B7:E7,"<=" & B10), but need to do it for text instead of numbers. This is basically what I've got:
A B C
Missy 342 Missy 4
[Code].....
Column B is created using a search function, and returning only the Missy or Mike, (using a search function, which I know how to do), and then I want the counts to populate in column C based on how many times either Missy or Mike pops up in the range.
Problem is, when I try and write a formula like =COUNTIF($D$2:$D$9,VALUE(E2)) it doesnt work, but if I put in =COUNTIF($D$2:$D$9,"*Missy*") it works (of course).
Is there any way to get it to reference the text in the cell since the text in the search criteria will constantly change?
I'm trying to create a VB Script for an AS/400 app. It's all written but I'm trying to feed the data for part from an excel file. If B5 was 10 I need the final output in my cell to read:
autECLSession.autECLPS.SendKeys "10"
Here is my current formula ="autECLSession.autECLPS.SendKeys"""""&B5*100&"""""" It's reading as autECLSession.autECLPS.SendKeys""10"" which has two sets of quotes not just one and no space between sendkeys and "10"
A6 is a month column and looks like this "Jan-08" the '3107', '3207', '4107', etc. are the names of specific sheets, so I'm summing over multiple sheets.
i need to copy a range from one workbook ("weekly data") to another ("Yearly data"). The range to copy is called 'weekly data'. The ranges to paste to are called 'week 1' through to 'week 52' (this is the yearly data sheet). On the yearly sheet there is a cell Po1' that week on week will state the week number ie 'week13', 'week14' etc. code to use this data in 'P1' to tell the macro to copy to the appropriate week. I have attached as far as i got (then failed due to lack of experiance).
presuming that both books are open
HTML windows("weekly data").activate sheets("weekly transfer").select range("weekly data").copy windows("yearly data").activate 'only one sheet in workbook range ' data from p1 .paste
I have a list of text quotes in column A. I then have column B which will have a Y entered if the quote is used in a presentation. I currently have around 100 quotes.
On a separate sheet, I want to be able to effectively say in one cell - If column B has text in it, copy the quote from column A. But the difficult part is how do I make this happen so that I can have multiple quotes being pulled into one cell?
