Free Microsoft Excel 2013 Quick Reference

Using text in cell as reference to range name

I have a question concerning the use of Text as reference to a range name.

Is there a way where I can type a text in one cell and use this as reference to a range name used in a formula in a different cell?

Example

Sheet 1, Cell B6 contains Text: AccountNo
Sheet 1, Cell B7 contains no: 12345

Sheet 2, Range B2:C30 is named AccountNo and contains Account Nos (column B) and balances (Column C)

What I want is to make a vlookup or sumif that used cell B6 to find the range B2:C30 in sheet 2.
e.g. +vlookup(B7,B6,2,false)

Hope you can help, thanks in advance
Torben


Post your answer or comment

comments powered by Disqus
I need to use the name of a worksheet in a specific cell, to select that worksheet in Visual Basic. Basically I'm trying to refresh external data in only certain worksheets in my book. The name of the worksheets with the data I want to refresh are located in a few cells. I want to have a single macro able to be activated on multiple worksheets that when I run it refreshes the data on the worksheets that are listed in cells on that active sheet.

ie: Cells K1 and K2 in sheets 1, 2, and 3 have the names of two other worksheets in the workbook. Let's say K1 in Sheet 1 has the text "Sheet 4" and K2 has the text "Sheet 5". When I run the macro I want to refresh the data in Sheets 4 and 5. But I want to have the option of changing say K1 to Sheet 6 and then when I run the macro it refreshes Sheets 6 and 5.

Is this possible? I've looked at a ton of threads, but none of them seemed to quite fit what I'm doing. Thanks.

I have several sheets rolling into a summary sheet. The data is by location
and data type. Using SUMIF referring to range names to get the numbers in
the right place. My formulas are not updating automatically though automatic
calculation is selected in Options.

In order to update the formulae, must go through each cell and F2 [ENTER].

I tried F9 and all the variations of F9 - does not work.

Anyone know what's wrong?

Hi,

I was wondering if it's possible to align text in cell as "justify"
just like in Word?

An is there any chance of spelling also =)

Yours,
Sirri

I would like to know if there is anyway to take text that is in a cell & make vba use it as code. Here is the line I am using it in.

	VB:
	
MsgBox (Cells(Range("help_available").Row, Target.Column).Text) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I need it to take the text in the cell that comes up and process it as code. The text will look something like this.


	VB:
	
"Here is the list of names:" & chr(13) & chr(13) & range(firstName) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The msgbox from above is used over and over again in different situations. Also I want the flexiblity to put the code right into a hidden cell on the spreadsheet.

I have a variant array vArray(), i want to output it into range start with left top Cell(1,2), how can I output the vArray() directly on the range with left top cell(1,2)? I know i can use

	VB:
	
Range("A1:D3").value=vArray 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
to output the value, but i want to know whether i can use left top cell as reference to output array onto the range directly without specifying range from and to.

Thanks

This may seem blasphemous to you excel experts, but is there a way to use the text in a specific cell as verbatim code in a macro? I ask because for a novice at VBA like myself, it is much less daunting to use excel formulas to create the code I need in cells and call each cell in turn than to deal with loops. For instance, if I have a cell with the text "ActiveWindow.Close" without the quotes (note that is not actually code, hence no code tags). I would like a macro to use this as if it were in the actual code and close the active window. Thanks.

I am trying to create a formula using the text within a cell as part of the formula. I have been able to Concatenate and get the formula to read as text but I can't figure out how to convert it back to a formula to pull the linked info. Below is the Concatenated formua.

Col A Col B
MED REV M01 'K:18A30Medicaid FY08MED REV M01.xls'!FFP_RATE

Ok Excel gurus..what is the magic command to change col B into a true formula?

Thanks
Debbie

Hi there

i have just started working on Vb code to keep track of file changes on different environments like dev, test, uat, stage and production

What i want to know is the following

Is it possible to compare texts in cell?
Is possible to pass to a cell, the file directory of where the contents of the release are?
Is it possible to read folder contents i.e. read dll names, aspx names etc into another cell value?

please advice

I posted earlier, but I may have worded things badly, and so, with apologies, let me try again.

I'm faced with a purchase order form, and on the worksheet the produces the printed PO there are a number of drop down boxes that refer to a named range on another worksheet (in one example, "trimsize") which simply consists of a number of choices for the user to make. The user makes his choice on the PO form worksheet, and it displays for printing or distribution. I am being asked to create a series of cells in the workbook that take the displayed data (both in standard cells and in drop down text boxes) and create cells that can then be exported or extracted to another file.

Displaying the results of the content cells is, of course, no problem, but how can I take the displayed choices in these drop down boxes as text data in another cell? Yes, the cells containing the list of choices is available, but I need to capture the text data that the used has chosen. I can find ways to link a cell to the dropdown box, but not how to take the choice the user makes from the drop down box as usable text data.

There must be some way to do this other than a macro. Any help tremendously appreciated.

I have this formula =IF(ISBLANK('A Griffiths'!$M$15:Q16),"No","Yes")

A Griffiths is a sheet name. I need to copy this formula down another 15 rows with 15 other peoples named sheets.

I have column Q whihc contains these peoples names (which are the same names as the sheet names). So what I want to do instead of having that A Griffiths is have Q3 which contains the text A Griffiths. Then I could just copy the fomrula down instead of constantly typing out the sheet names.

I have tried =IF(ISBLANK('Q3'!$M$15:Q16),"No","Yes") but it did not recognise that it should be using the text from Q3 as the sheet name.

I also tried =IF(ISBLANK('&text(Q3,)&'!$M$15:Q16),"No","Yes") but again it doesnt recognise I want it to use the text in Q3 as the sheet name to refer to.

Anyone?

Thanks

Hello, I am trying to use a cell reference that refers to the name of a named range in a formula. For example, in the formula "=average(a2,>1)", the cell reference "A2" actually contains the text of the named range "DATA1.1.1_". Is this even possible? If not, is there a way I can make this work? I have a about 800 very long formula's that I need to write and the only thing that is changing in each formula is one named range. I would like be able to set a list of named ranges, then just autofill the formula down the column.

Hi, I am trying to run solver to find a minimum but I want it to refer to ranges as opposed to specific cells. Because the user can change the size of the target variables. I am using the code:

	VB:
	
SolverReset 
SolverOk SetCell:="$A$5", MaxMinVal:=2, ValueOf:="0", ByChange:="$B$8:$E$8" 
SolverAdd CellRef:="$B$8:$E$8", Relation:=5, FormulaText:="binary" 
SolverAdd CellRef:="$J$19", Relation:=1, FormulaText:="$D$1" 
SolverOk SetCell:="$A$5", MaxMinVal:=2, ValueOf:="0", ByChange:="$B$8:$E$8" 
SolverSolve 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
But i want the range :="$B$8:$E$8" to be refernced by defining the range as:

	VB:
	
 Range 
Set SolveBinRange = Range("b8").Resize(1, countRow - 1) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
How would I use the range SolveBinRange in the solver code?
Many thanks

Hi,
I have a large spreadsheet which i'm trying to break up into smaller ones. the original large one would have used "indirect" in certain sheets to refer to ranges eg ranges would have had codes such as A255A and would have been recognised in the original spreadsheet. however after breaking up the spreadsheet (by using the option of moving certain worksheets to a new book) the new workbook does not understand that indirect($A$2) with A255A in cell A2 refers to a range in the original workbook. Would anyone have any ideas on how to over come this?
thanks James

ps the main idea for breaking up the large workbook is in order to make the smaller ones faster - as it is it the large one is painfully slow - by breaking it up into smaller wokbooks based on the different functions it carries out can i expect the smaller individual workbooks to operate at a higher speed?

I have created a spreadsheet with lots of data. Most of the cells have formulas to calculate their value that are dependent on other cells. there are a certain number of cells that require the end users to input data in order for the rest of the cells to fill. Is there any way that I could insert text into these cells (they might be red text that says "insert your data here") so that the user would easily recognize where they are supposed to enter the data, but wasn't actually writing in the cell? Because if I just type in the cell, then all of the other cells have errors because they are using text in calculations. I know that as soon as the text is replaced it would work, but that does not work for me. Does anyone know of any way to do this? thanks.

I want to read variable from cell. Is it possible to convert value stored as
text to logical refrence value!

For example

I want to use this forumla
=HLOOKUP("Currency";table1;1;FALSE)

But i want to read RANGE TABLE name from another cell - i want it to be
variable

IF i store table1 in cell A4 and write formula
=HLOOKUP("Currency";A4;1;FALSE) then i get error cuz it reads "table1" and
thinks i supply text but i want to supply reference value which is table1 not
"table1"

Maybe there is way to store text in cell as reference value ?

hi guys,

i have a named range; 2columns, 1 col with a code, 1 col with text explaining the code. elsewhere, as part of a data cleaning exercise, we are mapping the codes to a user defined short name (the mapping is potentially many codes to one user short name - things got a little sloppy over time ).
there is one user short name for each row, and separate cells in the row contain the codes we are mapping - one code per cell. the mapping process is iterative:
1. take a short name
2. go to the named range (in reality, a different worksheet)
3. find how many codes have been used for that short name
4. fill in the cells in the row with the codes
(tedious at best)
i would like to mouse over the cells, one at a time, and have a pop-up display the text explaining the code.
so i think i need vlookup under the control of a mouse over event in a macro? using excel 2003.
or do i need to delve into vba?
i'm no expert - so a mild learning experience is great but a moon shot i don't think i could handle.
can you guide me to the start of the string? and sort of suggest an approach that will work? then i'll go read the right book to get some code snippets?
thanks,
ron

Hello everyone,

I am a teacher creating a Points System in excel. Each student has the chance to earn 100 points in a day. Each time they do something inappropriate, they lose a certain number of points. There are two times when I might need to enter text into a cell instead of a number. 1. A student is absent. 2. The student is sent home on suspension. In the first case, I enter "absent" and I want excel to simply skip it over. In the second case, I enter "sent home" and I want excel to assign them 0 points for the day. When I use the average formula to calculate their average points for a month, Excel ignores both cells that have text in them and calculates an average of the other days. Is there a way to have it ignore cells that say "absent" and count cells that say "sent home" as 0? If not, that isn't a big deal, I can simply put 0 in the cell instead of "sent home".

However, my real problem is when I go to graph the points system for a student. Excel counts any cells with text in them as 0. So on the days that they are absent, the graph shows them as getting 0 points. Is there a way to have the column graph display the text in the cell instead of the column for that particular date? I know this is asking excel to do a lot, but, it is a pretty awesome program and I don't understand half of what it can do. So I am hoping someone out there has a solution for me. Thanks in advance for your help.

Russell

ps. here is a link to an example file. I will leave it up there for a while, but it may come down at some point.

Hi,

I want to colour format a range of cells. The critiae for color coding the
cells depends upon a name , ie if a string of text in a cell refers to a
name, colour format the cell for example green. Conditional formatting doesnt
seem to work as the condition is too complex, i think. ie example below if a
string of text in a cell contains either of the names andrea, robert or john
, color the cell green , if a cell contains either of the names bob or jack
color format the cell blue.

Hello,
in sheet1, I would like to add text in cells in Column C from a range of row (by FirstRow to LastRow.

In Cell C11 to C?? (LastRow) i would like to add "U-".
does anyone could help me to perform this ?
Thanks in advance.
Herve

To start I am using Excel 2007 and any help would be greatly appreciated.

I want to know is there a method of having faded text in a cell (This name will be the cell title) and as you write into the cell this name will disappear and be substituted for whatever is written in the cell (not as faded text).
Is this possible? It's kind of like what you would see in online registration forms.

Regards,
David

I am trying to use SUMIFS in 2007 to replace an array function I used in older versions to a sum based on multiple criteria. One criterion has blanks as a legitimate entry and when I use SUMIFS or even just SUMIF and try to use the blank cell as the criterion it comes back as zero sum. If I check for equality between my formula reference cell (blank) and a blank cell in the criterion column, it comes back as TRUE (=A5=C27 returns TRUE).

If I use the same criteria but du it as a SUM array function it recognizes the blanks and returns the proper sum.

Is there a way to get SUMIF/S to recognize blanks as criterion?
Thanks,
Phil

I could use some help writing my macro for excel. This is what I have:

1) I have 3 files in C:/sample named 1.xls, 2.xls, and 3.xls (this
varies from 2 - 30 files)
2) I need to save each one using text in cell E28

I would like:

1) An input prompt when Macro starts to ask how many files.
2) Then the Macro to run through all numbered files saving the file
using what text is in cell E28 as the new file name.

I have been able to get it to do the first file, but I can't get the
variable to change on the second and additional files.

Thanks for your Help.

I’m confused by what happens when I refer to a named range.
For example, I entered four integers as follows:
A1=1, A2=2, A3=3, A4=4,
then defined a named range “xrange” as these four cells - A1, A2, A3, A4.

Then if type “=xrange” into cell B2 and press enter, then the displayed result is 2.
But if I type “=xrange” into cell B2, and then press Ctrl-Shift-Enter (for an array formula), then the displayed result is 1.

Why does this difference exist, and how is the processing different in this situation for an array formula as opposed to a non-array formula? (I’m using Excel 2003)

( I encountered this problem as I was working on a complex array formula, then realized I wasn’t sure what was happening in the background array processing.)

Thanks in advance for any explanation.

I have used this code to find certain "text markers" which identify, for example, column headers.

	VB:
	
Cells.Find(What:="ST:", After:=ActiveCell, LookIn:=xlValues, LookAt _ 
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ 
True, SearchFormat:=False).Activate 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Use Case
Record entries are in rows. A row may have one or more comments, each in separate columns. A comment may include a Status flag, which includes the prefix of "ST:". I only want to find the last comment that includes a status string, since that should be the most recent status for that record (in rows). Some records may not include any comments that contain status strings.

So, the desired variation on the above cells.find search is this:
What: Identify a "marker" string to find. In this case, it is "ST:", case sensitive.Where: Search only in the active row, where I have already identified the last filled column in that row, which is the most recent entry.Direction: Search from right (last column) to left (column A) and stop on the first match. The marker, "ST:" will not be the only text in the cell.While I would prefer to use an efficient command like cells.find, I know that I can also move across the row right to left and check each cell. I have seen at least one other forum entry taht Where elegance falls short, brute force will get the work done.

If a target string is found in a cell, I can do the rest to extract the status text (8 characters) that come after the "ST:" marker.

Many thanks for suggestions if there is a single command solution. I am programming the brute force - cell-by-cell - method in the interim.


No luck finding an answer? You could always try Google.