I'm working on a budget file which has been revised so many times, that the original formulas are hard coded or had values pasted over them. What I need is to have the formulas working where I can just plug the numbers on details page in each line/columns and all the linked tabs would pull the info from the details tab(so the summary page would have the budget pulled by month by month)..attaching the file
Please see attached Budget file.
would greatly appreciate ALL THE HELP!!
assuming i read your sheet
you wanted to update FY13 budget into column Q
there are two cells which i columned in Orange in column O which i dont understand the purpose of it is
either fix the source for it or delete it and the error will go away
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.
Thank you so very much!!.........I just browsed quickly and all seems working YAY(will look in depth in the morning)...........................can you plz advise what was the issue of not pulling and how did you fix all the cell/formulas to pull the info from the details tab to the summary tab?
Once again, greatly appreciated all your help!!
there was no formulas in half the cells in column Q
so i copied the formulas from column N and updated formulas to be referring to sheet details but column S instead of N
this was done by using the replace feature (ctrl +H)
highlight area you want then replace !N with !S
Got it, it was my Bad! I didn't changed the column O in Summary tab to say FY 2015(since I'm working on 2015 budgets), it still shows FY 2014 and that is why you copied from S column from the details sheet when it should have been column P...I should have been much clearer I guess....so all I've do is change the S to P now right? so all actuals from FY 2014 will pull in summary page? and if yes then what would be my steps just follow what you advised above?"this was done by using the replace feature (ctrl +H) highlight area you want then replace !S with !P"?
yes
make sure you highlight the section you want to replace only
otherwise it might do the whole sheet by mistake
Done, highlighted the FY 2014 column Q and replaced !S with !P all looking GREAT......
Thx again and have a great day/night...if I need any more help I'll bother you again, hope you don't mind
sure, ill be subscribed to thread still
Hello, bothering you again I've couple of questions and wanted to attached the Rev.WIP Budget xls that I modified after your assistance and the original Budget xls so I can update the original/replace( I copied the whole sheet and tried to paste in the original file)with the modified sheets and when I did that the original sheet was poinitng to the Rev.WIP sheet which I don't want..I just want to able to replace the old non working formula in the original with what you modified. But in the reply section here, I don't see an option to attach files? if you can let know how I can attach files then will send both files to you with my queries.
Many thanks again!
to attach file go to advanced>manage attachments
screen will pop up
add files>select files
do this for all the files you want to attach and then press done
Thx attached both files for your review, the main Budget file is WIP-2015-Westshore xls(you'll see all other tabs too)...I want to just replace the Details and Summary tabs in the Original file with what you had modified in the Rev-WIP Budget xls which I updated later...
But before you do the above request, please modify the Rev WIP file under column R that you put Not Sure? that is supposed to be The 2013 actual from column AP..sowhat I need is column R to reflect 2013 actual from column AP, but not in the hard coded/pasted values way like I've...but with the formulas and all...and also please advise how did you do that? meaning changed the format and what I need to do to change it back to pasted values after I'm done with the real numbers...and alsoplease explain the process in columns V & W(guess so sorted all the G/L account codes?)
Look forward to learn more today
Did you get both the files?
Would greatly appreciate your help
that column R "not Sure reviewed" was put there in the original file i did not add that
i'm guessing whoever did that, put it in to cross check something...i don't know what unfortunately
to be honest i dont know what you are comparing in your sheet
your header in O = budget 12 mos FY2014
Q = Actual FY2013
and R is the difference between budget 14 and FY13 actual
anyway
i think i have done what you asked?
updated column Q to point to details column AP = FY2013 actual using that find and replace method i showed you previously
Sorry I may have confused, what I meant was pls modify/replace the column R content(which should be 2013 Actual) with column AP contents in the Rev-WIP-2015 Budget file ,but not in the hard coded/pasted values way like I've...but with the formulas and all...and also please advise how did you do that? meaning changed the format and what I need to do to change it back to pasted values after I'm done with the real numbers...and also please explain the process in columns V & W(guess youu sorted all the G/L account codes?)
And once you have modified the Rev-WIP-2015 Budget file with the above request, then I want the Details and summary sheets from Rev-WIP-2015 Budget file to be replaced in the other file name WIP-2015-Westshore xls(this is main budget file as you can see the other tabs with info and such) and Yes I can see why you would say this "to be honest i don't know what you are comparing in your sheet your header in O = budget 12 mos FY2014 Q = Actual FY2013 and R is the difference between budget 14 and FY13 actual" and once you do the above you'll see it all makes sense....I hope if I didn't mess it up again lol........................FYI summary sheet should only have budget for 2015 comparing with the FY 2014 actual"
columns U, V and W are also not mine
i dont know what check its doing
i did not touch anything except for summary column Q
your REV-WIP file has the correct formula's in there?
column Q is referring to column P in details (which is your 2014 actual)
Yes on all counts, so my question now is how can I move/copy the detail and summary sheets from Rev-WIP file which has the correct formulas to the other Budget file without it referencing/pointing to the Rev-WIP file?
the summary formulas are correct on the Main file
wouldnt you just copy and paste the data details sheet on REV-WIP to WIP-Westshore file?
would be a direct copy and paste as the sheet layout is exactly the same
Last edited by humdingaling; 12-01-2014 at 10:47 PM.
hmmm, my msg didn't go thru.....YES to all counts, so now how can I move/replace the summary and details sheet from Rev-WIP file to the other WIP-2015 file without it's referencing to the Rev-WIP file?
Got it, guess there was a delay....Thx and I'll try to just copy and paste...................and also how can I change the format of a column i.e column P Q R S where all the values are pasted over as hard coded values back to normal format where I can just type a number and value shows up and adds down?
you would need to manually fix those formulas
i went and did most of the ones i could find but you best run your eye thru them to see if they are all aligned
a quick way to check the formulas is using the
Ctrl+` Toggle Value/Formula display
Thank you so very much!
I need you help again....Please see attached xls. I need to filter the 2015 tab as 2014 tab, and appreciate the steps on how you did it...
Many thanks in advanc
Highlight data area, in your example it would be A1:N2086
un-merge cells
click on custom Sort
click on use my data headers and sort by Extended comments (should be column H)
you will be left with a whole lot of "Total For" rows at the bottom, you can delete those
Column C,D, I,J,K,H can all be deleted (they are all blanks)
now reselect all your data (should be A1:H1669 now)
and click on subtotal
choose following options
at each change in: >>select extended comments
use function>>sum
add subtotal to >>check transaction amount (no other options should be checked)
leave default yes to
replace current subtotal and summary below data
end result should be like attached sheet
i left it at this point...if you want the formulas and groupings gone you can select all copy and paste values and just manually ungroup
Appreciated as always!!
not a problem
Hello There!, need some help...attaching two A/R files....I want to replicate the 2013 A/R collection analysis report and make a new for and with the 12-31-14 aging data................may be you can get rid of the zero balances(or just make two tabs one with and one without zeros)...The logic in column N need to be applied..and color coded by member ship types....and also sort all columns so the allowance stay with the correct line if the report gets sorted....hope it makes sense..you can use your best judgment if it didn't made sense................
Many thanks in advance.
had a quick look
seems like there is data missing? Column J ?
like are you meant to be data entry something else in ?
also besides color...how can you tell which person has what type of ownership?
I added the missing columns/data and everything is there now...and I also added a new TAB for color code for each membership types and color coded each membership in column D....but left the main TAB uncolored...as I just want Allowance column N in main TAB to show the color with the logic formula...like the 2013 worksheet.
Hope this helps and also I didn't do the calculation at bottom...
Many thanks in advance.
you really should have a field for type of membership rather than using color
color does not lend itself to be manipulated easily
even in your sheet you had two different shades of blue so it didnt match up properly
here is the solution with member type in an actual column
Solution using color for membership....however it requires UDF (user defined field) to obtain the background color
i really don't recommend it
Sorry for the confusion, I added the member type column and also color coded it, can you please just replicate the column N as it is in 2013 A/R worksheet would greatly appreciated.......and also can you add the sort feature in each column as it is in 2013 worksheet so the allowance stay with the correct line if the report gets sorted and resorted?
As always, many thanks in advance.
i did it in my first reply
look at that file and see if it does what you want
Formula in column N
Formula:
=IF(L2>LOOKUP(M2,$R$1:$R$5,$O$1:$O$5),L2-LOOKUP(M2,$R$1:$R$5,$O$1:$O$5)*LOOKUP(M2,$R$1:$R$5,$P$1:$P$5),L2*LOOKUP(M2,$R$1:$R$5,$Q$1:$Q$5))
Yes you did, then I deleted the extra tab and added the member type changed some coloring and resent, but the Logic isn't doing what it does in column N in 2013 worksheet...for example if you look in 2013 file for member B2000 Brain Hameroff's allowance comes to $2,294.22 and in your file it comes to $15,892.78 and also can you please add the sort feature after you fix the logic in 2014 file so I've exactly the same sorting in each column so the allowance stay with the correct line/member type if the report gets sorted and resorted?
for some reason the formula didnt like Corporate or Marina
so i changed the formula
Formula:=IF(L2>INDEX($O$2:$O$5,MATCH(M2,$R$2:$R$5,0)),(L2-INDEX($O$2:$O$5,MATCH(M2,$R$2:$R$5,0)))*INDEX($P$2:$P$5,MATCH(M2,$R$2:$R$5,0)),L2*INDEX($Q$2:$Q$5,MATCH(M2,$R$2:$R$5,0)))
i missed a bracket
try this
if you enter in the same figures for Brian now it gives the same result
=IF(L2>LOOKUP(M2,$R$1:$R$5,$O$1:$O$5),(L2-LOOKUP(M2,$R$1:$R$5,$O$1:$O$5))*LOOKUP(M2,$R$1:$R$5,$P$1:$P$5),L2*LOOKUP(M2,$R$1:$R$5,$Q$1:$Q$5))
not exactly sure what sorting you want
can you rephrase ?
Thx and what I meant by sort feature ...was if you see the 2013 file...all the columns has the sorting feature and I wanted the 2014 file to do the same as well as if we sort by color code or by member type the allowance stays with that line,,,,i,e, if I were to sort all the owner then B2000 Hameroff balance of $$$ should stays with that line....hope I made sense lol
And also do you have any comparison charts/dash boards? as I want to compare three years of data...it could be revenue or expenses with trend lines.......so I've 2013, 2014 and 2015 data...I want to compare in charts.....jan to dec for all months for these three years,,,,and also by quarters in another charts with trend line.......if you have any dashboard like that or just good charts for comparing please send....would greatly appreciated.
is this what you mean by sort?
RE: Dashboarding
you can put the data into a pivot table and use pivot chart and slicers to achieve what you are asking
google dashboard pivot table slicer and there should be some videos on how to do it
its important you understand how to do it from scratch rather than have something ready built so you can maintain/change/edit it ongoing
so have a go at it and if you have certain hiccups i can help trouble shoot you thru it
Yes That's it and I'll create dashboard then will if I get stuck will touch base with yaa...Thx again.....and btw the 2013 file has a smaller formula than yours......can you explain me the difference and what your logic is doing vs the 2013 logic?
ok for your reference that is called Filter not sorting...i added subtotal down the bottom instead of sum so it calculates based on what is in the filter and not just all the data
breaking down the formula the principle is exactly the same
the formula i put has to search for what member is then calculate based on that
2013 is smaller because its exactly the member...ie it doesnt have to spend time looking it up therefore is shorter
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks