• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

If and Vlookup formula

Hello,



Would you please help me to write formulas in columns E,F, H, I, rows 4:15.

ROWS C & D are empty.

In E and F numbers will be coming from the Actual table results by months From January to December; and in H, I- from targets.

Numbers should be changing in E,F,H, I after selection of the month in cell E3 and type of product in H1.

The tables of the actual and targets data have the same format as this summary table from A to E. After E (January) from F to P (February and December).

Thank you.

I wanted to upload a file, but it does not have an option for Excel...

Overall Types



Type 1



B

C

D

Actual

Target

Variance

Actual

Target

Variance

Jan-15

Jan-15

Jan-15

Jan-15

Jan-15

Jan-15

P1 - New Patients
P2 - 2nd Month Patients
P3 - 3rd Month Patients
P4 - Patients > 3 Months
P1 - New Patients' Billings
P1 - Billings per Patient
P2 - 2nd Month Patients' Billings
P2 - Billings per Patient
P3 - 3rd Month Patients' Billings
P3 - Billings per Patient
P4 - Patients > 3 Months Billings
P4 - Billings per Patient
 
Last edited by a moderator:
Excel files can ofcourse be uploaded. After all it's an excel forum. Would be criminal to have an upload file option here without accepting excel files :).
 
I'm not 100% sure I can solve your problem so might be better if you can create a sample file or change up the confidential data. That way everyone can check it and help solve the problem.
 
I think I have a general idea of what all is in your spreadsheet but your initial description and the spreadsheet aren't quite matching up.

Can you re-state exactly where you are having problems and need help with?

Also, does your monthly data come in 1 sheet each or did you put it that way?

Most of what you need seems like it will be very easily possible using pivot tables and/or using getpivotdata formula instead of using all the formulas you have used, if all of the data was just available in 1 sheet/table.

I generally try and avoid formulas unless it is needed for some business logic.
 
Last edited:
The task- to populate data into the "variance analysis actual vs target" report by the use of formulas from two tables above and the from tables in sheet "targets". Pivot table can work only for target tables.

I thought that it should be some kind of if vlookup formulas..
 
If you have a single table with all the monthly actual data and then add all the targets also into the same table (matching up appropriate columns) and just add a extra column that has value "Actuals" for Actuals and "Target" for Target then pretty pretty much all the the things you've done should be possible to do using just pivot tables.

The key really is proper data preparation/setup. Once data is setup correctly, everything else is easy to do and maintain too.

There is the addin from Microsoft called Power Query which can help with the data preparation part in case you don't have the data in the format I have specified.

If you are having trouble understanding whatever I've stated above, I'll try to send in a sample as soon as I can.
 
If you an urgent need then do state exactly what part you are having trouble with. We could solve that part for now and then I can work on what I was saying as that will take some time but should be a better/cleaner solution.
 
You asked to send you a file to understand better what I need, and it looks like it did not help.
It is simple what I need, like I wrote several times- I need to populate a table with data from 2 tables as actuals into cells E44:55(from E7:p19) and H44:55(from E25:p37). Second table E7:p19 is a dynamic table in a sense that data is being changed by selection a type of a Prpduct(GFAP) in cell H22 by a drop-down arrow.

The target data should be populated into cells F44:55 and I44:55 from the 14 data target tables in a Sheet "Target".

In order to understand it better, you need to look at those reports.
Thank you.
 
You've stated your requirement, which is wonderful as it's always good to know what you really want. What you did not state though is which specific part you are facing a problem. You pointed it out in your initial post but those cell references did not match up with the file you sent and that was why I asked you to re-state the problem area.

Anyway, don't need any more information now. Will send you something as soon as I'm done with it.
 
HI SM,

There is an error in the formula in I44:I55 after each change of the product in cell H22 by an arrow down.
Can you look please?
Thank you.
 
Hi SM,

Please disregard my previous e-mail. I found a problem. It was not a formula error, it was a problem with Target template.

I highly appreciate your very professional and efficient help!!!!

Would you please explain me how this formula works?
.=INDEX(OFFSET(Target!$F$22,MATCH(Analysis!$H$41,Target!$B$22:$B$229,0)-1,,12,12),MATCH(Analysis!$B44,Target!$C$22:$C$33,0),MATCH(Analysis!I$43,Target!$F$21:$Q$21,0))
Thank you very much-:)
 
Back
Top