Can I auto-populate from a merged cell?
Hello all!
First time on the forum and looking forward to getting connected. I'm trying to populate cells from one worksheet into another sheet in the same workbook. Problem may be that they are merged cells, since I've had no trouble doing it in other cells. Is this even doable?
Hope the question makes some sense. Appreciate any help out there.
Bruce
Hi, welcome to the forum
1st, we all pretty much try and advise against using merged cells whenever possible, they cause all sorts of problems with formulas.
2nd keep in mind, that in any range of cells that are merged, ONLY the top-left most cell actually contains data, all the others are empty. So in a merged range A1:J1, only A1 will ever contain data.
Having said that, what exactly are you trying to do?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Thanks for the quick reply, Ford. The document I'm working on comes from a client with the basic structure they're looking for and I'm adding the calculations, some buttons to link to various sheets, and then making it pretty for them. The structure they provided has an Executive Summary page at the front, which will largely populate from the other sheets. That's where the problem comes in since they set it up using quite a few merged cells, both on the content sheets and the Executive Summary. No problem getting the calculations to copy over, it's those darn merged cells!
And this is why we almost always advocate not using them lol, but reality is what counts.
We will need more info to see if we can come up with any suggestions
The document is attached. The Executive Summary tab explains pretty clearly what they want to accomplish. I may just need to rework it and figure out another way to get it done.
So on Cust Demo, you will enter the Cust details? e10:e13
And you want that referenced in Exec Summary E10:E13?
Looks like you were almost there, you fell prey to what I said about only the left-most cell containing data...
='2. Customer Demographics'!E10
It's interesting you point that out, because that one worked even with merged cells. It's the ones coming from other sheets like Customer Industry that are merged across multiple rows that seem to be my problem.
OK I think you got me going the right direction though and I changed it to reference just the one cell, not the range, and it worked. When the source cell is blank, the Exec Summary displays zero though, which I don't love but probably can live with. Perhaps an IF function would fix that but I keep getting error message when I try that. Better work on this tomorrow with a fresh brain.
Really appreciate your help tonight, Ford!
Bruce
Last edited by bruce_a; 08-21-2016 at 01:02 AM.
Try this:
1- Un-merge cells on "1. Executive Summary!B22:O29"
2- Use =CONCATENATE('3. Customer Industry'!B52:O63) in the first cell "1. Executive Summary!B22"
press control+shift+enter to enter the formula
3- Merge again the cells on "1. Executive Summary!B22:O29"
=if(your-formula="","",your-formula)=COUNTIFS(Statistics!$B:$B,"*"&D$2&"*",Statistics!$C:$C,">="&$C3,Statistics!$C:$C,"<"&EDATE($C3,1))
Not seen the data, but as already said advice yor client NOT to work with merged cells. Have you considered adding aĺl data on 1 sheet and then use a pivot table to analyse the data.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Getting back to it again today and will try the suggestions. Really appreciate the assist! I'll update later on.
forilohud, that seems to be working for me. Really appreciate the suggestion. That's a new tool for me! Thanks!
Coming back to this thread with an additional question if anyone can help. I'm using the =concatenate function as noted above and the calculation works fine. Problem is I'm trying to format the cell where the =concatenate is as currency but it will not reflect that change and even displays as such in the formatting dialog box. Any suggestions for formatting this or some type or workaround?
Thanks much!
Bruce
Concatenate always produces a text answer, even if it looks like a number/currency - and no amount of formatting will change that. A way around that is to use the TEXT function to "force" formatting, something like
TEXT(K7,"$0")
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks