reference question #2

A few days ago, some kind person introduced me to the INDIRECT function 
as a way of keeping a formula pointed at a fixed cell even after I've 
inserted a column or two on the worksheet. (Thank you!)

Turns out, though, that while I need that fixed reference to a given 
column, I actually want a relative reference to the rows on the worksheet.

Does anyone have a suggestion about how to set my formula up so the 
reference to a given column is absolute but the row floats?

Thanks!
0
user4293 (34)
5/11/2006 2:51:12 AM
excel 39879 articles. 2 followers. Follow

1 Replies
724 Views

Similar Articles

[PageSpeed] 7

=INDIRECT("B"&ROW(1:1))

for B1

-- 

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging."  Lord Healey


"david" <user@example.net> wrote in message 
news:9a033$4462a697$cf705e58$12577@PRIMUS.CA...
>A few days ago, some kind person introduced me to the INDIRECT function as 
>a way of keeping a formula pointed at a fixed cell even after I've inserted 
>a column or two on the worksheet. (Thank you!)
>
> Turns out, though, that while I need that fixed reference to a given 
> column, I actually want a relative reference to the rows on the worksheet.
>
> Does anyone have a suggestion about how to set my formula up so the 
> reference to a given column is absolute but the row floats?
>
> Thanks! 


0
Peo
5/11/2006 3:29:25 AM
Reply:

Similar Artilces:

Naive Serialization Question
Hello, I have a CMap object m_mMapFrames which looks like this: CMap<int, int, CTFrame, CTFrame&>, where CTFrame is a custom object. I have successfully serialized this object by overriding SerializeElements, thus: template<> inline void AFXAPI SerializeElements<CTFrame>( CArchive& ar, CTFrame* pNewFrame, int nCount ) { for ( int i = 0; i < nCount; i++, pNewFrame++ ) { pNewFrame->Serialize(ar); } } and simply adding: m_mMapFrames->Serialize(ar); to my document's serialize function. However, due to its size, I would like to declare m_mMa...

Balance Transfer Credit Cards #2
balance transfers are a popular feature for credit card offers. Usually, new credit cards offer a 0 % annual percentage rate, or a 0 balance transfer for a limited time, and then the introductory rate expires and the APR increases dramatically. If you are trying to decrease debt on the credit cards you hold a high interest rate, a 0 percent balance transfer credit card offer may be your best option for paying off your debt. Also, a 0 percent interest balance transfer can get you some other perks through credit card companies that offer rewards. There are several things to look for when sear...

PlotArea question
Hi there, I have a chart I'm trying to build with VBA (Excel 2000). It's a xlBarClustered type (horizontal bar chart) but it has unusually long labels. What I want to do is to make the ratio of space for the labels to bars roughly 75/25. PlotArea seems to include BOTH the data and the labels, so resizing this doesn't seem to help - I just end up with more whitespace if I try reducing the plotarea size. Is there any way of controlling the size of the labels versus the bars? Any help much appreciated Gromit Hi, The simply answer is no. Excel automatically decides the area allo...

Cell Value as Named Range Reference
Little bit of a quirky question... Trying to use a cell value as a reference in a formula, where that cell value is the name of a named range. So, assume A1:A3 is a named range: Rng1. And, B1:B3 is Rng2. I want to get the correlation vale for A1:A3 and B1:B3 So, cell D1 contains "Rng1" and D2 has "Rng2"; and cell D3 has the formula: =correl(D1,D2). But I get an error. Have also tried using Indirect to no avail. Any help would be hugely appreciated. Thank you. =CORREL(INDIRECT(D1),INDIRECT(D2)) -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "ste...

Need to reference a block of cells from another sheet.
Dear All, apologies if this is probably a mundane question, but I didn't know what 'search terms' to put in google to return any results.. I've got a Spreadsheet with 12 tabs (sheets), I need part of tab 12 (sheet) to appear on tabs 1-11 (sheets), so that if I change data in tab 12 (sheet), tabs 1-11 (sheets) are changed too.. the cells themselves will never move location, just the data within them.. Is it possible? Regards Paul. On your sheet1 select the cell that should receive the data from Sheet12; Hold down the Shift key and Click on Sheet11 << this shoul...

refering to a name range area as a reference value multiple times on a worksheet at different locations
i was wondering if i had posted this in the right section as it could apply to both please could you take a look at the following post http://groups.google.com/group/microsoft.public.excel.misc/browse_thread/thread/f088a5737dd26570 thank you Is there some reason you can't post your question here instead of asking us to look elsewhere? -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Blinds Nottingham" <bradrail.blinds.awnings@googlemail.com> wrote in message news:d8768e43-e035-45eb-856a-0ccdc843441d@g1g2000yqi.googlegroup...

Sent mail folder question for MVPs
I have two questions about Outlook, both related to having multiple mail accounts (e.g one Exchange and one IMAP). 1) Can I have mail from my imap account go to that accounts sent mail folder instead of the Exchange or default one? 2) Can I disable spam filtering for one account while allowing for another? 3) Can I have rules/filters on folders other than inbox? Thanks for any help. 1) Yes, but you need to use rules to move the mail by account and if it can't connect to the imap for some reason, no copy is saved. 2) No. 3) Only if you use Run Now. -- Diane Poremsky [MVP - O...

Pivot Table question #3
Hi Folks: I'm helping a pal with a table that has little data, and when the PT is setup, the result is 90% of the cells have the word "blank" Any way to have the cells show nothing (be empty) ?? Thanks Steve Try just typing a space character over it the (blank). Debra Dalgleish has a pivottable addin that you may want to use to make somethings easier: http://www.contextures.com/xlPivotAddIn.html and for instructions: http://www.contextures.com/xlPivotAddIn02.html s boak wrote: > > Hi Folks: > > I'm helping a pal with a table that has little data, and wh...

Converting Leads #2
When we convert leads and choose disqualify as the option, we get a drop down with a few selections (lost, canceled, etc.) Can we control this drop down to add more reasons to the field? If so, how do i go about making that change? Thanks Tiffany On May 13, 5:55=A0am, Tiffany <Tiff...@discussions.microsoft.com> wrote: > When we convert leads and choose disqualify as the option, we get a drop = down > with a few selections (lost, canceled, etc.) > > Can we control this drop down to add more reasons to the field? > If so, how do i go about making that change? > > T...

What is IVA# in Distribution Reference and Reference?
We did an inventory adjustment and now we get a unknown amount in Debit under the Inventory account and Credit for COGS account in the Transaction Entry Zoom. We're trying to figure out where that amount came from but we couldn't zoom more than what it's currently shown. In both fields, Reference and Distribution Reference, it's shown IVA#######... that is unknown to or setup by us in anywhere, i.e. Audit Trail, Source Document. Does this amount affect the dollar in the accounts mentioned? And can anyone please help to where we could drill down the IVA details?...

An English question about programming, need help!
In the book "Programming Server-side Applications for Microsoft Windows 2000" by Jeffrey Richter, I found such a sentence: "I know developers who don't even think twice about allocating megabytes of storage for tasks that need no more than a few kilobytes, tops." (on page 5) What I couldn't understand is the last word "tops". Who can tell me what is the exact meaning of "a few kilobytes, tops."? I'm not a native English speaker, and I think this question is more relevant to programming than English. So I post the question here other than so...

Date Formula #2
I am trying to write a formula that works off what is in two separate cells. It has several parts and maybe not all of them can be done. It would be great if someone could help me out. Here goes. If A2 & A3 each have a "N" in it then I want cell A5 to show today's date plus 20 weeks. However, if only 1 of the cells A2 or A3 has a "N" in I still want A5 to show today's date plus 20 weeks. Can this also be done? If both A2 & A3 have "Y" in them then I want A5 to freeze with the date last show when 1 of the two cells had a "N" it ...

Custom Number Formats #2
How do I create a custom number format to return thousands e.g. if the value is 1000 the format shows 1 Under Format -> Cells in the Custom format box type #, (make sure you put the comma in). This will only show the thousands part, note that it will round numbers like 2598 up to 3 instead of 2. Andrea Jones http://www.allaboutoffice.co.uk http://www.stratatraining.co.uk http://www.allaboutclait.com "Mary Ann" wrote: > How do I create a custom number format to return thousands e.g. if the value > is 1000 the format shows 1 Try this custom number format: 0, Or: ...

macro button refers to prev. workbook?
Hi, When I create a macro in Excel, and assign a button to it, it works fine the first time, but when I re-open the workbook for a second time, and try to run the macro again, the macro seems to refer to the previous workbook...For some reason, the macro saves the name of the previous workbook... Sub test() ' Dim name As String Dim adress As String Sheets("sheet1").Select Range("A1").Select name = ActiveSheet.Range("B6").Value adress = ActiveSheet.Range("B4").Value ActiveWorkbook.SaveAs Filename:="C:\WINDOWS\Desktop\Temp...

want questions on EXCEL-2007
Hello Everyone, I want some questions on EXCEL-2007 for checking competency level of the user. Please help. -- Ananta Gupta Hello Ananta. Go to this page:- http://www.ecdl.org/programmes/index.jsp?p=109&n=185 There are sample test papers here. Go to ECDL / ICDL Advanced Syllabus Version 2.0 and, in that section, VISTA 2007 then Sample Test Paper (VISTA 2007) - ECDL / ICDL Advanced Spreadsheets. There are 20 questions in here that you might like to ask. Don't worry too much about the VISTA bit as that is not important (I have tested it on other oper...

Column and Row reference
How do i hide the column and row reference number in a sheet, when viewing the worksheet i do not to show the top and right hand side references Dave Tools>Options>View. Uncheck "row and column headers". Good idea to spend a few minutes browsing through the various Tools>Options tabs to see what else is available to toggle on/off. Gord Dibben Excel MVP On Mon, 19 Jul 2004 15:53:02 -0700, "Dave" <Dave@discussions.microsoft.com> wrote: >How do i hide the column and row reference number in a sheet, when viewing the worksheet i do not to show the top an...

Missing References
I had a problem today with a missing reference - Microsoft Forms 2 on a colleague's machine at work (Office 2003; VBA 6.5; XP). I've read Chip Pearson's "Missing References in VBA" which is a geat explanation about how to fix problems like this. Oddly the library didn't seem to be flagged as "missing" and was in fact present in the system32 directory but didn't show up in the list of available references. Now to the questions: I'm not clear on whether if I write code in a project that references a library which is not part of the &qu...

Silly question...
Why does A.D. say that it's going to delete the associated mailbox when you delete a distribution group? There's no mailbox created is there? And I don't believe it kills all the user mailboxes that were in the distribution group that is being deleted. Thanks, D A distribution group is a mail enabled object hence the reference to mailbox deletion. As you suggest there is no mailbox and it will not delete any mailboxes of users included in the distribution group. Nue "Dan" <Dan@discussions.microsoft.com> wrote in message news:68EB152B-4F73-4B27-89E4-41C37D...

Reference
Is it possible to create a reference to another object such as: int & x = i; but using an image list, and referencing different objects depending on a condition? such as : CImageList & imageList; if (thumbnail == 1) imageList = m_ImageListThumb; else imageList = m_ImageListFullSize; Hope this makes sense! Ben You're better off using pointers for this. I believe, depending on the class, that what you're trying to will make a copy of the object instead of referencing it. "Ben Williamson" <oakdaleclose@googlemail.com> wrote in message news:43ee4603...

PAYROLL QUESTION #2
WHERE CAN I FIND A TEMPLATE FOR PAYROLL WITH CORRECT TAX DEDUCTIONS OF THE CURRENT YEAR? You can use Google to search for Excel Payroll Templates. I would guess that the most useful templates will not necessarily have this year's data, but will allow you to enter the tax rates yourself. If you need specific help, it would probably be useful to indicate which country, state/province/taxing district you're looking for. Please also turn off your CAPS LOCK. It makes your posts much more difficult to read, and is considered the equivalent of shouting in newsgroup etiquette. In ar...

Excel file size question
Is there a maximum size of file recommended? Or does is simply matter how much memory is in the computer. I have a database file approaching 3 meg and it is taking a noticable time to save. Shall i just let it grow, or shall i strip out some of the older lines and archive them somewhere. Just wondering what people tend to do. Thanks, Rick Rick First try resetting the used range on the sheets. It's at www.contextures.com. Save the file again, and check the size. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "R D S&qu...

Macro to Reference Column Next to Current Reference
Hi, I'd like to automate the following procedure using a macro. Please help. 1) i have 2 sheets: Sheet1 & Sheet2 2) In Sheet1, Cells B2,D2,F2 has the formulas "=Sheet2! F2", "=Sheet2!K2", "=Sheet2!P2" correspondingly. 3) Every month when i do my work, i'd have to manually rekey the formulas to reference the subsequent columns, ie, Cell B2,D2,F2 will change from above to "=Sheet2! G2", "=Sheet2!L2", "=Sheet2!Q2" and in the following month, I have to change manually again to "=Sheet2! H2", "=Sheet2!M2&qu...

Delete Rows #2
I would like to be able to delete a row with a name in it based on a cell with that name from another cell in a different worksheet. Any ideas? Thanks, Bernie Hi Bernie This example loop through row 1 - 100 on the activesheet and if the value in A is the same as in Sheets("Yoursheet").Range("A1").Value in delete the row Maybe you can use a Autofilter if you have many rows (faster) See this page for more info http://www.rondebruin.nl/delete.htm Sub Example2() Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long Dim StartRow As Long Dim End...

Sheet Reference
Is there a formula for listing the current Sheet. I know about the filename cell("filename",A1) which returns the entire path, but I just want the Sheet name to appear. John, You still use the CELL("filename") function, but you need to trim the result a bit. Like this: =MID(CELL("filename"),FIND("]",CELL("filename"))+1,99) -- Ture Magnusson Microsoft MVP - Excel Karlstad, Sweden "John" <anonymous@discussions.microsoft.com> wrote in message news:0c6001c3a856$291c9a20$a501280a@phx.gbl... > Is there a formula for listing...

2 Different print areas?????????
Hello. I would like to print a sheet, and I want to leave out 1 row. I just want the printer to skip it and keep going. When I assign print areas, the computer splits it up onto 2 pages. Any way to get around that? Miles -- mcr1 ------------------------------------------------------------------------ mcr1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15496 View this thread: http://www.excelforum.com/showthread.php?threadid=277504 Hi check out: http://www.rondebruin.nl/print.htm#non-contiguous -- Regards Frank Kabel Frankfurt, Germany "mcr1"...