I'm looking for an efficient way to copy an entire table of figures that contain the INDIRECT function formulae to refer to groups of markets from the second sheet in the workbook.
I'm wondering if it's possible to use the fill handle to copy the table down to create each month for a year.
I have attached an example of how I've done it thus far.
Any help would be appreciated.
how are you grouping regions?
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
also what purpose does
ROWS(B$1:B1)-1)*14 serve?
it gives you zero all the time?
oh,...that's meant to increment your table to next month.... but you have different region groups in there....?
back to original question in post #2
Last edited by humdingaling; 08-04-2014 at 01:38 AM.
Please find the attached sheet to see of this works as per your requirement. I have made a small change in the sheet layout. See if this suits you.
The formula used in the attached sheet is as below......
In C5and then copy across and down.
Please Login or Register to view this content.
Last edited by sktneer; 08-04-2014 at 03:25 AM.
Regards
sktneer
Treat people the way you want to be treated. Talk to people the way you want to be talked to.
Respect is earned NOT given.
Hi,
@humdingaling - On my example sheet, I put a comment in the Market Regions cells to indicate which exact markets they refer to.
@sktneer - WoW! That's some string!! Thanks, it seems to do the trick perfectly, although I have to admit I don't understand the exact workings behind it.
Ahead of seeing your reply, I actually completed it by using your INDIRECT formula as per the example sheet, selecting the whole table (for Jan for example) and used the fill handle to copy down. I then just used the find and replace tool correct the row references, and that did the job.
Thank you both for your feedback.
Owen
Glad your issue has been resolved.
I think offset was the better choice rather than indirect so I changed the formula and made some changes in the sheet layout. The plus point regarding this is that you have only one formula for the whole sheet which takes care of every row and column and the data will be auto populated once it is entered in the master sheet.
The formula is a bit lengthy but if you pay some attention you will come to know what is it doing exactly and when you have complex lookup like this and you need only one formula, you have to compromise somewhere.
Anyways if that takes care of your question, please mark your thread as Solved. (You know how to do that.)
Hi again sktneer,
I can definitely see the advantages of your suggestion, and yes, I will take the time to figure it out. I was simply under time constraints yesterday so I had to get it done somehow!
Owen
on your example you had region 1-2-3 on jan and region 4-5-6 on feb
hence the question of grouping
if they were the same thing you weren't far off
Formula:
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks