## Problem Copying Down An Indirect Formula |

## Problem Copying Down An Indirect Formula - Excel |
View Answers |

When I try to copy this formula down, it stays the same and won't reference the cells I need it to....any ideas??

=INDIRECT("PO_Order_Form!B5")

=INDIRECT("PO_Order_Form!B5")

## Subscribe for Weekly Excel Tips and Tricks

### Helpful tutorials delivered to your email!

## Similar Topics

Workbook A has a cell that gets data from workbook B as an external link.

It does this using the indirect function because it needs to concatenate the

path and filename from other cells.

Problem is that if workbook B is closed, the cell in workbook A shows #REF!.

So can indirect work using closed external files or must the external files

be opened?

Is there a clever way of making that work?

Thanks!

It does this using the indirect function because it needs to concatenate the

path and filename from other cells.

Problem is that if workbook B is closed, the cell in workbook A shows #REF!.

So can indirect work using closed external files or must the external files

be opened?

Is there a clever way of making that work?

Thanks!

I have a spreadsheet that when I copy the formula, it copies correctly

(changes the cells it should subtract), however, the result stays the same.

It matches the formula I copied it from, even though the cells to calculate

are now different. I even did a paste special and said only formula, but

still, same result. If I actually type in the formula, it works fine, but I

have a lot I need to copy and paste. Is there a way I can fix this?

Thanks,

(changes the cells it should subtract), however, the result stays the same.

It matches the formula I copied it from, even though the cells to calculate

are now different. I even did a paste special and said only formula, but

still, same result. If I actually type in the formula, it works fine, but I

have a lot I need to copy and paste. Is there a way I can fix this?

Thanks,

I have a co-worker's file that he is having trouble with. He is using Excel 2000 SP3. When copying a cell with a formula in it of "=D6+C6" and pasting it into the next cell down, it will display the same value in the cell as the calculated value from above, but has the correct formula displayed in the formula bar of "=D7+C7".

Example:

A1: 50

A2: 10

B1: 60

B2: 20

A3: Formula: =A1+A2 Displays: 60

Right click A3, Copy, right click B3, paste

A3 displays 60

When I click save, it will change the display value to 80.

I am trying this on his workstation and mine. Mine has Office 2010, so I think there might be an issue with the file itself.

Also, not just copy and paste. I can also just click the top cell after filling in the formula and then drag the bottom right of the cell downward and it will do the same of filling in the correct formula, but have the incorrect value.

I know that I could get him to just click save each time before really looking at the results, but that is just a band aid to the problem.

Any ideas how to fix this?

Example:

A1: 50

A2: 10

B1: 60

B2: 20

A3: Formula: =A1+A2 Displays: 60

Right click A3, Copy, right click B3, paste

A3 displays 60

When I click save, it will change the display value to 80.

I am trying this on his workstation and mine. Mine has Office 2010, so I think there might be an issue with the file itself.

Also, not just copy and paste. I can also just click the top cell after filling in the formula and then drag the bottom right of the cell downward and it will do the same of filling in the correct formula, but have the incorrect value.

I know that I could get him to just click save each time before really looking at the results, but that is just a band aid to the problem.

Any ideas how to fix this?

Hi,

How do I enter the value in a cell (say B4) into a formula as a constant so it doesn't change when I copy it down the column?

For example,

B4 = 2.4 (a constant)

This is the result I want:

F6 =C8 + B4*4.243

F7 =C9 + B4*4.243

F8 =C10 + B4*4.243

B4 remains constant as I copy F6 down.

What's the correct formula for F6 so I can copy it down and keep B4 fixed?

Thanks.

How do I enter the value in a cell (say B4) into a formula as a constant so it doesn't change when I copy it down the column?

For example,

B4 = 2.4 (a constant)

This is the result I want:

F6 =C8 + B4*4.243

F7 =C9 + B4*4.243

F8 =C10 + B4*4.243

B4 remains constant as I copy F6 down.

What's the correct formula for F6 so I can copy it down and keep B4 fixed?

Thanks.

Hello

I'm quite experienced Excel user. I've never come across this problem but tinkering in every conceivable way within Excel settings and the solution has eluded me.

I have added a worksheet created elsewhere (it is a form I need printing, with the data coming from 2 sheets I have created from scratch) which has pre formatted cells for Date and Client Name etc.

When I try to reference the cell in this added sheet from my 2 sheets, instead of the result, it always displays the formula, not the result.

I have tried doing it from one of my sheets to reference to this new, and that displays the result and not formula. I can't imagine why it's doing this and I've never seen it happen before.

Formatting cells, giving cells names rather than the usually adequate of reference to the Cell Number doesn't change things. I wonder if I've picked up some legacy protection from the original form but can't see anywhere in the tools etc that's obvious.

There's about 50+ cells that need referencing and I got to get this done for work.

Please help me. This is my first need to post on a Excel Forum as I've always found help or answers from other peeps or internet but this one is making me scratch my head big time.

I'm quite experienced Excel user. I've never come across this problem but tinkering in every conceivable way within Excel settings and the solution has eluded me.

I have added a worksheet created elsewhere (it is a form I need printing, with the data coming from 2 sheets I have created from scratch) which has pre formatted cells for Date and Client Name etc.

When I try to reference the cell in this added sheet from my 2 sheets, instead of the result, it always displays the formula, not the result.

I have tried doing it from one of my sheets to reference to this new, and that displays the result and not formula. I can't imagine why it's doing this and I've never seen it happen before.

Formatting cells, giving cells names rather than the usually adequate of reference to the Cell Number doesn't change things. I wonder if I've picked up some legacy protection from the original form but can't see anywhere in the tools etc that's obvious.

There's about 50+ cells that need referencing and I got to get this done for work.

Please help me. This is my first need to post on a Excel Forum as I've always found help or answers from other peeps or internet but this one is making me scratch my head big time.

Today I ran into an odd problem. I typed in values for column A rows 1 through 10 then values for column B rows 1 through 10. then in column C, I made the formula C1=A1/B1. The math was correct it showed 542 in the C1 cell. So I dragged that formula down and it showed 542 in all column C cells which is not correct. And when I went to check to see if the formula was correctly dragged it was. For instance, the formula in C2 is =A2/B2 however the value of that cell showed 542 which was not the correct math/value.

But it gets even more weird. When I click on the Column C cells and then it shows the formula up top in the formula bar and if I put my cursor anywhere in the formula bar and hit Return the formula does not change however the correct value then appears in the Column C cell. It is like the act of putting the cursor in the formula activates it to work properly but until it is activated that C cell shows the value of the cell which it was dragged down from.

This is quite bizarre. Has anyone ever seen this before? I have no idea what is going on. I ran a scan for viruses and none were found. I tried it on several new/different spreadsheets but it keeps happening.

Thanks for any tips on this.

But it gets even more weird. When I click on the Column C cells and then it shows the formula up top in the formula bar and if I put my cursor anywhere in the formula bar and hit Return the formula does not change however the correct value then appears in the Column C cell. It is like the act of putting the cursor in the formula activates it to work properly but until it is activated that C cell shows the value of the cell which it was dragged down from.

This is quite bizarre. Has anyone ever seen this before? I have no idea what is going on. I ran a scan for viruses and none were found. I tried it on several new/different spreadsheets but it keeps happening.

Thanks for any tips on this.

Hello -

I am using Excel 2007. In prior versions of excel, when copying data, if I wanted to copy visible cells only, I would select "Go To, Special, Visible Cells Only" and then copy a range a cells. The default was always set to copy everything (including hidden cells), unless I specifically selected copy visible cells only.

In 2007, the default is somehow set to always copy only visible cells. Sometimes, I want to be able to copy all cells including those hidden but cannot seem to figure out how to swith this default option. I looked under Excel Options and did not see an option there.

I don't want to have to unhide and rehide everything each time i copy. I know i could do the hiding and unhiding via VBA but would prefer not to have to.

Thanks for your suggestions.

I am using Excel 2007. In prior versions of excel, when copying data, if I wanted to copy visible cells only, I would select "Go To, Special, Visible Cells Only" and then copy a range a cells. The default was always set to copy everything (including hidden cells), unless I specifically selected copy visible cells only.

In 2007, the default is somehow set to always copy only visible cells. Sometimes, I want to be able to copy all cells including those hidden but cannot seem to figure out how to swith this default option. I looked under Excel Options and did not see an option there.

I don't want to have to unhide and rehide everything each time i copy. I know i could do the hiding and unhiding via VBA but would prefer not to have to.

Thanks for your suggestions.

Hi there,

I'm very new to Excel, and I'm having trouble figuring a few things out. Hopefully this will be very easy for you guys!

In Sheet 1, I have a column of cells that I would like to also appear in Sheet 2. If I add a new row to the column in Sheet 1, I would also like it to be updated automatically in Sheet 2. Currently, I can get it to show the contents of individual cells from Sheet 1 in Sheet 2 by using this formula in the formula bar for each cell in Sheet 2:

=Sheet1!A3 (or whichever cell it is)

That's fine, but I'd like to just have a formula that will reproduce the entire column (ie. without a fixed range, as new rows are going to be added to the column).

If anyone could point me in the right direction, I'd be very very grateful. Thanks.

I'm very new to Excel, and I'm having trouble figuring a few things out. Hopefully this will be very easy for you guys!

In Sheet 1, I have a column of cells that I would like to also appear in Sheet 2. If I add a new row to the column in Sheet 1, I would also like it to be updated automatically in Sheet 2. Currently, I can get it to show the contents of individual cells from Sheet 1 in Sheet 2 by using this formula in the formula bar for each cell in Sheet 2:

=Sheet1!A3 (or whichever cell it is)

That's fine, but I'd like to just have a formula that will reproduce the entire column (ie. without a fixed range, as new rows are going to be added to the column).

If anyone could point me in the right direction, I'd be very very grateful. Thanks.

In excel, I tried to convert numbers to number format, using

format/cells/number from the category list. But it won't take, and stays

text-like. Any ideas?

format/cells/number from the category list. But it won't take, and stays

text-like. Any ideas?

I am trying to do a very simple copy and paste of a simple formula in Excel 2007 and nothing seems to be working. Excel will not let me copy and paste a formula and will only paste the value into to workbook. For instance, if I make a very simple spreadsheet such as:

A1 type in 10, A2 type in =A1 (calculated A2 to be 10)

B1 type in 5

And then click the copy on B1, and then click paste special on cell B2, the only options it gives me are text and unicode text and so no matter how I paste, cell b2 will always be populated with a value of 5. I cannot imagine a simpler copy and paste and no matter what I do I can't make this work.

All cells are formatted as general. It seems that all copy and pasting of formulas in my excel has been disabled. If I open any spreadsheet on my computer, I can't copy and paste formulas, but do the exact same thing on the exact same spreadsheet on any other computer and it works no problem.

Any help?

A1 type in 10, A2 type in =A1 (calculated A2 to be 10)

B1 type in 5

And then click the copy on B1, and then click paste special on cell B2, the only options it gives me are text and unicode text and so no matter how I paste, cell b2 will always be populated with a value of 5. I cannot imagine a simpler copy and paste and no matter what I do I can't make this work.

All cells are formatted as general. It seems that all copy and pasting of formulas in my excel has been disabled. If I open any spreadsheet on my computer, I can't copy and paste formulas, but do the exact same thing on the exact same spreadsheet on any other computer and it works no problem.

Any help?

I would like to copy a small table from Word into one cell in an Excel

worksheet. The first column of the table is a list of numbers. I tried

converting the table into text with manual line breaks and tab stops to

divide columns and rows, but that didn't solve my problem.

Excel pastes the data into several rows. When I try to merge them, I

get a warning that the selection contains multiple data values, and

merging into one cell keeps the upper-left most data only.

What I tried that didn't work:

* Formatting the Excel cells as text before pasting the data.

* The various options for "Paste Special." The closest I got was

inserting the table as a Document Object, which could be a workaround,

I guess.

What I am saving for when all else fails:

* The obvious solution of copying row by row into one Excel cell.

The data in the table is information about my dad's medications. I

would like to have reference charts of how to identify the strength of

each tablet by its color and markings. I got the info from the

manufacturers' websites and entered it into tables in Word, which I

would like to copy into a more comprehensive file I am creating in

Excel. The first column of each table is the strength of the tablet,

entered as 1 mg., 2 mg., etc. The subsequent columns describe the

shape, color, and markings. There are 3 tables, each with about 4-5

rows.

Is there a way to copy each one - whether as a table or as text - into

a single Excel cell without losing data?

Many thanks.

worksheet. The first column of the table is a list of numbers. I tried

converting the table into text with manual line breaks and tab stops to

divide columns and rows, but that didn't solve my problem.

Excel pastes the data into several rows. When I try to merge them, I

get a warning that the selection contains multiple data values, and

merging into one cell keeps the upper-left most data only.

What I tried that didn't work:

* Formatting the Excel cells as text before pasting the data.

* The various options for "Paste Special." The closest I got was

inserting the table as a Document Object, which could be a workaround,

I guess.

What I am saving for when all else fails:

* The obvious solution of copying row by row into one Excel cell.

The data in the table is information about my dad's medications. I

would like to have reference charts of how to identify the strength of

each tablet by its color and markings. I got the info from the

manufacturers' websites and entered it into tables in Word, which I

would like to copy into a more comprehensive file I am creating in

Excel. The first column of each table is the strength of the tablet,

entered as 1 mg., 2 mg., etc. The subsequent columns describe the

shape, color, and markings. There are 3 tables, each with about 4-5

rows.

Is there a way to copy each one - whether as a table or as text - into

a single Excel cell without losing data?

Many thanks.

Hi,

I use filters a lot at my work. One thing I've noticed is that if I have something filtered and want to copy and paste only what I see from one column to another column right next to it, it will copy what is filtered, but then paste into the unfiltered portion.

Is there some way to copy only what I see when it's filtered, THEN paste only onto the adjancent cells which is also filtered?

For example let's say i have each of the number below in a cell (Fig 1). And then I hide rows 2 -3, so only 1 and 4 is showingI only see 1 & 4 in column A. I want to copy and paste 1 and 4 to the next column over one row so it looks like Fig. 2 when unhidden. Not like Fig. 3.

Fig. 1

1

2

3

4

Fig. 2

1 1

2

3

4 4

Fig. 3

1 1

2 4

3

4

I use filters a lot at my work. One thing I've noticed is that if I have something filtered and want to copy and paste only what I see from one column to another column right next to it, it will copy what is filtered, but then paste into the unfiltered portion.

Is there some way to copy only what I see when it's filtered, THEN paste only onto the adjancent cells which is also filtered?

For example let's say i have each of the number below in a cell (Fig 1). And then I hide rows 2 -3, so only 1 and 4 is showingI only see 1 & 4 in column A. I want to copy and paste 1 and 4 to the next column over one row so it looks like Fig. 2 when unhidden. Not like Fig. 3.

Fig. 1

1

2

3

4

Fig. 2

1 1

2

3

4 4

Fig. 3

1 1

2 4

3

4

Is there a trick to copy-paste a group of cells into Outlook? I don't want to paste as a picture (shows up as an attachment and is lost when someone else Replies), but formatting is skewed when pasted as an Excel object. Right now I'm recreating the table in Word, then pasting, which doesn't loose formatting.

A specific problem is cells which don't have borders show up with very light grey borders in Outlook. This, I do not want.

TiA

A specific problem is cells which don't have borders show up with very light grey borders in Outlook. This, I do not want.

TiA

Hey all,

How do you turn off the blinking border around a cell that you are copying while still having it as an active cell? I don't want to have to press "esc" after everytime I copy an item.

Thanks,

Jeremy

How do you turn off the blinking border around a cell that you are copying while still having it as an active cell? I don't want to have to press "esc" after everytime I copy an item.

Thanks,

Jeremy

I have an excel worksheet that adds two other worksheets in a data

triangle. I copied it to create a new data set and used find &

replace to change the worksheet references to the new ones.

The cells still contain the result of the old formula referring to the

previous worksheets. The only way I can get the formula to return the

correct result is to edit (F2) each cell and press enter. Calc now

(F9) does nothing.

I've seen this before, but this time, I need to calculate many

thousands of cells and don't have time for this workaround.

Any ideas?

Thanks.

Don S

triangle. I copied it to create a new data set and used find &

replace to change the worksheet references to the new ones.

The cells still contain the result of the old formula referring to the

previous worksheets. The only way I can get the formula to return the

correct result is to edit (F2) each cell and press enter. Calc now

(F9) does nothing.

I've seen this before, but this time, I need to calculate many

thousands of cells and don't have time for this workaround.

Any ideas?

Thanks.

Don S

I have a formula in cell C1 (=A1/B1). I want to copy this formula down to row C20, however rows A2 to A20 are empty so C2 to C20 show (#DIVO!). Is there something I can put in the formula to make column C be blank until the data is enter into Columns A & B?

Hello All

I wonder if you could help with this one, I have a list of data like this (no spaces or other characters)

JN551122B

What I need to do is split each of the characters in to its own cell

if JN551122B was in cell A1 I'd want to return:

B2 C2 D2 E2 F2 G2 H2 I2 J2

J N 5 5 1 1 2 2 B

The nearest I have got is the right function, but it brings over the preceeding characters, and I need each character in its own cell. Hopefully I could copy/paste a formula and change the reference for each cell value, i.e. 1 would return J, 2 = N, 3 = 5, 4 = 5 etc.

Thanks for your help!

I wonder if you could help with this one, I have a list of data like this (no spaces or other characters)

JN551122B

What I need to do is split each of the characters in to its own cell

if JN551122B was in cell A1 I'd want to return:

B2 C2 D2 E2 F2 G2 H2 I2 J2

J N 5 5 1 1 2 2 B

The nearest I have got is the right function, but it brings over the preceeding characters, and I need each character in its own cell. Hopefully I could copy/paste a formula and change the reference for each cell value, i.e. 1 would return J, 2 = N, 3 = 5, 4 = 5 etc.

Thanks for your help!

Hello from sunny Madrid

I have created a complex formula to help me calculating Golf handicaps for players.

The formula is in the first cell and I want to drag the formula across to the right to cover the 18 holes.

But ALL the values change and I want some to change and some to stay constant.

How do I tell excel to drag the formula across but keep certain parts of the formula the same..ie ....=D11.... in the formula when dragged across doesn't go D12,D13;D14 in each cells formula and should stay as D11 for all.

Many thanks for your help

Ole

I have created a complex formula to help me calculating Golf handicaps for players.

The formula is in the first cell and I want to drag the formula across to the right to cover the 18 holes.

But ALL the values change and I want some to change and some to stay constant.

How do I tell excel to drag the formula across but keep certain parts of the formula the same..ie ....=D11.... in the formula when dragged across doesn't go D12,D13;D14 in each cells formula and should stay as D11 for all.

Many thanks for your help

Ole

I get the error message "reference is not valid" each time I open my spreadsheet. I get this message three times, and once I am done clicking ok on all three of them, my spreadsheet works just fine. The problem is, I have to send it to a bunch people.

I have a sheet with raw data a sheet with pivot tables a sheet with a dashboard and a simple macro

I don't have any #REF cells either.

Someone has any idea of what could be the problem?

thanks

NA

I have a sheet with raw data a sheet with pivot tables a sheet with a dashboard and a simple macro

I don't have any #REF cells either.

Someone has any idea of what could be the problem?

thanks

NA

Ran into a problem I've never seen before. When dragging a cell across that contains a sum equation the result was the following cells with the same value. Inside the cell it showed what would be the correct new equation, but the value was still from the original cell. Any ideas on what's causing this?

If I double click the cell and then hit enter it will re-total the area and then give me the correct value, but I'd obviously rather is just work right the first time.

If I double click the cell and then hit enter it will re-total the area and then give me the correct value, but I'd obviously rather is just work right the first time.

Hi everyone,

I'm going nuts trying to figure out how to autofill text from one cell

to another. For example, everytime I type text into cell A20, I want

the exact same thing to cell BL20. The same for B20, BL20, etc.

How do I go about that? I already know how to copy formulas from one

cell to another by dragging the skinny black line of the cell. I guess

I just need the formula for a simple IDENTICAL copying of text.

Thanks in advance,

Mike

I'm going nuts trying to figure out how to autofill text from one cell

to another. For example, everytime I type text into cell A20, I want

the exact same thing to cell BL20. The same for B20, BL20, etc.

How do I go about that? I already know how to copy formulas from one

cell to another by dragging the skinny black line of the cell. I guess

I just need the formula for a simple IDENTICAL copying of text.

Thanks in advance,

Mike

I have sheet with rows and each row has formulas based on cells in that row.

I would like to have a new row automatically inserted with the formulas

updating to reference the cells in the new row. Let's say I start with 50

rows and after they are filled up, a new row is added automatically? How do I

do this?

I would like to have a new row automatically inserted with the formulas

updating to reference the cells in the new row. Let's say I start with 50

rows and after they are filled up, a new row is added automatically? How do I

do this?

Hi Guys

I have the following in G2:

=VLOOKUP(A2,Sheet1!A:D,4,FALSE)

The formula returns the correct result, which in this case is a number - 2

When I fill down my range, the formula copies correctly, but every result is the same. 2.

However, if I go to the next cell down (G3) and activate it by pressing F2, and then hit Enter, the result changes and is now correct.

Any ideas whats going wrong?

I have the following in G2:

=VLOOKUP(A2,Sheet1!A:D,4,FALSE)

The formula returns the correct result, which in this case is a number - 2

When I fill down my range, the formula copies correctly, but every result is the same. 2.

However, if I go to the next cell down (G3) and activate it by pressing F2, and then hit Enter, the result changes and is now correct.

Any ideas whats going wrong?

This is my first post in these forums.

I am trying to create a formula which will look at the data in columns A - I (50 rows) and copy this data to columns K - S but ignoring any blank cells. Thereby consolidating the data in the upper rows with no spaces.

I am sure there is a relativley simple answer but it is driving me mad!

Hope you "Guru's" can help

Thanks

Added example spreadsheet to aid assistance.

I am trying to create a formula which will look at the data in columns A - I (50 rows) and copy this data to columns K - S but ignoring any blank cells. Thereby consolidating the data in the upper rows with no spaces.

I am sure there is a relativley simple answer but it is driving me mad!

Hope you "Guru's" can help

Thanks

Added example spreadsheet to aid assistance.

is it possible to put two formulasinto one cell?

On the attached timesheet there are columns IN, OUT, IN,OUT

The lunch time is worked out by deducting 'C8 from C9' and this works fine when all 'IN, OUT' cells are filled in but for some reason when just the first two cells are filled in it gives a -12 hour answer.

looking at cell 'C11 ' in the attached worksheet, I currently have the formula

=IF(C13=0,","SUM(C9-C8)) The reason for this is to make it look tidy by having blank cells until a calculation is needed,, My problem is, If someone just comes in for the morning one day for instance 8am to 12 noon then they won't have lunch, but C11 will show -12:00

Can I put another formula into C11 to tell it not to deduct C8 from C9 until a time is put into C9.

The following is the sort of thing (many variations) I have been trying but because I'm hopeless at formulas I'm not having any success

Thanks

=IF(C13=0,","SUM(C9-C8))OR,IF(C9,ISBLANK,"",SUM(C9-C8)

On the attached timesheet there are columns IN, OUT, IN,OUT

The lunch time is worked out by deducting 'C8 from C9' and this works fine when all 'IN, OUT' cells are filled in but for some reason when just the first two cells are filled in it gives a -12 hour answer.

looking at cell 'C11 ' in the attached worksheet, I currently have the formula

=IF(C13=0,","SUM(C9-C8)) The reason for this is to make it look tidy by having blank cells until a calculation is needed,, My problem is, If someone just comes in for the morning one day for instance 8am to 12 noon then they won't have lunch, but C11 will show -12:00

Can I put another formula into C11 to tell it not to deduct C8 from C9 until a time is put into C9.

The following is the sort of thing (many variations) I have been trying but because I'm hopeless at formulas I'm not having any success

Thanks

=IF(C13=0,","SUM(C9-C8))OR,IF(C9,ISBLANK,"",SUM(C9-C8)