I would really appreciate this Excel Help!

[Closed]
Report
-
Posts
138
Registration date
Thursday January 21, 2010
Status
Member
Last seen
May 8, 2019
-
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

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
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!
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
Posts
138
Registration date
Thursday January 21, 2010
Status
Member
Last seen
May 8, 2019
7
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......
Posts
138
Registration date
Thursday January 21, 2010
Status
Member
Last seen
May 8, 2019
7
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.....
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
you cannot have 5000 column :P
Posts
138
Registration date
Thursday January 21, 2010
Status
Member
Last seen
May 8, 2019
7
Increase Kar Sakte Hain Bhaijaan :p

Kahan Ho Aap :p