How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Jason Jones Your Own Question
Jason Jones
Jason Jones, Computer Technician
Category: Microsoft Office
Satisfied Customers: 16661
Experience:  Over 10 years work experience.
30759776
Type Your Microsoft Office Question Here...
Jason Jones is online now
A new question is answered every 9 seconds

I am trying to make a group of merged cell on one sheet equal

This answer was rated:

I am trying to make a group of merged cell on one sheet equal another group of merged cells on another sheet, this is a very simple formatting formula I have done heaps of times and always works but not this time.

The sheet has a macro which I have trned off and it still happens and I have tried from a second sheet to another and it works but not from the sheet I am wanting

I also have another spread sheet which a John B for J Mar worked on both of these which has an update which has stopped working.

I require assistance with both of these please

Hello and thank you for your question today. My name is Jason. I look forward to helping you today.

May I take a look at this document?

Please, be forewarned that the site is not secure:
- Click the following link: http://www.wikisend.com
- Upload the file to that website
- Once it is uploaded, the resulting page will display a "File ID" number.
- Please, give me that "File ID Number"
Customer: replied 4 years ago.

Hi Jason,


 


File ID is File ID: 344228


 


the issue is I am trying to have the menu items on the menu compendium sheet shaded in green to a column in the COGS page with corresponding unit price and cost as Identify it . Its not working though, not sure why as I can make merged cells on the compendium page equal merged cells on the same page and can make single line merged cells to the COGS page?


 


Also you will notice that I have it working from COGS to sheet 2?? but not from Compendium to Cogs??


 


On file ID 295864 I have an issue with the update starting Staff cost button on top left of the P&L page. how this works is that the roster is written at the beginning of the week it populates on the evening cash up sheets next to a cell called Budget under Staff at the bottom. at the end of the week or when the day is finished you should be able to update the P&L page and it should update, but down't could you fix this please and have a look at any other issue or improvements you think could work


 


Pass word is great91


 


 

On the 344228 file, I see what you are trying to do.

For example, instead of using the formula ='Menu Compendium'!B224:M224 in B7, it would be best to use the following formula instead because they are already merged.
='Menu Compendium'!B224

If you wish to set this up the way that I think you are describing, then you will first have to merge the cells on the 2nd sheet before you setup your pointers. Essentially, you would be setting up a type of template on the other sheet.

Please, let me know if that does or does not make sense.


Customer: replied 4 years ago.

Yes makes sence however I have tryed this and i the cogs to Sheet 2 example it dosn't matter whether you merges cells it still equals the cell you are pointing??


 


Try it you will see that in the Compendium to Cogs the #VALUE! error comes up

I see that you rated me instead of continuing. Are finished and do you wish for me to exit the question?

Was that a mistake?
Customer: replied 4 years ago.

No have already sent this, however you test the issue??


 


Yes makes sence however I have tryed this and i the cogs to Sheet 2 example it dosn't matter whether you merges cells it still equals the cell you are pointing??


 


 


 


Try it you will see that in the Compendium to Cogs the #VALUE! error comes up



I see the #value! you mentioned. That is because you did the same thing that I mentioned earlier.

You used this formula: ='Menu Compendium'!V8:AL9 instead of the correct formula which would be:

='Menu Compendium'!V8
Customer: replied 4 years ago.

The target cell is a merged cell and when I type = then choose the cell it chooses the entire group, as it does when you do the same on the same sheet, you will notice that the green cells are copied down the sheet poulating the various menus.


 


This also dosn't happen for the cogs to sheet 2 for grouped cells??


 


Yes.

But that is not the correct way to do it.

When you merge the cells, it merges the cells.

IE:
If A1 to C3 is merged, then the pointer would be =A1 and NOT A1:C3

That is the problem.

It looks like you are doing this backwards in a way. The sheet that you pull the values from should not have any merged cells for this and other reasons. The back-end sheet should just have values.

Customer: replied 4 years ago.

I don;t understand?


 


The sheet I pull values from (Compendium) is made up of merged cells?


 


So why does it work fro Cogs to sheet 2?


 


I have done this a thousand times and this is the first time this has happened

It works in Sheet 2 because you merged those cells before you put the formula in it.

Customer: replied 4 years ago.

I just merged some cell on cogs and made it equal merged cells on compendium error occurs?


 


Have you tryed this?


 


 


I have tried this thousands of times and I just did it again with your worksheet. It worked on your worksheet.

It will ONLY work if you use the following formula: ='Menu Compendium'!V8

If you use this formula, it will not work: ='Menu Compendium'!V8:AL9

Customer: replied 4 years ago.

so why when


 


I do the same from sheet 2 to cogs and it works, but from Sheet 2 to compenduim it dosn;t?



Thank you for the update.

May I please, start a remote screen sharing session with you so that I can better help you resolve this problem?

Customer: replied 4 years ago.

yes please, as this dosn't make sense to me?


 


 



Thank you.

AFTER YOU CLICK THE LINK BELOW, you can continue.

Please, click the following link and allow the remote app to run: http://166.78.231.170:8040/?Session=c1afe5b4-963e-4321-9ebe-3cc6fae363ff


Then on that page click the button that says, "Join Session".

Please, click the following link and allow the remote app to run: http://166.78.231.170:8040/?Session=c1afe5b4-963e-4321-9ebe-3cc6fae363ff



Are you having any problems with running that remote application after it is downloaded?

Which Internet browser are you currently using (Internet Explorer, Firefox, Safari, or something of the like)?

Customer: replied 4 years ago.

Safari



All is now working.

If you ever wish to work with me in the future, you can ask your new questions and have them sent to me directly at the following page: http://www.justanswer.com/computer/expert-computertechmaster/

Thank you for your time and please, do not forget to rate my help below.

- Jason
Jason Jones and 4 other Microsoft Office Specialists are ready to help you