Inserting formula into multiple cells already containing formula

Question

Hello,

I have been given a access database with several excel workbooks pulling data from it and each other and making calculations based upon that data and it's results and asked to transfer it to SQL Server due to size restraints.

PowerPivot has been identified as the way forward for future calculations and data entries, but I have been tasked with recreating the current excel spreadsheets pulling the data with PowerPivot.

PowerPivot in this instance will not be used how it was intended to. I have to recreate the same columns from the database and further columns and sheets in excel which make calculations upon them. Additional workbooks will then pull information from this base one.

The database in SQL has been completed fine, and getting the data to export into excel via powerpivot can be achieved but simple calculations in the additional columns required is where I have run into a wall. I have managed to get cells which need to have a ranged sum e.g. =SUM(C2:F2) as these cells fall under Values.

But I am having trouble recreating VLOOKUP functions or calculations which reference Date columns for instance.

The only way round this I have found so far is to convert my powerpivot table into formula. I can then reference the cell by adding "=Value" to turn the CUBEMEMBER(...) to =VALUE(CUBEMEMBER(.....))

My problem is I have thousands of rows which I have to apply =Value to the start of it. Does anyone know how to add the Value formula to the already existing CUBEMEMBER formula across multiple cells?

Also if more data is added to Powerpivot, will I be able to automatically apply Value to the start of new cubemember cells.

I have tried to use a find and replace but came to no solution. e.g. find all CUBEMEMBER and Replace with VALUE(CUBMEMBER

This just brought back an error about not closing the parentheses.

Answer 1

Not really sure of the scenario which you posted, but can you check whether the links below would help?

http://vlookupweek.wordpress.com/tag/vlookup-and-powerpivot/

http://javierguillen.wordpress.com/2012/02/10/simulating-an-approximate-match-vlookup-in-powerpivot/

If not, could you simulate the issue with an example here?

---

Cheers,
Jason
P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

Answer 2

These did help, but we have been instructed to drop PowerPivot for now until we get all the spreadsheets and current data successfully transferred into SQL Server and displaying correctly. A solution is now being created using Sharepoint. After this it seems I will be put back onto Powerpivot.

I did know that I wasn't using PowerPivot for it's intended purpose and thankfully we are now approaching the problem in a much more logical way.

Sorry if i wasted any time.