Manual Input of Cell Refs

I need to do a calc on sheet 1 which will use data from  sheet 2.
I want to point to the row on sheet 2 by manually inputting its number in
sheet 1 at ,say, A1.
Ex:
    In sheet 1  a3  I have the formula     =Sum(sheet2!A?:G?)
     where the value of ? has been inserted manually in Sheet1 at A1

TIA
Craig


0
cd819 (4)
7/30/2004 12:26:58 PM
excel 39879 articles. 2 followers. Follow

2 Replies
412 Views

Similar Articles

[PageSpeed] 41

Hi
try
=  =Sum(INDIRECT("'sheet2'!A" & A1 & ":G" & A1))

-- 
Regards
Frank Kabel
Frankfurt, Germany


Craig wrote:
> I need to do a calc on sheet 1 which will use data from  sheet 2.
> I want to point to the row on sheet 2 by manually inputting its
> number in sheet 1 at ,say, A1.
> Ex:
>     In sheet 1  a3  I have the formula     =Sum(sheet2!A?:G?)
>      where the value of ? has been inserted manually in Sheet1 at A1
> 
> TIA
> Craig
0
frank.kabel (11126)
7/30/2004 12:35:08 PM
Craig wrote...
...
>Ex:
>In sheet 1  a3  I have the formula     =Sum(sheet2!A?:G?)
>where the value of ? has been inserted manually in Sheet1 at A1

Try

=SUM(INDEX(Sheet2!$A:$G,A1,0)

--
Message posted from http://www.ExcelForum.com

0
7/30/2004 7:23:47 PM
Reply:

Similar Artilces:

If any cell in a range is blank question
Hello. I have two sheets in a workbook. One is a Summary sheet, and the next is titled "Bldg 5". What I am trying to make sure is that the range A1:K20 on the Bldg 5 sheet has had data entered to them (ie, they are not blank). The question that corresponds to Summary sheet A1 is "Has the data for Bldg 5 been updated?" If ANY blank in Bldg 5's range A1: K20, then put a "NO" in Summary A1's cell. If ALL of the Bldg 5 range had data in it, then put a "YES" in Summary A1. I also need A1 to update as data is entered into the Bldg 5 range (a...

Splitting a cell that has word wrap as the delimiter
Hi - I have an excel cell that has name on one line, then alt+enter, address on the next line, then alt+enter, state on the next line, etc... I want to split them - I tried using Data Text to Columns, but I can't figure out to use the Alt+enter as the delimiter. Any ideas? Check other and place the cursor in the box, hold down Alt while typing 010 on the numpad, release the Alt key -- Regards, Peo Sjoblom "Leslie" <anonymous@discussions.microsoft.com> wrote in message news:074301c3c3f9$59b12340$a001280a@phx.gbl... > Hi - > > I have an excel cell that ...

Changing color of a range of cells dending up on a value in anothr
Hi, I need to change the color of a range of cell, depending up on the value in another cell. I am using Excel 2003. And it allows me to give only 3 conditions when using conditional format. But I have 7 differnt conditions. Thanks in advance for any help. Srajes. Unless you move to XL2007, you will need to use VBA. Here are some sites that will help http://www.ozgrid.com/VBA/excel-conditional-formatting-limit.htm http://www.mvps.org/dmcritchie/excel/condfmt.htm Alternatively, there is an add-in here http://www.xldynamic.com/source/xld.CFPlus.Download.html -- Steve "Srajes&qu...

Text Box Linked To Cell
My goal is to have a text box that displays the contents of a particular cell. How do I link the text box to the cell? Thanks, Denise Hi denise, Make a Text Box from the drawing toolbar. Select the box and in the Formula Bar, type = and then select the cell, press enter. ( IE: =$C$5 ) The cell can also be on a different sheet, just do the same. ( IE: =Sheet2!C5 ) Dave denise Wrote: > My goal is to have a text box that displays the contents of a > particular > cell. How do I link the text box to the cell? > > Thanks, > Denise -- Piranha --------------------------...

Truncated Merged Cells
Actually, I have truly MERGED cells that contain LINKS to other workbooks. The merged cells were created exactly as you described and the data within them (text) comes from several other workbooks, with the appropriate references. We created them in 95 and later converted them to 97. We can use them in Excel XP but they lose the data that would be displayed on 2002 and 2003. Have not tried 2000 yet. Thanks for the help. We are very concerned about this problem. Our workbooks are complex with many links. Elsa I only have the ideas that I already posted. And you can find posts in goog...

Is there a way to Sort Data by font color or cell shading?
I have a long list of items that I sort by either item number or date depending on what I'm looking for. From time to time these items status will change and I will gray them out by just shading the background of the cell gray. Is there a way I can sort these items by the background/shading of the cell first and then the secondary sort be date? So all the cells which contain gray shading will be first in the list (sorted by date if possible) then the rest of the list without the gray shading will follow by date as well. Thanks. Take a look at this link: http://www.cpearson.c...

Cell identity when printing comments on separate page
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel When printing comments at end of page, it identifies the cell by letters and numbers. (i.e. Cell: AA33) Afterward the comment is shown for that cell. <br><br>Question: Instead of the cell identity being shown this way, is it possible ithe cell can also be identified by its X axis header for that column with the y access header for that Row. <br><br>Thank for your assistance. <br><br>Carl Sorry Carl, but I'm not clear on what you're asking for... In 'MySpeak' ...

Formula
Have a simple idea that is giving me fits. Have a worksheet with numberic data and labels in rows and columns. Below each column I use a simple SUM() function which works fine, until I disturb a row of data. By disturb, what I mean is that if I move the top row of data down to the bottom of the array, highlight the entire data area, move that area up one row, I lose the correct references in the SUM() function. I'm just moving the data area, not the row containing my SUM() functions - that stays put. IOW, the SUM() function is in row 12, the data area extends from row 1 through ...

copy a file to all users via a script instead of manually
I have a d:\users folder on my server. inside users, each user has a folder named with their name. inside each of those folders is a desktop folder. I have a file on the C drive that I need to copy to each of those desktop folders, thereby giving each user this shortcut. Does anyone have a script that could do that, instead of me copying this file manually a few hundred times? many thanks if you do! "Rich" <richjone@noemail.noemail> wrote in message news:5E68A580-FA4D-4B73-A548-0ED9D74AF34C@microsoft.com... > I have a d:\users folder on my server. inside...

Assigning a color to a cell
I saw some other post about kind of the same problem but not this way. I have a risk evaluation formula in a cell. Output range is between 1 and 9 If between 1 and 3, I want to assign the color lightgreen to it If between 4 and 6 I want to assign the color yellow to it and red if 7 to 9. Is this possible within the Function use ? If not, How can I apply that automatically ? a macro ? How ? TIA Hi See menu Format > Conditional format. It has a very helpful wizard. (Remember to consider what should happen at exact numbers 3, 6, 9.) HTH. Best wishes Harald "Junkyard Engineer&qu...

Inserting an image into a cell
Is there any way to insert an image (or a series of images) into a cell rather than onto a worksheet? Many thanks hi unless they have changed something in 2007, about the only way you can do that is insert it as a comment. http://www.contextures.com/xlcomments02.html#Picture Regards FSt1 "Trent SC" wrote: > Is there any way to insert an image (or a series of images) into a cell > rather than onto a worksheet? > > Many thanks > > > Hi, Not into the cell but maybe you could use the comment to hold your image. http://www.contextures.com/xlcomments0...

Running macros for different cells at the same time
Hi all, Is it possible to run different macros on different cells at the sam time? If so how do I go about setting it up? Cheers Michae -- Message posted from http://www.ExcelForum.com Hi not really sure what you're trying to do. Could you give an example? -- Regards Frank Kabel Frankfurt, Germany > Hi all, > > Is it possible to run different macros on different cells at the same > time? If so how do I go about setting it up? > > Cheers > > Michael > > > --- > Message posted from http://www.ExcelForum.com/ Yes it is possible to run different m...

Input text message (dialog box) for data validation
Hello all: This may sound idiotic to some, but I've already been wasting too much time with this option, and am looking for some guru insight. I am trying to input text into the Data Validation Text Box that looks something like this: ------------------------------------------------------------------------------------------------------------------------------------- Q3) Was this a prospective or retrospective analysis or study documenting the effects of XYZ that compared and reported the data in the Marketing Strategy Program? a) Yes b) No c) This appears to be a concise comparison, but ot...

Word Wrap in Cells
Hiya, Have a strange one. Have word wrap set to on in cell format and have text in paragraphs (using alt-ret) within the cell. Some sentences (all within the same cell)are wrapped but others aren't. Any ideas? New one on me this. Many thanks, Shaun Hi if you have lots of text in your cell (>1000 characters) this could happen -- Regards Frank Kabel Frankfurt, Germany "Shawk" <shawk@clara.co.uk.3guesses> schrieb im Newsbeitrag news:1103641104.32476.1@demeter.uk.clara.net... > Hiya, > > Have a strange one. Have word wrap set to on in cell format and ha...

Manual removal
Anyone know of the proper way to manually remove CRM for Outlook. I went to remove it, i get the message that it has to be uninstalled by the person who installed it which is the user. BUT, that user left long ago and the account was deleted. I just have not had luck finding the manual removal instructions. -- Thanks Knavas This doesnt always work. I have had to reinstall the OS before. In HKEY_LOCAL_MACHINE, expand SOFTWARE, expand Microsoft, and then expand MSCRM. Delete the key: MSCRM If possible use Add/Remove Programs to uninstall the application. If Microsoft CRM Sales fo...

Splitting multiple cell contents from 1st column into 4 columns
n my first column I have 4 cells (1-4) (5-8) etc. with general content. The content from each of these 4 cells needs to be placed in a separate column to make a list that I can sort etc. Example: COLUMN A 1 01-04-425-001 2 Heatherridge Road #301 3 Harry Smith 4 H25 Condo Fairfax Place 5 01-05-356-041 6 McGrath Street # 56 7 Mary Jones 8 B45 Condo Lemon Circle . . . . . . and so on and so on every 4 cells. I have hundreds of 4 cell descriptions. I just can't do this one by one. Can anyone help, PLEASE, PLEASE. I am really a novice at Excel though I use it for e...

how read value from last selected cell? It is possible? how get adress last selected cell?
for example. I write to cell A11 value "hello", and next: IF I click "ENTER" or IF I click mouse in any different cell (A15, B10, C1... free choice) I wont to display: MsgBox "Value in your last selected cell = ???" On this example MsgBox "Value in your last selected cell = hello" it is very important for me. I try with event of Private Sub Worksheet_Change(ByVal Target As Range) ?? but :( thanks everybody, Andrzej Andrzej You have little choice but to use the Worksheet_Change() event as the Worksheet_SelectionChange() event, give you the cell ...

How do I pass series data x-value range to a chart from a cell?
I have a series on a line chart (x-values "=Sheet1!$A$1:$A$10"). I also have two worksheet cells (B1 & B2) that contain the start and end date for the x-axis of the chart. I want to be able to link the chart series data to the start and end date values so that the chart automatically adjusts when I change the dates in the worksheet cells. I can create a string or address for the x-values but how do I do not know how to pass this to the chart series?? I know I can do this using a macro, but I want to do it without macros so that friends who use MicrosoftCharts (the cheap, n...

Is there a way to protect individual cells?
Hello, is there a way to protect individual cells? Thank you -- amos7 ----------------------------------------------------------------------- amos79's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2759 View this thread: http://www.excelforum.com/showthread.php?threadid=47112 amos79 Wrote: > Hello, is there a way to protect individual cells? Thank you! Go to *Tools/Protection/Protect Sheet* Regards -- BenjieLo ----------------------------------------------------------------------- BenjieLop's Profile: http://www.excelforum.com/member.php?action=g...

This one is tricky....Macro to save file as cell value x in direct
Hi Two cells A1 & B2 I am trying to create a Macro to attach to a button to Save an .xls as "A1".xls in directory R:/SALES/Team 318/"B2" instead of typing it in all of the time.... Any helpers? Past attempts at this have failed.....the winner will get a big MVP vote! Absolute respect Andy Try something like: Sub Test() Dim mypath As String Dim sfile As Range, sdir As Range mypath = "R:\SALES\Team 318\" Set sfile = Sheets("Sheet1").Range("A1") Set sdir = Sheets("Sheet1").Range("B2") ThisWorkbook.SaveAs Filen...

in VBA how to retreive cell values 1 byte at a time?
How would I write VBA to retrieve (get at) the value in a cell byte by byte from its natural form? i.e. no conversions are to be performed. E.g., if a string, then I may get more bytes than for a number. Thanks. Penny, If the cell contains text, you can retrieve characters (bytes) with the MID function. =MID(A2, CharPosition, 1). You can get the actual byte values with the CODE function =CODE(MID(A2, CharPosition, 1)). If a number, you can extract the decimal digits of the number in the same way, but you're not getting actual bytes (as stored by Excel). Excel stores numbers in ...

Can I format an entire row based on the value of a single cell?
I would like to use the conditional formatting feature to format an entire row rather than just a single cell. For example: If the cell value = "Total" then the entire row is bold. select your range (multiple rows???). Then with A3 (say) the activecell in that selection: format|Conditional formatting formula is: =$a3="total" apply a nice format LTShelley wrote: > > I would like to use the conditional formatting feature to format an entire > row rather than just a single cell. For example: If the cell value = "Total" > then the entire row is bo...

cell address combination
I'm using regular Excel formula in the cells (not scripting, mainly because I don't know how). I have a problem with trying to combine offset() and small() my actual code is: =OFFSET(small(E4:E18,1),0,-2) and it obviously doesn't work. Is there any way to use those two features together? If it helps I'm trying to make a gradebook capable of weighting categories (e.g. 50% of avg for quiz, etc.) and dropping lowest quizzes. I have all the code working except this one combination of finding the lowest grade using small() and returning its corresponding number using offset(). ...

Macro entering data into a protected Cell??
I'm trying to create a time sheet and I want to have a macro insert the current time into a protected cell (to stop people editing the time manually) Is this possible?? Any tips/hints See your other thread -- Regards Ron de Bruin http://www.rondebruin.nl <chortler@fetchmail.co.uk> wrote in message news:1121246433.314393.17210@o13g2000cwo.googlegroups.com... > I'm trying to create a time sheet and I want to have a macro insert the > current time into a protected cell (to stop people editing the time > manually) > Is this possible?? Any tips/hints > ...

How to add time from multiple cells & date Cells & machine type c
I'm trying to add up the amount of machinery downtime from a number of different cells but the thing is that I need to only have it added up by the date set in another cell (Which can have multiple cells of the same date) as well as the specific name of that machinery in another cell(which can also have multiples of the same machinery name). How can i get to just add up for that specific date, machine name, and time. Example: Date (Cell 1A thru 50A) Machinery Name(Cell 1B thru 50B) Downtime(Cell 1J thru 50J) Please help Thanks Example:Sheet1 Data Column A ...