How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask The-PC-Guy Your Own Question

The-PC-Guy
The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1847
Experience:  20 years experience providing remote computer support
62934938
Type Your Microsoft Office Question Here...
The-PC-Guy is online now
A new question is answered every 9 seconds

I want to reference the same cell over multiple sheets. When

This answer was rated:

I want to reference the same cell over multiple sheets. When I place ='#1'!$V2, it not only holds the column, but also the row # XXXXX the sheet. Why??? Help!

The-PC-Guy :

Thanks for using JustAnswer. My name isXXXXX will do whatever I can to answer your question

The-PC-Guy :

do you want the row to change when you copy the formula or not?

The-PC-Guy :

are you here?

The-PC-Guy :

do you want the row to change when you copy the formula or not?

The-PC-Guy :

i wasn't sure from your initial question

Customer:

That would be ideal

The-PC-Guy :

so which is it

The-PC-Guy :

'#1'

The-PC-Guy :

is the sheet name

The-PC-Guy :

and you want to refer to cell V2? regardless of where the formula is

The-PC-Guy :

or do you just want the V column?

Customer:

I have data in another sheet organized by column (V2,V3,V4) that I would like to copy into a row on another sheet

Customer:

one minute....let me try to explain

Customer:

I have 97 sheets of data

Customer:

I'm trying to compile the column V from all of those 97 sheets into a summary table

Customer:

Column V can have up to 5 rows (V2,V3,V4,V5)

Customer:

V6

Customer:

And I'm trying to copy them into columns in the summary table

Customer:

So I'm trying to get row 2 in my summary to do the following:

Customer:

Row #1 ='#1'!$V2 (next cell) = '#1'!$V3 (next cell) '#1'!$V4

Customer:

Row #2 ='#2'!$V2 (next cell) = '#2'!$V3 (next cell) '#2'!$V4

Customer:

I've tried writing out each formula across the top for all 5 V cell references with $V$2, $V$3, etc and just filling down but the autocomplete messes it all up

Customer:

I land up with column progression to W in some rows if I fill sideways

Customer:

And the worksheet reference stays constant at '#1' if I fill down

Customer:

It doesn't make sense....wondering if this data set being built on an earlier version of MS office is causing problems because some cells fill properly and others do not

The-PC-Guy :

you may need a macro to do this

Customer:

I've never done one

The-PC-Guy :

i could do one for you

Customer:

please

The-PC-Guy :

will the cells be the same in each sheet

The-PC-Guy :

so it will always be V2

The-PC-Guy :

V3

The-PC-Guy :

ext..

Customer:

'yes

The-PC-Guy :

the easiest way to do this would be if you could send me your worksheet

The-PC-Guy :

do you need instructions for that

Customer:

sure

Customer:

email?

The-PC-Guy :

do you have a paperclip icon at the top of your chat window

The-PC-Guy :

where you type text?

The-PC-Guy :

sys image

The-PC-Guy :

but works on xls files

The-PC-Guy :

if you can get me the file it shouldn't take too long to do as I have written similar code for others

Customer:

did that work?

The-PC-Guy :

looks like it

The-PC-Guy :

so which sheet is the output supposed to go on?

Customer:

Trying to take the values in worksheets #1 through 94 in column V and place them in columns G-K in the Total WSL charts worksheet

Customer:

also want to transfer column W to columns L-P

The-PC-Guy :

ok i got the first part

The-PC-Guy :

colimn W from the # XXXXX

The-PC-Guy :

is blank

The-PC-Guy :

did you see that

The-PC-Guy :

i can do the first part no problem

The-PC-Guy :

but not sure what you mean colum @

The-PC-Guy :

column W

Customer:

ok I may not have sent the most recent version as I just added column W today

Customer:

one se

Customer:

sec

The-PC-Guy :

what version of excel are you using by the way?

Customer:

10

The-PC-Guy :

2010

The-PC-Guy :

?

The-PC-Guy :

wanna make sure macros will work

Customer:

yes

The-PC-Guy :

okay

The-PC-Guy :

if you send me the new file

The-PC-Guy :

i should be able to do what you need

Customer:

column W can be inserted really anywhere in the sheet...for I have different progression data in columns L-P in the summary sheet...column W can be inserted before or after

The-PC-Guy :

so V should go in G-K

The-PC-Guy :

W go in L-P

The-PC-Guy :

does that sound good to you

Customer:

let's put W in Q-U

Customer:

otherwise yes

The-PC-Guy :

ok

The-PC-Guy :

no problemmmmmmmmmm

The-PC-Guy :

give me a few minutes

The-PC-Guy :

to do it

Customer:

sounds good

Customer:

just a quick fyi...some additional sheets (i.e. #68) are now present in the workbook but not added to the Total WSL Charts sheet. I've never worked with Macros but hoping they autopopulate if I insert rows for those sheets after it is written?

The-PC-Guy :

it will autopopulate for however many sheets you have a # XXXXX

The-PC-Guy :

it doesn't care if you have 1 sheet

The-PC-Guy :

or 3 million sheets

Customer:

k

The-PC-Guy :

almost done

The-PC-Guy :

done:

Donwload the file from this link

http://ge.tt/1Easw001/v/0?c

Since this is a macro, you by default will get a security warning with a yellow bar

it will say something like "Macros have been disabled".

when you first start excel

you will need to click enable, or it wont work

you will notice a populate button that I added to the right of all the data.

click that to update the data

let me know if you have any questions, problems or concerns.

PLEASE REMEMBER TO RATE MY SERVICE SO I GET PAID FOR MY TIME

YOU DO SO WITH THE SMILEYS DIRECTLY BENEATH THIS CHAT

thanks

The-PC-Guy :

thanks

Customer:

sweet let me try it quick

Customer:

the link isn't working

The-PC-Guy :

u let me double check it

The-PC-Guy :

http://ge.tt/1Easw001/v/0?c

The-PC-Guy :

how is it not working?

The-PC-Guy :

what happens when you click it

Customer:

ok now it worked...the one above chrome said it could not find

The-PC-Guy :

LOL

The-PC-Guy :

gotto love chrome

Customer:

looks great...only thing is I just realized some of the sheets have a 6th row in column V

Customer:

can I just insert another column and drag/fill in the summary sheet?

The-PC-Guy :

well we can certainly modify for that, we just change the columns that the data goes too

The-PC-Guy :

so it would go from G to L then

The-PC-Guy :

instead of G to K

The-PC-Guy :

and R to V

Customer:

do I need to step in to the macro to do so?

The-PC-Guy :

ill fix it in about 30 seconds

The-PC-Guy :

do we need to add an extra value for W as well

Customer:

yes

Customer:

better question would have been....do you need to step into the macro?

Customer:

lol

Customer:

I actually think I see it now in the code but it's probably safer if I stay out of it

The-PC-Guy :

yes

The-PC-Guy :

hands off

The-PC-Guy :

here is the file with the changes

The-PC-Guy :

when you do rate, I would appreciate a bonus, for the extra work

The-PC-Guy :

thanks

Customer:

you're the man....thank you very much

The-PC-Guy :

i know

The-PC-Guy :

ur welecom

The-PC-Guy :

PLEASE RATE WITH THE SMILEYS BELOW THIS CHAT

Customer:

lmao....u saved a life today. haha

The-PC-Guy and 3 other Microsoft Office Specialists are ready to help you