I would really appreciate this Excel Help!

Closed
ghannouge - Aug 1, 2010 at 06:27 AM
Game Start Now Posts 138 Registration date Thursday January 21, 2010 Status Member Last seen May 8, 2019 - Aug 2, 2010 at 09:37 AM
Hello,

I have
Sheet 1 & Sheet 2

Sheet 1 = contains a list of clients and the total of USD entered each day
Sheet 2 = contains the list of clients totaled USD for each day

My question is

='August 2010 '!C106

The above formula is from sheet 2 to take the information from sheet 1, my problem is, i want the formula to be as following

='August 2010 '!C106
='August 2010 '!D106
='August 2010 '!E106

but when i drag this formula, it gives me the following:

='August 2010 '!C106
='August 2010 '!C107
='August 2010 '!C108

PLEASE HELP! i have more than 5000 each day to change the total and it's killing me!

Im not that advanced in Excel.


3 replies

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Aug 1, 2010 at 07:15 AM
You need to anchor the row by using $

='August 2010 '!C$106
That would solve the issue of row changing. For column changing, it seems that you are dragging down. For that you would need to indirectly get the column
0
Thanks, but when i did that i got the following:

='August 2010'!C$213
='August 2010'!C$213
='August 2010'!C$213

It did not change , but stayed the same!
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Aug 2, 2010 at 08:50 AM
As I mentioned, that only fixed your row issue that as you drag down, row remains constants. For changing column as you drag down, you have to use formula. or rather combination formula

=indirect(ADDRESS(106, 3, ,,"'August 2010'"))

In this you have to come up with formula that would change "3" as you drag down

Since you have not given where the formula is, lets say this formula is going on row 3

In that case you can use
=indirect(ADDRESS(106, ROW(), ,,"'August 2010'"))

Lets say that is its not on row 3 but rather row 1 then
=indirect(ADDRESS(106, ROW() + 2, ,,"'August 2010'"))
As you can see, adding 2 to still come up with column 3


Again you cannot possibly having 5000 columns in your workbook, so your question is half complete. But still you can use the above solution
0
Game Start Now Posts 138 Registration date Thursday January 21, 2010 Status Member Last seen May 8, 2019 7
Aug 2, 2010 at 03:45 AM
Means You Are Dragging Down Right..........

If You Dragged Your Formula To The Right Side You Will Get Your Answer.. After Complete Your 5000 Dragging To The Right Side. You Should Copy Your Returned Data To The Down Word :p

Its Easy Dude......
0
Game Start Now Posts 138 Registration date Thursday January 21, 2010 Status Member Last seen May 8, 2019 7
Aug 2, 2010 at 03:58 AM
Dragged Your Formula To Right Side. You Will Get The Perfect Answer.....

After Complete Your Dragging To The Right Side..

Select Your Formula. Cut Them

Then Right Click & Press Paste Special...

Then There Are Two Check Box At Last.

Click On Transpose Button. Then Press Ok

Done.....
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Aug 2, 2010 at 08:41 AM
you cannot have 5000 column :P
0
Game Start Now Posts 138 Registration date Thursday January 21, 2010 Status Member Last seen May 8, 2019 7
Aug 2, 2010 at 09:37 AM
Increase Kar Sakte Hain Bhaijaan :p

Kahan Ho Aap :p
0