Reference information from one sheet to a second sheet

Hi
I have a spreadsheet titled 2006 on sheet one.  This sheet has column a - v 
with information.  I want to have column b and I through s automatically fill 
in on sheet 2.  I was successful in using =(2006!b3) for the first column I 
wanted to be on sheet 2 and it worked up until row 23 then only filled in 
with 0 from there on.  Any suggestions?
0
Kathleen (34)
3/23/2006 4:51:25 PM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
372 Views

Similar Articles

[PageSpeed] 28

This must mean that the corresponding cell in the 2006 sheet is empty
Try =IF(ISBLANK(2006!B3),'"",2006!B3)
That a pair of double quotes between the commas.
If you later have a sheet name with spaces in it, you will need single 
quotes around the sheet name as in 'Year 2006'!B3
best wishes
-- 
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Kathleen" <Kathleen@discussions.microsoft.com> wrote in message 
news:2EFE61AC-2780-4022-926A-25A9E2711C65@microsoft.com...
> Hi
> I have a spreadsheet titled 2006 on sheet one.  This sheet has column a - 
> v
> with information.  I want to have column b and I through s automatically 
> fill
> in on sheet 2.  I was successful in using =(2006!b3) for the first column 
> I
> wanted to be on sheet 2 and it worked up until row 23 then only filled in
> with 0 from there on.  Any suggestions? 


0
bliengme5824 (3040)
3/23/2006 8:06:25 PM
How many rows do you have on sheet 2006?

"Kathleen" wrote:

> Hi
> I have a spreadsheet titled 2006 on sheet one.  This sheet has column a - v 
> with information.  I want to have column b and I through s automatically fill 
> in on sheet 2.  I was successful in using =(2006!b3) for the first column I 
> wanted to be on sheet 2 and it worked up until row 23 then only filled in 
> with 0 from there on.  Any suggestions?
0
3/23/2006 8:49:15 PM
It sounds as if you only have data up to row 23 in your 2006 sheet -
thereafter, Excel will insert a 0 if the cell is blank on the sheet you
are bringing data from. To prevent this happening, you can amend your
formula to:

=IF('2006'!B3 = "","",'2006'!B3))

then copy this down and across.

Hope this helps.

Pete

0
pashurst (2576)
3/24/2006 12:49:00 AM
Reply:

Similar Artilces:

defrag the private information store
Is there a way to defrag the private information store? Use eseutil utility , but you must shutdown information store. "REI" <sales@radioeng.com> wrote in message news:0d3301c3b351$790f13e0$a401280a@phx.gbl... > Is there a way to defrag the private information store? > > Please let us know what version of Exchange you are using. "REI" <sales@radioeng.com> wrote in message news:0d3301c3b351$790f13e0$a401280a@phx.gbl... > Is there a way to defrag the private information store? > > 1. please make sure you know WHY you need to do a defrag...

Putting two sets of data in one Office account
Need a suggestion from you experts out there. I use two laptops, both with Office and I extensively use the mail and task functions on each. I had backed up my work computer, including my Outlook data into a pst file, before attempting the reformat the hard drive. There is a hardware problem with the computer that may take me a few days to a week to get fixed. Since I have a few days, I was wondering about loading the SECOND pst file on my working laptop, but was wondering if there was a way to do so where I could keep the data separate. I wasn't sure if there was a way to do so ...

Collating information in a different location
I have a large sheet (200X200 approx) of individuals that has name, team and a number of Stats. I need some way of moving details of an entire team (10 rows) data to a different location/sheet. I want to be able to put the team code into a cell and all of the data for that team appears underneath for whichever column required (sales etc) Can this be done and where to start? Tom :confused: -- tom300181 ------------------------------------------------------------------------ tom300181's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21580 View this thread...

Multiple desktop alerts for one unopened email
whenever there's a new email, outlook will notify me using desktop alert and the envelop icon at the system tray. the problem i'm having is that desktop alert will display 2-3 times for the same email. is there anyway to fix this??? ...

Standard Row Height Across Sheets
I need to standardize the row height in about 50 different sheets, wher the row height varies a lot by sheet. I only know how to do this b row, which could take a while. Is there a way to do this automatically? Thanks. -Lor -- Message posted from http://www.ExcelForum.com this should do it Sub rh() For Each ws In Worksheets ws.Rows("1:10").RowHeight = 22 Next End Sub -- Don Guillett SalesAid Software donaldb@281.com "elbows >" <<elbows.15oq4d@excelforum-nospam.com> wrote in message news:elbows.15oq4d@excelforum-nospam.com... > I need to stand...

Worksheet has large page numbers in middle of sheet
how do you get to put these page numbers on worksheet that don't print What you're seeing is the User Interface for the Page Break Preview view. In article <FED72CA1-77DD-47AA-A904-6F4284491BE8@microsoft.com>, knifty <knifty@discussions.microsoft.com> wrote: > how do you get to put these page numbers on worksheet that don't print View>Normal or View>Pagebreak view will switch you forth and back. Gord Dibben MS Excel MVP On Mon, 19 Feb 2007 13:30:08 -0800, knifty <knifty@discussions.microsoft.com> wrote: >how do you get to put these page numb...

schedule plan for every 3 month in one sheet excel
hi there i would like to ask you , how i can do in excel schedule plan for 3 month in one sheet In a chart?? What do you have so far? "jameela" <jameela @discussions.microsoft.com> wrote in message news:D62F6B0C-D6E4-4E00-85BF-C191EEA7A60A@microsoft.com... > hi there > > i would like to ask you , how i can do in excel schedule plan for 3 month > in > one sheet ...

Sort by Date and Copy results to another sheet
I'm still working on my little project and can't seem to find what i'm looking for in any of the forums or help files, I know it can be done as I did it a college. using the following data example Date Time Name details 11/03/08 17:00 greg wanted to party 12/03/08 10:55 fred wanted help 13/03/08 11:45 paul wanted to speak to dave 13/03/08 13:30 harry wanted to go home I want to leave the data intact and un edited in the main sheet ...

Can I reinstall my 2007 office pro on my new laptop. Old one died
My old laptop has a bad motherboard and i want to install office 2007 on my new one. I have it installed on my desktop and how do I get a new activation key. Provided its a retail copy of Office, simply install on new PC, having first uninstalled any Trial version & Activation Wizard and rebooted. If its an oem version of office, the licence died with the old PC Retail versions of Office are licensed for two devices for us by an individual user, excepting H&S which is licensed for three "Don62" <Don62@discussions.microsoft.com> wrote in message news:8E...

Sending several pictures as one whole imagen
Publisher 2007 Designing my Flyer I need to use several pictures . I want to send it as a WHOLE UNIQUE MESSAGE ...... NOT SHOWING MULTIPLE ATTATCHMENTS at the upper part of my Flyer. How can I avoid those references to the original pictures used to compose the flyer.? PDF is the preferred way. You could change your publication to a web page(HTML), but many folks do not accept HTML emails and the images will end up being attachments. There is an add-in for PDF on the Microsoft download web site. http://office.microsoft.com/en-us/downloads/CD101950461033.aspx -- Mary Sauer MSFT M...

Error Message: "Cannot shift objects off sheet."
My spreadsheet (form) uses 187 columns for data entry. I am attempting to "hide" select columns in the spreadsheet and am able to do so up to a point. However, after hiding approximately 30 columns, I am receiving the error message, "Cannot shift objects off sheet." What does this error message mean? I've requested assistance from two Excel experts here at work and they are not able to identify the issue. Thanks! Hi, Maybe this KB is of relevance. http://support.microsoft.com/kb/q170081/ Cheers Andy AndrewS wrote: > My spreadsheet (form) uses 187 columns f...

How do I go to a second page while creating a document of labels
I trying to create a mailing list of various people in Word labels. Once I complete page one how do I get the program to give me a page 2 with the same format? Labels are just tables, so you can add table rows by pressing Tab at the end of the last label. You can continue to do this until you fill a new page. This will work provided the labels are set up as a single row. Some label setups, however, include a narrow separator row between rows of labels. For that type of setup, you will need to copy the entire page and paste it onto a new one. To make it easier to see what you...

Allow unit accounts to be either income or balance sheet
There are several Tech Knowledges about BBF on Unit accounts. I suggest allowing the user define the type of account it should be. ---------------- 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 link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.m...

Creating a pop-up information window
Hi All, I'm working on a project for university and have come to a point i Excel where I'm a bit stuck. For the sake of clarity, I won't go int the exact details of what my spreadsheet is supposed to do, let me jus describe the basics in a more understandable way...... Suppose you have a worksheet that has, say, 10 small jpegs of variou makes of car and all the pictures are from the side. Now suppose yo have a button underneath each picture that says 'More Info?' on it What I want to be able to do is click any of the buttons and have window pop-up (a form?) that has fur...

probably an easy one but... (using SUMPRODUCT?)
Hi guys, looks like a nice forum you have here. I've started making a record of all the purchases every month that m kitchen makes. I've set every supplier to an ID code (001,002,003 et etc) and i record this code in the same row as each purchase amount. S for a month, i have a list of purchases, with totals excluding vat, va and then a total inclusive of vat. At the end of the month i can simpl sum the last column to see how much i've spent at the end of ever month. However i'd like to see how much i've spent with each supplier for th month. This i'm struggling a bi...

Files wiped clean on only one opersonal profile setting
Still trying to figure this one out. We run Windows XP SP3 with 5 user profiles. For some reason the 1st personal profile setting was wiped clean except for the start up programs. All documents,files,wallpaper,screen saver ,iTunes,they all disappeared. All the Virus/Malware scans come up with nothing. Of course this user didn't back any of this up so his stuff is gone as far as we know so far. Thanks for any input. "blueoyster" wrote: > Still trying to figure this one out. We run Windows XP SP3 with 5 user > profiles. For some reason the 1st personal ...

Apply more than one master page
MS Office 2007 Small Business Publisher. XP Pro OS. I have a 208 page instruction manual. I developed a master page with a header, footer, page # of # to show automatic sequential pages, company logo, etc. This master page is applied to page 2 through 208. I developed other master pages that I want to apply to groups of pages throughout the document. They contain graphic "tabs" that show up on the vertical border of the document. So as a result I would have the main master page set at 2-208, then another master page set at page 2-9, another set at 10-16, another at 17-26, etc. I...

Help needed with Macro to send data to another sheet/workbook
The spreadsheet I've created makes the user select various options from different drop down menus... once selected the data is displayed on another part of the same sheet. I have a macro in place to clear these cells when the button is selected, I will still be looking to run this macro also. I'm looking for help to create a macro that will allow this data to be transferred onto another workbook/sheet on a button click. The export of data must not overwirte the existing data on the other workbook/sheet and should start on a new line each time the button is selected. Anyone got any su...

I dont have scanpst.exe folder. How do I add one so I can fix Out
I get a message stating that I need to run scanpst.exe as I have a corrupt folder. In the file path under Office 12, there is not scanpst.exe folder. Is there somewhere I can download this file? Mary It is not a folder but a tool for scanning your pst files for errors. Do a search for scanpst.exe and when you find it go to it and run it with Outlook closed. -- Peter Please Reply to Newsgroup for the benefit of others Requests for assistance by email can not and will not be acknowledged. "Mary O" <Mary O@discussions.microsoft.com> wrote in message new...

combine/merge 2 worksheet (tabs) into 1 work sheet
Hi, after reading countless forum discussions, i am still very confused for something that should be fairly simple! i have a workbook file with several work sheets (tab) that have data comparing two year revenue income. And now we need to compare them side by side vertically. I tried consolidation but it doesn't work. VB is too hard. I can't imagine doing cut and paste for 40 tabs. worksheet (tab1) shows year, number and ratio 2010 $8,782 811% 2009 $964 worksheet (tab2) shows 2010 $3,425 -33% 2008 $5,112 what i would like to see is ...

Exch 5.5 Information Store does not start. Please help
Hi, I ran an offline defrag on the priv.edb today. During the last two weeks the Priv.edb had grown to almost double its normal size, caused by a loop in one of the mailboxes. Yesterday we decided to activate circular logging on the Priv.edb to prevent the server from crashing as the disk ran completely out of space. At the end of this first workday of the year we decided to do the defrag. I had to locate the temp.edb on another machine because on the Exchange server was not enough space. The operation went without any warnings or error messages. After it completed I restarted the server, and...

How do to e-mail only one page of a workbook?
One page or one worksheet? Whatever the case see Ron de Bruin's site for methods. Also see his SendMail add-in which is very handy. http://www.rondebruin.nl/sendmail.htm Gord Dibben MS Excel MVP On Sun, 17 Jun 2007 17:38:01 -0700, william <william@discussions.microsoft.com> wrote: ...

How to show 5 data series on one chart
Hello, Here are the data that I have: - a category that will be my X axis - a percentage that will be my Y axis. I have a percentage value per category per year. - 3 company names - 2 years (2004 and 2009) What I want to show is the following: For each company, I would like to plot a solid colored line representing the different % for each category for 2004 and a dotted colored (same) line representing different % for each category for 2009. As a result I would get three sets of lines, three different colors, and within each set a solid and a dotted line. I currently have my table orga...

linking selected data between Excell sheets
Hi, I have 1 Excel file, 2 Sheets. Sheet A contains names, addresses, zip, etc and a column that has a value ("X"). Now I need to link some of the data that contains that "X" into sheet B. ... if there's an "X", then some of the info of that line should be found in sheet B. Q: how do I link only marked entries? Q: how do I link only specific cells? Thanx, Alex. my way I would introduce another column in sheet a that will show the data you want when the value X is present and link that column back into sheet b -- paul remove nospam for email addy! &q...

can i register a digit in excell sheet cell
how i can register a digit in microsoft excell sheet. example1 in column A i want register ( H) ,so now H will show all cell in A column.as show in Fig1. Fig1 column A 1 abc H 2 123 H 3 xyz H i am using Office 2000 his product key is BT8PM-KHK3J-TQW3Y-944V4-VD9X3 One guess as to what you're after .. (Try on a *spare* copy of your sheet) Assuming you have in A1:A3 : abc 123 xyz Put in B1: =A1&" H" Copy B1 ...