Referencing a Row value from within a VLOOKUP

Hello group,

I've worked with excel for many years, but I could use some help throwing 
this together.  The background:

I have two sheets within the same workbook.  One sheet has check numbers and 
values that have been issued by the company.  The other sheet has that same 
information, but only what has been reported back to us by the bank.  I 
working on streamlining the comparison between the two sheets to more easily 
see what checks are outstanding (have not been cashed at the bank).  Also, 
if the check has been cashed, I want to compare the value that the bank 
recorded with the value that our company recorded to insure that they match 
up.

My problem is that the table is going to grow as the year goes on and I'm 
not sure how to get the VLOOKUP table array to grow with it.  In the 
following formula, F5 is the cell that contains the check number on our 
company detail sheet, Bank DetailB6:C17206 is the the table array where the 
banks check number and value is stored:
=IF(F5="","",IF(ISNA(VLOOKUP(F5,'Bank Detail'!$B$6:$C$17206,1,FALSE)),"No 
Match","Match"))

Is there a way to use the indirect function and/or the Row function to 
update the row reference to 17206 (the footer row in my bank detail sheet? 
This way, as more bank detail is added, that row reference will remain at 
the bottom of the list.

I know the syntax doesn't work, but I would like it to do this:
=IF(F5="","",IF(ISNA(VLOOKUP(F5,'Bank Detail'!$B$6:$C$ROW('Bank 
Detail'!C17206),1,FALSE)),"No Match","Match"))

Maybe there's a better way to go about this.  I'm open to suggestions if the 
community has any.  Right now, each sheet has the following columns:

1.) Check Number
2.) Value
3.) Match? - if the check number is found on the other sheet, "Match" is 
entered in to the cell, otherwise "No Match" is entered
4.) Amount of non-matching Checks - If Cell "Match?" = "No Match", Value 
amount is entered into the cell
5.) Amount of Matching Check from other sheet if Values differ - If a check 
number match is found on the other sheet, this cell compares the two values 
and return the other sheets value only if the two values don't match.


Any help/comments/suggestions will be appreciated.

Michael 


0
mbehm (14)
8/10/2006 2:55:43 PM
excel 39879 articles. 2 followers. Follow

5 Replies
809 Views

Similar Articles

[PageSpeed] 7

What problem do you anticipate having if you would simply oversize the
table, to take into consideration future expansion?

I don't think that an extra 1 or 2,000 rows would hurt.

What are your concerns about this?
-- 

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"Michael Behm" <mbehm@hutchtel.net> wrote in message
news:%23h1dvzIvGHA.1504@TK2MSFTNGP03.phx.gbl...
Hello group,

I've worked with excel for many years, but I could use some help throwing
this together.  The background:

I have two sheets within the same workbook.  One sheet has check numbers and
values that have been issued by the company.  The other sheet has that same
information, but only what has been reported back to us by the bank.  I
working on streamlining the comparison between the two sheets to more easily
see what checks are outstanding (have not been cashed at the bank).  Also,
if the check has been cashed, I want to compare the value that the bank
recorded with the value that our company recorded to insure that they match
up.

My problem is that the table is going to grow as the year goes on and I'm
not sure how to get the VLOOKUP table array to grow with it.  In the
following formula, F5 is the cell that contains the check number on our
company detail sheet, Bank DetailB6:C17206 is the the table array where the
banks check number and value is stored:
=IF(F5="","",IF(ISNA(VLOOKUP(F5,'Bank Detail'!$B$6:$C$17206,1,FALSE)),"No
Match","Match"))

Is there a way to use the indirect function and/or the Row function to
update the row reference to 17206 (the footer row in my bank detail sheet?
This way, as more bank detail is added, that row reference will remain at
the bottom of the list.

I know the syntax doesn't work, but I would like it to do this:
=IF(F5="","",IF(ISNA(VLOOKUP(F5,'Bank Detail'!$B$6:$C$ROW('Bank
Detail'!C17206),1,FALSE)),"No Match","Match"))

Maybe there's a better way to go about this.  I'm open to suggestions if the
community has any.  Right now, each sheet has the following columns:

1.) Check Number
2.) Value
3.) Match? - if the check number is found on the other sheet, "Match" is
entered in to the cell, otherwise "No Match" is entered
4.) Amount of non-matching Checks - If Cell "Match?" = "No Match", Value
amount is entered into the cell
5.) Amount of Matching Check from other sheet if Values differ - If a check
number match is found on the other sheet, this cell compares the two values
and return the other sheets value only if the two values don't match.


Any help/comments/suggestions will be appreciated.

Michael



0
ragdyer1 (4060)
8/10/2006 3:07:52 PM
Two concerns I have:
1.)  With the current calculations, the workbook is already not performing 
very fast.  I don't know if adding extra rows to the formula will further 
degrade it.
2.)  I am not the one who will be using this sheet.  It worries me to make 
assumptions about how much the sheet will grow.  If I underestimate, then 
things will need to be re-worked in the future.

Granted, by adding 10000 rows to the formula, it would probably be covered, 
but do you guys think that will affect the performance at all?

Thanks for your reply RD


"RagDyeR" <ragdyer@cutoutmsn.com> wrote in message 
news:%231u%23L8IvGHA.4968@TK2MSFTNGP03.phx.gbl...
> What problem do you anticipate having if you would simply oversize the
> table, to take into consideration future expansion?
>
> I don't think that an extra 1 or 2,000 rows would hurt.
>
> What are your concerns about this?
> -- 
>
> Regards,
>
> RD
> ----------------------------------------------------------------------------
> -------------------
> Please keep all correspondence within the Group, so all may benefit !
> ----------------------------------------------------------------------------
> -------------------
>
> "Michael Behm" <mbehm@hutchtel.net> wrote in message
> news:%23h1dvzIvGHA.1504@TK2MSFTNGP03.phx.gbl...
> Hello group,
>
> I've worked with excel for many years, but I could use some help throwing
> this together.  The background:
>
> I have two sheets within the same workbook.  One sheet has check numbers 
> and
> values that have been issued by the company.  The other sheet has that 
> same
> information, but only what has been reported back to us by the bank.  I
> working on streamlining the comparison between the two sheets to more 
> easily
> see what checks are outstanding (have not been cashed at the bank).  Also,
> if the check has been cashed, I want to compare the value that the bank
> recorded with the value that our company recorded to insure that they 
> match
> up.
>
> My problem is that the table is going to grow as the year goes on and I'm
> not sure how to get the VLOOKUP table array to grow with it.  In the
> following formula, F5 is the cell that contains the check number on our
> company detail sheet, Bank DetailB6:C17206 is the the table array where 
> the
> banks check number and value is stored:
> =IF(F5="","",IF(ISNA(VLOOKUP(F5,'Bank Detail'!$B$6:$C$17206,1,FALSE)),"No
> Match","Match"))
>
> Is there a way to use the indirect function and/or the Row function to
> update the row reference to 17206 (the footer row in my bank detail sheet?
> This way, as more bank detail is added, that row reference will remain at
> the bottom of the list.
>
> I know the syntax doesn't work, but I would like it to do this:
> =IF(F5="","",IF(ISNA(VLOOKUP(F5,'Bank Detail'!$B$6:$C$ROW('Bank
> Detail'!C17206),1,FALSE)),"No Match","Match"))
>
> Maybe there's a better way to go about this.  I'm open to suggestions if 
> the
> community has any.  Right now, each sheet has the following columns:
>
> 1.) Check Number
> 2.) Value
> 3.) Match? - if the check number is found on the other sheet, "Match" is
> entered in to the cell, otherwise "No Match" is entered
> 4.) Amount of non-matching Checks - If Cell "Match?" = "No Match", Value
> amount is entered into the cell
> 5.) Amount of Matching Check from other sheet if Values differ - If a 
> check
> number match is found on the other sheet, this cell compares the two 
> values
> and return the other sheets value only if the two values don't match.
>
>
> Any help/comments/suggestions will be appreciated.
>
> Michael
>
>
> 


0
mbehm (14)
8/10/2006 3:26:19 PM
I don't specify rows at all.  Try this:

=IF(F5="","",IF(ISNA(VLOOKUP(F5,'Bank Detail'!$B:$C,1,FALSE)),"No
Match","Match"))

I wouldn't worry about looking values up in the first 5 rows of your
range.  When will your lookup value ever be the same as the title of
your other page?

Hope this helps,

Alex.


Michael Behm wrote:
> Hello group,
>
> I've worked with excel for many years, but I could use some help throwing
> this together.  The background:
>
> I have two sheets within the same workbook.  One sheet has check numbers and
> values that have been issued by the company.  The other sheet has that same
> information, but only what has been reported back to us by the bank.  I
> working on streamlining the comparison between the two sheets to more easily
> see what checks are outstanding (have not been cashed at the bank).  Also,
> if the check has been cashed, I want to compare the value that the bank
> recorded with the value that our company recorded to insure that they match
> up.
>
> My problem is that the table is going to grow as the year goes on and I'm
> not sure how to get the VLOOKUP table array to grow with it.  In the
> following formula, F5 is the cell that contains the check number on our
> company detail sheet, Bank DetailB6:C17206 is the the table array where the
> banks check number and value is stored:
> =IF(F5="","",IF(ISNA(VLOOKUP(F5,'Bank Detail'!$B$6:$C$17206,1,FALSE)),"No
> Match","Match"))
>
> Is there a way to use the indirect function and/or the Row function to
> update the row reference to 17206 (the footer row in my bank detail sheet?
> This way, as more bank detail is added, that row reference will remain at
> the bottom of the list.
>
> I know the syntax doesn't work, but I would like it to do this:
> =IF(F5="","",IF(ISNA(VLOOKUP(F5,'Bank Detail'!$B$6:$C$ROW('Bank
> Detail'!C17206),1,FALSE)),"No Match","Match"))
>
> Maybe there's a better way to go about this.  I'm open to suggestions if the
> community has any.  Right now, each sheet has the following columns:
>
> 1.) Check Number
> 2.) Value
> 3.) Match? - if the check number is found on the other sheet, "Match" is
> entered in to the cell, otherwise "No Match" is entered
> 4.) Amount of non-matching Checks - If Cell "Match?" = "No Match", Value
> amount is entered into the cell
> 5.) Amount of Matching Check from other sheet if Values differ - If a check
> number match is found on the other sheet, this cell compares the two values
> and return the other sheets value only if the two values don't match.
> 
> 
> Any help/comments/suggestions will be appreciated.
> 
> Michael

0
8/10/2006 3:52:03 PM
Thanks RD and Alex for you posts.  I think I will just go with your 
suggestion Alex and see how it runs.  It probably won't make a big 
difference on the system resources anyways.

Michael


"Alex" <alex-brewer@rocketmail.com> wrote in message 
news:1155225123.555786.286300@i3g2000cwc.googlegroups.com...
>I don't specify rows at all.  Try this:
>
> =IF(F5="","",IF(ISNA(VLOOKUP(F5,'Bank Detail'!$B:$C,1,FALSE)),"No
> Match","Match"))
>
> I wouldn't worry about looking values up in the first 5 rows of your
> range.  When will your lookup value ever be the same as the title of
> your other page?
>
> Hope this helps,
>
> Alex.
>
>
> Michael Behm wrote:
>> Hello group,
>>
>> I've worked with excel for many years, but I could use some help throwing
>> this together.  The background:
>>
>> I have two sheets within the same workbook.  One sheet has check numbers 
>> and
>> values that have been issued by the company.  The other sheet has that 
>> same
>> information, but only what has been reported back to us by the bank.  I
>> working on streamlining the comparison between the two sheets to more 
>> easily
>> see what checks are outstanding (have not been cashed at the bank). 
>> Also,
>> if the check has been cashed, I want to compare the value that the bank
>> recorded with the value that our company recorded to insure that they 
>> match
>> up.
>>
>> My problem is that the table is going to grow as the year goes on and I'm
>> not sure how to get the VLOOKUP table array to grow with it.  In the
>> following formula, F5 is the cell that contains the check number on our
>> company detail sheet, Bank DetailB6:C17206 is the the table array where 
>> the
>> banks check number and value is stored:
>> =IF(F5="","",IF(ISNA(VLOOKUP(F5,'Bank Detail'!$B$6:$C$17206,1,FALSE)),"No
>> Match","Match"))
>>
>> Is there a way to use the indirect function and/or the Row function to
>> update the row reference to 17206 (the footer row in my bank detail 
>> sheet?
>> This way, as more bank detail is added, that row reference will remain at
>> the bottom of the list.
>>
>> I know the syntax doesn't work, but I would like it to do this:
>> =IF(F5="","",IF(ISNA(VLOOKUP(F5,'Bank Detail'!$B$6:$C$ROW('Bank
>> Detail'!C17206),1,FALSE)),"No Match","Match"))
>>
>> Maybe there's a better way to go about this.  I'm open to suggestions if 
>> the
>> community has any.  Right now, each sheet has the following columns:
>>
>> 1.) Check Number
>> 2.) Value
>> 3.) Match? - if the check number is found on the other sheet, "Match" is
>> entered in to the cell, otherwise "No Match" is entered
>> 4.) Amount of non-matching Checks - If Cell "Match?" = "No Match", Value
>> amount is entered into the cell
>> 5.) Amount of Matching Check from other sheet if Values differ - If a 
>> check
>> number match is found on the other sheet, this cell compares the two 
>> values
>> and return the other sheets value only if the two values don't match.
>>
>>
>> Any help/comments/suggestions will be appreciated.
>>
>> Michael
> 


0
mbehm (14)
8/10/2006 5:46:07 PM
You didn't bring up your problem with calc efficiency in your OP,

*BUT* Alex's formula is referencing 2 *entire* columns of over 65,000 rows 
each!

On another note, can you explain the concept of your formula to me?
You're referencing *2* columns in your Vlookup formula, but only using  the 
first column for lookup *and* return purposes.

Vlookup is supposedly a "not too efficient" function in the first place.

Try this formula, and see if there's any improvement:

=IF(F5="","",IF(ISNA(MATCH(F5,'Bank Detail'!$B$6:$B$17206,0)),"No 
Match","Match"))

Also, post the other formulas your using in the other columns.
-- 
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Michael Behm" <mbehm@hutchtel.net> wrote in message 
news:erTX%23SKvGHA.1504@TK2MSFTNGP03.phx.gbl...
> Thanks RD and Alex for you posts.  I think I will just go with your 
> suggestion Alex and see how it runs.  It probably won't make a big 
> difference on the system resources anyways.
>
> Michael
>
>
> "Alex" <alex-brewer@rocketmail.com> wrote in message 
> news:1155225123.555786.286300@i3g2000cwc.googlegroups.com...
>>I don't specify rows at all.  Try this:
>>
>> =IF(F5="","",IF(ISNA(VLOOKUP(F5,'Bank Detail'!$B:$C,1,FALSE)),"No
>> Match","Match"))
>>
>> I wouldn't worry about looking values up in the first 5 rows of your
>> range.  When will your lookup value ever be the same as the title of
>> your other page?
>>
>> Hope this helps,
>>
>> Alex.
>>
>>
>> Michael Behm wrote:
>>> Hello group,
>>>
>>> I've worked with excel for many years, but I could use some help 
>>> throwing
>>> this together.  The background:
>>>
>>> I have two sheets within the same workbook.  One sheet has check numbers 
>>> and
>>> values that have been issued by the company.  The other sheet has that 
>>> same
>>> information, but only what has been reported back to us by the bank.  I
>>> working on streamlining the comparison between the two sheets to more 
>>> easily
>>> see what checks are outstanding (have not been cashed at the bank). 
>>> Also,
>>> if the check has been cashed, I want to compare the value that the bank
>>> recorded with the value that our company recorded to insure that they 
>>> match
>>> up.
>>>
>>> My problem is that the table is going to grow as the year goes on and 
>>> I'm
>>> not sure how to get the VLOOKUP table array to grow with it.  In the
>>> following formula, F5 is the cell that contains the check number on our
>>> company detail sheet, Bank DetailB6:C17206 is the the table array where 
>>> the
>>> banks check number and value is stored:
>>> =IF(F5="","",IF(ISNA(VLOOKUP(F5,'Bank 
>>> Detail'!$B$6:$C$17206,1,FALSE)),"No
>>> Match","Match"))
>>>
>>> Is there a way to use the indirect function and/or the Row function to
>>> update the row reference to 17206 (the footer row in my bank detail 
>>> sheet?
>>> This way, as more bank detail is added, that row reference will remain 
>>> at
>>> the bottom of the list.
>>>
>>> I know the syntax doesn't work, but I would like it to do this:
>>> =IF(F5="","",IF(ISNA(VLOOKUP(F5,'Bank Detail'!$B$6:$C$ROW('Bank
>>> Detail'!C17206),1,FALSE)),"No Match","Match"))
>>>
>>> Maybe there's a better way to go about this.  I'm open to suggestions if 
>>> the
>>> community has any.  Right now, each sheet has the following columns:
>>>
>>> 1.) Check Number
>>> 2.) Value
>>> 3.) Match? - if the check number is found on the other sheet, "Match" is
>>> entered in to the cell, otherwise "No Match" is entered
>>> 4.) Amount of non-matching Checks - If Cell "Match?" = "No Match", Value
>>> amount is entered into the cell
>>> 5.) Amount of Matching Check from other sheet if Values differ - If a 
>>> check
>>> number match is found on the other sheet, this cell compares the two 
>>> values
>>> and return the other sheets value only if the two values don't match.
>>>
>>>
>>> Any help/comments/suggestions will be appreciated.
>>>
>>> Michael
>>
>
> 

0
ragdyer1 (4060)
8/10/2006 7:05:09 PM
Reply:

Similar Artilces:

Referencing merged cells
I'm having trouble referencing a merged cell in another workbook. Say I merge cells A1 to C1 in workbook 1. When I make this the active cell, the Name Box says "A1". When I go to another workbook (say, workbook 2), go to cell B1, type "=" and then go to the merged cell and select it, I get 'workbook1'!$A$1:$C$1 as the formula and, of course, the "#VALUE" error. I've successfully tried typing "=sum(" without the quotes hen clicking on the contents of the cell and then adding the ")" and it works O.K. but there should be an easi...

The most occurence of a value in a column
I have 1 to 5 as values in a column. How can I find the number with the most occurrence in that column? Thanks, cpliu Hi =MODE(A1:A100) -- Regards Frank Kabel Frankfurt, Germany "cpliu" <chanciusliuDeleteThis@yahoo.com> schrieb im Newsbeitrag news:Xns959C8C47C309BchanciusliuDeleteThi@130.133.1.4... > I have 1 to 5 as values in a column. How can I find the number with the > most occurrence in that column? > > Thanks, > > cpliu Try: =INDEX(A1:A5,MATCH(MAX(COUNTIF(A1:A5,A1:A5)),COUNTIF (A1:A5,A1:A5),0)) Array-entered, meaning press ctrl/shift/enter. H...

Multiple SEARCHes within same TEXT string
Hi, I have a column which contains text strings that represent colours. However, the *actual* colour is often buried in other text i.e. METBLACKPAINT. I have a list of 12 single-word colours i.e. BLACK, and want to identify in one formula which (if any) of the 12 is buried in the text string (and then have the formula return that colour) If I had just 7 colours, I'd just go with nested IFs and SEARCH, but as I have 12 that is not an option. Any ideas would be greatly appreciated. Many thanks, Jason Assume your 12 single-word colours eg: BLACK, WHITE, etc are in a defined co...

Referencing another field if result is N/A
Hello, I have a situation where I am referencing data on another workbook. The reason behind this is the data in Column H can be entered incorrectly and often is. I am able to prevent this going forward but can not do so for the items already entered. I enter a formula via VBA, however, sometimes the result will be #N/A and if so I would prefer to leave the old value and just highlight it that it needs to be verified by hand. I am not sure if I can just "leave" the value as it has pretty much already been replaced. Due to this, I copy the column to a different colum...

This forum does have value!
As year end approaches and I am considering upgrading to Money 2005, I found the message traffic extremely valuable. After this summers fiasco with the update to MSN Money locking us out of our local data file, I thought, or was hoping M$ would make an extra effort to make sure that Money 2005 was a significant improvement and without the assorted problems that I have found posted here. Some still unresolved. I have made my decision and I do not care how much work it is going to take me, bt I am going back to Quicken after giving Money a two year trial. This program is just not ready f...

This workbook is currently referenced by another workbook and cannot be closed.
The message is given when I try to close a workbook / kill Microsoft Excel. I'm using Microsoft Excel 2002 / XP. The spreadsheet includes a reference to another sheet that someone else referenced in another message here. I put the AddIn sheet in via: Tools, AddIns..., Analysis ToolPack - VBA. I've not referenced any function or procedure in the add-in. I get a Triangular Warning / Error ? It's got a graphic of a Yellow Triangle with an Exclamation Mark inside. The subject is the exact text of the warning. A Microsoft Search on the subject yielded nothing. Google yielded one ...

Printing only certain rows
Is there a way (custom view, filter, etc) to only print rows in a print area that have a value greater than x amount in a certain column? For instance, I have a worksheet that shows all outstanding accounts. Each account has it's own row (and the sheet has a couple of header rows). One of the columns shows the total days overdue the account is. However, I only want to print the header rows and the subsequent rows with account balances greater than 90 days. It seems incredible there is no easy way to do this, but I have been searching Excel help without any luck. Thanks I'...

Group rows / columns: +/- symbol at top or to the left?
Hi there If you group columns or rows, you will see a little symbol "+" or "-" to indicate the grouped section. I am wondering if there is a way to tell excel to display the symbol on the top of the grouped section (instead at the bottom in case of grouped rows) or to the left (instead to the right in case of grouped columns). Help greately appreciated! samkut Data/Group and Outline/Settings... In article <1128961350.756875.320910@g49g2000cwa.googlegroups.com>, samkut@web.de wrote: > If you group columns or rows, you will see a little symbol "+" ...

how to highlight current row & column automatically by a color
I'm using Excel 2003, can u plz help me out that how to highlight current row & column with a color automatically to identify my position. Though excel help us by highlighting column & row number, but i want whole column & row to be highlighten to better finding my cell position and contents. So When I change my cell position, the highlighted row & column move appropriately. Take a look at http://cpearson.com/excel/rowliner.htm In article <4076508D-B500-4491-8BE9-C70A825EEBC2@microsoft.com>, asif4u <asif4u@discussions.microsoft.com> wrote: > I&#...

Move Column Data into a row
Hello, I have text in a column that I want to move into a row instead, is this possible without typing it all over again? Thanks -- avidcat ------------------------------------------------------------------------ avidcat's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29659 View this thread: http://www.excelforum.com/showthread.php?threadid=493706 Copy the data and then do a Paste Special using Transpose. Rgds, ScottO "avidcat" <avidcat.202gsm_1134625201.0378@excelforum-nospam.com> wrote in message news:avidcat.202gsm_1134625201.0378@excel...

Worksheet doesn't permit F4 absolute referencing tool in formulas
I've never seen this before in a worksheet and can't find a Tool-Option that would change it. You can of course type the dollar signs in, but the F4 key won't toggle. Sure it's something simple, any help appreciated! -- ronthedog ------------------------------------------------------------------------ ronthedog's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26504 View this thread: http://www.excelforum.com/showthread.php?threadid=397725 ...

Keep main title at top row, even when scolling down
Hi, how do you keep the main title at the top of the worksheet, even i you have scrolled down 30 rows? Thanks -- Message posted from http://www.ExcelForum.com To keep row 1 at the top put your cursor in A2 and select window from the menu, then Freeze pane. You can also freeze columns at the left. The rule is the rows above your cursor and the columns to the left will be frozen, so if you put your cursor on B6 rows 1-5 and column A will be frozen. to remove use Window, Unfreeze pane. In excel this does not affect the printing if you want to something similar for printing use File, Page s...

Autofitting a row
Is it just me, or has something changed with Excel 2003? If I have a cell with wrap text, shouldn't the row size automatically increase if the text exceeds the cell size? For some reason, my text just gets squished. I know I can go to Format-Cells-Autofit, but if I do that, the row could SHRINK if I have too little text in the cell. Here's what I want: I want to increase the height of the cell to a decent size (e.g. 30). IF the text exceeds the cell, then I want it to expand. But if it doesn't, I want the cell to remain at 30 - not decrease. Is there a way to do this? ...

Formula Changes when data entered in referenced range
I have 5 formulas in a worksheet that refer to a range. When data is entered in the range, the formulas adjust to exclude the cells with data. Range B5:B211 When data is entered in B5, range in the formulas becomes B6:B212. The workbook has nearly 200 worksheets with the same formulas. I'm working in Excel 2003. Does anybody have any idea what's going on????? Might help if you include the formulae that you are using. Regards Trevor "mac849" <mac849@discussions.microsoft.com> wrote in message news:33BDD2CD-C071-4DAE-A821-4967E94C16A3@microsoft.com... >...

referencing the cell above
Hi I have a kin a formula wich gets the value from the cell above and then add some. What I get is an list of values incrementing. When I delete one row in this list, all cells below 'crashes' with #REF!. What I need is that for the formulas to always look one cell up, even if I delete a row. Now it seems that if I delete Row8, then when Row9 become Row8 it reference to it self and then ov course goes bananas... Hope I made myself understandable ;-) Any ideas folks? -------- stuhag --------- Instead of using say =A19, use =OFFSET(A20,-1,0). This would go in A20, and as it does...

Scatter chart without numerical values
For some time now, when I create a scatter chart from tabulated (numerical) data, the chart does not display the x/y values, but the serquence numbers instead. This is similar to a bar chart, but should'nt happen with a scatter chart. The scatter charts have worked ok in the past. I'm using Excel 2000 in a company office environment where I have little control over the Excel installation/set-up. -- Regards Fred Are you really making an XY chart? If you make a Line chart with two numeric columns AND the first row have labels, Excel thinks both columns are y-data and uses 1,2,3...

vlookup probs
hello I want to put in a vlookup formula so that i can change a referance number in a cell and it will give me the relavent information ie 3000 will refer to bill 3002 will be harry etc. The formula i have put in is :- =VLOOKUP(D3,data,3) where d3 is the cell with the numbers i want to change to get different information. So ideally it will look for the value i type in d3 in the range i have called data and give me the value in column 3 on the same row within the data field. I hope i have explained clearly. thanks jim I should add that data is on sheet 1 of the workbook, i think this is w...

Convert data from rows to columns
I have a range of information on a spreadsheet as follows: A B 1 Name: Tom Smith Job Title: Sales Organisation: Made up Telephone: 1111 111 111 Email: tom.smith@madeup.co.uk Subject: Not much I have another 300+ entries of data (of 8 rows exactly as above) totalling 2629 rows. the example above is how it appears on my spreadsheet i.e. headings and names in the same cell (Name: Tom Smith) and sometimes seperate cells (Email: / tom.smith@madeup.co.uk) I need to create 6 columns for Name/Job title/Organisation/Telephone/Email/Subject and then move the data into the relevant columns....

Pivot tables 2 row labels
I am trying to create a pivot table and want to have sales rep names and company as the rows. Is there a way to have the sales rep name repeat everytime with the company instead of how it looks below? I am going to have to use the output of the sheet to do a vlookup on another sheet and in this format I can't make it work. Thanks! Row Labels Jane Doe Company 1 John Doe Company 2 Company 1 Company 3 Company 5 Nope--not if you want to keep it a pivottable. But you could convert to values (or copy|paste values in a different locatio...

Indirect Range Referencing
Hello, I work in a paper mill. I am building a spreadsheet with several charts. I have a wide sheet of paper coming off of a paper machine of varying widths. This paper is wound up on a "reel".I have a scanner that measures the weight of the paper on the reel, and each scan gives me a 600 point array. I can calculate where the edges of the paper lie within the array, no problem, and plot the whole thing very nicely. The problem is that the paper then goes into a re-winder, where the wide sheet is cut into smaller sheets, that make up "rolls". I can calculate the sta...

Net book value should be zero when an asset is retired
When an asset is retired and has not been fully depreciated, the net book value is calculated as cost basis less LTD depreciation. Our client believes that this is bad accounting practice. The net book value should be zero when an asset has been retired, even if it has not bee fully depreciated. I understand that when running FA reports it excludes assets with a status of retired, however, when using smartlist > fixed assets book, there is no field to filter out all assets with a status of retired. This makes it difficult for the client to correctly analyse their assets, especial...

Words print with spaces within them that are not on the screen
When I print a document in Word 2007 that is formatted as justified, sometimes a word will look like there is a space in it that does not belong (example: "w ithin", instead of "within"). Also, there may be an extra space showing between two words (example: "or have any obligation", instead of "or have any obligation". This does not show up on the screen in print layout or in print preview. Also, when I show formatting on the screen, there are not any extra symbols for spaces where they are appearing on the printed document. How can I...

Search from value to value on text fields
I have a suggestion concerning search on text fields. Fx the zipcode field. In Denmark we often want to search for an interval fx 5000 to 5999. This isn't possible on Text fields in CRM. You have to create a search with a lot of OR's in AX it is possible to use the syntax 5000..5999. Or a selection of method Between or From and To. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this li...

need help calculating intermediary values
I'm not well enough versed in spreadsheet use to calculate some intermediate values I need. I would be greatful for anyone who can offer a formula or suggestion. I have 1 guidline column, numbered 1-30. Then 2 other columns, A & B are beside it. The values in A & B change as they go from 1-30, they change at different rates. I have some intermediary values at given points but need to calculate what the values in between would be, based on the fixed values at 1, 4, 8, 15 and 30. Below, I have shown the layout as best I can without including an attachment. Thanks. chris@cjalexander...

Default value 07-11-07
Is there any way to initially put the Available qty = the In qty? Because I need the available qty to be deducted everytime there is a transaction? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200707/1 On Wed, 11 Jul 2007 03:08:54 GMT, "EMILYTAN via AccessMonster.com" <u33296@uwe> wrote: >Is there any way to initially put the Available qty = the In qty? >Because I need the available qty to be deducted everytime there is a >transaction? You can use the AfterUpdate event of the Form to set the default value...