making cells in tab2 = cells in tab1 in same workbook

My workbook has 4 Tabs PCM Data, Analytical Data, QA-QC, & View Report. I am trying to get 2 cells in View Report = 2 cells in QA-QC. I am trying to get the date(B10) in tab View Report = Date in tab QA-QC(D9), I tried: ='QA-QC'!D9 That didn't work. I am also trying to have tab View Report cell Analyst(F10) = tab QA-QC cell =K9:M9 I tried ='QA-QC'!K9:M9 and I am get an error: #value! What do I need to do to make this work? Also If there is no date entered in tab QA-QC I want the date cell in tab View Report to be blank. the password for the attachment is 12345.Please feel free to look at the file.

Hello, Jimb01. Welcome to forum!

Please attach a dummy file with your expected results. So the members can see your data structure. Scroll down click Go Advanced then down click Manage Attachments, upload your dummy workbook

Hello,

the formula in View Report in cell B10 works correctly. The problem is that the cell in sheet QA-QC is blank. If you reference an empty cell with the syntax =Sheet1!A1, then that cell will show a zero. A zero formatted as a date will look like 0-Jan-1900.

You can change the date format of that cell this way: click the cell, open the format dialog, click "Custom" and in the "Type field" after the yyyy enter two semicolons ;;

I assume that your date format is mm/d/yyyy, so the Type field should look like this

mm/dd/yyyy;;

This will suppress zero values from being displayed.

As to the second issue: K9:M9 is a reference to a merged cell. You can simply change the formula to

='QA-QC'!K9

In order to suppress the 0 when no name has been selected in the QA sheet, you can use the custom format

;;;@

Thank yoy that worked

Now I have a Major issue. By law we are required to show the asbestos fibers found. So, if we find 5 1/2, we must show it as 5.5 which I am in tab QA-QC cell B17 for test #1 and G17 for test #2. The fibers are the divided by the Fields in this case 100. We must show it as 5.5/100 in cell C21 for test #1 and C22 for test #2, but it is rounding up to 6. We must do it as and show it as 5.5/100. That infomation needs to be shown in tab "View report as well. I have added a new copy of the file with the fixes you suggested and I entered 5.5 for test 1 and 7.5 for test 2 so you can see what I mean. Once again to unprotect the sheet and Share the workbook is 12345.

Thank you so much. I am learning alot. You are great!!!

Please do not quote whole posts. Use the Quick Reply box or the Post Reply button, NOT the Quote button to post an answer.

The sheet is protected and the file is shared. I cannot unprotect the shared workbook, because it has a password. If you need help with this, please upload a copy that can actually be used.

By the way, sharing a workbook will send it on a path to failure, data loss, corruption and frustration.

And finally, please do not use Visitor Messaging to ask questions or draw attention to your question. This creates noise and clutter that nobody is keen on.

Hello,

the cell delivers the result of a calculation in P2 and is formatted to show the resulting number as a fraction. Unfortunately, you cannot show a fraction like 5.5/100. You can change the format for the fraction to show more digits, so the number displays like 11/200.

Or, since the cell does not have any dependents, you could use a formula like

=B17&"/"&E17

This will simply concatenate the value in B17 and the value in E17, with a / sign between the values. The result will be text, not a number.

cheers,

Use the same techniques as described above for the QC analyst cell. Again, these are merged cells and you only need to refer to the top left cell of a merged range. Also, to suppress zeros, use one of the custom formats described above.

cheers,