I have a Workbook with two sections of data. The sections called Pay takes the data from the corresponding section called Utilities. So that Pay 1 takes from Utilities 1 and Pay 2 takes from Utilities 2 and so forth. I have only computed the formula from Utitlies 1 into Pay 1, but would like a smart way to do the same for all of the other sections, without doing it manually. I can't just drag the code, as the code would then be shifted.
Thanks in advance!
Last edited by Allerdrengen; 02-16-2018 at 09:41 AM.
Sorry, I have no idea how what you are asking relates to the data you've provided. You are going to need to give more detail.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Agreed.... an inadequate description. But, always game for a wild guess....
=IF(LEFT(AD$1,3)="pay","",OFFSET($B2,,3*(COLUMNS($AD:AD)-1)-4*INT((COLUMNS($AD:AD)-1)/4),,))
in AD2, copied across and down. Also, being in DK, you will probably need ; instead of ,as the separator.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Sorry for the bad explanation and thanks for helping. I have uploaded a more detailed Test WORKBOOK.
The first section of data stops at column BT. I have computed the code from column BU to column CL (representing January).
I'd like a clever way to do the same for the next months, and get the corresponding data, so that UID-February (columns CM-DD) gets its data from Utilities February 2018 section. Hope it clears it up!
Last edited by Allerdrengen; 02-16-2018 at 08:09 AM.
Just select everything from BT onwards that relates to January and copy paste to the February section - the cell references will change to suit. If this isn't what you mean, I'm stumped!
Why does your data need to be laid out in this way? Perhaps if you explain your ultimate aim, we can guide you to a better solution. And the colours need to go, or at least be toned down.
Nope. No idea... If you don't make the effort to show us what you want, we won't have clue what you want. There is virtually NOTHING in BU to CL and no indication of what's supposed to happen thereafter.
I'll play guessing games once, but not endlessly!!!
I do not mean this either unkindly or sarcastically, but please remember that we are not mind-readers!
There should be formulas in some of the columns from BU to CL, I have no clue why it doesn't show with you. I'll try looking into it.
There are. Did you see post #5?
That's not the problem. The problem is your (sorry!) very poor attempt at telling and showing us what you want.
Unfortunately this doesn't work. Ill upload another file, with the codes for February so you can see what i mean.
The layout is not my choice and must be this way, unfortunately. Sorry again.
That was for Glenn Kennedys comment
Alright. So I've deleted some of the columns to make it simpler. Between the columns with formulas are some blank columns, but thats just to show how the original file is looking.
I have now put formulas in UID-January (BU-CB) and UID-February (CC-CJ). They both get their values from their respective cells in section "Utilities January 2018 (C-P)" and "Utilities February 2018 (Q-AD)".
Example for January: Column BY is getting its values from Column L.
Example for February: Column CG is getting its values from Column Z.
So for March: Column CO should get its values from Column AN.
This should be the same for the other Columns in the following months, with their respective formulas. I hope you get the pattern.
If I just click and drag the cells, the formulas get shifted.
Thanks!
And the point is of course, a smart way of following this pattern for the rest of the months, instead of putting it in manually.
Don't click and drag - copy and paste, as I advised in post #5.
If I copy and paste the cells are still shifted.
OK - so tell us what goes wrong. How about creating a cut-down version of the workbook with just enough data in it to prove that copy and paste isn't working and to show us why.
I have made another guess.
Allerdrengen. It would have helped if you had commented on the possible solution that I offered waaay back at Post 3... It would have given me some indication that I was/was not on the right track.
I have assumed that what you want are columns BY, CG, CO, etc, populated with the data from columns L, Z, AN, etc.
This formula, in BY3, copied down to BY 20 does that. Then select all of the cells in the range BY3:BY20. Copy. Paste into CG, CO, etc.
Yes sorry for that.
And thank you very much! I think thats the solution I was looking for. Can the same formula be used for referencing more than one cells? Ex. if the reference looks like this "=D3+E3+F3+G3+H3", just like in Cell CA3 from my example Workbook.
Here's another version. Formulae in BY, CA & CB. Copy BY3 to CB20. Paste into CG3, CO3, etc....
Thank you very much, Ill play around with it!
You're welcome.. If you hit a snag, shout. Just make sure that your expected answers are shown, clearly unambiguously!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks