How To Reference Cell Values In A Cell Containing Only Text
Jul 24, 2014
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 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 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've got a workbook with about 1,000 worksheets in it and I need to add up the values across the sheets from the same cell reference. I can get this bit to work with this formula:
=SUM('1:2241'!BJ3)
So this adds up the contents of cell BJ3 across all worksheets located between 1 and 2241. However I also need to count the number of times a zero appears (zeros only not blanks) so I thought this might work:
One of my longest running problems with Excel is how to use a cell value to point to a reference. If I have three sheets, 'Main' and 'Data 1' and 'Data 2'. In the Main sheet I'd like to be able to have a value of either 1 or 2 in cell A1. Then in cell B1, use the value of A1 to point to cell A1 in either Data 1 or Data 2 sheets.
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.
The first three columns of a spread sheet we use if function to insert values to these cells based on the next three column values. We have already done it using IF function. However, the same function should happen through macro by referring to the column headers.
Example 1: If in column E header (E1) ‘Contract_Status’ and E2 cell value is ‘FAIL TO PAY’ then A2 value should be ‘No Owner’, B2 value should be ‘Terminated’ and C2 value should be ‘FTP’
Example 2: If in column E header (E1) ‘Contract_Status’ and E3 cell value is ‘TERMINATED’ then A3 value should be ‘No Owner’, B3 value should be ‘Terminated’ and C3 value should be ‘TERMINATED’
Example 3: If in column E header (E1) ‘Contract_Status’ and E4 cell value is ‘EXPIRED’ AND column F header (F1) ‘Contract_Renewal_Status’ and F4 cell value is ‘Cancelled by Customer’ then A4 value should be ‘No Owner’, B4 value should be ‘Renewal Cancellation’ and C4 value should be ‘cancelled’
I'm trying to combine text and a cell reference using Indirect. Cell B1 contains the text "Region" (no quotation marks). In another cell I'm trying to create a reference to "Region total" (no quotation marks). By my limited understanding the below should work: =INDIRECT("b1"&" total")
But the cell returns #REF!. I've tried pretty much every combination of quotation marks around different elements of the formula I can think of. Cell B1 is a drop down list, don't imagine that's relevant, but it may be.
I am building a summary spreadsheet which will bring in a number of values from other spreadsheets located in folders on a network. Each set of external references will return the same cells, but the location and filenames are different. In order to make it quick and easy to modify the file names and locations without having to change the cell references in my summary sheet i want to type the full reference in a cell as text then use this in another cell as part of the external reference...
I'm in the leadup to my A2 ICT controlled conditions piece where i have to make an Excel system. My chosen subject for this is to use the school house points system. I'm going to make a system where the results of house matches are inputted and points are awarded accordingly.
I just have a couple of queries after looking at my initial designs.
1. Is it possible to get excel to look through a sheet, find a text reference, then bring up something in a cell located in the same row? For example, for it to locate one of the House team names, then go along the row and find the ammount of house points they have in order to make a total for them. Is this also possible over a number of sheets? (In order to keep a running total on a separate sheet). E.G. House, Games Played, Wins, Losses, Draws, House Points
2. For the house cross country event they use a different system which differs from everything i have designed. What i basically need to do is be able to use a drop down against each finishing position where you can select one of the four house teams, then it takes the points won for that finishing position and puts them into a table.
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 am trying to multiply a range of cells by a cell reference. The cells currently have hard coded values in them. I know with past special you can multiply a range of cells by a copied #. I want a similar function to that just instead of a copied cell its a cell reference. No VBA.
I want to use a value in one cell as a row designation, and a value in another cell as a column designation. Ultimately, the values will be text which will refer to row and column headers. What formula would allow me to do this? example:
A1 contains B B1 contains 2 B2 contains "tribbles"
Is there a macro that will allow me to create text to row from a comma separated cell, but also associate the cell to its left automatically?
The table below exemplifies what I need. The top of the spreadsheet is how my data is currently. The portion after the break is how I would like it to be.
Let's say I've ended up with the number 8 in Cell D4 for example. Is there a formula that can return the letter "G" (The 8th Column) so I can use it in future cell references ? If so, let's say we store that in Cell B5. How do I now refer to a cell in a chosen Row of that same Column by reference to Cell B5 ? For example if I want to refer to Cell G33 can you refer to this Cell in some form like Cell(Contents of Cell B5;33) ??? Don't want to use R1C1 type references if possible.
I have 2 problems relating to LOOKUP. Not sure if Excel can perform these calculations as they could get to complex.
Problem 1 Can it be possible to have excel look at data from one cell reference another cell then display the results from the cell next to it in another cell, sort of example:
Tab 1 (Never changes) AB Bob1 Jon2 Fred3
Tab 2 (Dynamic, changes each week) AB Jon Fred Bob
So it would work as follows. Tab2 column B will take Tab2 column A’s data check Tab1 column A and display Tab1 column B’s result.
Problem 2
Weekly league rank table that shows position movements week by week Example.
Week1 1Jon 2Bob 3Fred
Week2 1FredUp 2 2BobNot Moved 3JonDown 2
Can Excel calculate/show the actual movements of league positions?
I'm creating 10 'categories' for Column "C" that I plan on using drop-downs to select from. Column "H" has a value tied to "C". What I want to do, is have Excel SUM all of the values in Column "H" where the Category in "C" is a specific text value (e.g. say a category is "hardware", I want a cell with a formula that sums all of the values in "H" where "C" is "hardware".)
In order to transfer a column of data into a Crystal Report I need to add a comma (,) to the end of each cell in a range. These cells are all comprised of numeric characters, representing player card numbers.
I'm working on a project for use by our sales teams to for them to easily find out which campaigns are currently being offered in their regions. The data looks like this:
Participating Regions
Campaign
IN, OH, MI, IL, NE, AZ
Campaign A
IN, OH, MI, IL, NE, AZ
Campaign B
IL, IN
Campaign C
IL, IN
Campaign D
All
Campaign E
IL, WI, MN
Campaign F
This is going to be embedded internally and needs to be as simple to use as possible. I would like for column A to be filterable by state, either with a drop down menu or a selector. My problem is that many states could be in one cell, or individually in a different cell, and the default autofilters recognize these as two different values. I would like the sales rep to be able to filter "IN" for example and get 5 results. One solution that I am aware of is for me to duplicate each campaign on a different row and list one state per row for that campaign. The issue with that is that this is going to be updated frequently and that is a tedious manual process. I am also aware that you can use a text filter for "contains" specific words, but even that is more intricate than what I am aiming for.
