PDA

View Full Version : [SOLVED] Transposing formulas



Mchaudhary
10-13-2013, 05:01 PM
Hi everybody,
Any help is appreciate. Please suggest me a logic to create a macro. I have a sheet containing some financial data. Data is organized. Sheet has various formulas. Some formulas refers to upper or below rows and some others refer to left/right columns. I have to organize this sheet’ data from horizontal to vertical.

For example, I need the brokers on first column, only the dates will be horizontal.

I am transposing data but I get #Ref error in some rows. I need all the formulas on a sheet referring to the correct data cells.
How should I transpose all the data so that formulas’ reference will change according to correct transposed data cells ?

Thank you very much.
Manoj

Mchaudhary
10-14-2013, 05:12 AM
Hi everyone,

I think this is the best site to get solutions on VBA, so requested for a help. Is no one here to help me? At least give me a reply, please.

thank you,
Manoj

Aflatoon
10-14-2013, 10:50 PM
I suggest you post a workbook rather than a picture, so people can see what they're actually dealing with, and test solutions.

Mchaudhary
10-15-2013, 01:14 AM
Hi Aflatoon,

Pls find the attached sample. I need all the brokers on row no. 3 ( i.e. Brk1, Brk2, Dlr1 etc. ) should come before every line item ( i.e. india sales , south asia sales etc.). I need all the formulas on the sheet.

thank you very much for your help.

Regards,
Manoj

Aflatoon
10-15-2013, 01:35 AM
Please include an example sheet showing what you expect the result to be. I cannot tell from your description.

Mchaudhary
10-15-2013, 01:58 AM
Hi,

Please find the attachment as a result for two line items.

thank u very much,

Manoj

Aflatoon
10-15-2013, 02:19 AM
Do you actually need formulas or can the data just be rearranged in code?

Mchaudhary
10-15-2013, 09:03 AM
Sir, we need the formulas on sheet. This is the main problem.

Mchaudhary
10-15-2013, 03:19 PM
The formulas show the relationship between various line items. And Clients need it.

Aflatoon
10-16-2013, 01:02 AM
How do the formulas (rather than the values they return) show a relationship? They would simply point to another cell.

Mchaudhary
10-16-2013, 01:34 AM
Let me explain...

1. please see the formula given on row no. 11,12,13,14 is

India - PSY = India - ASP/India - sales

2. Row Headers ( Total Wholesale, Total PSY etc.) indicate the use of SUM formula ....... Please see the word TOTAL

Column header Average indicate the use of AVERAGE formula..... Please see the word Average

3. Other formulas ( i.e. pointing to only cell values ) can be ignored. And values can be return.

thanks

Mchaudhary
10-16-2013, 03:43 AM
Does it possible to put formulas searching the words in my case ?

thanks

Aflatoon
10-16-2013, 05:18 AM
I don't know what you mean by that - can you give an example?

Aussiebear
10-16-2013, 03:12 PM
@Aflatoon I think the OP is suggesting that "words" means named ranges?

Aflatoon
10-16-2013, 03:20 PM
Could be, but if I'm going to embark on this (I know it has also been posted on a few other sites) I'd like to know for sure first. :)

Personally I imagine it would be as easy to just get the original report amended to produce the desired output.

Aussiebear
10-16-2013, 07:16 PM
Could be, but if I'm going to embark on this (I know it has also been posted on a few other sites) I'd like to know for sure first.

Really and no link provided here to date.......


Personally I imagine it would be as easy to just get the original report amended to produce the desired output.

I agree wholeheartedly.

Mchaudhary
10-17-2013, 02:43 AM
Exactly I was mean to search the named ranges to get the desired result.

Thanks

Mchaudhary
10-17-2013, 03:46 AM
HI,
I am encountering an error while posting the url links.

I had posted the thread first on this site, therefore links are not present here.

thanks,

Aussiebear
10-17-2013, 04:10 AM
I am encountering an error while posting the url links.

I believe that this would occur because your post count at the time was under the limit determined by the administrators.


I had posted the thread first on this site, therefore links are not present here.

However you have since posted this issue onto other forums, hence the need to indicate this to those members here who are trying to contribute to your cause.

Mchaudhary
10-17-2013, 04:54 AM
thank u

I hope to get a reply.

Regards,
Manoj

Aussiebear
10-17-2013, 09:41 AM
And I hope you indicate which other forums you have also raised this issue in.

Mchaudhary
10-17-2013, 10:45 AM
Mrexcel & The code cage.

but no response till now.

thanks,
Manoj

Aussiebear
10-18-2013, 04:43 AM
Yes, well that is understandable given that almost all forums have the same expectation. That is, that when a member cross posts the same issue to multiple forums, they show some respect to those people who offer to assist by indicating the other forums. Why should someone here try to assist when a solution or part thereof has already been provided elsewhere.

Mchaudhary
10-19-2013, 10:04 PM
any way I have solved my problem.

thank u

SamT
10-22-2013, 06:00 AM
Please read the FAQ link in my signature.