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

View 7 Replies


ADVERTISEMENT

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

View 4 Replies View Related

Cell Reference Cell Then Append Specific Text Using Vb

May 16, 2009

This is probably really easy but I don't know what I'm doing wrong.

View 2 Replies View Related

Convert Cell Reference Text To Cell Formula

Aug 18, 2009

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.

View 3 Replies View Related

Reference Cell Values In VBA

Nov 14, 2013

How do I reference value of a cell in VBA example..

Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveSheet.Range("$A$1:$XFD$10000").AutoFilter Field:=2, Criteria1:= _
"NAME"

I want to replace hard coded "NAME" with the contents of a cell in another sheet?

View 2 Replies View Related

Add Up The Values Across The Sheets From The Same Cell Reference?

Feb 21, 2014

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:

=COUNTIF('1:2241'!BJ3, "=0")

However it just returns the #VALUE error.

View 5 Replies View Related

Using Cell Values In Reference Formula

Jul 27, 2006

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.

View 3 Replies View Related

Count If To Reference Cell With Text?

Aug 21, 2013

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?

View 4 Replies View Related

Cell Reference In Middle Of Text

Jul 10, 2009

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"

View 3 Replies View Related

SUMPRODUCT With Cell Reference Rather Than Text

Sep 2, 2008

{=SUM(IF(MONTH('3107'!$A$2:$A$188)=MONTH($A6),'3107'!F$2:F$188))+SUM(IF(MONTH('3207'!$A$2:$A$188)=MONTH($A6),'3207'!F$2: F$188))+SUM(IF(MONTH('4107'!$A$2:$A$188)=MONTH($A6),'4107'!F$2:F$188))+SUM(IF(MONTH('4207'!$A$2:$A$188)=MONTH($A6),'4207 '!F$2:F$188))+SUM(IF(MONTH('4307'!$A$2:$A$188)=MONTH($A6),'4307'!F$2:F$188))+SUM(IF(MONTH('AS21'!$A$2:$A$188)=MONTH($A6) ,'AS21'!F$2:F$188))}

Failed to convert using

=SUMPRODUCT(--((IF(MONTH('3107'!$A$2:$A$188)=MONTH($A6),'3107'!F$2:F$188))+SUMPRODUCT(--(IF(MONTH('3207'!$A$2:$A$188)=MONTH($A6),'3207'!F$2:F$188))+SUMPRODUCT(--IF(MONTH('4107'!$A$2:$A$188)=MONTH($A6),'4107'!F$2:F$188))+SUMPRODUCT(--(IF(MONTH('4207'!$A$2:$A$188)=MONTH($A6),'4207'!F$2:F$188))+SUMPRODUCT(--((IF(MONTH('4307'!$A$2:$A$188)=MONTH($A6),'4307'!F$2:F$188))+SUMPRODUCT(--(IF(MONTH('AS21'!$A$2:$A$188)=MONTH($A6),'AS21'!F$2:F$188)))))))))

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.

View 9 Replies View Related

Insert Values Based On Other Cell Value Reference

Mar 21, 2014

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’

View 9 Replies View Related

Combining Text And Cell Reference With Indirect

Oct 2, 2008

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.

View 2 Replies View Related

Cell Text As Part Of External Reference

Apr 2, 2007

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...

View 9 Replies View Related

Returning Multiple Values With Duplicate Cell Reference

Jul 11, 2014

From the following example:

A1 TAG B1 mileage
G10-1622 15246
G10-1622 15246
G10-1583 15246
G10-1622 28659
G10-1195 28659
G10-1622 28659

desired results

Enter tag in cell
G10-1662
Mileage
15246
15246
28659
28659

I would like to enter a particular TAG # in I5 and return all the mutiple associated data

See attached : tire2.0.xls‎

View 9 Replies View Related

Match Cell Reference Rather Than Specific Text Or Value - N/A Error

Dec 7, 2013

I'm getting an N/A error on my MATCH formula.

I'm trying to match a cell reference, rather than a specific text or value.

Here is the syntax I used. MATCH(Cell,Table Array Name, 0)

I have made sure that the text matches exactly and the cell formats are the same.

View 1 Replies View Related

Look Through A Sheet, Find A Text Reference, Then Bring Up Something In A Cell

Jan 13, 2008

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.

View 12 Replies View Related

Change Formula Cell Reference Based On Another Cell Reference

Jan 8, 2012

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?

View 4 Replies View Related

Multiply Range Of Cells By Cell Reference Without Changing Hard Coded Values?

Aug 20, 2014

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.

View 5 Replies View Related

Combining Two Values In Two Separate Cells To Make A Cell Reference Or Index Refer.

Jul 15, 2009

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"

An imaginary function might go like this........

View 3 Replies View Related

Combined Cell Values (Text) In 1 Cell?

Mar 19, 2014

If I have text in B1 (Apples)
And text in C1 (Bananas)

How do I write a formula in cell A1 that will combine the text of both so that it reads 'Apples Bananas' in cell A1?

Everything I can think of gives me errors.

View 5 Replies View Related

Text To Row Instead Of Text To Column Using Comma Separated Values And Associate Cell To The Left

May 16, 2013

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.

I have over 600 rows that needs this done.

1,2,3,4
B

[Code]....

View 3 Replies View Related

How To Match A Cell Data With A Range Of Cells And Return Cell Reference In Another Cell

Dec 12, 2012

i want to match a cell data with a range of cells and if matches return the cell reference in another cell

View 3 Replies View Related

Reference To Cell Column Letter By Reference To Another Cell Value

Apr 25, 2014

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.

View 9 Replies View Related

Look At Data From One Cell Reference Another Cell Then Display The Results From The Cell Next To It

Jun 19, 2008

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 As data check Tab1 column A and display Tab1 column Bs 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?

View 9 Replies View Related

How Do I Sum Values When Text Is Also In Cell ?

Feb 17, 2009

Cells have both text and values in and I need a formula to sum up only the values.

An example is:

joe 22
jim 33
sue 44

...where the formula needs to produce the answer 99.

View 6 Replies View Related

How Can I SUM Values Only When Specific Text In Another Cell In The Row Is Met?

Oct 24, 2008

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".)

View 8 Replies View Related

Finding Values By Using Text In Other Cell

Jul 3, 2014

I am trying to create a cell that show the value of all specified rows with specific name.

For example:

Apples $2 $5 $10
Oranges $5 $10 $50
Apples $5 $ 6 $ 60

(each of these will be in their own cells, 3 rows and 4 columns)

How do I create a cell that will sum the total value of all the Apples by using Apples as the variable?

View 1 Replies View Related

Appending Text To Cell Values

Oct 14, 2008

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.

View 4 Replies View Related

Multiple Text Values In One Cell

Jun 5, 2012

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.

View 6 Replies View Related

Replacing Text With Cell Values?

Nov 8, 2012

I have a data starting from column D. And the list goes like below:

D1: TO
E1: CC
F1: BCC
G1: Body
H1: Text1
I1: Text2
J1: Text3

I would like to obtain the final body in column K after replacing the text with column H, I, J.

Below is how my data looks and How i would like to obtain the final body.
To
CC
BCC
Body
Text1
Text2
Text3
Final Body

View 1 Replies View Related







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