LinkBack URL
About LinkBacks
Help - intermediated level of skills - please no macros but need a way to transfer data from cells J9 through S23 to cells C23 to S47. I have manually shown some of the appropriate results. I have tried various "lookup" scenarios without success
Help - intermediated level of skills - please no macros but need a way to transfer data from cells J9 through S23 to cells C23 to S47. I have manually shown some of the appropriate results. I have tried various "lookup" scenarios without success
Problems:
1. When I open the file, I am told there are circular references (which I haven't tried to resolve).
2. The ranges . . . transfer data from cells J9 through S23 to cells C23 to S47 . . . overlap.
3. The ranges, as above, are not the same size. 150 vs 425 cells.
4. The range selection looks "odd".
Have you provided the correct file for the question?
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
I am attaching a new file which should eliminate any circular references and maybe the description of the problem is a little easier to understand. The info in Table One is just a series of how much each
item will cost at various points in time. Then Table Two is suppose to summarize those costs by year.
I have filled in values manually in Table Two as an example.
Many thanks for any help you can offer .....and please try to avoid macros if there is a lookup function that can work
Clear the target area. Then, in cell C29, use the formula:Formula:
Please Login or Register to view this content.
many thanks and how easy! I did it and worked perfectly so I went to the main document - where all cells in table one are formulas - then I did the formula you suggested for table two and got the following #SPILL! which I have not looked up yet
That just means that there is still some data, or formulae, or whatever, in the range the formula wants to write to.
Just make sure all the cells below and too the right are empty.
C29=IF($B29<>"",SUMPRODUCT(FILTER($E$10:$O$21,$E$9:$O$9=$G$9)*($B$10:$B$21=$B29)*(FILTER($E$10:$O$21,$E$9:$O$9=$F$9)=C$28)),"")
Copy across and down
Wow! I'm Impressed...it works great.....even on my big document!
Thank you very much. One last question which I'm sure is a piece of cake....How do I get rid of all the zeros that are placed where empty cells would look nicer?
I can't thank you enough
Stu W
Like this:
Formula:Please Login or Register to view this content.
for hide 0
custom format [=0]""
Thank you for the extra effort. I did run into some problems when I put the formula into my main document so I am sending the whole thing now. it's not much larger than the sample but I get confused with the syntax when I tried to expand the formula to the main document. I am uploading to my original post
Again many thanks for your assistance
The file in the OP looks to be the original with circular references.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks