Values to be static once formula retrieved using a formula

[laansesu]

Dear All,

I want to know whether it is possible to make a result static once formula has retrieved using a formula

Ex: I've a sheet where I keep on enter the values everyday & want to retrieve these values based on date. I used below formula to retrieve the data from one sheet to another

=IF(B$4='Rej Rep'!$E$3,VLOOKUP($A5,'Rej Rep'!$B$8:$C$30,2,0),0)

Rej Rep is Sheet1; B$4 has date 01/09/2015; 'Rej Rep'!$E$3 has date 01/09/2015, then it retrieves the value using VLOOKUP formula else it will be zero

So if I use if/nested-if it will make value zero in case date of 2 sheets doesnt match.

Please let me know a way to do this with only formulas.

[shukla.ankur281190]

Please Login or Register  to view this content.

May be this will work?

[laansesu]

Thanks for your reply.

Please find the attached file in which 1st sheet for entering the values and next sheets to retrieve the values based on our formula.

Rej Rep - E3 will have a date based on which I want to populate the result in all the sheets.

My concerns in this case are:

1. My task should be so simple that I want to enter the values in a single sheet and result should be distributed in other sheets

2. I even dont want to work on the other sheets once I enter the values in the first sheet

3. Result that has been displayed in the other sheets should be static once it has been retrieved using our formula

4. I want to know the way to do this only with formulas rather than VB Code

[shukla.ankur281190]

Hey I looked at you file but it seems that every sheet (GF,FF,PC GF and all that) need to different lookup table array. You can simply change them according your need. How ever above formula proved for GF sheet only and it will work in only GF sheet. If I will change =IFERROR(VLOOKUP($A5,IF(B$4='Rej Rep'!$E$3,'Rej Rep'!$B$8:$C$30),2,0),"") this formula in to =IF(B$4='Rej Rep'!$E$3,VLOOKUP($A5,'Rej Rep'!$E$8:$F$30,2,0),0) formula it will work for FF sheet.

Take a look in both formula the all syntax are same except 'Rej Rep'!$B$8:$C$30 & Rej Rep'!$E$8:$F$30.

Hope you will have now understand what i want to explain. However If still doubt then google for Vlookup formula basic

[laansesu]

Thanks for your reply once again. But my basic problem is that I want to make result static once the data is retrieved from 1st sheet based on our formula

Vlookup is not my problem, I even can retrieve the data using Index also, but I want to make result static with the formula & date that I'm inputting.

Is that possible? Please let me know whether this can be done with formula or can go with only VB.

[humdingaling]

Is that possible? Please let me know.

No it is not without VBA

formula by definition is meant to change according to the inputs
it wont know when something has been retrieved and it certainly wont know when it needs to stop updating

[shukla.ankur281190]

Yes it can't possible without VBA. Formals has their own limitation dear but thru VBA it will force to excel to do the things according to VBA code designer...

[laansesu]

Its very sad that Microsoft excel is not upto my standard!!!!!

Why I'm keep on asking this question is that I'm familiar with most of the functions but not with VBA. Someone can give a solution with VBA but if I need extended support I wish not to come here for asking this again and again.

[humdingaling]

if you indeed dont want formulas to update turn off calculation
you cant have it both ways where formulas update and formulas dont update
how would a program cognitively know you want something to update unless you instruct it to do so

your standard requires reasoning & comprehension
if Excel could do the that then i dare say there would be many people out of jobs

also if you want vba you need to be much more specific on exactly what you want